ERROR: Altering of Table, could not replace the table name with the temporary one

GoogleCodeExporter commented 9 years ago

What steps will reproduce the problem?
1. select a table
2.Click on Structure, select a column e.g. "colorname"
3.Click on Edit, change from "colorname" to "colorinput"

What is the expected output? What do you see instead?
Column name should be renamed,

I see the error:
ERROR: Altering of Table mytable failed - could not replace the table name with 
the temporary one.
This may be a bug that needs to be reported at 
http://code.google.com/p/phpliteadmin/issues/list

What version of the product are you using? On what operating system? Which
Database Extension (PDO/SQLiteDatabase/SQLiteDatabase3 - see Database
structure-tab in phpLiteAdmin)?
phpliteadmin 1.9.4.1, on Windows 7 with PHP 5.3.1

Please provide any additional information below.

Original issue reported on code.google.com by Thomas.S...@googlemail.com on 20 Nov 2013 at 9:43

GoogleCodeExporter commented 9 years ago

Thanks for your report. To find and fix the problem I need some more 
information. Could you please enable debug output (set $debug=true in your 
configuration), retry what you did and post here all the debug output that 
occurs?
Thanks in advance.

(For the record: This is another concrete example of famous issue #12 ...)

Original comment by crazy4ch...@gmail.com on 20 Nov 2013 at 9:53

GoogleCodeExporter commented 9 years ago

Hello,
here is the debug output
----------------------------------------------------------------------
ALTER TABLE QUERY=(ALTER TABLE "mytable" CHANGE "Test" 'TestXX' TEXT), 
tablename=(mytable), alterdefs=(CHANGE "Test" 'TestXX' TEXT)ALTER TABLE: 
table=(mytable), alterdefs=(CHANGE "Test" 'TestXX' TEXT)SQL?
SQL?
origsql=(CREATE TABLE `mytable` ( installelementid INTEGER PRIMARY KEY 
AUTOINCREMENT , tabsjobtkid INT, textid INT, controldef TEXT, printertypeid 
INT, openui TEXT, CheckMy TEXT, HelpItem TEXT, Test TEXT ))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'mytable'|mytable|"is_myta
ble"|`mytable`|\[mytable\])\s*+(\(.*+)$/is)ERROR: could not get rid of CREATE 
TABLE
----------------------------------------------------------------------

I am not the SQLite expert, but I think there is no ALTER TABLE ... CHANGE 
command for rename a column?

Original comment by Thomas.S...@googlemail.com on 20 Nov 2013 at 10:07

GoogleCodeExporter commented 9 years ago

Thanks for the debug output.
Yes, you are correct that SQLite does not support ALTER TABLE ... CHANGE. But 
phpLiteAdmin does by using a workaround (building an altered copy of the table 
and replacing the old table with the copy). And as this is rather tricky, there 
are still cases in which it fails such as yours.

I will have a look at this and inform you if I have a patch to test or if I 
need additional information.

Original comment by crazy4ch...@gmail.com on 20 Nov 2013 at 10:13

GoogleCodeExporter commented 9 years ago

Hmm. Strange. I tried to reproduce this with your create table statement but 
could not.
Could you please provide me with some information from your php configuration? 
To get it, create a php file with the following content and upload it to your 
server:
<?php phpinfo(); ?>
I am only interested in what it says in the "PCRE" section. So something like 
this:

pcre
PCRE (Perl Compatible Regular Expressions) Support  enabled
PCRE Library Version    8.02 2010-03-19

Directive   Local Value Master Value
pcre.backtrack_limit    100000  100000
pcre.recursion_limit    100000  100000

Also to be on the safe side that it is a problem specific to the server rather 
than to the database: Can you upload the db-file of a db where altering a table 
fails for you?

Original comment by crazy4ch...@gmail.com on 20 Nov 2013 at 10:26

GoogleCodeExporter commented 9 years ago

I am a bit confused about this line of your debug output:
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'mytable'|mytable|"is_myta
ble"|`mytable`|\[mytable\])\s*+(\(.*+)$/is)
Where does "is_mytable" come from here? It normally should look like this:
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'mytable'|mytable|"mytable
"|`mytable`|\[mytable\])\s*+(\(.*+)$/is)

Not that it matters in your case as your CREATE TABLE statement uses the 
backtick notation (`mytable`). But if the debug output is somehow different 
from what the official phpLiteAdmin 1.9.4.1 release produces, this looks like 
you are using a modified version of phpLiteAdmin?

Original comment by crazy4ch...@gmail.com on 20 Nov 2013 at 11:02

GoogleCodeExporter commented 9 years ago

Hello
sorry if I have mixed something.. 

I now have made some new tests and debug output. Sometimes the rename works 
fine but sometimes not. For example here I tried to rename the column 
"ldapgroup" to "ldapgroupX". It shows me "..has been successfully altered" but 
if I go back and refresh, the column is still "ldapgroup"
---------------------------------------------------------
ALTER TABLE QUERY=(ALTER TABLE "is_ldapgroup" CHANGE "ldapgroup" 'ldapgroupX' 
INTEGER), tablename=(is_ldapgroup), alterdefs=(CHANGE "ldapgroup" 'ldapgroupX' 
INTEGER)ALTER TABLE: table=(is_ldapgroup), alterdefs=(CHANGE "ldapgroup" 
'ldapgroupX' INTEGER)SQL?
SQL?
origsql=(CREATE TABLE "is_ldapgroup" ( "ldapgroupid" INTEGER PRIMARY KEY 
AUTOINCREMENT, "ldapgroup" varchar(250) NOT NULL ))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'is_ldapgroup'|is_ldapgrou
p|"is_ldapgroup"|`is_ldapgroup`|\[is_ldapgroup\])\s*+(\(.*+)$/is)createtemptable
SQL=(CREATE TEMPORARY TABLE 't1384959057' ( "ldapgroupid" INTEGER PRIMARY KEY 
AUTOINCREMENT, "ldapgroup" varchar(250) NOT NULL ))SQL?
def=CHANGE "ldapgroup" 'ldapgroupX' INTEGERaction=(change), column=(ldapgroup), 
column_escaped=(ldapgroup)preg_column_definition=(\s*+(?:'(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++)preg_columns_b
efore=((?:((?:\s*+(?:'(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'
(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?)preg_c
olumn_to_change=(\s*(?:'ldapgroup'|ldapgroup|"ldapgroup"|`ldapgroup`|\[ldapgroup
\])(?:\s+varchar\(250\))?(\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"`\[]*|"(?:[^"]*+|"")*+
"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))+)?)CREATE TEMPORARY TABLE 
't1384959057' ( "ldapgroupid" INTEGER PRIMARY KEY AUTOINCREMENT, "ldapgroup" 
varchar(250) NOT NULL )CREATE TEMPORARY TABLE 't1384959057' ( "ldapgroupid" 
INTEGER PRIMARY KEY AUTOINCREMENT,'ldapgroupX' INTEGER NOT NULL 
)/^\s*+(CREATE\s++TEMPORARY\s++TABLE\s++'t1384959057'\s*+\()(?:((?:\s*+(?:'(?:[^
']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'
(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?\s*(?:'
ldapgroup'|ldapgroup|"ldapgroup"|`ldapgroup`|\[ldapgroup\])(?:\s+varchar\(250\))
?(\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"`\[]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?
:[^\]]*+|\]\])*+\]))+)?(,\s*(.+))?\s*\)\s*$/srecreate=(CREATE INDEX 
"is_ldapgroup_idx_ldapgroup_ldapgroup" ON "is_ldapgroup" ("ldapgroup");)BEGIN; 
CREATE TEMPORARY TABLE 't1384959057' ( "ldapgroupid" INTEGER PRIMARY KEY 
AUTOINCREMENT, "ldapgroup" varchar(250) NOT NULL ); INSERT INTO 
"t1384959057"("ldapgroupid", "ldapgroup") SELECT "ldapgroupid", "ldapgroup" 
FROM "is_ldapgroup"; DROP TABLE "is_ldapgroup"; CREATE TABLE 'is_ldapgroup' ( 
"ldapgroupid" INTEGER PRIMARY KEY AUTOINCREMENT,'ldapgroupX' INTEGER NOT NULL 
); INSERT INTO "is_ldapgroup"("ldapgroupid", "ldapgroupX") SELECT 
"ldapgroupid", "ldapgroup" FROM "t1384959057"; DROP TABLE "t1384959057"; CREATE 
INDEX "is_ldapgroup_idx_ldapgroup_ldapgroup" ON "is_ldapgroup" ("ldapgroup"); 
COMMIT;
--------------------------------------------------------

But here, in table is_language I tried to rename column "confignameX" to 
"configname" and it works fine.

------------------------------------------------------------------
ALTER TABLE QUERY=(ALTER TABLE "is_language" CHANGE "confignameX" 'configname' 
INTEGER), tablename=(is_language), alterdefs=(CHANGE "confignameX" 'configname' 
INTEGER)ALTER TABLE: table=(is_language), alterdefs=(CHANGE "confignameX" 
'configname' INTEGER)SQL?
SQL?
origsql=(CREATE TABLE 'is_language' ( "languageid" INTEGER PRIMARY KEY 
AUTOINCREMENT, "languagedescription" varchar(255) NOT NULL, "textid" int(10) 
NOT NULL, "countrycode" varchar(10) NOT NULL,'confignameX' INTEGER NOT NULL ))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'is_language'|is_language|
"is_language"|`is_language`|\[is_language\])\s*+(\(.*+)$/is)createtemptableSQL=(
CREATE TEMPORARY TABLE 't1384959362' ( "languageid" INTEGER PRIMARY KEY 
AUTOINCREMENT, "languagedescription" varchar(255) NOT NULL, "textid" int(10) 
NOT NULL, "countrycode" varchar(10) NOT NULL,'confignameX' INTEGER NOT NULL 
))SQL?
def=CHANGE "confignameX" 'configname' INTEGERaction=(change), 
column=(confignameX), 
column_escaped=(confignameX)preg_column_definition=(\s*+(?:'(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++)preg_columns_b
efore=((?:((?:\s*+(?:'(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'
(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?)preg_c
olumn_to_change=(\s*(?:'confignameX'|confignameX|"confignameX"|`confignameX`|\[c
onfignameX\])(?:\s+INTEGER)?(\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"`\[]*|"(?:[^"]*+|""
)*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))+)?)CREATE TEMPORARY TABLE 
't1384959362' ( "languageid" INTEGER PRIMARY KEY AUTOINCREMENT, 
"languagedescription" varchar(255) NOT NULL, "textid" int(10) NOT NULL, 
"countrycode" varchar(10) NOT NULL,'confignameX' INTEGER NOT NULL )CREATE 
TEMPORARY TABLE 't1384959362' ( "languageid" INTEGER PRIMARY KEY AUTOINCREMENT, 
"languagedescription" varchar(255) NOT NULL, "textid" int(10) NOT NULL, 
"countrycode" varchar(10) NOT NULL,'configname' INTEGER NOT NULL 
)/^\s*+(CREATE\s++TEMPORARY\s++TABLE\s++'t1384959362'\s*+\()(?:((?:\s*+(?:'(?:[^
']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'
(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?\s*(?:'
confignameX'|confignameX|"confignameX"|`confignameX`|\[confignameX\])(?:\s+INTEG
ER)?(\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"`\[]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\
[(?:[^\]]*+|\]\])*+\]))+)?(,\s*(.+))?\s*\)\s*$/srecreate=(CREATE INDEX 
"is_language_CountryCode" ON "is_language" ("countrycode");)recreate=(CREATE 
INDEX "is_language_LanguageDescription" ON "is_language" 
("languagedescription");)BEGIN; CREATE TEMPORARY TABLE 't1384959362' ( 
"languageid" INTEGER PRIMARY KEY AUTOINCREMENT, "languagedescription" 
varchar(255) NOT NULL, "textid" int(10) NOT NULL, "countrycode" varchar(10) NOT 
NULL,'confignameX' INTEGER NOT NULL ); INSERT INTO "t1384959362"("languageid", 
"languagedescription", "textid", "countrycode", "confignameX") SELECT 
"languageid", "languagedescription", "textid", "countrycode", "confignameX" 
FROM "is_language"; DROP TABLE "is_language"; CREATE TABLE 'is_language' ( 
"languageid" INTEGER PRIMARY KEY AUTOINCREMENT, "languagedescription" 
varchar(255) NOT NULL, "textid" int(10) NOT NULL, "countrycode" varchar(10) NOT 
NULL,'configname' INTEGER NOT NULL ); INSERT INTO "is_language"("languageid", 
"languagedescription", "textid", "countrycode", "configname") SELECT 
"languageid", "languagedescription", "textid", "countrycode", "confignameX" 
FROM "t1384959362"; DROP TABLE "t1384959362"; CREATE INDEX 
"is_language_CountryCode" ON "is_language" ("countrycode"); CREATE INDEX 
"is_language_LanguageDescription" ON "is_language" ("languagedescription"); 
COMMIT;
------------------------------------------------------------------- 

But here in table is_ldap I tried to rename column "ldap_sslX" to "ldap_ssl" 
and it throws the error.
--------------------------------------------------------------------------
ALTER TABLE QUERY=(ALTER TABLE "is_ldap" CHANGE "ldap_sslX" 'ldap_ssl' 
INTEGER), tablename=(is_ldap), alterdefs=(CHANGE "ldap_sslX" 'ldap_ssl' 
INTEGER)ALTER TABLE: table=(is_ldap), alterdefs=(CHANGE "ldap_sslX" 'ldap_ssl' 
INTEGER)SQL?
SQL?
origsql=(CREATE TABLE [is_ldap] ( [ldapid] INTEGER PRIMARY KEY AUTOINCREMENT, 
[ldap_server] varchar(100) NOT NULL DEFAULT ('127.0.0.1'), [ldap_port] 
varchar(10) NOT NULL DEFAULT ('389'), [ldap_dn] varchar(250) NOT NULL DEFAULT 
('DC=testserver,DC=danka,DC=de'), [ldap_atdn] varchar(250) NOT NULL DEFAULT 
('testserver.danka.de'), [ldap_active] int(10) NOT NULL DEFAULT ('0'), 
[ldap_addressfieldsactive] int(10) NOT NULL DEFAULT ('1'), [ldap_addressfield1] 
varchar(255) NOT NULL DEFAULT ('displayname'), [ldap_addressfield2] 
varchar(255) NOT NULL DEFAULT ('mail'), [ldap_addressfield3] varchar(255) NOT 
NULL, [ldap_addressfield4] varchar(255) NOT NULL DEFAULT ('department'), 
[ldap_addressfield5] varchar(255) NOT NULL DEFAULT ('telephonenumber'), 
[ldap_addressfield6] varchar(255) NOT NULL DEFAULT 
('physicaldeliveryofficename'), [ldap_addressfield7] varchar(255) NOT NULL 
DEFAULT ('l'), [ldap_adminname] varchar(250) NOT NULL, [ldap_adminpwd] 
varchar(250) NOT NULL, [ldap_autoupdate] int(10) DEFAULT ('0'), [ldap_location] 
varchar(200) DEFAULT NULL, [ldap_sslX] int(10) DEFAULT 0))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'is_ldap'|is_ldap|"is_ldap
"|`is_ldap`|\[is_ldap\])\s*+(\(.*+)$/is)createtemptableSQL=(CREATE TEMPORARY 
TABLE 't1384960332' ( [ldapid] INTEGER PRIMARY KEY AUTOINCREMENT, [ldap_server] 
varchar(100) NOT NULL DEFAULT ('127.0.0.1'), [ldap_port] varchar(10) NOT NULL 
DEFAULT ('389'), [ldap_dn] varchar(250) NOT NULL DEFAULT 
('DC=testserver,DC=danka,DC=de'), [ldap_atdn] varchar(250) NOT NULL DEFAULT 
('testserver.danka.de'), [ldap_active] int(10) NOT NULL DEFAULT ('0'), 
[ldap_addressfieldsactive] int(10) NOT NULL DEFAULT ('1'), [ldap_addressfield1] 
varchar(255) NOT NULL DEFAULT ('displayname'), [ldap_addressfield2] 
varchar(255) NOT NULL DEFAULT ('mail'), [ldap_addressfield3] varchar(255) NOT 
NULL, [ldap_addressfield4] varchar(255) NOT NULL DEFAULT ('department'), 
[ldap_addressfield5] varchar(255) NOT NULL DEFAULT ('telephonenumber'), 
[ldap_addressfield6] varchar(255) NOT NULL DEFAULT 
('physicaldeliveryofficename'), [ldap_addressfield7] varchar(255) NOT NULL 
DEFAULT ('l'), [ldap_adminname] varchar(250) NOT NULL, [ldap_adminpwd] 
varchar(250) NOT NULL, [ldap_autoupdate] int(10) DEFAULT ('0'), [ldap_location] 
varchar(200) DEFAULT NULL, [ldap_sslX] int(10) DEFAULT 0))SQL?
def=CHANGE "ldap_sslX" 'ldap_ssl' INTEGERaction=(change), column=(ldap_sslX), 
column_escaped=(ldap_sslX)preg_column_definition=(\s*+(?:'(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++)preg_columns_b
efore=((?:((?:\s*+(?:'(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'
(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?)preg_c
olumn_to_change=(\s*(?:'ldap_sslX'|ldap_sslX|"ldap_sslX"|`ldap_sslX`|\[ldap_sslX
\])(?:\s+int\(10\))?(\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"`\[]*|"(?:[^"]*+|"")*+"|`(?
:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))+)?)CREATE TEMPORARY TABLE 't1384960332' 
( [ldapid] INTEGER PRIMARY KEY AUTOINCREMENT, [ldap_server] varchar(100) NOT 
NULL DEFAULT ('127.0.0.1'), [ldap_port] varchar(10) NOT NULL DEFAULT ('389'), 
[ldap_dn] varchar(250) NOT NULL DEFAULT ('DC=testserver,DC=danka,DC=de'), 
[ldap_atdn] varchar(250) NOT NULL DEFAULT ('testserver.danka.de'), 
[ldap_active] int(10) NOT NULL DEFAULT ('0'), [ldap_addressfieldsactive] 
int(10) NOT NULL DEFAULT ('1'), [ldap_addressfield1] varchar(255) NOT NULL 
DEFAULT ('displayname'), [ldap_addressfield2] varchar(255) NOT NULL DEFAULT 
('mail'), [ldap_addressfield3] varchar(255) NOT NULL, [ldap_addressfield4] 
varchar(255) NOT NULL DEFAULT ('department'), [ldap_addressfield5] varchar(255) 
NOT NULL DEFAULT ('telephonenumber'), [ldap_addressfield6] varchar(255) NOT 
NULL DEFAULT ('physicaldeliveryofficename'), [ldap_addressfield7] varchar(255) 
NOT NULL DEFAULT ('l'), [ldap_adminname] varchar(250) NOT NULL, [ldap_adminpwd] 
varchar(250) NOT NULL, [ldap_autoupdate] int(10) DEFAULT ('0'), [ldap_location] 
varchar(200) DEFAULT NULL, [ldap_sslX] int(10) DEFAULT 0)CREATE TEMPORARY TABLE 
't1384960332' ( [ldapid] INTEGER PRIMARY KEY AUTOINCREMENT, [ldap_server] 
varchar(100) NOT NULL DEFAULT ('127.0.0.1'), [ldap_port] varchar(10) NOT NULL 
DEFAULT ('389'), [ldap_dn] varchar(250) NOT NULL DEFAULT 
('DC=testserver,DC=danka,DC=de'), [ldap_atdn] varchar(250) NOT NULL DEFAULT 
('testserver.danka.de'), [ldap_active] int(10) NOT NULL DEFAULT ('0'), 
[ldap_addressfieldsactive] int(10) NOT NULL DEFAULT ('1'), [ldap_addressfield1] 
varchar(255) NOT NULL DEFAULT ('displayname'), [ldap_addressfield2] 
varchar(255) NOT NULL DEFAULT ('mail'), [ldap_addressfield3] varchar(255) NOT 
NULL, [ldap_addressfield4] varchar(255) NOT NULL DEFAULT ('department'), 
[ldap_addressfield5] varchar(255) NOT NULL DEFAULT ('telephonenumber'), 
[ldap_addressfield6] varchar(255) NOT NULL DEFAULT 
('physicaldeliveryofficename'), [ldap_addressfield7] varchar(255) NOT NULL 
DEFAULT ('l'), [ldap_adminname] varchar(250) NOT NULL, [ldap_adminpwd] 
varchar(250) NOT NULL, [ldap_autoupdate] int(10) DEFAULT ('0'), [ldap_location] 
varchar(200) DEFAULT NULL, [ldap_sslX] int(10) DEFAULT 
0)/^\s*+(CREATE\s++TEMPORARY\s++TABLE\s++'t1384960332'\s*+\()(?:((?:\s*+(?:'(?:[
^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'
(?:[^']++|'')++'|[^ 
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?
:[^']*+|'')*+'|[^'",`\[ 
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?\s*(?:'
ldap_sslX'|ldap_sslX|"ldap_sslX"|`ldap_sslX`|\[ldap_sslX\])(?:\s+int\(10\))?(\s+
(?:(?:'(?:[^']*+|'')*+'|[^,'"`\[]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]
]*+|\]\])*+\]))+)?(,\s*(.+))?\s*\)\s*$/s
-------------------------------------------------------------------------

So I am a little bit confused why the rename sometimes works and sometime not
best regards

Original comment by Thomas.S...@googlemail.com on 20 Nov 2013 at 3:14

GoogleCodeExporter commented 9 years ago

Thanks for the new debug output, I will have a look at it.
It is no surprise for me that it sometimes works and sometimes doesn't. It 
heavily depends on the CREATE TABLE statement of your original table. (And 
unfortunately also sometimes on the configuration of your server like how big 
the backtrack limit of pcre is etc.)

Original comment by crazy4ch...@gmail.com on 20 Nov 2013 at 3:46

GoogleCodeExporter commented 9 years ago

Ok I am currently inspecting the third example which I could reproduce.
It seems to be caused by the way the DEFAULT value is put into brackets.

In this table:
CREATE TABLE [is_ldap2] ( [ldap_server] varchar(100) NOT NULL DEFAULT 
'127.0.0.1', [ldap_sslX] int(10) DEFAULT 0)
It is possible to rename ldap_sslX to ldap_ssl.

But in this one:
CREATE TABLE [is_ldap3] ( [ldap_server] varchar(100) NOT NULL DEFAULT 
('127.0.0.1'), [ldap_sslX] int(10) DEFAULT 0)
it is not possible.

And this one again works:
CREATE TABLE [is_ldap4] ( [ldap_server] varchar(100) NOT NULL DEFAULT ( 
'127.0.0.1' ), [ldap_sslX] int(10) DEFAULT 0)

The problem is that stuff in quotes like '127.0.0.1' is currently only allowed 
in column constraints of the original CREATE TABLE statement if there is some 
white space before it. Obviously there is no such rule in SQL as supported by 
SQLite. I guess it is safe to make the white space here optional.
You find a version of phpLiteAdmin attached that should work in the third 
example.
Please check if it does and if you still have similar problems with this 
version.

This version also fixes another problem:
Previously, column definitions always needed to define a type or at least 
"something" additionally. But SQLite does not force you to define a type. So 
this is legal SQLite SQL:
CREATE TABLE test1(a,b,c,d,e,f)
In this example you could not for example rename the column c. With the 
attached version you can.

I will also have a look at your first example of post 6. This seems to be 
another problem. I think maybe other stuff like foreign keys or something like 
this is involved here but I need to have a closer look at it.

Original comment by crazy4ch...@gmail.com on 20 Nov 2013 at 5:53

Changed state: Accepted
Added labels: Target-1.9.5

Attachments:

phpliteadmin.php

GoogleCodeExporter commented 9 years ago

Hello,
thanks a lot for your nice help.
For your information. The complete SqLite database was migrated from a MySQL 
DB. I did this with a convert script. This could be the reason why the default 
values are in this way

Original comment by Thomas.S...@googlemail.com on 21 Nov 2013 at 8:24

GoogleCodeExporter commented 9 years ago

Hmmm,
I have rechecked the default values if the table is_ldap. But the default value 
is '172.0.0.1' not ('127.0.0.1').
I do not know why phpLiteAdmin creates the SQL Create with round brackets?
I attach a screenshot of the default value

Original comment by Thomas.S...@googlemail.com on 21 Nov 2013 at 8:32

Attachments:

default_value.jpg

GoogleCodeExporter commented 9 years ago

What you see there are the default values and of course they are withou the 
brackets.  The create table ststatement that is used when altering a table is 
the one that was used when the table was create.  SQLite stores this and we 
retrieve it for altering.  It's no problem that the brackets are there because 
it is legal sql. So we need to support it. 
Have you had a try with the ne version?

Original comment by crazy4ch...@gmail.com on 21 Nov 2013 at 8:38

GoogleCodeExporter commented 9 years ago

I have tested it now with the new version 1.9.5 and now the rename works fine 
:-)
By the way: is it not possible to edit the default values?

Original comment by Thomas.S...@googlemail.com on 21 Nov 2013 at 11:10

GoogleCodeExporter commented 9 years ago

Sorry for the late reply and thanks for confirming that the change seems to 
solve your problems.
Unfortunately it is not possible to change the default value at the moment. We 
will make this possible in future versions. I will open a new issue for it.

Original comment by crazy4ch...@gmail.com on 23 Dec 2013 at 10:26

GoogleCodeExporter commented 9 years ago

I again checked all examples posted in this issue with the current development 
version and all of them work correctly so I think this issue is fixed.

Original comment by crazy4ch...@gmail.com on 2 Jan 2014 at 3:42

Changed state: Fixed

jatpat / phpliteadmin

ERROR: Altering of Table, could not replace the table name with the temporary one #234