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
Triage: Needs Triage: D2 (Serious)

[9 Aug 2012 17:34] Amin Chavarria
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
[9 Aug 2012 17:49] Valeriy Kravchuk
Can you upload the problematic script?
[10 Aug 2012 4:20] Amin Chavarria
Here the script DDL

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

[11 Aug 2012 10:47] Valeriy Kravchuk
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.
[15 Aug 2012 17:27] Amin Chavarria
Hi  Valeriy,

I installed 5.2.42, but the same error

regadrs
amin
[7 Sep 2012 6:09] Philip Olson
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.