Error on editing/deleting columns

[Former user]

Hi there,

I have problems similar to #143 and #159 but both are resolved so I think I have to open a new one.

The error message says:

#!

ERROR: Altering of Table Seminare failed - (drop) - Pattern did not match on your original CREATE TABLE statement. PREG ERROR: No error
This may be a bug that needs to be reported at https://bitbucket.org/phpliteadmin/public/issues?status=new&status=open

I will attach my database to show the problem.

If I try to modify a column of the table Seminare I get the mentioned error. Please see debug output below.

ALTER TABLE QUERY=(ALTER TABLE "Seminare" DROP "Seminarnummer"), tablename=(Seminare), alterdefs=(DROP "Seminarnummer")ALTER TABLE: table=(Seminare), alterdefs=(DROP "Seminarnummer")SQL?
origsql=(CREATE TABLE 'Seminare' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [title] varchar(50) NOT NULL, [promotedby] varchar(50) not null, [promotedbyorg] varchar(50) not null, [promotedbycontact] varchar(50) not null, [documentation] varchar(100) NULL, [leader] varchar(100) NULL, [orgform] varchar(100) NULL, [tnmin] INTEGER DEFAULT '0' NULL, [tnmax] INTEGER DEFAULT '1000' NULL, [suggestedduration] VARCHAR(100) NULL, [suggesteddate] VARCHAR(100) NULL, [suggestedplace] VARCHAR(100) NULL, [suggestedcost] VARCHAR(100) NULL, [category] INTEGER NOT NULL, [targetgroup] VARCHAR(150) NULL, [target] TEXT NULL, [notes] VARCHAR(250) NULL , 'creator' TEXT, 'created' DATETIME NOT NULL , 'modified' DATETIME, 'speaker' TEXT, 'credits' INTEGER, 'sortdate' DATETIME, 'Seminarnummer' TEXT, 'eventnumber' TEXT, FOREIGN KEY (category) REFERENCES Kategorien(lfdnr)))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'Seminare'|Seminare|"Seminare"|`Seminare`|\[Seminare\])\s*+(\(.*+)$/is)createtemptableSQL=(CREATE TEMPORARY TABLE 't1479980417' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [title] varchar(50) NOT NULL, [promotedby] varchar(50) not null, [promotedbyorg] varchar(50) not null, [promotedbycontact] varchar(50) not null, [documentation] varchar(100) NULL, [leader] varchar(100) NULL, [orgform] varchar(100) NULL, [tnmin] INTEGER DEFAULT '0' NULL, [tnmax] INTEGER DEFAULT '1000' NULL, [suggestedduration] VARCHAR(100) NULL, [suggesteddate] VARCHAR(100) NULL, [suggestedplace] VARCHAR(100) NULL, [suggestedcost] VARCHAR(100) NULL, [category] INTEGER NOT NULL, [targetgroup] VARCHAR(150) NULL, [target] TEXT NULL, [notes] VARCHAR(250) NULL , 'creator' TEXT, 'created' DATETIME NOT NULL , 'modified' DATETIME, 'speaker' TEXT, 'credits' INTEGER, 'sortdate' DATETIME, 'Seminarnummer' TEXT, 'eventnumber' TEXT, FOREIGN KEY (category) REFERENCES Kategorien(lfdnr)))preg_alter_part=(/(?:DROP(?! PRIMARY KEY)|ADD(?! PRIMARY KEY)|CHANGE|RENAME TO|ADD PRIMARY KEY|DROP PRIMARY KEY)(?:\s+\((?:'(?:[^']++|'')++'|[^"'\[`)]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])+\)|\s+(?:'(?:[^']++|'')++'|[^,'"\[`]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\]))*/i)SQL?
def=DROP "Seminarnummer"preg_parse_def=/^(DROP(?! PRIMARY KEY)|ADD(?! PRIMARY KEY)|CHANGE|RENAME TO|ADD PRIMARY KEY|DROP PRIMARY KEY)(?:(?:\s+\((.+)\)\s*$)|(?:\s+"((?:[^"]|"")+)"|\s+'((?:[^']|'')+)')((?:\s+'((?:[^']|'')+)')?\s+((?:[A-Z]+\s*)+(?:\(\s*[+-]?\s*[0-9]+(?:\s*,\s*[+-]?\s*[0-9]+)?\s*\))?)\s*.*)?\s*$)?\s*$/iaction=(drop), column=(Seminarnummer), column_escaped=(Seminarnummer)preg_column_definition=(\s*+(?:'(?!Seminarnummer')(?:[^']++|'')++'|(?!Seminarnummer\s)[^ '"\[`,]+|"(?!Seminarnummer")(?:[^"]++|"")++"|`(?!Seminarnummer`)(?:[^`]++|``)++`|\[(?!Seminarnummer\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++)preg_columns_before=((?:((?:\s*+(?:'(?!Seminarnummer')(?:[^']++|'')++'|(?!Seminarnummer\s)[^ '"\[`,]+|"(?!Seminarnummer")(?:[^"]++|"")++"|`(?!Seminarnummer`)(?:[^`]++|``)++`|\[(?!Seminarnummer\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?!Seminarnummer')(?:[^']++|'')++'|(?!Seminarnummer\s)[^ '"\[`,]+|"(?!Seminarnummer")(?:[^"]++|"")++"|`(?!Seminarnummer`)(?:[^`]++|``)++`|\[(?!Seminarnummer\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?)CREATE TEMPORARY TABLE 't1479980417' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [title] varchar(50) NOT NULL, [promotedby] varchar(50) not null, [promotedbyorg] varchar(50) not null, [promotedbycontact] varchar(50) not null, [documentation] varchar(100) NULL, [leader] varchar(100) NULL, [orgform] varchar(100) NULL, [tnmin] INTEGER DEFAULT '0' NULL, [tnmax] INTEGER DEFAULT '1000' NULL, [suggestedduration] VARCHAR(100) NULL, [suggesteddate] VARCHAR(100) NULL, [suggestedplace] VARCHAR(100) NULL, [suggestedcost] VARCHAR(100) NULL, [category] INTEGER NOT NULL, [targetgroup] VARCHAR(150) NULL, [target] TEXT NULL, [notes] VARCHAR(250) NULL , 'creator' TEXT, 'created' DATETIME NOT NULL , 'modified' DATETIME, 'speaker' TEXT, 'credits' INTEGER, 'sortdate' DATETIME, 'Seminarnummer' TEXT, 'eventnumber' TEXT, FOREIGN KEY (category) REFERENCES Kategorien(lfdnr))CREATE TEMPORARY TABLE 't1479980417' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [title] varchar(50) NOT NULL, [promotedby] varchar(50) not null, [promotedbyorg] varchar(50) not null, [promotedbycontact] varchar(50) not null, [documentation] varchar(100) NULL, [leader] varchar(100) NULL, [orgform] varchar(100) NULL, [tnmin] INTEGER DEFAULT '0' NULL, [tnmax] INTEGER DEFAULT '1000' NULL, [suggestedduration] VARCHAR(100) NULL, [suggesteddate] VARCHAR(100) NULL, [suggestedplace] VARCHAR(100) NULL, [suggestedcost] VARCHAR(100) NULL, [category] INTEGER NOT NULL, [targetgroup] VARCHAR(150) NULL, [target] TEXT NULL, [notes] VARCHAR(250) NULL , 'creator' TEXT, 'created' DATETIME NOT NULL , 'modified' DATETIME, 'speaker' TEXT, 'credits' INTEGER, 'sortdate' DATETIME, 'Seminarnummer' TEXT, 'eventnumber' TEXT, FOREIGN KEY (category) REFERENCES Kategorien(lfdnr))/^\s*+(CREATE\s++TEMPORARY\s++TABLE\s++'t1479980417'\s*+\()(?:((?:\s*+(?:'(?!Seminarnummer')(?:[^']++|'')++'|(?!Seminarnummer\s)[^ '"\[`,]+|"(?!Seminarnummer")(?:[^"]++|"")++"|`(?!Seminarnummer`)(?:[^`]++|``)++`|\[(?!Seminarnummer\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?!Seminarnummer')(?:[^']++|'')++'|(?!Seminarnummer\s)[^ '"\[`,]+|"(?!Seminarnummer")(?:[^"]++|"")++"|`(?!Seminarnummer`)(?:[^`]++|``)++`|\[(?!Seminarnummer\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?\s*(?:'Seminarnummer'|Seminarnummer|"Seminarnummer"|`Seminarnummer`|\[Seminarnummer\])\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"\[`]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))+(,\s*(.+))?\s*\)\s*$/sSQL?

[phpLiteAdmin]

Alter Table: The temporary table is not created using CREATE TEMPORARY TABLE anymore, as foreign key constraints of the old table would fail for the temporary table as the referenced tables don't exist in the TEMP database. This should fix issue #310.

→ <<cset 0d81cafc945d>>

[phpLiteAdmin]

This should be fixed in git now. Please try the current development version: https://www.phpliteadmin.org/phpliteadmin-dev.zip

Please let me know if this fixes your problem.

[Sebastian Mery]

Hi,

thanks for the response but still no luck. I still get the same message and attached debug output:

SQL?
ALTER TABLE QUERY=(ALTER TABLE "Seminare" DROP "Seminarnummer"), tablename=(Seminare), alterdefs=(DROP "Seminarnummer")ALTER TABLE: table=(Seminare), alterdefs=(DROP "Seminarnummer")SQL?
origsql=(CREATE TABLE 'Seminare' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [title] varchar(50) NOT NULL, [promotedby] varchar(50) not null, [promotedbyorg] varchar(50) not null, [promotedbycontact] varchar(50) not null, [documentation] varchar(100) NULL, [leader] varchar(100) NULL, [orgform] varchar(100) NULL, [tnmin] INTEGER DEFAULT '0' NULL, [tnmax] INTEGER DEFAULT '1000' NULL, [suggestedduration] VARCHAR(100) NULL, [suggesteddate] VARCHAR(100) NULL, [suggestedplace] VARCHAR(100) NULL, [suggestedcost] VARCHAR(100) NULL, [category] INTEGER NOT NULL, [targetgroup] VARCHAR(150) NULL, [target] TEXT NULL, [notes] VARCHAR(250) NULL , 'creator' TEXT, 'created' DATETIME NOT NULL , 'modified' DATETIME, 'speaker' TEXT, 'credits' INTEGER, 'sortdate' DATETIME, 'Seminarnummer' TEXT, 'eventnumber' TEXT, 'cooperation' TEXT, FOREIGN KEY (category) REFERENCES Kategorien(lfdnr)))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'Seminare'|Seminare|"Seminare"|`Seminare`|\[Seminare\])\s*+(\(.*+)$/is)createtemptableSQL=(CREATE TABLE 't1479990361' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [title] varchar(50) NOT NULL, [promotedby] varchar(50) not null, [promotedbyorg] varchar(50) not null, [promotedbycontact] varchar(50) not null, [documentation] varchar(100) NULL, [leader] varchar(100) NULL, [orgform] varchar(100) NULL, [tnmin] INTEGER DEFAULT '0' NULL, [tnmax] INTEGER DEFAULT '1000' NULL, [suggestedduration] VARCHAR(100) NULL, [suggesteddate] VARCHAR(100) NULL, [suggestedplace] VARCHAR(100) NULL, [suggestedcost] VARCHAR(100) NULL, [category] INTEGER NOT NULL, [targetgroup] VARCHAR(150) NULL, [target] TEXT NULL, [notes] VARCHAR(250) NULL , 'creator' TEXT, 'created' DATETIME NOT NULL , 'modified' DATETIME, 'speaker' TEXT, 'credits' INTEGER, 'sortdate' DATETIME, 'Seminarnummer' TEXT, 'eventnumber' TEXT, 'cooperation' TEXT, FOREIGN KEY (category) REFERENCES Kategorien(lfdnr)))preg_alter_part=(/(?:DROP(?! PRIMARY KEY)|ADD(?! PRIMARY KEY)|CHANGE|RENAME TO|ADD PRIMARY KEY|DROP PRIMARY KEY)(?:\s+\((?:'(?:[^']++|'')++'|[^"'\[`)]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])+\)|\s+(?:'(?:[^']++|'')++'|[^,'"\[`]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\]))*/i)SQL?
def=DROP "Seminarnummer"preg_parse_def=/^(DROP(?! PRIMARY KEY)|ADD(?! PRIMARY KEY)|CHANGE|RENAME TO|ADD PRIMARY KEY|DROP PRIMARY KEY)(?:(?:\s+\((.+)\)\s*$)|(?:\s+"((?:[^"]|"")+)"|\s+'((?:[^']|'')+)')((?:\s+'((?:[^']|'')+)')?\s+((?:[A-Z]+\s*)+(?:\(\s*[+-]?\s*[0-9]+(?:\s*,\s*[+-]?\s*[0-9]+)?\s*\))?)\s*.*)?\s*$)?\s*$/iaction=(drop), column=(Seminarnummer), column_escaped=(Seminarnummer)preg_column_definition=(\s*+(?:'(?!Seminarnummer')(?:[^']++|'')++'|(?!Seminarnummer\s)[^ '"\[`,]+|"(?!Seminarnummer")(?:[^"]++|"")++"|`(?!Seminarnummer`)(?:[^`]++|``)++`|\[(?!Seminarnummer\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++)preg_columns_before=((?:((?:\s*+(?:'(?!Seminarnummer')(?:[^']++|'')++'|(?!Seminarnummer\s)[^ '"\[`,]+|"(?!Seminarnummer")(?:[^"]++|"")++"|`(?!Seminarnummer`)(?:[^`]++|``)++`|\[(?!Seminarnummer\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?!Seminarnummer')(?:[^']++|'')++'|(?!Seminarnummer\s)[^ '"\[`,]+|"(?!Seminarnummer")(?:[^"]++|"")++"|`(?!Seminarnummer`)(?:[^`]++|``)++`|\[(?!Seminarnummer\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?)CREATE TABLE 't1479990361' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [title] varchar(50) NOT NULL, [promotedby] varchar(50) not null, [promotedbyorg] varchar(50) not null, [promotedbycontact] varchar(50) not null, [documentation] varchar(100) NULL, [leader] varchar(100) NULL, [orgform] varchar(100) NULL, [tnmin] INTEGER DEFAULT '0' NULL, [tnmax] INTEGER DEFAULT '1000' NULL, [suggestedduration] VARCHAR(100) NULL, [suggesteddate] VARCHAR(100) NULL, [suggestedplace] VARCHAR(100) NULL, [suggestedcost] VARCHAR(100) NULL, [category] INTEGER NOT NULL, [targetgroup] VARCHAR(150) NULL, [target] TEXT NULL, [notes] VARCHAR(250) NULL , 'creator' TEXT, 'created' DATETIME NOT NULL , 'modified' DATETIME, 'speaker' TEXT, 'credits' INTEGER, 'sortdate' DATETIME, 'Seminarnummer' TEXT, 'eventnumber' TEXT, 'cooperation' TEXT, FOREIGN KEY (category) REFERENCES Kategorien(lfdnr))CREATE TABLE 't1479990361' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [title] varchar(50) NOT NULL, [promotedby] varchar(50) not null, [promotedbyorg] varchar(50) not null, [promotedbycontact] varchar(50) not null, [documentation] varchar(100) NULL, [leader] varchar(100) NULL, [orgform] varchar(100) NULL, [tnmin] INTEGER DEFAULT '0' NULL, [tnmax] INTEGER DEFAULT '1000' NULL, [suggestedduration] VARCHAR(100) NULL, [suggesteddate] VARCHAR(100) NULL, [suggestedplace] VARCHAR(100) NULL, [suggestedcost] VARCHAR(100) NULL, [category] INTEGER NOT NULL, [targetgroup] VARCHAR(150) NULL, [target] TEXT NULL, [notes] VARCHAR(250) NULL , 'creator' TEXT, 'created' DATETIME NOT NULL , 'modified' DATETIME, 'speaker' TEXT, 'credits' INTEGER, 'sortdate' DATETIME, 'Seminarnummer' TEXT, 'eventnumber' TEXT, 'cooperation' TEXT, FOREIGN KEY (category) REFERENCES Kategorien(lfdnr))/^\s*+(CREATE\s++TABLE\s++'t1479990361'\s*+\()(?:((?:\s*+(?:'(?!Seminarnummer')(?:[^']++|'')++'|(?!Seminarnummer\s)[^ '"\[`,]+|"(?!Seminarnummer")(?:[^"]++|"")++"|`(?!Seminarnummer`)(?:[^`]++|``)++`|\[(?!Seminarnummer\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?!Seminarnummer')(?:[^']++|'')++'|(?!Seminarnummer\s)[^ '"\[`,]+|"(?!Seminarnummer")(?:[^"]++|"")++"|`(?!Seminarnummer`)(?:[^`]++|``)++`|\[(?!Seminarnummer\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?\s*(?:'Seminarnummer'|Seminarnummer|"Seminarnummer"|`Seminarnummer`|\[Seminarnummer\])\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"\[`]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))+(,\s*(.+))?\s*\)\s*$/sSQL?

tia

Sebastian

[Sebastian Mery]

I also checked with a table without incoming references (just the n part of a 1:n relationship) and get the same message together with the following output:

ALTER TABLE QUERY=(ALTER TABLE "Termine" CHANGE "TerminVon" 'datefrom' DATETIME), tablename=(Termine), alterdefs=(CHANGE "TerminVon" 'datefrom' DATETIME)ALTER TABLE: table=(Termine), alterdefs=(CHANGE "TerminVon" 'datefrom' DATETIME)SQL?
origsql=(CREATE TABLE [Termine] ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [ModulID] INTEGER NOT NULL, [TerminVon] DATE NULL, [TerminBis] DATE NULL, Ort TEXT NULL, FOREIGN KEY (ModulID) REFERENCES Module(lfdnr)))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'Termine'|Termine|"Termine"|`Termine`|\[Termine\])\s*+(\(.*+)$/is)createtemptableSQL=(CREATE TABLE 't1479993099' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [ModulID] INTEGER NOT NULL, [TerminVon] DATE NULL, [TerminBis] DATE NULL, Ort TEXT NULL, FOREIGN KEY (ModulID) REFERENCES Module(lfdnr)))preg_alter_part=(/(?:DROP(?! PRIMARY KEY)|ADD(?! PRIMARY KEY)|CHANGE|RENAME TO|ADD PRIMARY KEY|DROP PRIMARY KEY)(?:\s+\((?:'(?:[^']++|'')++'|[^"'\[`)]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])+\)|\s+(?:'(?:[^']++|'')++'|[^,'"\[`]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\]))*/i)SQL?
def=CHANGE "TerminVon" 'datefrom' DATETIMEpreg_parse_def=/^(DROP(?! PRIMARY KEY)|ADD(?! PRIMARY KEY)|CHANGE|RENAME TO|ADD PRIMARY KEY|DROP PRIMARY KEY)(?:(?:\s+\((.+)\)\s*$)|(?:\s+"((?:[^"]|"")+)"|\s+'((?:[^']|'')+)')((?:\s+'((?:[^']|'')+)')?\s+((?:[A-Z]+\s*)+(?:\(\s*[+-]?\s*[0-9]+(?:\s*,\s*[+-]?\s*[0-9]+)?\s*\))?)\s*.*)?\s*$)?\s*$/iaction=(change), column=(TerminVon), column_escaped=(TerminVon)preg_column_definition=(\s*+(?:'(?!TerminVon')(?:[^']++|'')++'|(?!TerminVon\s)[^ '"\[`,]+|"(?!TerminVon")(?:[^"]++|"")++"|`(?!TerminVon`)(?:[^`]++|``)++`|\[(?!TerminVon\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++)preg_columns_before=((?:((?:\s*+(?:'(?!TerminVon')(?:[^']++|'')++'|(?!TerminVon\s)[^ '"\[`,]+|"(?!TerminVon")(?:[^"]++|"")++"|`(?!TerminVon`)(?:[^`]++|``)++`|\[(?!TerminVon\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?!TerminVon')(?:[^']++|'')++'|(?!TerminVon\s)[^ '"\[`,]+|"(?!TerminVon")(?:[^"]++|"")++"|`(?!TerminVon`)(?:[^`]++|``)++`|\[(?!TerminVon\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?)preg_column_to_change=(\s*(?:'TerminVon'|TerminVon|"TerminVon"|`TerminVon`|\[TerminVon\])(?:\s+DATE)?(\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"`\[]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))+)?)CREATE TABLE 't1479993099' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [ModulID] INTEGER NOT NULL, [TerminVon] DATE NULL, [TerminBis] DATE NULL, Ort TEXT NULL, FOREIGN KEY (ModulID) REFERENCES Module(lfdnr))CREATE TABLE 't1479993099' ( [lfdnr] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, [ModulID] INTEGER NOT NULL, [TerminVon] DATE NULL, [TerminBis] DATE NULL, Ort TEXT NULL, FOREIGN KEY (ModulID) REFERENCES Module(lfdnr))/^\s*+(CREATE\s++TABLE\s++'t1479993099'\s*+\()(?:((?:\s*+(?:'(?!TerminVon')(?:[^']++|'')++'|(?!TerminVon\s)[^ '"\[`,]+|"(?!TerminVon")(?:[^"]++|"")++"|`(?!TerminVon`)(?:[^`]++|``)++`|\[(?!TerminVon\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?!TerminVon')(?:[^']++|'')++'|(?!TerminVon\s)[^ '"\[`,]+|"(?!TerminVon")(?:[^"]++|"")++"|`(?!TerminVon`)(?:[^`]++|``)++`|\[(?!TerminVon\])(?:[^\]]++|\]\])++\])(?:\s*+(?:'(?:[^']*+|'')*+'|[^'",`\[ ]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?\s*(?:'TerminVon'|TerminVon|"TerminVon"|`TerminVon`|\[TerminVon\])(?:\s+DATE)?(\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"`\[]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))+)?(,\s*(.+))?\s*\)\s*$/s

BTW, which "language" should be used for the debug output to get a good formatting?

tia

Sebastian

[phpLiteAdmin]

reopened as user reports problem not solved yet

[phpLiteAdmin]

This is strange.... I can edit your database fine without problems. Also, my debug output is identical to yours up to the step where the error happens and the regex doesn't match. But both the regex and the input is the same on your side and mine.... What is your php version? Can you please tell me what the values in the "pcre" section of your phpinfo() say? The machine I tested returns this:

PCRE (Perl Compatible Regular Expressions) Support  enabled
PCRE Library Version    8.30 2012-02-04
Directive   Local Value Master Value
pcre.backtrack_limit    1000000 1000000
pcre.recursion_limit    100000  100000

Regarding debug output: SQL is fine. We should maybe add some more line-breaks that survive copy&paste...

[Sebastian Mery]

Hi there,

thanks for your efforts. The output is: pcre PCRE (Perl Compatible Regular Expressions) Support

enabled

PCRE Library Version

8.35 2014-04-04

Directive

Local Value

Master Value

pcre.backtrack_limit

1000000

1000000

pcre.recursion_limit

100000

100000

Kind regards

Sebastian

[Sebastian Mery]

Sorry, I forgot:

PHP Version 5.5.12-2ubuntu4.6

System Linux nagios-server 3.16.0-23-generic #31-Ubuntu SMP Tue Oct 21 17:56:17 UTC 2014 x86_64 Build Date Jul 2 2015 14:54:54 Server API Apache 2.0 Handler Virtual Directory Support disabled

[phpLiteAdmin]

Thank you. I checked another machine and there the same error occurs. For the record:

It works for me on PHP 5.4.7 for Windows with PCRE 8.12 2011-01-15.

It does not work for me on PHP 5.6.28-1~dotdeb+7.1 on Linux with PCRE 8.30 2012-02-04.

It does not work for you on PHP 5.5.12-2ubuntu4.6 on Linux with PCRE 8.35 2014-04-04.

I will look into this and let you know once I found out more.

[phpLiteAdmin]

I found that the error is within preg_column_definiton, which on the affected system splits the column definition that contains a DEFAULT value in single quotes. Can you confirm that editing the table with the following CREATE TABLE statement works?

CREATE TABLE [Termine2] ( [lfdnr] INTEGER DEFAULT 1 NOT NULL PRIMARY KEY AUTOINCREMENT, [ModulID] INTEGER NOT NULL, [TerminVon] DATE NULL, [TerminBis] DATE NULL, Ort TEXT NULL, FOREIGN KEY (ModulID) REFERENCES Module(lfdnr))

The only difference is that the default value of lfdnr is not put into quotes here (and the table is called Termine2).

[Sebastian Mery]

Hi there,

this looks quite good. I just tried once again the original table (Termine) which failed and the modified one (Termine2) which sucseeded…

hth

Sebastian

[phpLiteAdmin]

Thanks for your feedback. I simplified the preg_column_definiton regex and the input to a simple example where both php versions behave differently:

/(?:\s*+(?:'(?:[^']*+)*+'|[^'",`\[ ]*))++/
/(?:\s*+(?:'(?:[^']*+)*'|[^'",`\[ ]*))++/

Running preg_match() with these two patterns on the string

DEFAULT '1' NOT

behaves differently on both php versions. On php 5.4.7/PCRE 8.12, both expressions match the whole string. With php 5.6.28/PCRE 8.30, the first regex only matches the string "DEFAULT " and the second regex matches the whole string.

So the possessive quantifier makes the difference. But up to now, I don't understand why. Of course removing the possessiveness would fix the issue but might introduce new issues like increased memory consumption and reaching backtrack-limits in complex CREATE TABLE statements...

Please check out the version here. This version should alter your table successfully, as the quantifiers are not possessive in there.

I am not sure yet if there is a better solution... Might even be a bug in the pcre library used by php. At least I don't understand why they behave different.

[Sebastian Mery]

Hi,

half an hour ago I got a mail to update my php version on this server at least to 5.6. Would that make a difference? Currently I fixed this on one table by modifying the sqlite_master directly which works fine afterwards. I will give a try to the new version and send you feedback…

thanks for your efforts

Sebastian

[phpLiteAdmin]

Hi, I think I now found the change in the changelog of pcre that makes the difference:

Version 8.21 12-Dec-2011
[...]
8.  A possessively repeated conditional subpattern such as (?(?=c)c|d)++ was
    being incorrectly compiled and would have given unpredicatble results.

9.  A possessively repeated subpattern with minimum repeat count greater than
    one behaved incorrectly. For example, (A){2,}+ behaved as if it was
    (A)(A)++ which meant that, after a subsequent mismatch, backtracking into
    the first (A) could occur when it should not.

So I guess it works in old php/pcre versions as they have a bug which makes them backtrack even if they should not....

I need to think a little about this regex....

As old versions work, not new ones, updating to a newer php version should not make a difference.

[phpLiteAdmin]

Simplified this a little more:

Regex:
1:  /('([^']*)*+')++/
2:  /('([^']*)*+')+/
3:  /('([^']*)*')++/
4:  /('([^'])*+')++/

Input:
'1'

This is weird. Old php matches the whole input with all four regular expressions, whereas new php only matches the input with regex 2, 3 and 4. I really don't understand why the behavior of new php should be correct. I guess I need to ask the PCRE guys...

[phpLiteAdmin]

Okay, this is definitely a PCRE bug which was fixed in PCRE 8.36. The changelog says:

7. Fixed a bug concerned with zero-minimum possessive groups that could match an empty string, which sometimes were behaving incorrectly in the interpreter (though correctly in the JIT matcher). This pcretest input is an example:

   '\A(?:[^"]++|"(?:[^"]+|"")+")++' NON QUOTED "QUOT""ED" AFTER "NOT MATCHED

   the interpreter was reporting a match of 'NON QUOTED ' only, whereas the JIT matcher and Perl both matched 'NON QUOTED "QUOT""ED" AFTER '. The test for an empty string was breaking the inner loop and carrying on at a lower level, when possessive repeated groups should always return to a higher level as they have no backtrack points in them. The empty string test now occurs at the outer level.

I tested pcretest and pcre2test in different versions and the regex above matches in 8.36, 8.38 and 10.22 but not in 8.31 and 8.35.

The bug must have been introduced somewhere in between 8.12 and 8.30 and was fixed in 8.36. I will next try to find the first affected version. Then I will probably add a switch in phpLiteAdmin that will use a possessive quantifier only for pcre-versions without the bug.

[phpLiteAdmin]

The php changelog states that PHP 5.6.9 includes PCRE 8.37 and PHP 5.4.41 also includes PCRE 8.37. So recent PHP 5.6 versions should not be affected by this bug.

[phpLiteAdmin]

Don't use possesive quantifiers in buggy PCRE versions. This should fix issue #310 (ALTER TABLE sometimes did not work). From this commit on, PHP 5.2.4 is required at least (before 5.2.0)

→ <<cset 8efb4b97efd1>>