[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 Created: 2013-11-06  Updated: 2014-01-06  Resolved: 2013-11-14

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 10.0.5
Fix Version/s: 10.0.6

Type: Bug Priority: Blocker
Reporter: Jeremy Cole Assignee: Sergei Golubchik
Resolution: Fixed Votes: 2
Labels: date, datetime, innodb, online-ddl
Environment:

All

Issue Links:
Relates
relates to MDEV-4024 Found Index PRIMARY whose column info... Closed

 Description   

Note that this issue has been discussed somewhat in MDEV-4024, but this is a wider problem and not a duplicate.

Per the discussion in MDEV-4024, get_innobase_type_from_mysql_type was refactored in the following commits:

In addition to the incompatibilities in TINYINT UNSIGNED, ENUM, SET, and YEAR described in MDEV-4024, the DATETIME and DATE types are affected in a much more serious way.

Using DATETIME as an example (and the MySQL 5.5 code, but it doesn't actually matter), Field_datetime returns HA_KEYTYPE_ULONGLONG as its key_type:

And inside InnoDB in MySQL 5.5, it is mapped to DATA_INT in get_innobase_type_from_mysql_type:

However, in MySQL 5.5, it is NOT marked as DATA_UNSIGNED despite it being treated by the handler as HA_KEYTYPE_ULONGLONG. This should perhaps be considered a bug in MySQL/InnoDB, but it is ancient history at this point, and cannot be changed without extensive consideration.

In MariaDB since the refactor of get_innobase_type_from_mysql_type which occurred in the 5.3 series (and is now included in 5.5 and 10.0), DATETIME is now handled by the generic case for HA_KEYTYPE_ULONGLONG, which does set DATA_UNSIGNED (because this code is shared with BIGINT UNSIGNED, of course):

InnoDB's on-disk integer format for all sizes reverse the meaning of the sign bit and flips it with XOR on write and read (to allow the big endian byte representation of integers to sort correctly as binary data):

Nothing breaks immediately because of this, due to the fact that get_innobase_type_from_mysql_type is largely used only for table creation.

Where this breaks badly, is if InnoDB Online DDL is ever exercised on such a table. In that case the new table will be created using MariaDB's definition of these columns rather than MySQL's. When the values are copied from one table to another, it will be done using buffers inside InnoDB without reinterpreting the values, which will cause them to become corrupted in the destination table.

Additionally, the same problem exists in reverse with tables created on MariaDB during cross-grade to MySQL 5.6 – the exact same misinterpretation will occur.

Unfortunately there is not a very good way to determine whether any given table or column may be corrupted or how to properly interpret its values. Furthermore once corruption has occurred, single tables may contain a mix of corrupted-sign-bit and uncorrupted records, making either upgrade or downgrade impossible to resolve the situation.

How to repeat

# On a version of MySQL or any older release prior to the refactoring in 5.3:
 
 
 
set session time_zone="+00:00";
 
 
 
drop table if exists t_dt;
 
create table t_dt (
 
  vc varchar(100) not null,
 
  dt datetime not null,
 
  primary key (vc)
 
) engine=innodb; 
 
 
insert into t_dt (vc, dt)
 
values
 
  ("0000-00-00 00:00:00", "0000-00-00 00:00:00"),
 
  ("0450-12-31 12:34:56", "0450-12-31 12:34:56"),
 
  ("2013-11-04 12:34:56", "2013-11-04 12:34:56"),
 
  ("9876-11-04 12:34:56", "9876-11-04 12:34:56"),
 
  ("9999-12-31 23:59:59", "9999-12-31 23:59:59");
 
 
 
drop table if exists t_d;
 
create table t_d (
 
  vc varchar(100) not null,
 
  d date not null,
 
  primary key (vc)
 
) engine=innodb; 
 
 
insert into t_d (vc, d)
 
values
 
  ("0000-00-00", "0000-00-00"),
 
  ("0450-12-31", "0450-12-31"),
 
  ("2013-11-04", "2013-11-04"),
 
  ("9876-11-04", "9876-11-04"),
 
  ("9999-12-31", "9999-12-31");
 
 
 
# Verify that all values are correct:
 
 
 
set session time_zone="+00:00";
 
select * from t_dt;
 
select * from t_d;
 
 
 
    # +---------------------+---------------------+
 
    # | vc                  | dt                  |
 
    # +---------------------+---------------------+
 
    # | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
 
    # | 0450-12-31 12:34:56 | 0450-12-31 12:34:56 |
 
    # | 2013-11-04 12:34:56 | 2013-11-04 12:34:56 |
 
    # | 9876-11-04 12:34:56 | 9876-11-04 12:34:56 |
 
    # | 9999-12-31 23:59:59 | 9999-12-31 23:59:59 |
 
    # +---------------------+---------------------+
 
 
 
    # +------------+------------+
 
    # | vc         | d          |
 
    # +------------+------------+
 
    # | 0000-00-00 | 0000-00-00 |
 
    # | 0450-12-31 | 0450-12-31 |
 
    # | 2013-11-04 | 2013-11-04 |
 
    # | 9876-11-04 | 9876-11-04 |
 
    # | 9999-12-31 | 9999-12-31 |
 
    # +------------+------------+
 
 
 
# Upgrade to MariaDB 10.0 (10.0.4 in this case), using normal process.
 
 
 
## Exercise for the reader.
 
 
 
# Verify that all values are correct:
 
 
 
set session time_zone="+00:00";
 
select * from t_dt;
 
select * from t_d;
 
 
 
# Omitted.
 
 
 
# Check the InnoDB internal type for the columns:
 
 
 
select
 
  innodb_sys_tables.name as table_name,
 
  innodb_sys_columns.name as column_name,
 
  prtype & 0xff as mysql_type,
 
  mtype as innodb_type,
 
  prtype & 0x200 = 0x200 as is_unsigned
 
from information_schema.innodb_sys_columns
 
join information_schema.innodb_sys_tables using (table_id)
 
where innodb_sys_tables.name like "test/t_%"
 
  and innodb_sys_columns.name not in ("vc", "x");
 
 
 
    # +------------+-------------+------------+-------------+-------------+
 
    # | table_name | column_name | mysql_type | innodb_type | is_unsigned |
 
    # +------------+-------------+------------+-------------+-------------+
 
    # | test/t_d   | d           |         10 |           6 |           0 |
 
    # | test/t_dt  | dt          |         12 |           6 |           0 |
 
    # +------------+-------------+------------+-------------+-------------+
 
 
 
# Issue some ALTER that will use InnoDB Online DDL:
 
 
 
alter table t_dt add x int;
 
alter table t_d add x int;
 
 
 
# Notice that values are corrupted:
 
 
 
set session time_zone="+00:00";
 
select * from t_dt;
 
select * from t_d;
 
 
 
    # +---------------------+---------------------+------+
 
    # | vc                  | dt                  | x    |
 
    # +---------------------+---------------------+------+
 
    # | 0000-00-00 00:00:00 | 203-68-54 77:58:08 | NULL |
 
    # | 0450-12-31 12:34:56 | 653-80-85 89:92:64 | NULL |
 
    # | 2013-11-04 12:34:56 | 216-79-58 89:92:64 | NULL |
 
    # | 9876-11-04 12:34:56 | 79-79-58 89:92:64 | NULL |
 
    # | 9999-12-31 23:59:59 | 02-80-86 01:17:67 | NULL |
 
    # +---------------------+---------------------+------+
 
 
 
    # +------------+------------+------+
 
    # | vc         | d          | x    |
 
    # +------------+------------+------+
 
    # | 0000-00-00 | @384-00-00 | NULL |
 
    # | 0450-12-31 | @834-12-31 | NULL |
 
    # | 2013-11-04 | B397-11-04 | NULL |
 
    # | 9876-11-04 | J260-11-04 | NULL |
 
    # | 9999-12-31 | J383-12-31 | NULL |
 
    # +------------+------------+------+
 
 
 
# Notice that the types have changed:
 
 
 
select
 
  innodb_sys_tables.name as table_name,
 
  innodb_sys_columns.name as column_name,
 
  prtype & 0xff as mysql_type,
 
  mtype as innodb_type,
 
  prtype & 0x200 = 0x200 as is_unsigned
 
from information_schema.innodb_sys_columns
 
join information_schema.innodb_sys_tables using (table_id)
 
where innodb_sys_tables.name like "test/t_%"
 
  and innodb_sys_columns.name not in ("vc", "x");
 
 
 
  # +------------+-------------+------------+-------------+-------------+
 
  # | table_name | column_name | mysql_type | innodb_type | is_unsigned |
 
  # +------------+-------------+------------+-------------+-------------+
 
  # | test/t_d   | d           |         10 |           6 |           1 |
 
  # | test/t_dt  | dt          |         12 |           6 |           1 |
 
  # +------------+-------------+------------+-------------+-------------+



 Comments   
Comment by Jeremy Cole [ 2013-11-06 ]

Apologies for the terrible formatting. I didn't realize wiki markup was allowed/used. Should look better now.

Comment by VAROQUI Stephane [ 2013-11-08 ]

For my personal knowledge why not reimplementing online alter table the way it is done by the toolkits: full table copy. assync binlog catchup and rename table. Independent storage engine DDL change

It seams that the time spend on fixing would be better use for a more generic solution. Covering non transactional engines as well.

I'm using the toolkits and so far scary to use something else provide by any storage engine . May be the lack internal knowledge of every storage design push me to trust a solution that recreate a table from scratch. And may be the need of analyze and optimize table push me more in that feeling .

Comment by Jorge Manuel Silva [ 2013-11-08 ]

I am also able to reproduce this issue:

I started with MariaDB 5.3.12
Ran the creation and insertion SQL
Checked the data
Upgraded to MariaDB 10.0.5
Checked the data
Ran the ALTER table
And spooted the data corruption said here.

I then retried all the process from scratch but after upgrading to MariaDB 10.0.5 added to my.ini:
old_alter_table = ON

And with that switch I am unable to reproduce this bug.

Comment by Sergei Golubchik [ 2013-11-11 ]

This seems to the the full list of types where mtype/unsigned_flag change between InnoDB in MariDB-10.0.5 and MySQL-5.6:

BIT
DATE
DATETIME
DECIMAL UNSIGNED
DOUBLE UNSIGNED
FLOAT UNSIGNED
TIMESTAMP
YEAR

Comment by Sergei Golubchik [ 2013-11-11 ]

jeremycole — here's the proposed fix: http://bazaar.launchpad.net/~maria-captains/maria/10.0-serg/revision/3899

Comment by Jeremy Cole [ 2013-11-12 ]

I've sent a review by email cc the developer list.

Comment by Elena Stepanova [ 2013-11-13 ]

I've run some basic tests on the revision above.

The general test scenario:

  • bootstrap and start "old" server, all defaults (apart from plugin vs builtin innodb, where indicated);
  • create table (taken from the bugfix test):

CREATE TABLE t1
(
t1_BIGINT BIGINT,
t1_BIGINT_UNSIGNED BIGINT UNSIGNED,
t1_BINARY_100 BINARY(100),
t1_BIT_2 BIT(2),
t1_BIT_20 BIT(20),
t1_BLOB BLOB,
t1_CHAR_100 CHAR(100),
t1_CHAR_100_BINARY CHAR(100) BINARY,
t1_DATE DATE,
t1_DATETIME DATETIME,
t1_DATETIME_6 DATETIME(6),
t1_DECIMAL_10_3 DECIMAL(10,3),
t1_DECIMAL_10_3_UNSIGNED DECIMAL(10,3) UNSIGNED,
t1_DOUBLE DOUBLE,
t1_DOUBLE_UNSIGNED DOUBLE UNSIGNED,
t1_ENUM ENUM('a', 'b', 'c'),
t1_ENUM_BINARY ENUM('a','b') BINARY,
t1_ENUM_256 ENUM('a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
'a10', 'a11', 'a12', 'a13', 'a14', 'a15', 'a16', 'a17', 'a18', 'a19',
'a20', 'a21', 'a22', 'a23', 'a24', 'a25', 'a26', 'a27', 'a28', 'a29',
'a30', 'a31', 'a32', 'a33', 'a34', 'a35', 'a36', 'a37', 'a38', 'a39',
'a40', 'a41', 'a42', 'a43', 'a44', 'a45', 'a46', 'a47', 'a48', 'a49',
'a50', 'a51', 'a52', 'a53', 'a54', 'a55', 'a56', 'a57', 'a58', 'a59',
'a60', 'a61', 'a62', 'a63', 'a64', 'a65', 'a66', 'a67', 'a68', 'a69',
'a70', 'a71', 'a72', 'a73', 'a74', 'a75', 'a76', 'a77', 'a78', 'a79',
'a80', 'a81', 'a82', 'a83', 'a84', 'a85', 'a86', 'a87', 'a88', 'a89',
'a90', 'a91', 'a92', 'a93', 'a94', 'a95', 'a96', 'a97', 'a98', 'a99',
'a100', 'a101', 'a102', 'a103', 'a104', 'a105', 'a106', 'a107', 'a108',
'a109', 'a110', 'a111', 'a112', 'a113', 'a114', 'a115', 'a116', 'a117',
'a118', 'a119', 'a120', 'a121', 'a122', 'a123', 'a124', 'a125', 'a126',
'a127', 'a128', 'a129', 'a130', 'a131', 'a132', 'a133', 'a134', 'a135',
'a136', 'a137', 'a138', 'a139', 'a140', 'a141', 'a142', 'a143', 'a144',
'a145', 'a146', 'a147', 'a148', 'a149', 'a150', 'a151', 'a152', 'a153',
'a154', 'a155', 'a156', 'a157', 'a158', 'a159', 'a160', 'a161', 'a162',
'a163', 'a164', 'a165', 'a166', 'a167', 'a168', 'a169', 'a170', 'a171',
'a172', 'a173', 'a174', 'a175', 'a176', 'a177', 'a178', 'a179', 'a180',
'a181', 'a182', 'a183', 'a184', 'a185', 'a186', 'a187', 'a188', 'a189',
'a190', 'a191', 'a192', 'a193', 'a194', 'a195', 'a196', 'a197', 'a198',
'a199', 'a200', 'a201', 'a202', 'a203', 'a204', 'a205', 'a206', 'a207',
'a208', 'a209', 'a210', 'a211', 'a212', 'a213', 'a214', 'a215', 'a216',
'a217', 'a218', 'a219', 'a220', 'a221', 'a222', 'a223', 'a224', 'a225',
'a226', 'a227', 'a228', 'a229', 'a230', 'a231', 'a232', 'a233', 'a234',
'a235', 'a236', 'a237', 'a238', 'a239', 'a240', 'a241', 'a242', 'a243',
'a244', 'a245', 'a246', 'a247', 'a248', 'a249', 'a250', 'a251', 'a252',
'a253', 'a254', 'a255', 'a256'),
t1_FLOAT FLOAT,
t1_FLOAT_UNSIGNED FLOAT UNSIGNED,
t1_INT INT,
t1_INT_UNSIGNED INT UNSIGNED,
t1_LONGBLOB LONGBLOB,
t1_LONGTEXT LONGTEXT,
t1_MEDIUMBLOB MEDIUMBLOB,
t1_MEDIUMINT MEDIUMINT,
t1_MEDIUMINT_UNSIGNED MEDIUMINT UNSIGNED,
t1_MEDIUMTEXT MEDIUMTEXT,
t1_SET SET('a', 'b', 'c'),
t1_SET_BINARY SET('a','b') BINARY,
t1_SET_9 SET('a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9'),
t1_SMALLINT SMALLINT,
t1_SMALLINT_UNSIGNED SMALLINT UNSIGNED,
t1_TEXT TEXT,
t1_TIME TIME,
t1_TIME_4 TIME(4),
t1_TIMESTAMP TIMESTAMP,
t1_TIMESTAMP_5 TIMESTAMP(5),
t1_TINYBLOB TINYBLOB,
t1_TINYINT TINYINT,
t1_TINYINT_UNSIGNED TINYINT UNSIGNED,
t1_TINYTEXT TINYTEXT,
t1_VARBINARY_100 VARBINARY(100),
t1_VARCHAR_10 VARCHAR(10),
t1_VARCHAR_10_BINARY VARCHAR(10) BINARY,
t1_VARCHAR_500 VARCHAR(500),
t1_VARCHAR_500_BINARY VARCHAR(500) BINARY,
t1_YEAR_2 YEAR(2),
t1_YEAR_4 YEAR(4)
) ENGINE=InnoDB;

  • insert a few rows into the table;
  • select from the table (1.result);
  • stop server;
  • start the "new" server;
  • select from the table (2.result);
  • alter table add column x int;
  • select from the table (3.result);
  • compare 1.result to 2.result (should be identical)
  • compare 2.result and 3.result (should differ by x column)

The complete list of checked upgrade paths is below, if you see any gaps, please let me know.

Summary:

  • for 5.5 + InnoDB plugin => 10.0-before-fix I didn't get any visible data corruption, so I'm not sure whether this part of the testing is reliable;
  • otherwise for X => 10.0-before-fix I got visible data corruption on DATE and DATETIME (only);
  • for X => 10.0-with-fix I did not get any visible data corruption;
  • neither 10.0-before-fix => 5.6 nor 10.0-with-fix => 5.6 work, I filed a separate bug MDEV-5285 for that (there is also a comment there).

Upgrade paths
----------------

Upgrade to 10.0-before-fix:

5.5 => 10.0 corrupt
5.6 => 10.0 corrupt
5.5 + plugin => 10.0 ok
5.7 => 10.0 corrupt
10.0.1 => 10.0 ok
mysql-5.5.30 => 10.0 corrupt

Upgrade to 10.0-with-fix:

5.5 => 10.0-serg ok
5.5 + plugin => 10.0-serg ok
5.6 => 10.0-serg ok
5.7 => 10.0-serg ok
10.0.5 => 10.0-serg ok
10.0.4 => 10.0-serg ok
10.0.3 => 10.0-serg ok
10.0.2 => 10.0-serg ok
10.0.1 => 10.0-serg ok
10.0.0 => 10.0-serg ok
5.5.21 => 10.0-serg ok
5.5.21 + plugin => 10.0-serg ok
5.5.23 => 10.0-serg ok
5.5.25 + plugin => 10.0-serg ok
5.5.28a + plugin => 10.0-serg ok
5.5.29 => 10.0-serg ok
5.5.30 + plugin => 10.0-serg ok
5.5.32 + plugin => 10.0-serg ok
5.5.33a => 10.0-serg ok
mysql-5.6.13 => 10.0-serg ok
mysql-5.6.10 => 10.0-serg ok
mysql-5.6.5 => 10.0-serg ok
mysql-5.5.30 => 10.0-serg ok
mysql-5.5.32 => 10.0-serg ok
mysql-5.5.33 => 10.0-serg ok
5.3 => 5.5 => 10.0-serg ok

Upgrade from 10.0 to 5.6:

10.0 => mysql-5.6 - MDEV-5285
10.0-serg => mysql-5.6 MDEV-5285

Generated at Thu Feb 08 07:02:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.