Bug #74882 SHOW CREATE TABLE is different in NDB

• Submitted: 16 Nov 2014 15:08
• Modified: 17 Nov 2014 6:12
• Reporter: Odis Yuck
• Status: Verified
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: 5.6.19-ndb-7.3.6-cluster-gpl, 7.3.5, 7.3.7
• OS: Linux (Centos 7 (didn't check other systems))
• CPU Architecture: Any

[16 Nov 2014 15:08] Odis Yuck

Description:
SHOW CREATE TABLE is different in NDB and that's why some applications doesn't show foreign keys. There is no space after 'FOREIGN KEY' (but it is in output with innodb).

Here is example:
http://i.imgur.com/zI54b4s.jpg

How to repeat:
CREATE TABLE `table1` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
CREATE TABLE `table2` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `table1_id` int(11) unsigned NOT NULL, PRIMARY KEY (`id`),
KEY `table1_id` (`table1_id`), CONSTRAINT `FK` FOREIGN KEY(`table1_id`) REFERENCES `table1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

SHOW CREATE TABLE table2;

DROP TABLE table2;
DROP TABLE table1;

CREATE TABLE `table1` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `table2` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `table1_id` int(11) unsigned NOT NULL, PRIMARY KEY (`id`),
KEY `table1_id` (`table1_id`), CONSTRAINT `FK` FOREIGN KEY(`table1_id`) REFERENCES `table1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SHOW CREATE TABLE table2;

DROP TABLE table2;
DROP TABLE table1;

[17 Nov 2014 6:12] MySQL Verification Team

Hello Odis Yuck,
 
Thank you for the report.
Confirmed that with NDB table DDL - in [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name) there is no space between FOREIGN KEY &  (index_col_name). 

No issues while restoring using SQL dump but yes cosmetic difference between NDB and Innodb DDL.

Thanks,
Umesh

[17 Nov 2014 6:13] MySQL Verification Team

mysql> show variables like '%version%';
+-------------------------+------------------------------------------------------+
| Variable_name           | Value                                                |
+-------------------------+------------------------------------------------------+
| innodb_version          | 5.6.21                                               |
| ndb_version             | 459527                                               |
| ndb_version_string      | ndb-7.3.7                                            |
| ndbinfo_version         | 459527                                               |
| protocol_version        | 10                                                   |
| slave_type_conversions  |                                                      |
| version                 | 5.6.21-ndb-7.3.7-cluster-commercial-advanced-log     |
| version_comment         | MySQL Cluster Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                               |
| version_compile_os      | linux-glibc2.5                                       |
+-------------------------+------------------------------------------------------+
10 rows in set (0.01 sec)

mysql> CREATE TABLE `table1` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.94 sec)

mysql> CREATE TABLE `table2` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `table1_id` int(11) unsigned NOT NULL, PRIMARY KEY (`id`),
    -> KEY `table1_id` (`table1_id`), CONSTRAINT `FK` FOREIGN KEY (`table1_id`) REFERENCES `table1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (1.57 sec)

mysql> SHOW CREATE TABLE table2\G
*************************** 1. row ***************************
       Table: table2
Create Table: CREATE TABLE `table2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `table1_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `table1_id` (`table1_id`),
 CONSTRAINT `FK` FOREIGN KEY(`table1_id`) REFERENCES `table1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> DROP TABLE table2;
Query OK, 0 rows affected (0.41 sec)

mysql> DROP TABLE table1;
Query OK, 0 rows affected (0.41 sec)

mysql> CREATE TABLE `table1` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.46 sec)

mysql> CREATE TABLE `table2` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `table1_id` int(11) unsigned NOT NULL, PRIMARY KEY (`id`),
    -> KEY `table1_id` (`table1_id`), CONSTRAINT `FK` FOREIGN KEY (`table1_id`) REFERENCES `table1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.74 sec)

mysql> SHOW CREATE TABLE table2\G
*************************** 1. row ***************************
       Table: table2
Create Table: CREATE TABLE `table2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `table1_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `table1_id` (`table1_id`),
  CONSTRAINT `FK` FOREIGN KEY (`table1_id`) REFERENCES `table1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> DROP TABLE table2;
Query OK, 0 rows affected (0.19 sec)

mysql> DROP TABLE table1;
Query OK, 0 rows affected (0.14 sec)