Bug #49521 SHOW CREATE TABLE on IBMDB2I tables has incorrect fk constraint format
Submitted: 7 Dec 2009 21:54 Modified: 11 Dec 2009 7:17
Reporter: Tim Clark Email Updates:
Status: Patch pending Impact on me:
None 
Category:MySQL Server: DB2SE for IBM i Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[7 Dec 2009 21:54] Tim Clark
Description:
Running a SHOW CREATE TABLE statement on an IBMDB2I table having foreign key constraints generates an improperly formatted result. The constraint information runs together with the previous key's description. The information for each constraint should be delimited by commas.

How to repeat:
CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id));
CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE);
SHOW CREATE TABLE child;

with InnoDB gives

| child | CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

but with IBMDB2I gives

| child | CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  KEY `par_ind` (`parent_id`) CONSTRAINT `Q__TEST___CHILD__PAREN00001_00001` FOREIGN KEY (`parent_id`) REFERENCES `test`
.`parent` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
) ENGINE=IBMDB2I DEFAULT CHARSET=latin1 |

Suggested fix:
Format the output correctly.
[11 Dec 2009 7:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/93623

3260 V Narayanan	2009-12-11
      Bug#49521 SHOW CREATE TABLE on IBMDB2I tables has incorrect fk constraint format
      
      The fix inserts newline and comma characters as appropriate
      into the constraint reporting code to match the formatting
      required by SHOW CREATE TABLE. Additionally, a erroneously
      duplicated copy of check_if_incompatible_data() was removed
      from db2i_constraints.cc since the correct version is already
      in ha_ibmdb2i.cc.
     @ storage/ibmdb2i/db2i_constraints.cc
        Bug#49521 SHOW CREATE TABLE on IBMDB2I tables has incorrect fk constraint format
        
        - Insert newline and comma characters into the constraint reporting
          code to match the formatting required by SHOW CREATE TABLE.
        
        - Remove an erroneous copy of check_if_incompatible_data() from
          db2i_constraints.cc.
[19 Dec 2009 8:28] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:31] Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091211164058-ycpe0f20d1c4h1gl) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:35] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[15 Jan 2010 9:02] Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:joro@sun.com-20091211140719-1jfv6bt5v0xticng) (merge vers: 5.1.42) (pib:16)
[12 Mar 2010 14:19] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:34] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:50] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)