In place upgrade 5.5==>5.6: Date based columns corrupts columns that follows

Description

Follow the recommended steps for in place upgrade for this bugs fixes:
https://tokutek.atlassian.net/browse/DB-882 : Corrupted patitioned tables after 5.5->5.6 upgrade
https://tokutek.atlassian.net/browse/DB-886 : droping an imported file from tokudb 5.5 to PS5.6
described here :
https://docs.google.com/a/percona.com/document/d/1NYZDDXQR-L-Dv67b3KVbU6EEQ5MaO6iTGdC_WOgM7S0/edit?usp=sharing

Attached is the file used for the reproducer see below for highlight of the procedure:

EXAMPLE:

MYSQ5.5.41-tokudb-7.5.5:
mysql> select profile_id,type from ad_criterias limit 10;
--------------------+

profile_id

type

--------------------+

405

Exact

405

Phrase

405

Exact

406

Broad

--------------------+
4 rows in set (0.01 sec)

PS5.6.28-76.1:
mysql> select profile_id,type from ad_criterias limit 4;
-----------------------+

profile_id

type

-----------------------+

405

6 6 6 di

405

2 2 2 di

405

1 1 1 dis

406

 

-----------------------+
4 rows in set (0.00 sec)

High level Inplace Upgrade procedure:
a- Dump the schema for all TokuDB databases
b- Shutdown the server.
c- Move all TokuDB data files out of the way (mv data/toku data.back/)
d- Install Percona Server 5.6 with a new, empty data set
e- Recreate the TokuDB tables from the dumped schema in step a
f- Shutdown the server.
g- Delete all of the newly created TokuDB data files from step 9 in the data directory.
h- Move the TokuDB data files from step c (mv data.backup/toku data/)
e- Start the server again manually with the option --tokudb-strip-frm-data=TRUE
g- Start the server again normally

Environment

None

Activity

Show:
George Lorch
March 21, 2016, 5:45 PM

I think this issue is safe to close now that we understand the nature of the problem and the procedure needed to avoid it. We can re-open if needed.

George Lorch
February 29, 2016, 10:11 PM
George Lorch
February 26, 2016, 11:15 PM
Edited

I confirmed that the FT data files are not getting manipulated/changed as believed in PM discussion with Abdel.

What is happening is that in 5.5, datetime is stored in 5 bytes, in 5.6 it is stored in 8. This difference causes the calculations on where to find the var data type offset list to begin at the wrong location. So this can be reproduced with a table that looks like:

In theory, you could possibly do an ALTER TABLE to change datetime to timestamp, then an OPTIMIZE TABLE to ensure the rows have been converted, then go through the migration procedure, then ALTER TABLE to change back to the datetime.

George Lorch
February 26, 2016, 9:37 PM

Using the same repro process, but creating the table without the extra keys as:

Then executing this on the upgraded, stripped instance:

Crashes the server:

Won't Fix

Assignee

George Lorch

Reporter

Abdelhak Errami

Labels

None

External issue ID

None

Freshdesk Tickets

None

Priority

Major