Bug #63526 Valid script fails

Description:
This is spawned from: http://netbeans.org/bugzilla/show_bug.cgi?id=205677

After lots of testing it was diagnosed as a jdbc driver issue. Let me know if more details are needed.

How to repeat:
Ok - I reproduced the bug and also found out, why it worked in my tests.

== Step 1 == 

I reduced the problem to a small test-case (and did a fresh install of mysql on
a windows 2003 server system):

CREATE SCHEMA IF NOT EXISTS `xinco` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci ;
USE `xinco` ;

DROP TABLE IF EXISTS `xinco`.`xinco_core_data` ;
CREATE  TABLE IF NOT EXISTS `xinco`.`xinco_core_data` (
  `id` INT UNSIGNED NOT NULL ,
  `xinco_core_node_id` INT UNSIGNED NOT NULL ,
  `xinco_core_language_id` INT UNSIGNED NOT NULL ,
  `xinco_core_data_type_id` INT UNSIGNED NOT NULL ,
  `designation` VARCHAR(255) NOT NULL ,
  `status_number` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `xinco_core_data_FKIndex1` (`xinco_core_node_id` ASC) ,
  INDEX `xinco_core_data_FKIndex2` (`xinco_core_language_id` ASC) ,
  INDEX `xinco_core_data_FKIndex5` (`xinco_core_data_type_id` ASC) ,
  INDEX `xinco_core_data_index_designation` (`designation` ASC) ,
  INDEX `xinco_core_data_index_status` (`status_number` ASC) ,
  CONSTRAINT `fk_{0FE42428-0C82-42FC-923F-7314A740D04F}`
    FOREIGN KEY (`xinco_core_node_id` )
    REFERENCES `xinco`.`xinco_core_data` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_{249C3090-F2CF-4C6B-A528-C9DDF697E702}`
    FOREIGN KEY (`xinco_core_language_id` )
    REFERENCES `xinco`.`xinco_core_data` (`id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_{592A6D87-0049-4758-9D7F-D4F421983CDB}`
    FOREIGN KEY (`xinco_core_data_type_id` )
    REFERENCES `xinco`.`xinco_core_data` (`id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE) 
;

== Step 2 == 

I checked whether netbeans or somethink else is fishy.

I used a small java programm, that just issued the statements above directly
via jdbc and got the same problem.

At this time the bug was resolved, as not a bug in netbeans.

== Step 3 == 

I ran wireshark and had a look at the command stream

And I saw this:

CREATE  TABLE IF NOT EXISTS `xinco`.`xinco_core_data` (
  `id` INT UNSIGNED NOT NULL ,
  `xinco_core_node_id` INT UNSIGNED NOT NULL ,
  `xinco_core_language_id` INT UNSIGNED NOT NULL ,
  `xinco_core_data_type_id` INT UNSIGNED NOT NULL ,
  `designation` VARCHAR(255) NOT NULL ,
  `status_number` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `xinco_core_data_FKIndex1` (`xinco_core_node_id` ASC) ,
  INDEX `xinco_core_data_FKIndex2` (`xinco_core_language_id` ASC) ,
  INDEX `xinco_core_data_FKIndex5` (`xinco_core_data_type_id` ASC) ,
  INDEX `xinco_core_data_index_designation` (`designation` ASC) ,
  INDEX `xinco_core_data_index_status` (`status_number` ASC) ,
  CONSTRAINT `fk_`
    FOREIGN KEY (`xinco_core_node_id` )
    REFERENCES `xinco`.`xinco_core_data` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_`
    FOREIGN KEY (`xinco_core_language_id` )
    REFERENCES `xinco`.`xinco_core_data` (`id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_`
    FOREIGN KEY (`xinco_core_data_type_id` )
    REFERENCES `xinco`.`xinco_core_data` (`id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)

I think you see the problem (hint: look at the constraint names)

Basicly: The JBDC driver stripped out the parts in curly braces.

This could be an artifact of the jdbc escape handling, but this is speculation!

== Step 4 == 

Repeat the test against my local mysql system - I saw the same thing on the
wire but didn't get the failure and then it dawned on me. The default engine in
my install is MyISAM and that does not enforce constraints and I doubt it even
tries to create them, so the engine ignores the statements and just produces
the table successfully.

Long story short: The problem lies in the jdbc escape processing. It's
debatable whether escapes should only be replaced, if a valid is encountered or
not, but I think its a bug in the jdbc driver of mysql jdbc driver (I tested
the newest available version: 5.1.18). 

PS: Yes I know, that escape processing can be deactived per Statement, but I
consider the escape syntax a feature of jdbc and not a bug.

Referenced script

Attachment: xinco_MySQL.sql (application/octet-stream, text), 53.71 KiB.

Related Workbench file

Attachment: Xinco.mwb (application/octet-stream, text), 63.06 KiB.

Hi Javier and thanks for elaborate bug report. Please do tell us the SQL_MODE MySQL server is in when the test fails.

What do you mean by SQL MODE and how can I figure it out?

http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

I'm the cited analyst of the original bug. The server yields this:

SELECT @@GLOBAL.sql_mode;

=> nothing

SELECT @@SESSION.sql_mode;

=> STRICT_TRANS_TABLES

I think I found it: The problem happens in com.mysql.jdbc.EscapeProcessor#escapeSQL. The function recognizes the string in the create table statement as an escape sequence (line 136+138). The if construct beginning in line 182 tries to match a white-space collapsed version of the string to prefixes for valid jdbc-escapes (till line 300). 

No matching escape sequence is found and no else is defined, so neigther the token, nor a replacement are added to the resulting escaped SQL string. So I propose to add a final else-branch to the construnct at line 301:

 else {
     newSql.append(token);
 }

Thanks Matthias but it's not the case... Latest sources show

	} else {
		newSql.append(token); // it's just part of the query
	}
@ line 301 but it still fails.

Hey,

I had another look at the code at I think I'm still correct. You see the else-branch of the if statement starting on line 136 but my correction would apply to the inner if starting on line 182. That swallows the tokeen.

But I see another possible fix. The EscapeTokenizer does not take back-ticks into account and so does not parse the whole quoted sequenze as one token. So adding this to the cases sould fix it.

Matthias, yes you were right. I picked up code from outer block. Fix, as you proposed it is in review.

Pushed up to rev. 1122.

Added to changelog for 5.5.19: 

A problem with processing escape sequences (in
com.mysql.jdbc.EscapeProcessor#escapeSQL) could cause certain
statements to fail. For example, a CREATE TABLE statement with a
clause such as CONSTRAINT `fk_` was not parsed correctly.