[MDEV-4024] Found Index PRIMARY whose column info does not match that of MySQL - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.1, 5.5.30, 5.3.12
Fix Version/s: 10.0.5
Component/s: None
Labels:
None
Environment:
Linux (RHEL6)

Description

Updated by Elena
(I will leave the initial description intact at the end of this field, for the reference.)

The problem actually happens even inside the same 10.0 version, e.g. on the current 10.0-base, on an attempt to switch from XtraDB to InnoDB; also reproducible on an upgrade from 5.5, as initially described, and apparently on crossgrade from 5.6 too.

For some data types, if a table was created with XtraDB, an attempt to open it with InnoDB produces the described error. To my understanding, there is a mismatch in types inside InnoDB comparing to MySQL.

It doesn't however happen on an upgrade to MySQL 5.6.5, so I assume it's a genuine bug, probably a result of 5.6 InnoDB merge.

I've got the problem for the following data types:
ENUM
SET
YEAR
YEAR(2)

The erroneous mapping for all of them is the same, something about FIXVARBINARY vs INT, if I'm not mistaken.

The provided test case needs to be run with mysqld=--innodb command line option, assuming that XtraDB is built-in (it cannot have include/xtradb.so due to the specifics of the test). InnoDB is supposed to be present as a plugin, as it now happens in 10.0-base.
The test creates tables with a PK of various data types, then shuts down the server, restarts it with InnoDB plugin, runs mysql_upgrade and then runs SHOW CREATE for each table. As usual, MTR collects errors from the log file at the end. The following are produced as of 10.0-base revno 3467:

130112  5:18:59 [ERROR] Found index PRIMARY whose column info does not match that of MySQL.

130112  5:18:59 [ERROR] Build InnoDB index translation table for Table ./test/t_enum failed

130112  5:18:59 [ERROR] Found index PRIMARY whose column info does not match that of MySQL.

130112  5:18:59 [ERROR] Build InnoDB index translation table for Table ./test/t_set failed

130112  5:18:59 [ERROR] Found index PRIMARY whose column info does not match that of MySQL.

130112  5:18:59 [ERROR] Build InnoDB index translation table for Table ./test/t_year failed

130112  5:18:59 [ERROR] Found index PRIMARY whose column info does not match that of MySQL.

130112  5:18:59 [ERROR] Build InnoDB index translation table for Table ./test/t_year2 failed

Test case

# Run as mysql-test-run.pl --mysqld=--innodb <test name>

# (assuming that XtraDB is built in, and InnoDB exists as a plugin ha_innodb.so)

CREATE TABLE t_int (f1 INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_smallint (f1 SMALLINT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_tinyint (f1 TINYINT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_mediumint (f1 MEDIUMINT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_bigint (f1 BIGINT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_numeric (f1 NUMERIC(6,2) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_decimal (f1 DECIMAL(6,2) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_float (f1 FLOAT(6,2) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_double (f1 DOUBLE(6,2) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_bit (f1 BIT(2) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_date (f1 DATE PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_datetime (f1 DATETIME PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_datetime4 (f1 DATETIME(4) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_timestamp (f1 TIMESTAMP PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_timestamp3 (f1 TIMESTAMP(3) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_time (f1 TIME PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_time2 (f1 TIME(2) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_year (f1 YEAR PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_year2 (f1 YEAR(2) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_char (f1 CHAR(3) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_varchar (f1 VARCHAR(8) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_binary (f1 BINARY(3) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_varbinary (f1 VARBINARY(3) PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_enum (f1 ENUM('a','b') PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_set (f1 SET('a','b') PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t_text (f1 TEXT, PRIMARY KEY (f1(256))) ENGINE=InnoDB;

CREATE TABLE t_blob (f1 BLOB, PRIMARY KEY (f1(256))) ENGINE=InnoDB;

let $expect_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;

--error 0,1

--remove_file $expect_file

--write_file $expect_file

wait

EOF

--echo #

--echo # Shutdown the server

--echo #

--shutdown_server 10

--source include/wait_until_disconnected.inc

--append_file $expect_file

restart: --ignore-builtin-innodb --plugin-load=ha_innodb.so --innodb

EOF

--enable_reconnect

--source include/wait_until_connected_again.inc

--echo #

--echo # Run mysql_upgrade

--echo #

--exec $MYSQL_UPGRADE -uroot

SHOW CREATE TABLE t_int;

SHOW CREATE TABLE t_smallint;

SHOW CREATE TABLE t_tinyint;

SHOW CREATE TABLE t_mediumint;

SHOW CREATE TABLE t_bigint;

SHOW CREATE TABLE t_numeric;

SHOW CREATE TABLE t_decimal;

SHOW CREATE TABLE t_float;

SHOW CREATE TABLE t_double;

SHOW CREATE TABLE t_bit;

SHOW CREATE TABLE t_date;

SHOW CREATE TABLE t_datetime;

SHOW CREATE TABLE t_datetime4;

SHOW CREATE TABLE t_timestamp;

SHOW CREATE TABLE t_timestamp3;

SHOW CREATE TABLE t_time;

SHOW CREATE TABLE t_time2;

SHOW CREATE TABLE t_year;

SHOW CREATE TABLE t_year2;

SHOW CREATE TABLE t_char;

SHOW CREATE TABLE t_varchar;

SHOW CREATE TABLE t_binary;

SHOW CREATE TABLE t_varbinary;

SHOW CREATE TABLE t_enum;

SHOW CREATE TABLE t_set;

SHOW CREATE TABLE t_text;

SHOW CREATE TABLE t_blob;

==============================

Initial description

After upgrading from MySQL (Percona) 5.5.x to MariaDB-10.x, after a while these errors seem to appear in the logs:

130111 3:48:39 [ERROR] Found index PRIMARY whose column info does not match that of MySQL.
130111 3:48:39 [ERROR] Build InnoDB index translation table for Table ./dbname/tablename#P#p201209 failed

There is no obvious indication that the data is actually corrupted, but primary keys being broken seems like a potentially dangerous issue.

I have only seen this happen on partitioned tables (but most of my tables are partitioned so I wouldn't want to categorically say it doesn't happen on other tables).

I have another server containing the exact same data that has been initialized from empty from a mysqldump (the one reporting the error was upgraded in-place), and that one isn't producing the above error in the logs.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

innodb_1byte_data_int.patch.txt
6 kB
2013-10-30 23:44

Issue Links

is duplicated by

MDEV-5476 Found Index PRIMARY whose column info does not match that of MySQL ENUM type

Closed

relates to

MDEV-5248 Serious incompatibility and data corruption of DATETIME and DATE types due to get_innobase_type_from_mysql_type refactor combined with InnoDB Online DDL

Closed

Activity

Ascending order - Click to sort in descending order

Gordan Bobic created issue - 2013-01-11 12:15

Sergei Golubchik made changes - 2013-01-11 14:04

Field	Original Value	New Value
Assignee		Elena Stepanova [ elenst ]

Elena Stepanova added a comment - 2013-01-11 15:56

Hi Gordan,

Did you (or anybody who has enough access) do any ALTER table since the upgrade?
Can you provide the output of SHOW CREATE TABLE for the given table? If it's sensitive information, you can put it into the private section on our FTP (ftp://ftp.askmonty.org/private/) or just rename the columns. I'd prefer the unmodified version though, if it's possible.
Could you please also specify

which exact Percona version you upgraded from,
what 10.x build you are using (is it 10.0.0 binary/source release, or a build from current bzr sources, and in this case which revision),
whether you are using XtraDB or InnoDB plugin.

Thanks.

Elena Stepanova added a comment - 2013-01-11 15:56 Hi Gordan, Did you (or anybody who has enough access) do any ALTER table since the upgrade? Can you provide the output of SHOW CREATE TABLE for the given table? If it's sensitive information, you can put it into the private section on our FTP ( ftp://ftp.askmonty.org/private/ ) or just rename the columns. I'd prefer the unmodified version though, if it's possible. Could you please also specify which exact Percona version you upgraded from, what 10.x build you are using (is it 10.0.0 binary/source release, or a build from current bzr sources, and in this case which revision), whether you are using XtraDB or InnoDB plugin. Thanks.

Gordan Bobic added a comment - 2013-01-11 16:48

Definitely no ALTER TABLE statements since the upgrade.

An example of the create table has been uploaded, file name is ~~MDEV-4024~~-1.sql
The table definition is not modified in any way. There are many other similar tables that exhibit the same problem.

I have tried to fix the problem doing ALTER TABLE ... REBUILD PARTITION ... and that seems to make the problem go away for a particular partition, at least for a time, but I could have sworn that a table on which I had rebuilt the partitions had appeared with that error in the logs again afterwards.

Several different versions were upgraded from, all 5.5.x. At least the following three (multiple servers):
Percona 5.5.23-rel25.3.240.rhel6
Percona 5.5.28-rel29.1.335.rhel6
Oracle MySQL 5.6.6-m9-1.linux2.6

I am using MariaDB-10.0.0 RHEL6/CentOS6 binary rpms from the MariaDB rpm repository.

Regarding XtraDB/plugin - we were using whatever the defaults for Percona's MySQL 5.5. On 5.6, however, I don't think XtraDB/plugin would have been included since that wasn't a Percona version. I did notice that 5.6 uses InnoDB version 1.2.6 and MariaDB 10 uses 1.2.0, so I originally attributed the error in the log to that, but that doesn't explain the same problem manifesting on 5.5.x upgraded to MariaDB.

Gordan Bobic added a comment - 2013-01-11 16:48 Definitely no ALTER TABLE statements since the upgrade. An example of the create table has been uploaded, file name is MDEV-4024 -1.sql The table definition is not modified in any way. There are many other similar tables that exhibit the same problem. I have tried to fix the problem doing ALTER TABLE ... REBUILD PARTITION ... and that seems to make the problem go away for a particular partition, at least for a time, but I could have sworn that a table on which I had rebuilt the partitions had appeared with that error in the logs again afterwards. Several different versions were upgraded from, all 5.5.x. At least the following three (multiple servers): Percona 5.5.23-rel25.3.240.rhel6 Percona 5.5.28-rel29.1.335.rhel6 Oracle MySQL 5.6.6-m9-1.linux2.6 I am using MariaDB-10.0.0 RHEL6/CentOS6 binary rpms from the MariaDB rpm repository. Regarding XtraDB/plugin - we were using whatever the defaults for Percona's MySQL 5.5. On 5.6, however, I don't think XtraDB/plugin would have been included since that wasn't a Percona version. I did notice that 5.6 uses InnoDB version 1.2.6 and MariaDB 10 uses 1.2.0, so I originally attributed the error in the log to that, but that doesn't explain the same problem manifesting on 5.5.x upgraded to MariaDB.

Elena Stepanova added a comment - 2013-01-11 17:20

Gordan,
Thank you, it's easily reproducible with the provided DDL on Percona 5.5.28 => MariaDB 10.0.0 upgrade.
I will try to dig a bit more into it and then we'll see how to proceed.

Elena Stepanova added a comment - 2013-01-11 17:20 Gordan, Thank you, it's easily reproducible with the provided DDL on Percona 5.5.28 => MariaDB 10.0.0 upgrade. I will try to dig a bit more into it and then we'll see how to proceed.

Gordan Bobic added a comment - 2013-01-11 17:26

Marvellous, thank you. If you could please provide some feedback regarding the potential for actual data corruption as a consequence of this issue, that would be most appreciated.

Gordan Bobic added a comment - 2013-01-11 17:26 Marvellous, thank you. If you could please provide some feedback regarding the potential for actual data corruption as a consequence of this issue, that would be most appreciated.

Elena Stepanova added a comment - 2013-01-12 03:43 - edited

I cannot say for sure whether data corruption is possible due to this problem, as I'm not an expert at internals; as far as I can tell, in this particular place nothing critical should happen as the types are just compared there; but I suppose that conversion might fail somewhere else due to the same broken mapping. Anyway, I've updated the description and am passing it over to Monty now, who I'm sure will be able to answer at once.

I've set the tentative fix version to 10.0.1 – although it's very close to the release at the moment, I hope the fix will be primitive and fast; if not, it will probably go to 10.0.2.

Elena Stepanova added a comment - 2013-01-12 03:43 - edited I cannot say for sure whether data corruption is possible due to this problem, as I'm not an expert at internals; as far as I can tell, in this particular place nothing critical should happen as the types are just compared there; but I suppose that conversion might fail somewhere else due to the same broken mapping. Anyway, I've updated the description and am passing it over to Monty now, who I'm sure will be able to answer at once. I've set the tentative fix version to 10.0.1 – although it's very close to the release at the moment, I hope the fix will be primitive and fast; if not, it will probably go to 10.0.2.

Elena Stepanova made changes - 2013-01-12 03:43

Fix Version/s		10.0.1 [ 11400 ]
Affects Version/s		10.0.1 [ 11400 ]
Assignee	Elena Stepanova [ elenst ]	Michael Widenius [ monty ]
Description	After upgrading from MySQL (Percona) 5.5.x to MariaDB-10.x, after a while these errors seem to appear in the logs: 130111 3:48:39 [ERROR] Found index PRIMARY whose column info does not match that of MySQL. 130111 3:48:39 [ERROR] Build InnoDB index translation table for Table ./dbname/tablename#P#p201209 failed There is no obvious indication that the data is actually corrupted, but primary keys being broken seems like a potentially dangerous issue. I have only seen this happen on partitioned tables (but most of my tables are partitioned so I wouldn't want to categorically say it doesn't happen on other tables). I have another server containing the exact same data that has been initialized from empty from a mysqldump (the one reporting the error was upgraded in-place), and that one isn't producing the above error in the logs.	_Updated by Elena_ _(I will leave the initial description intact at the end of this field, for the reference.)_ The problem actually happens even inside the same 10.0 version, e.g. on the current 10.0-base, on an attempt to switch from XtraDB to InnoDB; also reproducible on an upgrade from 5.5, as initially described, and apparently on crossgrade from 5.6 too. For some data types, if a table was created with XtraDB, an attempt to open it with InnoDB produces the described error. To my understanding, there is a mismatch in types inside InnoDB comparing to MySQL. It doesn't however happen on an upgrade to MySQL 5.6.5, so I assume it's a genuine bug, probably a result of 5.6 InnoDB merge. I've got the problem for the following data types: ENUM SET YEAR YEAR(2) The erroneous mapping for all of them is the same, something about FIXVARBINARY vs INT, if I'm not mistaken. The provided test case needs to be run with mysqld=--innodb command line option, assuming that XtraDB is built-in (it cannot have include/xtradb.so due to the specifics of the test). InnoDB is supposed to be present as a plugin, as it now happens in 10.0-base. The test creates tables with a PK of various data types, then shuts down the server, restarts it with InnoDB plugin, runs mysql_upgrade and then runs SHOW CREATE for each table. As usual, MTR collects errors from the log file at the end. The following are produced as of 10.0-base revno 3467: {noformat} 130112 5:18:59 [ERROR] Found index PRIMARY whose column info does not match that of MySQL. 130112 5:18:59 [ERROR] Build InnoDB index translation table for Table ./test/t_enum failed 130112 5:18:59 [ERROR] Found index PRIMARY whose column info does not match that of MySQL. 130112 5:18:59 [ERROR] Build InnoDB index translation table for Table ./test/t_set failed 130112 5:18:59 [ERROR] Found index PRIMARY whose column info does not match that of MySQL. 130112 5:18:59 [ERROR] Build InnoDB index translation table for Table ./test/t_year failed 130112 5:18:59 [ERROR] Found index PRIMARY whose column info does not match that of MySQL. 130112 5:18:59 [ERROR] Build InnoDB index translation table for Table ./test/t_year2 failed {noformat} Test case {code:sql} # Run as mysql-test-run.pl --mysqld=--innodb <test name> # (assuming that XtraDB is built in, and InnoDB exists as a plugin ha_innodb.so) CREATE TABLE t_int (f1 INT PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_smallint (f1 SMALLINT PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_tinyint (f1 TINYINT PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_mediumint (f1 MEDIUMINT PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_bigint (f1 BIGINT PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_numeric (f1 NUMERIC(6,2) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_decimal (f1 DECIMAL(6,2) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_float (f1 FLOAT(6,2) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_double (f1 DOUBLE(6,2) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_bit (f1 BIT(2) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_date (f1 DATE PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_datetime (f1 DATETIME PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_datetime4 (f1 DATETIME(4) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_timestamp (f1 TIMESTAMP PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_timestamp3 (f1 TIMESTAMP(3) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_time (f1 TIME PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_time2 (f1 TIME(2) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_year (f1 YEAR PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_year2 (f1 YEAR(2) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_char (f1 CHAR(3) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_varchar (f1 VARCHAR(8) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_binary (f1 BINARY(3) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_varbinary (f1 VARBINARY(3) PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_enum (f1 ENUM('a','b') PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_set (f1 SET('a','b') PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t_text (f1 TEXT, PRIMARY KEY (f1(256))) ENGINE=InnoDB; CREATE TABLE t_blob (f1 BLOB, PRIMARY KEY (f1(256))) ENGINE=InnoDB; let $expect_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; --error 0,1 --remove_file $expect_file --write_file $expect_file wait EOF --echo # --echo # Shutdown the server --echo # --shutdown_server 10 --source include/wait_until_disconnected.inc --append_file $expect_file restart: --ignore-builtin-innodb --plugin-load=ha_innodb.so --innodb EOF --enable_reconnect --source include/wait_until_connected_again.inc --echo # --echo # Run mysql_upgrade --echo # --exec $MYSQL_UPGRADE -uroot SHOW CREATE TABLE t_int; SHOW CREATE TABLE t_smallint; SHOW CREATE TABLE t_tinyint; SHOW CREATE TABLE t_mediumint; SHOW CREATE TABLE t_bigint; SHOW CREATE TABLE t_numeric; SHOW CREATE TABLE t_decimal; SHOW CREATE TABLE t_float; SHOW CREATE TABLE t_double; SHOW CREATE TABLE t_bit; SHOW CREATE TABLE t_date; SHOW CREATE TABLE t_datetime; SHOW CREATE TABLE t_datetime4; SHOW CREATE TABLE t_timestamp; SHOW CREATE TABLE t_timestamp3; SHOW CREATE TABLE t_time; SHOW CREATE TABLE t_time2; SHOW CREATE TABLE t_year; SHOW CREATE TABLE t_year2; SHOW CREATE TABLE t_char; SHOW CREATE TABLE t_varchar; SHOW CREATE TABLE t_binary; SHOW CREATE TABLE t_varbinary; SHOW CREATE TABLE t_enum; SHOW CREATE TABLE t_set; SHOW CREATE TABLE t_text; SHOW CREATE TABLE t_blob; {code} ============================== h3. Initial description After upgrading from MySQL (Percona) 5.5.x to MariaDB-10.x, after a while these errors seem to appear in the logs: 130111 3:48:39 [ERROR] Found index PRIMARY whose column info does not match that of MySQL. 130111 3:48:39 [ERROR] Build InnoDB index translation table for Table ./dbname/tablename#P#p201209 failed There is no obvious indication that the data is actually corrupted, but primary keys being broken seems like a potentially dangerous issue. I have only seen this happen on partitioned tables (but most of my tables are partitioned so I wouldn't want to categorically say it doesn't happen on other tables). I have another server containing the exact same data that has been initialized from empty from a mysqldump (the one reporting the error was upgraded in-place), and that one isn't producing the above error in the logs.

Sergei Golubchik made changes - 2013-01-12 11:54

Assignee

Michael Widenius [ monty ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2013-02-12 23:22

Fix Version/s		10.0.2 [ 11900 ]
Fix Version/s	10.0.1 [ 11400 ]

Sergei Golubchik added a comment - 2013-04-04 15:17

This unintentional difference between XtraDB and InnoDB was introduced in 5.3. Your test case should show these warnings even in 5.3.

I'm not sure what I can do about it, fixing XtraDB in 5.3 to match InnoDB or vice versa will, again, create a similar incompatibility between pre-fix XtraDB (or InnoDB, whichever we fix) tables and post-fix tables.

Besides, it seems that the warning is not dangerous, it looks like no data corruption is possible, despite the warning.

Sergei Golubchik added a comment - 2013-04-04 15:17 This unintentional difference between XtraDB and InnoDB was introduced in 5.3. Your test case should show these warnings even in 5.3. I'm not sure what I can do about it, fixing XtraDB in 5.3 to match InnoDB or vice versa will, again, create a similar incompatibility between pre-fix XtraDB (or InnoDB, whichever we fix) tables and post-fix tables. Besides, it seems that the warning is not dangerous, it looks like no data corruption is possible, despite the warning.

Sergei Golubchik made changes - 2013-04-04 15:19

Affects Version/s		5.3.12 [ 12000 ]
Affects Version/s		5.5.30 [ 11800 ]

Sergei Golubchik made changes - 2013-04-04 15:20

Affects Version/s

10.0.0 [ 10000 ]

Sergei Golubchik made changes - 2013-04-04 15:20

Priority

Major [ 3 ]

Minor [ 4 ]

Sergei Golubchik made changes - 2013-04-05 13:55

Fix Version/s		10.0.3 [ 12900 ]
Fix Version/s	10.0.2 [ 11900 ]

Sergei Golubchik made changes - 2013-06-04 15:39

Fix Version/s		10.0.4 [ 13101 ]
Fix Version/s	10.0.3 [ 12900 ]

Jan Lindström (Inactive) added a comment - 2013-07-29 12:36

Inside a InnoDB the data is not corrupted. But what happens when you query is not fully known to me, if data type in MySQL and InnoDB are not exactly the same that could lead to data truncation, rounding, etc. Similarly, if you insert/update using MySQL data types and they are then incorrectly resolved to InnoDB data types similar could happen. Why both XtraDB and InnoDB use the same storage engine name ?

R: Jan

Jan Lindström (Inactive) added a comment - 2013-07-29 12:36 Inside a InnoDB the data is not corrupted. But what happens when you query is not fully known to me, if data type in MySQL and InnoDB are not exactly the same that could lead to data truncation, rounding, etc. Similarly, if you insert/update using MySQL data types and they are then incorrectly resolved to InnoDB data types similar could happen. Why both XtraDB and InnoDB use the same storage engine name ? R: Jan

Jan Lindström (Inactive) added a comment - 2013-07-29 13:16

Fundamental issue here is that when you do

create table a(date primary key) engine=innodb;

It is not 100% clear is this table using Oracle innodb implementation of Percona XtraDB innodb implementation or soon
for FusionIO work we might have also MariaDB innodb implementation. If we would have

create table a(data primary key) engine=xtradb; // Percona innodb
create table b(data primary key) engine=innodb; // Oracle innodb
create table c(data primary key) engine=mariadb; // MariaDB innodb

If you try to access table b but you only have engine a available it should give you nice understandable error message.

Jan Lindström (Inactive) added a comment - 2013-07-29 13:16 Fundamental issue here is that when you do create table a(date primary key) engine=innodb; It is not 100% clear is this table using Oracle innodb implementation of Percona XtraDB innodb implementation or soon for FusionIO work we might have also MariaDB innodb implementation. If we would have create table a(data primary key) engine=xtradb; // Percona innodb create table b(data primary key) engine=innodb; // Oracle innodb create table c(data primary key) engine=mariadb; // MariaDB innodb If you try to access table b but you only have engine a available it should give you nice understandable error message.

Jan Lindström (Inactive) added a comment - 2013-07-29 15:05

Based on code reading we have type #define DATA_FIXBINARY 3 /* binary string of fixed length / and type #define DATA_INT 6 / integer: can be any size 1 - 8 bytes */. I could not get any incorrect results from simple test. Actual message is harmless, innodb just does not create translation table.

Jan Lindström (Inactive) added a comment - 2013-07-29 15:05 Based on code reading we have type #define DATA_FIXBINARY 3 /* binary string of fixed length / and type #define DATA_INT 6 / integer: can be any size 1 - 8 bytes */. I could not get any incorrect results from simple test. Actual message is harmless, innodb just does not create translation table.

Jan Lindström (Inactive) added a comment - 2013-07-29 18:47

Reason for this is that XtraDB has:

if (field->real_type() == MYSQL_TYPE_ENUM

field->real_type() == MYSQL_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); }

While InnoDB has very different implementation, this:

case HA_KEYTYPE_BINARY:
if (field->type() == MYSQL_TYPE_TINY)

{ // compatibility workaround *unsigned_flag= DATA_UNSIGNED; return DATA_INT; }

return(DATA_FIXBINARY);

Thus, the different data types on dictionary. This is a bug.

Jan Lindström (Inactive) added a comment - 2013-07-29 18:47 Reason for this is that XtraDB has: if (field->real_type() == MYSQL_TYPE_ENUM field->real_type() == MYSQL_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); } While InnoDB has very different implementation, this: case HA_KEYTYPE_BINARY: if (field->type() == MYSQL_TYPE_TINY) { // compatibility workaround *unsigned_flag= DATA_UNSIGNED; return DATA_INT; } return(DATA_FIXBINARY); Thus, the different data types on dictionary. This is a bug.

Jan Lindström (Inactive) added a comment - 2013-07-30 08:17

I have now quite confident that this is what happens:

If table is created using XtraDB InnoDB the used type is DATA_INT and that type is also used when values are stored when innodb plugin is used. Only thing that the server notes is that MySQL data dictionary and InnoDB data dictionary are not sync. Conversion between MySQL and InnoDB is done based on InnoDB dictionary.

If table is created using innodb plugin the used type is FIXBINARY and again same type is used with XtraDB. We can easily fix the code so that all new created tables will use in innodb plugin DATA_INT, we may not change the existing tables, that is not safe because DATA_INT and FIXBINARY uses different storage format.

Jan Lindström (Inactive) added a comment - 2013-07-30 08:17 I have now quite confident that this is what happens: If table is created using XtraDB InnoDB the used type is DATA_INT and that type is also used when values are stored when innodb plugin is used. Only thing that the server notes is that MySQL data dictionary and InnoDB data dictionary are not sync. Conversion between MySQL and InnoDB is done based on InnoDB dictionary. If table is created using innodb plugin the used type is FIXBINARY and again same type is used with XtraDB. We can easily fix the code so that all new created tables will use in innodb plugin DATA_INT, we may not change the existing tables, that is not safe because DATA_INT and FIXBINARY uses different storage format.

Sergei Golubchik made changes - 2013-08-16 16:27

Fix Version/s		10.0.5 [ 13201 ]
Fix Version/s	10.0.4 [ 13101 ]

Jeremy Cole added a comment - 2013-10-30 23:44

We found this bug during upgrade. Please see attached a patch for the bug. However, fixing it is not quite as trivial as restoring the correct behavior. Each table affected must be adjusted back to the correct type/value. This could be done by the user (using ALTER TABLE ... ENGINE=InnoDB) or a patch could be made to automatically adjust this type (without copying any data) on e.g. table open.

I would find it rather irresponsible to leave this bug in MariaDB. Aside from its disabling the index translation table and generating complaintive log messages, it has a reasonably high chance of causing future more severe breakage if InnoDB changes its behavior and suddenly starts failing to understand ENUM/SET data in DATA_FIXBINARY columns. The fix is in theory quite simple (an InnoDB data dictionary fixup one time per table) – it should be done sooner rather than later.

The commit message from my patch follows, since it contains a reasonably complete explanation:

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 <name> 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:

Jeremy Cole added a comment - 2013-10-30 23:44 We found this bug during upgrade. Please see attached a patch for the bug. However, fixing it is not quite as trivial as restoring the correct behavior. Each table affected must be adjusted back to the correct type/value. This could be done by the user (using ALTER TABLE ... ENGINE=InnoDB) or a patch could be made to automatically adjust this type (without copying any data) on e.g. table open. I would find it rather irresponsible to leave this bug in MariaDB. Aside from its disabling the index translation table and generating complaintive log messages, it has a reasonably high chance of causing future more severe breakage if InnoDB changes its behavior and suddenly starts failing to understand ENUM/SET data in DATA_FIXBINARY columns. The fix is in theory quite simple (an InnoDB data dictionary fixup one time per table) – it should be done sooner rather than later. The commit message from my patch follows, since it contains a reasonably complete explanation: 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 <name> 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

Jeremy Cole made changes - 2013-10-30 23:44

Attachment

innodb_1byte_data_int.patch.txt [ 24103 ]

Jeremy Cole added a comment - 2013-10-30 23:47

Note that the patch contributed here (innodb_1byte_data_int.patch.txt) is given under the New BSD License.

Jeremy Cole added a comment - 2013-10-30 23:47 Note that the patch contributed here (innodb_1byte_data_int.patch.txt) is given under the New BSD License.

Sergei Golubchik made changes - 2013-10-30 23:53

Priority

Minor [ 4 ]

Major [ 3 ]

Sergei Golubchik made changes - 2013-11-01 00:21

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2013-11-01 15:38

Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Jeremy Cole added a comment - 2013-11-01 22:54

What's the fix?

Jeremy Cole added a comment - 2013-11-01 22:54 What's the fix?

Sergei Golubchik added a comment - 2013-11-02 01:37

http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/3874

Sergei Golubchik added a comment - 2013-11-02 01:37 http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/3874

Jeremy Cole added a comment - 2013-11-06 00:21

That is not really an adequate fix, IMHO. Also, why strip out useful explanatory comments about these special cases that I provided in my patch?

Additionally, this bug also affects YEAR type which is internally stored as TINYINT UNSIGNED equivalent.

Jeremy Cole added a comment - 2013-11-06 00:21 That is not really an adequate fix, IMHO. Also, why strip out useful explanatory comments about these special cases that I provided in my patch? Additionally, this bug also affects YEAR type which is internally stored as TINYINT UNSIGNED equivalent.

Elena Stepanova made changes - 2013-12-24 11:06

Link

This issue is duplicated by ~~MDEV-5476~~ [ ~~MDEV-5476~~ ]

Sergei Golubchik made changes - 2014-01-06 15:02

Link

This issue relates to ~~MDEV-5248~~ [ ~~MDEV-5248~~ ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 25800 ]

MariaDB v2 [ 43614 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 43614 ]

MariaDB v3 [ 62754 ]

Sergei Golubchik made changes - 2021-12-06 21:38

Workflow

MariaDB v3 [ 62754 ]

MariaDB v4 [ 146322 ]

People

Assignee:: Sergei Golubchik

Reporter:: Gordan Bobic

Votes:: 0 Vote for this issue

Watchers:: 10 Start watching this issue

Dates

Created:: 2013-01-11 12:15

Updated:: 2014-01-06 15:02

Resolved:: 2013-11-01 15:38

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Initial description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration