Bug #7393 for SQL-Translator: MySQL parser is too strict on commas

Wed Aug 18 05:48:07 2004 Guest - Ticket created

Subject:

MySQL parser is too strict on commas

The following SQL, which is legal for MySQL, generates an error: CREATE TABLE test ( id INT, PRIMARY KEY (id), ); The error is below. Use of trailing commas is convenient to simplify editing files and can arise as a result of commenting out lines. ERROR (line 4): Invalid constraint: Was expecting primary key def, or unique key def, or foreign key def ERROR (line 4): Invalid index: Was expecting normal index, or fulltext index ERROR (line 4): Invalid field: Was expecting field name but found ");" instead ERROR (line 4): Invalid create definition: Was expecting constraint, or index, or field, or comment, or /\\n/ ERROR (line 1): Invalid statement: Was expecting comment, or use, or set, or drop, or create Error: translate: Error with parser 'SQL::Translator::Parser::MySQL': no results at /usr/local/bin/sqlt-graph line 160. Sorry, I'm not familiar enough with Parse::RecDescent to suggest a fix.

Thu Dec 22 13:53:53 2005 Guest - Correspondence added

[guest - Wed Aug 18 05:48:07 2004]: Show quoted text

> The following SQL, which is legal for MySQL, generates an error: > > CREATE TABLE test ( > id INT, > PRIMARY KEY (id), > );

The following table is SILENTLY ignored: create table foo ( x int8 primary key, ); due to the trailing comma.

Thu Dec 22 14:00:16 2005 darren [...] cpan.org - Correspondence added

From:

Darren Chamberlain <darren [...] cpan.org>

[guest - Thu Dec 22 13:53:53 2005]: Show quoted text

> [guest - Wed Aug 18 05:48:07 2004]: >

> > The following SQL, which is legal for MySQL, generates an error: > > > > CREATE TABLE test ( > > id INT, > > PRIMARY KEY (id), > > );

> > The following table is SILENTLY ignored: > > create table foo ( > x int8 primary key, > ); > > due to the trailing comma.

The trailing comma is not valid in MySQL's grammar: $ mysql -s test Show quoted text

mysql> create table foo (

-> x int8 primary key, -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3

Tue Jan 03 04:45:32 2006 djh [...] cpan.org - Correspondence added

[DARREN - Thu Dec 22 14:00:16 2005]: Show quoted text

> [guest - Thu Dec 22 13:53:53 2005]:

> > The following table is SILENTLY ignored:

> The trailing comma is not valid in MySQL's grammar:

It turned out that what MySQL permits in the way of trailing commas is version dependent. The problem I reported was for 3.23 IIRC. It's different for at least some 4. versions. I think this was discussed on the list. It's a pity; it's a PITA to have to keep adding and deleting commas! Cheers, Dave