Bug #99791 MySQL 8 orphaned table due to unchecked non-existent row format check.
Submitted: 6 Jun 7:29 Modified: 17 Sep 13:09
Reporter: Marc Reilly Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S1 (Critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: corruption, Index column size too large. The maximum column size is 767 byte

[6 Jun 7:29] Marc Reilly
Description:
Tables created in versions < MySQL 8 which use row_format COMPRESSED or REDUNDANT, where row_format is not set explicitly in the Table DDL allow users create un-prefixed indexes on fields which exceed the maximum column size of 767 bytes.

The result is after a server reboot the table is inaccessible and *cannot be recovered.*

Notes:
- Tables created with default innodb_default_row_format=redundant on MySQL 8 do not show this behavior. They are handled correctly. 
- This only seems to happen for tables created on prior versions. i.e. shows up if index is added after upgrade.
- The default row_format in 5.7+ is dynamic but prior to that it was COMPACT so this is more likely to effect tables which were originally created on v < 5.7 OR 57 DBs where innodb_default_row_format value has been changed to COMPACT or REDUNDANT. Its a 'silent killer' so tables may be affected by this without the DBA knowing. Upon reboot innodb will not be able to open the table.
- The only way to recover from this seems to be a backup restore. Table is undroppable in recovery mode too.

How to repeat:
1. In MySQL 5.7 set innodb_default_row_format to redundant OR compact. Or use MySQL 56 which uses redundant by default.

2. Create 1 table `test_key_redundant_default` and another called `test_key_redundant_explicit`. Note how one has the row format set explicitly while the other does not.

CREATE TABLE `test_key_redundant_default` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `im_going_to_cause_problems` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test_key_redundant_explicit` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `im_going_to_cause_problems` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ROW_FORMAT=REDUNDANT DEFAULT CHARSET=utf8;

mysql>  select TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS from information_schema.tables where table_schema = 'test2';
-----------------------------------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME                  | ROW_FORMAT | CREATE_OPTIONS       |
-----------------------------------------------------------------------+
| test2        | test_key_redundant_default  | Redundant  |                      |
| test2        | test_key_redundant_explicit | Redundant  | row_format=REDUNDANT |
-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>  select * from information_schema.innodb_sys_tablespaces where NAME like 'test2/%';
-----------------------------------------------------------------------------------------------------------------------------------------------
| SPACE | NAME                              | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   119 | test2/test_key_redundant_explicit |    0 | Antelope    | Compact or Redundant |     16384 |             0 | Single     |          4096 |     98304 |          98304 |
|   120 | test2/test_key_redundant_default  |    0 | Antelope    | Compact or Redundant |     16384 |             0 | Single     |          4096 |     98304 |          98304 |
-----------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)

 
3. As a filler, insert a row into each table

insert into }}{{test_key_redundant_default(im_going_to_cause_problems) values('kncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdn');

insert into }}{{test_key_redundant_explicit(im_going_to_cause_problems) values('kncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdn');

4. Upgrade db instance to any MySQL 8 version 

5. Once upgraded add an index on each table

## No Warning
mysql> create index idx2 on test2.test_key_redundant_default (`im_going_to_cause_problems`);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

## Warning
mysql> create index idx2 on test2.test_key_redundant_explicit (`im_going_to_cause_problems`);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show warnings;
--------------------------------------------------------------------
| Level   | Code | Message                                                 |
--------------------------------------------------------------------
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
--------------------------------------------------------------------
1 row in set (0.00 sec)

6. Index will add successfully and table will be accessible but note one thing: The table with the explicit row format setting has its index automatically limited with 191 to keep it under the limit while the other table did not.

mysql> show CREATE TABLE `test_key_redundant_default`\G
*************************** 1. row ***************************
       Table: test_key_redundant_default
Create Table: CREATE TABLE `test_key_redundant_default` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `im_going_to_cause_problems` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx2` (`im_going_to_cause_problems`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show CREATE TABLE `test_key_redundant_explicit`\G
*************************** 1. row ***************************
       Table: test_key_redundant_explicit
Create Table: CREATE TABLE `test_key_redundant_explicit` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `im_going_to_cause_problems` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx2` (`im_going_to_cause_problems`(191))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT
1 row in set (0.00 sec)

7. The table will remain accessible until a reboot happens. After rebooting the instance you will notice that the test_key_redundant_default table becomes inaccessible with the following error while test_key_redundant_explicit is still accessible due to the index size being capped but the prefix. In this state the table is unable to be dropped in regular or innodb recovery mode

mysql> select count from test_key_redundant_default;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

mysql> select count from test_key_redundant_explicit;
----------
| count |
----------
|        1 |
----------
1 row in set (0.00 sec)

My.cnf
sql_mode='NO_ENGINE_SUBSTITUTION';

Suggested fix:
Detect max column size for tables created in prior versions and handle them in the same was as 8.0 created tables or throw an error blocking the DDL.

Also, may be useful to check for this in mysqlh upgrade checker.
[9 Jun 12:21] MySQL Verification Team
Hi Mr. Reilly,

Thank you for your bug report.

We do have several questions for you:

1. What 8.0 release did you use exactly ???

2. Have you run mysql_upgrade when upgrading 5.7 table to 8.0 ???

3. What is 'mysqlh' upgrade checker ???

4. Which 5.7 release did you use exactly ???

5. Did you upgrade the entire datadir or have you copied the table(s) ??

Thanks in advance.
[10 Jun 4:11] Marc Reilly
Hi,

Answers inline:

1. What 8.0 release did you use exactly ???
8.0.20. Should be reproducible using the above steps on all 8.0 versions.

2. Have you run mysql_upgrade when upgrading 5.7 table to 8.0 ???
According to the docs it is no longer required for 8.0.16+. However, I did run mysqlsh checkForServerUpgrade (8.0.20-1.el6) prior to upgrading  1 warning was reported for 'New default authentication plugin considerations', no other issues.
https://dev.mysql.com/doc/refman/8.0/en/updating-yum-repo.html 

```
`mysqlsh -- util checkForServerUpgrade`
The MySQL server at localhost, version 5.7.30 - MySQL Community Server (GPL),
will now be checked for compatibility issues for upgrade to MySQL 8.0.20...
.
.
.
Errors:   0
Warnings: 1
Notices:  0

No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
```

3. What is 'mysqlh' upgrade checker ???
Apologies for being unclear, meant mysqlsh -- util checkForServerUpgrade
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html

4. Which 5.7 release did you use exactly ???
5.7.30, just make sure you set @@global.innodb_default_row_format to redundant or compact to repro.

5. Did you upgrade the entire datadir or have you copied the table(s) ??
Upgraded entire DD using yum method and manual method. Same result in both cases.

The steps above should demonstrate the behavior pretty quickly but please LMK if you need additional info to repro.

Thanks!
[10 Jun 13:11] MySQL Verification Team
Thank you, Mr. Reilly,

This are the results that we have got with 5.7.30:

TABLE_SCHEMA	TABLE_NAME	ROW_FORMAT	CREATE_OPTIONS
test	test_key_redundant_default	Redundant
test	test_key_redundant_explicit	Redundant	row_format=REDUNDANT
SPACE	NAME	FLAG	FILE_FORMAT	ROW_FORMAT	PAGE_SIZE	ZIP_PAGE_SIZE	SPACE_TYPE	FS_BLOCK_SIZE	FILE_SIZE	ALLOCATED_SIZE
140	test/t1#P#p0#SP#s0	33	Barracuda	Dynamic	16384	0	Single	0	0	0
141	test/t1#P#p0#SP#s1	33	Barracuda	Dynamic	16384	0	Single	0	0	0
236	test/test_key_redundant_default	0	Antelope	Compact or Redundant	16384	0	Single	4096	98304	98304
237	test/test_key_redundant_explicit	0	Antelope	Compact or Redundant	16384	0	Single	4096	98304	98304

Error at line 12
Error at line 14

------------------------------------------------------------------------------------------------

Hence, none of inserts work on mysql-5.7.
[10 Jun 20:02] Marc Reilly
Hi,

Have you set sql_mode='NO_ENGINE_SUBSTITUTION' as per the my.cnf note at the bottom? In any case the inserts should not be necessary, I only included for demonstration purposes.

Thanks,
Marc
[11 Jun 13:03] MySQL Verification Team
Hi,

I repeated your steps , exactly as you have sent them to me.

I managed to repeat every warning and error that you have got.

Verified as reported.
[12 Jun 1:45] Marc Reilly
Great, thanks!

I'm gonna bump back to severity S1, I may have accidentally change to s3 with my previous update. 
Seems this falls under the definition of S1 as this manifests silently and no workaround is available once the bug occurs.(apart from backup restore) 

Thanks again,
Marc
[17 Sep 13:09] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.22 release, and here's the proposed changelog entry from the documentation team:

A REDUNDANT row format table created in an earlier version of MySQL,
where the row format was not defined explicitly, permitted the addition of
an index that exceeded the REDUNDANT row format index column size limit.
[21 Sep 16:33] MySQL Verification Team
Thank you, Daniel .......