| Bug #45983 | ibmdb2i_create_index_option=1 not working for primary key | ||
|---|---|---|---|
| Submitted: | 6 Jul 2009 21:01 | Modified: | 14 Jul 2009 15:03 |
| Reporter: | Tim Clark | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DB2SE for IBM i | Severity: | S3 (Non-critical) |
| Version: | 5.1.35 | OS: | Any |
| Assigned to: | Tim Clark | CPU Architecture: | Any |
| Tags: | ibmdb2i, ibmdb2i_create_index_option, primary key | ||
[8 Jul 2009 9:10]
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/78191 3014 V Narayanan 2009-07-08 Bug#45983 ibmdb2i_create_index_option=1 not working for primary key With ibmdb2i_create_index_option set to 1, creating an IBMDB2I table with a primary key should produce an additional index that uses EBCDIC hexadecimal sorting. However, this does not work. Adding indexes that are not primary keys does work. The ibmdb2i_create_index_option should be honoured when creating a table with a primary key. This patch adds code to the create() function to check for the value of the ibmdb2i_create_index_option variable and, when appropriate, to generate a *HEX-based shadow index in DB2 for the primary key. Previously this behavior was limited to secondary indexes. Additionally, this patch restricts the creation of shadow indexes to cases in which a non-*HEX sort sequence is used, as the documentation for ibmdb2i_create_index_option describes. Previously, the shadow index would in some cases be created even when the MySQL-specific index used *HEX sorting, leading to redundant indexes. Finally, the code used to generate the list of fields for indexes and the code used to generate the SQL statement for the shadow indexes has been refactored into individual functions. @ mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45983.result Bug#45983 ibmdb2i_create_index_option=1 not working for primary key Result file for the test case. @ mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45983.test Bug#45983 ibmdb2i_create_index_option=1 not working for primary key Add tests to verify that the ibmdb2i_create_index_option is being honoured when creating a table with a primary key. @ storage/ibmdb2i/ha_ibmdb2i.cc Bug#45983 ibmdb2i_create_index_option=1 not working for primary key - Add code to the create() function to check for the value of the ibmdb2i_create_index_option variable and, when appropriate, to generate a *HEX-based shadow index in DB2 for the primary key. - Restrict the creation of shadow indexes to cases in which a non-*HEX sort sequence is used. - Refractor code used to generate the list of fields for indexes and the code used to generate the SQL statement for the shadow indexes into individual functions. @ storage/ibmdb2i/ha_ibmdb2i.h Bug#45983 ibmdb2i_create_index_option=1 not working for primary key Add function prototypes for the functions that. - Generate the list of fields for indexes - Generate the SQL statement for the shadow indexes
[10 Jul 2009 11:20]
Bugs System
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:v.narayanan@sun.com-20090709125144-y5h71bbcqkw2vmei) (merge vers: 5.4.4-alpha) (pib:11)
[13 Jul 2009 17:48]
Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090713174543-cd2x7q1gi1hzoand) (version source revid:staale.smedseng@sun.com-20090710151930-6e6kq5tp7ux1rtbh) (merge vers: 5.1.37) (pib:11)
[14 Jul 2009 15:03]
MC Brown
An entry has been added to the 5.1.37 and 5.4.4 changelogs: When creating tables using the IBMDB2I storage engine with the ibmdb2i_create_index_option option set to 1, creating an IBMDB2I table with a primary key should produce an additional index that uses EBCDIC hexadecimal sorting, but this index was not created.
[12 Aug 2009 22:11]
Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 23:03]
Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48]
Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32]
Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[7 Oct 2009 16:25]
Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
Description: With ibmdb2i_create_index_option set to 1, creating an IBMDB2I table with a primary key should produce an additional index that uses EBCDIC hexadecimal sorting. However, this does not work. Adding indexes that are not primary keys does work. How to repeat: > use test; > set ibmdb2i_create_index_option=1; > create table t1 (i int primary key) engine=ibmdb2i; If I then go the IBM i command line and DSPLNK OBJ('/qsys.lib/"""test""".lib/*'), there is only the physical file associated with the table. There is no additional index. Object link Type Attribute "t1".FILE FILE PF QSQJRN.JRN JRN QSQJRN0001.JRNRCV JRNRCV SYSCHKCST.FILE FILE LF SYSCOLUMNS.FILE FILE LF SYSCST.FILE FILE LF SYSCSTCOL.FILE FILE LF SYSCSTDEP.FILE FILE LF SYSINDEXES.FILE FILE LF SYSKEYCST.FILE FILE LF SYSKEYS.FILE FILE LF Suggested fix: Honor the ibmdb2i_create_index_option when creating a table with a primary key.