commit bde9c7b38362a5c292545e60abeef3b6d60bc3cc Author: Jeremy Cole Date: Tue Oct 29 15:15:17 2013 -0700 Use correct InnoDB storage type for 1-byte ENUM and SET In MariaDB 5.3, the get_innobase_type_from_mysql_type function was rewritten in revision 2661.777.35, but this rewrite failed to account for the previous special handling of ENUM, SET, and TINYINT UNSIGNED. These types have previously been mapped to InnoDB's type DATA_INT with flag DATA_UNSIGNED. After this commit they were mapped to DATA_FIXBINARY instead. A fix to revert to the old type for TINYINT UNSIGNED was later made in revision 2661.777.47, but ENUM and SET were not fixed. This InnoDB internal data type difference makes upgrades from older versions (using data files without rebuild) incompatible in a subtle way. Errors of the form "Found index whose column info does not match that of MySQL." will be logged when tables are opened. InnoDB will fail to build a per-table index "translation table" (share->idx_trans_tbl) which will cause all future index usage to fall back to finding the index by walking the index list and comparing index name instead of using a fast lookup table. Query results should be unaffected by this bug, because the on-disk representation of the two types is identical, and all usage by the MySQL layer is unaffected. This change additionally sorts the get_innobase_type_from_mysql_type cases for integer types from smallest-to-largest, and adds an explanatory comment about TINYINT UNSIGNED, ENUM, and SET. (Note that some errors in revisions 2661.777.35 and 2661.777.47 were fixed in later merges, so the diffs in those revisions do not accurately represent the final state.) See the Bazaar revisions on Launchpad: * http://bazaar.launchpad.net/~maria-captains/maria/10.0-base/revision/2661.777.35 * http://bazaar.launchpad.net/~maria-captains/maria/10.0-base/revision/2661.777.47 Change-Id: I5f0f448c959d09dbb6f2757edc331c1b7b82be84 Reviewed-by: Pavel Ivanov diff --git a/mysql-test/suite/innodb/r/innodb_1byte_data_int.result b/mysql-test/suite/innodb/r/innodb_1byte_data_int.result new file mode 100644 index 0000000..28cb85a --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_1byte_data_int.result @@ -0,0 +1,23 @@ +# Create a table with a 1-byte ENUM, 1-byte SET, and TINYINT UNSIGNED. +CREATE TABLE t1 +( +t1_enum ENUM("a", "b", "c"), +t1_set SET("a", "b", "c"), +t1_tinyint_s TINYINT, +t1_tinyint_u TINYINT UNSIGNED +) ENGINE=InnoDB; +# All t1 fields' mtypes should be 6 (DATA_INT). +SELECT +name, +mtype, +(prtype & 512) = 512 AS is_unsigned +FROM information_schema.INNODB_SYS_COLUMNS +WHERE name LIKE "t1\_%" +ORDER BY name; +name mtype is_unsigned +t1_enum 6 1 +t1_set 6 1 +t1_tinyint_s 6 0 +t1_tinyint_u 6 1 +# Cleanup +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_1byte_data_int-master.opt b/mysql-test/suite/innodb/t/innodb_1byte_data_int-master.opt new file mode 100644 index 0000000..3ad568c --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_1byte_data_int-master.opt @@ -0,0 +1 @@ +--loose-innodb-sys-columns diff --git a/mysql-test/suite/innodb/t/innodb_1byte_data_int.test b/mysql-test/suite/innodb/t/innodb_1byte_data_int.test new file mode 100644 index 0000000..26ca8a5 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_1byte_data_int.test @@ -0,0 +1,25 @@ +--source include/have_innodb.inc + +--echo # Create a table with a 1-byte ENUM, 1-byte SET, and TINYINT UNSIGNED. + +CREATE TABLE t1 +( + t1_enum ENUM("a", "b", "c"), + t1_set SET("a", "b", "c"), + t1_tinyint_s TINYINT, + t1_tinyint_u TINYINT UNSIGNED +) ENGINE=InnoDB; + +--echo # All t1 fields' mtypes should be 6 (DATA_INT). + +SELECT + name, + mtype, + (prtype & 512) = 512 AS is_unsigned +FROM information_schema.INNODB_SYS_COLUMNS +WHERE name LIKE "t1\_%" +ORDER BY name; + +--echo # Cleanup + +DROP TABLE t1; diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index b04cd9d..9c24244 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -5736,16 +5736,25 @@ get_innobase_type_from_mysql_type( *unsigned_flag = 0; switch (field->key_type()) { + /* Unsigned Integer Types */ + /* + NOTE: + There is no HA_KEYTYPE_UINT8 for TINYINT UNSIGNED to match + the storage of TINYINT (signed) as HA_KEYTYPE_INT8. + Instead, TINYINT UNSIGNED is stored as HA_KEYTYPE_BINARY, + see case HA_KEYTYPE_BINARY below. + */ case HA_KEYTYPE_USHORT_INT: - case HA_KEYTYPE_ULONG_INT: case HA_KEYTYPE_UINT24: + case HA_KEYTYPE_ULONG_INT: case HA_KEYTYPE_ULONGLONG: *unsigned_flag = DATA_UNSIGNED; /* fall through */ + /* Signed Integer Types */ + case HA_KEYTYPE_INT8: case HA_KEYTYPE_SHORT_INT: - case HA_KEYTYPE_LONG_INT: case HA_KEYTYPE_INT24: - case HA_KEYTYPE_INT8: + case HA_KEYTYPE_LONG_INT: case HA_KEYTYPE_LONGLONG: return(DATA_INT); case HA_KEYTYPE_FLOAT: @@ -5753,8 +5762,20 @@ get_innobase_type_from_mysql_type( case HA_KEYTYPE_DOUBLE: return(DATA_DOUBLE); case HA_KEYTYPE_BINARY: - if (field->type() == MYSQL_TYPE_TINY) - { // compatibility workaround + if (field->type() == MYSQL_TYPE_TINY + || field->real_type() == MYSQL_TYPE_ENUM + || field->real_type() == MYSQL_TYPE_SET) + { + /* + The TINYINT UNSIGNED, ENUM, and SET types return as + HA_KEYTYPE_BINARY from key_type(), but they are + stored in InnoDB as (DATA_INT | DATA_UNSIGNED). + The on-disk format is identical to DATA_FIXBINARY, + since it is just one byte of binary data for both. + This difference in type must however be retained for + compatibility of the stored data dictionary entries + for these columns. + */ *unsigned_flag= DATA_UNSIGNED; return DATA_INT; }