MySQL Bugs: #66285: Error in the SQL Editor creating foreign key

Bug #66285	Error in the SQL Editor creating foreign key
Submitted:	9 Aug 2012 17:34	Modified:	7 Sep 2012 6:09
Reporter:	Amin Chavarria	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.41	OS:	Any (Vista Home Premium SP2, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	editor, foreign key, SQL

Description:
Executing a script in SQL Editor, it's get error creating foreign key.
When I run a script in the SQL Editor, I get an error when creating a foreign key composed of more than two lines the sentences of FK.

How to repeat:
creating the FK

Suggested fix:
one line sentence

Can you upload the problematic script?

Here the script DDL

Attachment: full_db_arte - copia.sql (text/x-sql), 3.17 KiB.

Script uploaded works in mysql command line client when connected to local MySQL server 5.5.28:

...
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> show create table cat_cliente\G
*************************** 1. row ***************************
       Table: cat_cliente
Create Table: CREATE TABLE `cat_cliente` (
  `IDCLIENTE` int(11) NOT NULL AUTO_INCREMENT,
  `RAZON_SOCIAL` varchar(100) COLLATE latin1_spanish_ci DEFAULT NULL,
  `CALLE` varchar(100) COLLATE latin1_spanish_ci DEFAULT NULL,
  `COLONIA` varchar(100) COLLATE latin1_spanish_ci DEFAULT NULL,
  `CIUDAD` varchar(100) COLLATE latin1_spanish_ci DEFAULT NULL,
  `IDESTADO` int(11) DEFAULT NULL,
  `CP` varchar(6) COLLATE latin1_spanish_ci DEFAULT NULL,
  `RFC` varchar(13) COLLATE latin1_spanish_ci DEFAULT NULL,
  `TELEFONO` varchar(13) COLLATE latin1_spanish_ci DEFAULT NULL,
  `EMAIL` varchar(100) COLLATE latin1_spanish_ci NOT NULL,
  `CONTRASENA` varchar(15) COLLATE latin1_spanish_ci NOT NULL,
  `NOMBRE` varchar(50) COLLATE latin1_spanish_ci DEFAULT NULL,
  `APELLIDO_PATERNO` varchar(50) COLLATE latin1_spanish_ci DEFAULT NULL,
  `APELLIDO_MATERNO` varchar(50) COLLATE latin1_spanish_ci DEFAULT NULL,
  `REGISTRO` datetime NOT NULL DEFAULT '9999-01-01 00:00:00' COMMENT 'Fecha de alta en la tienda',
  `ULTIMO_ACCESO` datetime DEFAULT '9999-01-01 00:00:00',
  `NOTICIAS` int(11) NOT NULL DEFAULT '0',
  `CONTRATO` int(11) NOT NULL DEFAULT '0' COMMENT '0 = no acepto contrato\n1 = si acepto contrato',
  `ESTADO_CUENTA` varchar(10) COLLATE latin1_spanish_ci NOT NULL,
  PRIMARY KEY (`IDCLIENTE`),
  UNIQUE KEY `EMAIL_UNIQUE` (`EMAIL`),
  KEY `FK_ESTADO` (`IDESTADO`),
  CONSTRAINT `FK_ESTADO` FOREIGN KEY (`IDESTADO`) REFERENCES `cat_estado` (`idestado`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci COMMENT='''Catalo de cliente'
1 row in set (0.02 sec)

but does not work when executed from Workbench (same error as you reported). Obviously Workbench introduces the problem.

Hi  Valeriy,

I installed 5.2.42, but the same error

regadrs
amin

Fixed as of the upcoming Workbench 5.2.43, and here's the changelog entry:

 Under certain conditions, MySQL Workbench would generate invalid SQL
 when dealing with foreign keys. 

Thank you for the report.