Error on editing/deleting columns

Issue #310 resolved
Former user created an issue

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?

Comments (19)

  1. phpLiteAdmin repo owner

    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>>

  2. 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

  3. 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

  4. phpLiteAdmin repo owner

    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...

  5. 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

  6. 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

  7. phpLiteAdmin repo owner

    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.

  8. phpLiteAdmin repo owner

    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).

  9. 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

  10. phpLiteAdmin repo owner

    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.

  11. 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

  12. phpLiteAdmin repo owner

    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.

  13. phpLiteAdmin repo owner

    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...

  14. phpLiteAdmin repo owner

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

    1. 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.

  15. phpLiteAdmin repo owner

    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.

  16. phpLiteAdmin repo owner

    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>>

  17. Log in to comment