Bug #83380 for SQL-Translator: MySQL parser does not recognize a create with an embedded comment before an auto

Sat Feb 16 14:58:51 2013 chris+rt [...] chrisdolan.net - Ticket created

Subject:

MySQL parser does not recognize a create with an embedded comment before an auto_increment

Consider the following SQL snippet: CREATE TABLE `city_hours` ( `id` integer(11) NOT NULL comment 'Each row is a datetime range during which Artists and Locations may book Performances for a given City.' auto_increment, `city_id` integer(11) NOT NULL ); SQL::Translator reports the following for that SQL: ERROR (line 1): Invalid statement: Was expecting comment, or use, or set, or drop, or create, or alter, or insert, or delimiter, or empty statement If I remove the "comment '.*'" clause, then it parses successfully. Other columns with comments are fine, just the auto_increment one fails.

Sat Feb 16 15:06:09 2013 chris+rt [...] chrisdolan.net - Correspondence added

I believe the error is that a comment is not considered a field_qualifier, by this definition: field : field_comment(s?) field_name data_type field_qualifier(s?) field_comment2(?) reference_definition(?) on_update(?) field_comment(s?) Really, I think the comment should be allowed to be interspersed between any other qualifiers rather than being treated specially as in the above rule.

Sat Feb 16 15:06:11 2013 chris+rt [...] chrisdolan.net - Status changed from 'new' to 'open'

Sat Feb 16 15:11:12 2013 chris+rt [...] chrisdolan.net - Correspondence added

One last note I forgot to mention. The "faulty" SQL with the comment before the auto_increment came from the DBI translator, not from a hand-written schema. So another possible solution would be to alter that generator to make SQL that the parser can understand, although that's a less elegant solution than fixing the parser.

Sat Mar 09 01:44:49 2013 ribasushi [...] leporine.io - Correspondence added

On Sat Feb 16 15:06:09 2013, CDOLAN wrote: Show quoted text

> I believe the error is that a comment is not considered a > field_qualifier, by this definition: > > field : field_comment(s?) field_name data_type field_qualifier(s?) > field_comment2(?) > reference_definition(?) on_update(?) field_comment(s?) >

I rather you insert more "field_comment" pieces in the grammar. Basically split the parsing into a: field_definition_chunk | field_comment And have P::RD loop over until it reaches the ; Care to package this up with a test addition to t/14postgres-parser.t ? Cheers

Mon Sep 07 05:55:33 2015 mendoza [...] pvv.ntnu.no - Correspondence added

Happens with a stock mysqldump call for me too: CREATE TABLE `gc_discount` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ' ', ... ) ENGINE=InnoDB AUTO_INCREMENT=288 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; Happening in 0.11021; Removing COMMENT ' ' helps.

Tue Sep 08 13:25:07 2015 ilmari [...] ilmari.org - Correspondence added

CC:	undisclosed-recipients:;
Subject:	Re: [rt.cpan.org #83380] MySQL parser does not recognize a create with an embedded comment before an auto_increment
Date:	Tue, 08 Sep 2015 18:24:42 +0100
To:	bug-SQL-Translator [...] rt.cpan.org
From:	ilmari [...] ilmari.org (Dagfinn Ilmari Mannsåker)

"Nicolas Mendoza via RT" <bug-SQL-Translator@rt.cpan.org> writes: Show quoted text

> Happens with a stock mysqldump call for me too: > > CREATE TABLE `gc_discount` ( > `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ' ', > ... > ) ENGINE=InnoDB AUTO_INCREMENT=288 DEFAULT CHARSET=utf8; > /*!40101 SET character_set_client = @saved_cs_client */;

I can't reproduce this particular problem (but there's a separate bug in that leading spaces in quoted strings got stripped). The fix for the original problem is to add COMMENT <string> to the field_qualifier production instead of having it in a specific place in the field production. This also matches MySQL's behaviour in that multiple COMMENTs are allowed, but only the last one takes effect. Both these fixes (and some fixes for multi-line comments in various producers) are now in git. -- "I use RMS as a guide in the same way that a boat captain would use a lighthouse. It's good to know where it is, but you generally don't want to find yourself in the same spot." - Tollef Fog Heen

Thu Sep 10 03:23:39 2015 ilmari+cpan [...] ilmari.org - Status changed from 'open' to 'patched'

Bug #83380 for SQL-Translator: MySQL parser does not recognize a create with an embedded comment before an auto_increment