MySQL Bugs: #87229: Inplace upgrade 5.5 -> 5.6 -> 5.7 -> 8.0.2 fails: Error in fixing SE data

Bug #87229	Inplace upgrade 5.5 -> 5.6 -> 5.7 -> 8.0.2 fails: Error in fixing SE data
Submitted:	28 Jul 2017 3:20	Modified:	30 Aug 2017 14:54
Reporter:	Jesper wisborg Krogh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S2 (Serious)
Version:	8.0.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If you have tables created in MySQL 5.5 and then have undergone in-place upgrades to 5.6, followed by 5.7, and finally to 8.0, the upgrade to the new data dictionary fails with:

2017-07-28T03:14:44.274883Z 1 [Note] Created Data Dictionary for upgrade
2017-07-28T03:14:44.298185Z 2 [ERROR] Error in fixing SE data for db1.t1
2017-07-28T03:14:46.004390Z 0 [ERROR] Failed to Populate DD tables.
2017-07-28T03:14:46.004416Z 0 [ERROR] Aborting

Additionally if there are tables with decimal columns created in 5.1, then they fail with the error:

How to repeat:
1. Install 5.5 (I used 5.5.56)
2. Create table:

CREATE SCHEMA db1;
CREATE TABLE db1.t1 (
   id int unsigned NOT NULL,
   PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO db1.t1 VALUES (1);

3. Upgrade in-place to 5.6 (I used 5.6.36)
4. Execute mysql_upgrade for 5.6
5. Restart MySQL
6. Upgrade in-place to 5.7 (I used 5.7.18)
7. Execute mysql_upgrade for 5.7
8. Restart MySQL
9. Upgrade in-place to 8.0.2

Posted by developer:
 
For the upgrade from 5.1 (same principle as before - starting with 5.1.73), I use the tables and data:

CREATE SCHEMA db1;
CREATE TABLE db1.t1 (
   id int unsigned NOT NULL,
   PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO db1.t1 VALUES (1);

CREATE TABLE db1.t2 (
   id int unsigned NOT NULL,
   val decimal(6,4) DEFAULT NULL,
   PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO db1.t2 VALUES (1, 1.2345);

With this the errors for the data dictionary upgrade are:

2017-07-28T03:23:36.355071Z 2 [ERROR] Error in fixing SE data for db1.t1
2017-07-28T03:23:36.357070Z 2 [ERROR] InnoDB: Column precision type mismatch(i.e NULLs, SIGNED/UNSIGNED etc) for col: val
2017-07-28T03:23:36.357106Z 2 [ERROR] InnoDB: Column val for table: `db1`.`t2` mismatches with InnoDB Dictionary
2017-07-28T03:23:36.357123Z 2 [ERROR] Error in fixing SE data for db1.t2
2017-07-28T03:23:37.128175Z 0 [ERROR] Failed to Populate DD tables.
2017-07-28T03:23:37.128200Z 0 [ERROR] Aborting

Note the different error for the decimal column.

Posted by developer:
 
The workaround is to execute OPTIMIZE TABLE (or other copying ALTER) - this can be done as late as just before the upgrade to 8.0.2.

Posted by developer:
 
my.cnf:

[mysqld]
basedir               = /upgrade/mysql
datadir               = /upgrade/data
socket                = /upgrade/run/mysql.sock
pid_file              = /upgrade/run/mysql.pid

port                  = 3306
innodb_file_per_table = 1

Is there any way to check which tables on a 5.7 instance need rebuilding?

Posted by developer:
 
There is unfortunately no straight forward way to check. The only way I know of is to use mysqlfrm and check field_type:

shell$ mysqlfrm --diagnostic -vv data/db1/t1.frm
...
[{'bytes_in_col': 8,
  'charset': 8,
  'charset_low': 0,
  'comment': '',
  'comment_length': 0,
  'default': None,
  'enums': [],
  'field_length': 8,
  'field_type': 246,              <----- this one
  'field_type_name': 'decimal',
  'flags': 3,
  'flags_extra': 67,
  'interval_nr': 0,
  'name': 'val',
  'recpos': 6,
  'unireg': 0,
  'unireg_type': 0}]
...

If field_type = 0, you are using the old decimal type, if field_type = 246, you use the new.

The values of field_type comes from include/mysql.h.pp:

typedef enum enum_field_types {
  MYSQL_TYPE_DECIMAL, MYSQL_TYPE_TINY,
  MYSQL_TYPE_SHORT, MYSQL_TYPE_LONG,
  MYSQL_TYPE_FLOAT, MYSQL_TYPE_DOUBLE,
  MYSQL_TYPE_NULL, MYSQL_TYPE_TIMESTAMP,
  MYSQL_TYPE_LONGLONG,MYSQL_TYPE_INT24,
  MYSQL_TYPE_DATE, MYSQL_TYPE_TIME,
  MYSQL_TYPE_DATETIME, MYSQL_TYPE_YEAR,
  MYSQL_TYPE_NEWDATE, MYSQL_TYPE_VARCHAR,
  MYSQL_TYPE_BIT,
  MYSQL_TYPE_TIMESTAMP2,
  MYSQL_TYPE_DATETIME2,
  MYSQL_TYPE_TIME2,
  MYSQL_TYPE_JSON=245,
  MYSQL_TYPE_NEWDECIMAL=246,
  MYSQL_TYPE_ENUM=247,
  MYSQL_TYPE_SET=248,
  MYSQL_TYPE_TINY_BLOB=249,
  MYSQL_TYPE_MEDIUM_BLOB=250,
  MYSQL_TYPE_LONG_BLOB=251,
  MYSQL_TYPE_BLOB=252,
  MYSQL_TYPE_VAR_STRING=253,
  MYSQL_TYPE_STRING=254,
  MYSQL_TYPE_GEOMETRY=255
} enum_field_types;

From my query on I_S it looks like the column has MYSQL_TYPE_NEWDECIMAL in both the old and new case. However the high bits of prtype are different, but I don't know why..

prtype for old is: 4130038 ( 1111110000010011110110 )
prtype for new is   525558 ( 0010000000010011110110 )
xor                          1101110000000000000000 
old >> 16                    111111 = 63

This query lists the columns in need of an upgrade because of decimal types in my case:
SELECT
  t.NAME,
  c.*
FROM
  information_schema.INNODB_SYS_COLUMNS c
  LEFT JOIN information_schema.INNODB_SYS_TABLES t USING (TABLE_ID)
WHERE
  c.MTYPE=3
  AND PRTYPE >> 16 = 63
  AND PRTYPE & 255 /* DATA_MYSQL_TYPE_MASK */ = 246 /* MYSQL_TYPE_NEWDECIMAL */;

Comparing mysqlfrm output on 5.7.18 on one decimal-upgrade affected table. The column is 100% identical. However the table a new MYSQL_VERSION_ID as expected: it changes from 50147 to 50718.

But the prtype did change.

the first two bytes are storing charset collation codes

This is the SQL I now use to identify which tables need to be rebuild before upgrading to 8.0.2

-- Find tables which do not have their tablespace registered in INNODB_SYS_TABLESPACES
-- This happens for tables created on MySQL 5.1
--
-- This ignores partitioned tables as mysql_upgrade should have ran
-- ALTER TABLE … UPGRADE PARTITIONING on them during the 5.7 upgrade.
--
-- The join might not work for non-ascii table names.
--
-- FIX: Run OPTIMIZE TABLE on them.

SELECT
  it.table_schema,
  it.table_name
FROM
  information_schema.TABLES it
  LEFT JOIN information_schema.PARTITIONS ip
    ON (it.TABLE_SCHEMA=ip.TABLE_SCHEMA AND it.TABLE_NAME=ip.TABLE_NAME
        AND ip.PARTITION_NAME IS NOT NULL)
  LEFT JOIN information_schema.INNODB_SYS_TABLESPACES ts
    ON ts.NAME=CONCAT(it.table_schema, '/', it.table_name)
WHERE
  it.ENGINE='InnoDB'
  AND it.TABLE_TYPE='BASE TABLE'
  AND it.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
  AND ip.PARTITION_NAME IS NULL
  AND ts.SPACE IS NULL;

-- Find tables with "new" decimal columns which have a binary (63) charset
-- instead of latin1 (8)
--
-- FIX: Run OPTIMIZE TABLE on them
SELECT
  t.NAME AS table_name,
  c.NAME AS column_name
FROM
  information_schema.INNODB_SYS_COLUMNS c
  LEFT JOIN information_schema.INNODB_SYS_TABLES t USING (TABLE_ID)
WHERE
  c.MTYPE=3                    /*   3 = DATA_FIXBINARY                 */
  AND PRTYPE >> 16 = 63        /*  63 = DATA_MYSQL_BINARY_CHARSET_COLL */
  AND PRTYPE & 255             /* 255 = DATA_MYSQL_TYPE_MASK           */
  = 246                        /* 246 = MYSQL_TYPE_NEWDECIMAL          */;

The SQL file to check which tables need to be rebuild

Attachment: mysql80_upgrade_check.sql (application/sql, text), 1.50 KiB.

Posted by developer:
 
Fixed as of the upcoming 8.0.3 release, and here's the changelog entry:

File-per-table tablespaces created prior to MySQL 5.6 caused a failure
during an in-place upgrade to MySQL 8.0.2. The tablespaces were not
registered with the InnoDB SYS_TABLESPACES system table, as required.

Tables with decimal columns created prior to MySQL 5.5 also
caused a failure during an in-place upgrade to MySQL 8.0.2, due to a
precision type mismatch.