| Bug #15308 | Problem of Order with Enum Column in Primary Key | ||
|---|---|---|---|
| Submitted: | 29 Nov 2005 10:59 | Modified: | 20 Jun 2010 0:43 |
| Reporter: | Didier G. | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.0.16/5.0.17 BK | OS: | Linux (Debian 2.6/Linux Suse) |
| Assigned to: | CPU Architecture: | Any | |
[29 Nov 2005 14:35]
MySQL Verification Team
This bug not happend on 4.1.XX and only affects InnODB engine on 5.0.17.
miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.17-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database db7;
Query OK, 1 row affected (0.04 sec)
mysql> use db7
Database changed
mysql> CREATE TABLE `findrecord` (
-> `ind` enum('0','1','2') NOT NULL default '0',
-> `string1` varchar(250) NOT NULL,
-> PRIMARY KEY (`ind`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO `findrecord` VALUES ('1', '');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `findrecord` VALUES ('2', '');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SELECT * FROM `findrecord` ORDER BY string1;
ERROR 1032 (HY000): Can't find record in 'findrecord'
mysql>
mysql> ALTER TABLE `findrecord` ADD INDEX ( `string1` );
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `findrecord` ORDER BY string1;
+-----+---------+
| ind | string1 |
+-----+---------+
| 1 | |
| 2 | |
+-----+---------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE `findrecord` ADD `string2` VARCHAR( 250 ) NOT NULL ;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `findrecord` ORDER BY string2;
ERROR 1032 (HY000): Can't find record in 'findrecord'
mysql>
mysql> ALTER TABLE `findrecord` CHANGE `ind` `ind` TINYINT NOT NULL DEFAULT '0';
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM `findrecord` ORDER BY string2;
+-----+---------+---------+
| ind | string1 | string2 |
+-----+---------+---------+
| 2 | | |
| 3 | | |
+-----+---------+---------+
2 rows in set (0.01 sec)
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `findrecord` (
-> `ind` enum('0','1','2') NOT NULL default '0',
-> `string1` varchar(250) NOT NULL,
-> PRIMARY KEY (`ind`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO `findrecord` VALUES ('1', '');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `findrecord` VALUES ('2', '');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SELECT * FROM `findrecord` ORDER BY string1;
+-----+---------+
| ind | string1 |
+-----+---------+
| 1 | |
| 2 | |
+-----+---------+
2 rows in set (0.00 sec)
miguel@hegel:~/dbs/4.1> bin/mysql -uroot db7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.16-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `findrecord` (
-> `ind` enum('0','1','2') NOT NULL default '0',
-> `string1` varchar(250) NOT NULL,
-> PRIMARY KEY (`ind`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> INSERT INTO `findrecord` VALUES ('1', '');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `findrecord` VALUES ('2', '');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM `findrecord` ORDER BY string1;
+-----+---------+
| ind | string1 |
+-----+---------+
| 1 | |
| 2 | |
+-----+---------+
2 rows in set (0.00 sec)
[29 Nov 2005 14:58]
Didier G.
So do I have to come back to a 4.1 version ? or remain in 5.015? .... what are you planning to do?
[29 Nov 2005 15:15]
Didier G.
Is it a "ipod grade" bug?
[29 Nov 2005 16:20]
Heikki Tuuri
Hi! I will look at this in the next hour. Regards, Heikki
[29 Nov 2005 17:40]
Heikki Tuuri
Jan,
the reason for the bug is that in:
} else {
/* Here we handle all other data types except the
true VARCHAR, BLOB and TEXT. Note that the column
value we store may be also in a column prefix
index. */
CHARSET_INFO* cs;
ulint len;
const mysql_byte* src_start;
int error=0;
if (is_null) {
buff += key_part->length;
continue;
}
cs = key_part->field->charset();
src_start = record + key_part->offset;
if (key_part->length > 0 && cs->mbmaxlen > 1) {
len = (ulint) cs->cset->well_formed_len(cs,
(const char *) src_start,
(const char *) src_start + key_part->le\
ngth,
key_part->length / cs->mbmaxlen,
&error);
} else {
len = key_part->length;
}
memcpy(buff, src_start, len);
buff+=len;
/* Pad the unused space with spaces */
if (len < key_part->length) {
len = key_part->length - len;
memset(buff, ' ', len);
buff+=len;
}
}
you are accessing key_part->field->charset() even though the type may not be a string type! I checked that for an INT, charset->mb_maxlen == 1, while for an ENUM, charset->mb_maxlen == 3! That is, MySQL thinks the ENUM is a UTF-8 string though it really is stored in a binary format. But it might as well crash since charset is not a sensible attribute for an INT.
Fix: only if:
(mysql_type == MYSQL_TYPE_VAR_STRING
|| mysql_type == MYSQL_TYPE_STRING
|| mysql_type == MYSQL_TYPE_BIT)
&& !(field->real_type() == FIELD_TYPE_ENUM
|| field->real_type() == FIELD_TYPE_SET))
do access key_part->field->charset(). Otherwise, do not look at the charset! Check inside gdb that MYSQL_TYPE_BIT has a sensible charset. Check it separately in 4.1 and 5.0.
Regards,
Heikki
I have derived string types from:
ha_innodb.cc:
if (field->real_type() == FIELD_TYPE_ENUM
|| field->real_type() == FIELD_TYPE_SET) {
/* MySQL has field->type() a string type for these, but the
data is actually internally stored as an unsigned integer
code! */
*unsigned_flag = DATA_UNSIGNED; /* MySQL has its own unsigned
flag set to zero, even though
internally this is an unsigned
integer type */
return(DATA_INT);
}
switch (field->type()) {
/* NOTE that we only allow string types in DATA_MYSQL
and DATA_VARMYSQL */
case MYSQL_TYPE_VAR_STRING: /* old <= 4.1 VARCHAR */
case MYSQL_TYPE_VARCHAR: /* new >= 5.0.3 true VARCHAR */
if (field->binary()) {
return(DATA_BINARY);
} else if (strcmp(
field->charset()->name,
"latin1_swedish_ci") == 0) {
return(DATA_VARCHAR);
} else {
return(DATA_VARMYSQL);
}
case MYSQL_TYPE_BIT:
case MYSQL_TYPE_STRING: if (field->binary()) {
return(DATA_FIXBINARY);
} else if (strcmp(
field->charset()->name,
"latin1_swedish_ci") == 0) {
return(DATA_CHAR);
} else {
return(DATA_MYSQL);
}
case FIELD_TYPE_NEWDECIMAL:
return(DATA_FIXBINARY);
[29 Nov 2005 17:43]
Heikki Tuuri
Jan, this bug came from the bug fix of http://bugs.mysql.com/bug.php?id=14056 It is not yet in any published 4.1 version. Fix this quickly, so that it does not get into any 4.1 release! Regards, Heikki
[30 Nov 2005 6:29]
Jan Lindström
Heikki, bug #14056 is not fixed on 4.1. Should it be fixed there also ?
[30 Nov 2005 8:06]
Jan Lindström
Bit type seems to have correct charset information, below information from gdb:
Breakpoint 1, ha_innobase::store_key_val_for_row (this=0x8c72930, keynr=0,
buff=0x8c72b58 '¥' <repeats 16 times>, "test", buff_len=1, record=0x8c5a748 "") at ha_innodb.cc:2842
2842 KEY* key_info = table->key_info + keynr;
(gdb) next
2843 KEY_PART_INFO* key_part = key_info->key_part;
(gdb)
2844 KEY_PART_INFO* end = key_part + key_info->key_parts;
(gdb)
2845 char* buff_start = buff;
(gdb)
2852 DBUG_ENTER("store_key_val_for_row");
(gdb)
2879 bzero(buff, buff_len);
(gdb)
2881 for (; key_part != end; key_part++) {
(gdb)
2882 is_null = FALSE;
(gdb)
2884 if (key_part->null_bit) {
(gdb)
2895 field = key_part->field;
(gdb)
2896 mysql_type = field->type();
(gdb)
2898 if (mysql_type == MYSQL_TYPE_VARCHAR) {
(gdb)
2956 } else if (mysql_type == FIELD_TYPE_TINY_BLOB
(gdb)
3025 int error=0;
(gdb)
3027 if (is_null) {
(gdb)
3037 if (mysql_type == MYSQL_TYPE_VAR_STRING
(gdb)
3043 cs = key_part->field->charset();
(gdb)
3044 src_start = record + key_part->offset;
(gdb) p *cs
$1 = {number = 63, primary_number = 0, binary_number = 0, state = 817, csname = 0x851d053 "binary",
name = 0x851d053 "binary", comment = 0x851d05a "", tailoring = 0x0, ctype = 0x86353a0 "", to_lower = 0x86354c0 "",
to_upper = 0x86354c0 "", sort_order = 0x0, contractions = 0x0, sort_order_big = 0x0, tab_to_uni = 0x0,
tab_from_uni = 0x0, caseinfo = 0x86188a0,
state_map = 0x86501a0 "\022\001\001\001\001\001\001\001\001\032\032\032\032\032", '\001' <repeats 18 times>, "\032\b!\v\002\001\021\n\001\001\025\001\001\001\020\024\016\016\016\016\016\016\016\016\016\016\027\026\t\b\b\001\030\002\037", '\002' <repeats 11 times>, " \002\002\002\002\002\002\002\002\002\036\002\002\001\023\001\001\002\033\002\037", '\002' <repeats 11 times>, " \002\002\002\002\002\002\002\002\002\036\002\002\001\021", '\001' <repeats 75 times>...,
ident_map = 0x86502a0 "", strxfrm_multiply = 1, caseup_multiply = 1 '\001', casedn_multiply = 1 '\001',
mbminlen = 1, mbmaxlen = 1, min_sort_char = 0, max_sort_char = 255, pad_char = 0 '\0',
escape_with_backslash_is_dangerous = 0 '\0', cset = 0x8635640, coll = 0x8635600}
(gdb) p mysql_type
$2 = MYSQL_TYPE_BIT
Regards,
Jan
[30 Nov 2005 11:21]
Heikki Tuuri
Jan, good luck that this bug did not get into any 4.1 version. But http://bugs.mysql.com/bug.php?id=14056 should be fixed also in the 4.1 tree. Regards, Heikki
[1 Dec 2005 11:58]
Jan Lindström
Fix for 5.0 proposed.
[17 Jan 2006 16:18]
Alexander Ivanov
Fixed in 5.0.19.
[18 Jan 2006 0:11]
Mike Hillyer
Added note to 5.0.19 changelog:
<listitem>
<para>
Performing an <literal>ORDER BY</literal> on an
indexed <literal>ENUM</literal> column returned error. (Bug
#15308)
</para>
</listitem>
[5 May 2010 15:23]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 1:44]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:04]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:32]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:00]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 22:53]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[17 Jun 2010 12:08]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:54]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:35]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
Description: Hi, Since version 5.0.16, some query on my database don't work.(But work great in version 5.0.15) Especially those with Enum in Primary Key. Query generate a error when a column in order clause is not indexed How to repeat: -- -- Structure of table `findrecord` -- CREATE TABLE `findrecord` ( `ind` enum('0','1','2') NOT NULL default '0', `string1` varchar(250) NOT NULL, PRIMARY KEY (`ind`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Insert data in table `findrecord` -- INSERT INTO `findrecord` VALUES ('1', ''); INSERT INTO `findrecord` VALUES ('2', ''); -- -- ! Can't order by `string1` on table `findrecord` ! -- SELECT * FROM `findrecord` ORDER BY string1; mysql> ERROR 1032 (HY000): Can't find record in 'findrecord' -------------------------------------------------------------------- -- 1) Put an index on the column -------------------------------------------------------------------- ALTER TABLE `findrecord` ADD INDEX ( `string1` ); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 -- -- Now, i can order by `string1` on table `findrecord` -- SELECT * FROM `findrecord` ORDER BY string1; +-----+---------+ | ind | string1| +-----+---------+ | 1 | | | 2 | | +-----+---------+ 2 rows in set (0.00 sec) -------------------------------------------------------------------- -- Now, I add a column `string2` on table `findrecord` -------------------------------------------------------------------- ALTER TABLE `findrecord` ADD `string2` VARCHAR( 250 ) NOT NULL ; -- -- ! Can't order by `string2` on table `findrecord` ! -- SELECT * FROM `findrecord` ORDER BY string2; mysql> ERROR 1032 (HY000): Can't find record in 'findrecord' -------------------------------------------------------------------- -- 2) Modify type of column `ind` on table `findrecord` -------------------------------------------------------------------- ALTER TABLE `findrecord` CHANGE `ind` `ind` TINYINT NOT NULL DEFAULT '0'; -- -- Now, i can order by `string2` on table `findrecord` -- SELECT * FROM `findrecord` ORDER BY string2; +-----+---------+ | ind | string1| +-----+---------+ | 2 | | | 3 | | +-----+---------+ 2 rows in set (0.00 sec)