| Bug #99791 | MySQL 8 orphaned table due to unchecked non-existent row format check. | ||
|---|---|---|---|
| Submitted: | 6 Jun 2020 7:29 | Modified: | 17 Sep 2020 13:09 |
| Reporter: | Marc Reilly | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Dictionary | Severity: | S3 (Non-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 | ||
[9 Jun 2020 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 2020 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 2020 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 2020 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 2020 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 2020 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 2020 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 2020 16:33]
MySQL Verification Team
Thank you, Daniel .......
[8 Jun 2023 16:38]
Ionel Sirbu
Hello, Our team is currently preparing for a MySql migration from 5.7.38 to 8.0.32 & they tested that on a couple of snapshots, from which they got some nice migration reports, with identified issues. One of those issues is the current one: > Creating indexes larger than 767 bytes on tables with redundant row format might cause the tables to be inaccessible. > You have one or more tables with redundant row format. Creating an index larger than 767 bytes might cause the table to be inaccessible (only 50 tables are shown). > More Information: > https://bugs.mysql.com/bug.php?id=99791 Looking at the history of the bug, it mentions it was fixed in release 8.0.22, but then we're migrating to the newer 8.0.32, so we are a bit confused as to why the issue is still reported. Is there any chance the upgrade checker utility hasn't been updated to reflect that? If you could confirm that, it would be very much appreciated. Thank you!
[9 Jun 2023 11:51]
MySQL Verification Team
Hi, Our Reference Manual for 8.0 contains the section on the maximum sizes of indices in latest 8.0.
[9 Jun 2023 12:21]
Ionel Sirbu
Hello, I'm sorry, that doesn't quite answer my previous question. I was just asking to confirm whether this bug has indeed been fixed in version 8.0.22 & whether the fact that it still gets reported by the Upgrade Checker Utility could be due to the fact that the tool hasn't been updated yet to reflect the fix. To give you an example, checking the indexes for one of the reported tables, there's only one index using a `varchar(18)` column, so even with utf8mb4 that still falls well below the reported 767 bytes limit. I think we get the issue reported purely because the table uses the `Compact` row format for some reason. So, has this issue indeed been fixed & can we ignore those reports about it? Thank you!
[9 Jun 2023 12:27]
MySQL Verification Team
Hi, That bug that you mentioned is fix. It is fixed so that there are no more orphaned tables. But, during migration, your character set can be changed to one with more bytes per character. In that case, if the size of any of the rows exceeds the maximum for that row format, you will get an error. We also must inform you that this is not a forum for asking questions. This is a forum for bug reports with fully repeatable test cases. You have not reported a bug , but instead you are asking for a free support.
[9 Jun 2023 12:42]
Ionel Sirbu
Ok, thanks for clarifying that, much appreciated!
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.