MySQL Bugs: #115629: Upgrading from 8.0.20 to 8.0.28 corrupts a db table with a check constraint

Bug #115629	Upgrading from 8.0.20 to 8.0.28 corrupts a db table with a check constraint
Submitted:	17 Jul 2024 12:23	Modified:	13 Mar 16:53
Reporter:	Dmitry Zenovich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Upgrading from 8.0.20 to 8.0.28 makes tables containing a check constraint calling regexp_like() with parameters of incompatible charsets inaccessible (the table is shown by 'show tables', but it's impossible to select data from the table, repair the table, alter the table, or delete the table).

Besides fixing the bug, please let me know if there is a way to get access to the data (in my real table there is much more columns than listed in "How to repeat" scenario and real data that I would like to restore).

How to repeat:
1. On 8.0.20 create a table

CREATE TABLE `t` (
  `custom_prefix` varchar(14) NOT NULL,
  CONSTRAINT `ck_t_custom_prefix` CHECK (regexp_like(cast(`custom_prefix` as char charset binary),_utf8mb4'^[a-z0-9-]+$'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Upgrade to 8.0.28.
3.

REPAIR TABLE `t` EXTENDED;
+-------+--------+----------+--------------------------------------------------------------------------------------------------------+
| Table | Op     | Msg_type | Msg_text                                                                                               |
+-------+--------+----------+--------------------------------------------------------------------------------------------------------+
| db.t  | repair | Error    | Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like. |
| db.t  | repair | Error    | Incorrect information in file: './db/t.frm'                                                            |
| db.t  | repair | error    | Corrupt                                                                                                |
+-------------------------+--------+----------+--------------------------------------------------------------------------------------+
mysql> select * from `t`;
ERROR 3995 (HY000): Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like.
mysql> ALTER TABLE `t` DROP CHECK `ck_t_custom_prefix`;
ERROR 3995 (HY000): Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like.
mysql> show create table `t`;
ERROR 3995 (HY000): Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like.
mysql> drop table `t`;
ERROR 3995 (HY000): Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like.

Suggested fix:
Probably the MySQL server should fail on upgrading DBs with such tables keeping the DBs unchanged.

Hi Mr. Zenovich,

Thank you for your bug report.

However, this is not a bug.

FRM files have existed in 5.7 and prior versions,  only. They were never used in any 8.0 release.

Hence, your upgrade to 8.0.20 has gone wrong. If you still have your 5.7 installations, we recommend you to dump all the schemas (except for 'mysql' schema) with our mysqldump utility and then imported to the fresh 8.0 installation, as described in our Reference Manual.

Not a bug.

I insist it is a bug. Please, reread the bug report, ignore the 'REPAIR TABLE `t` EXTENDED' part (if you don't want to see the word FRM), the bug is not about FRM at all.

Also, there is no such file ./db/t.frm on my disk (just for you to be sure I haven't used MySQL 5.x)

Hi Mr. Zenovich,

Sorry, but those of these messages imply that there is something very wrong with your installation.

| db.t  | repair | Error    | Character set 'binary' cannot be used in
conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like. |
| db.t  | repair | Error    | Incorrect information in file:
'./db/t.frm'                                                           
|
| db.t  | repair | error    | Corrupt                                   
                                                

That can not be ignored.

Also, let us inform you about this forum.

This is a forum with bugs with repeatable test cases. Each of these test cases should contain a set of SQL statements that lead to the bug reported.

We have not received such a test case from you, at all.

This is not a support forum, but forum for reports with fully repeatable test cases.

We do have links for both free and paid support and we are sending those to you .......

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our free support on  forums  at http://forums.mysql.com/

Thank you for your interest in MySQL.

How to repeat:
1. On 8.0.20 create a table

CREATE TABLE `t` (
  `custom_prefix` varchar(14) NOT NULL,
  CONSTRAINT `ck_t_custom_prefix` CHECK (regexp_like(cast(`custom_prefix` as char charset binary),_utf8mb4'^[a-z0-9-]+$'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Upgrade to 8.0.28.

3.

mysql> select * from `t`;
ERROR 3995 (HY000): Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like.
mysql> ALTER TABLE `t` DROP CHECK `ck_t_custom_prefix`;
ERROR 3995 (HY000): Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like.
mysql> show create table `t`;
ERROR 3995 (HY000): Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like.
mysql> drop table `t`;
ERROR 3995 (HY000): Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like.

I think this probably related to this breaking change in MySQL 8.0.22.(8.0.20->8.0.22 will repro) MySQL should prob allow users to access such tables post upgrade to correct the constraint. There should prob also be a mysqlsh upgrade precheck[1]

> Regular expression functions such as REGEXP_LIKE() yielded inconsistent results with binary string arguments. These functions now reject binary strings with an error. (Bug #98950, Bug #98951, Bug #31031886, Bug #31031888)

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-22.html
https://github.com/mysql/mysql-server/commit/1443fe70ad411d639a060202e8330cbb6e29a584

[1]
$ mysqlsh --version
mysqlsh   Ver 8.0.38 for Linux on x86_64 - for MySQL 8.0.38 (MySQL Community Server (GPL))

$ mysqlsh --user=root --password=msandbox --socket=/tmp/mysql_sandbox8020.sock -- util check-for-server-upgrade  --target-version=8.0.38 --output-format=JSON
WARNING: Using a password on the command line interface can be insecure.
{
    "serverAddress": "/tmp%2Fmysql_sandbox8020.sock",
    "serverVersion": "8.0.20 - MySQL Community Server - GPL",
    "targetVersion": "8.0.38",
    "errorCount": 0,
    "warningCount": 0,
    "noticeCount": 0,
    "summary": "No known compatibility errors or issues were found.",
    "checksPerformed": [
        {
            "id": "reservedKeywordsCheck",
            "title": "Usage of db objects with names conflicting with new reserved keywords",
            "status": "OK",
            "detectedProblems": []
        },
        {
            "id": "checkTableOutput",
            "title": "Issues reported by 'check table x for upgrade' command",
            "status": "OK",
            "detectedProblems": []
        },
        {
            "id": "mysqlDollarSignNameCheck",
            "title": "Check for deprecated usage of single dollar signs in object names",
            "status": "OK",
            "detectedProblems": []
        }
    ],
    "manualChecks": []
}

Hi,

This is expected behaviour.

There was a change in the conversions between character sets that could be used after 8.0.20.

Hence, you first have to use ALTER TABLE in 8.0.20 with  CONVERT() as explained in our Reference Manual and only then attempt to upgrade.

Not a bug.

I understand, but it is not possible to get back to MySQL 8.0.20 after the DB has been upgraded to 8.0.28. Shouldn't there be a way for altering the table after upgrading? I cannot even drop this table!

I completely agree with Marc Reilly: "MySQL should prob allow users to access such tables post upgrade to correct the constraint."

Hi,

Our Manual recommends backing up the original datadir prior to upgrading.

There is no way to re-use old datadir after upgrade.

Hence, that is why we recommend doing a backup prior to upgrading.

Not a bug.

The problem is that the DB hadn't been backed up before the upgrade. Moreover, even if it had been backed up, it wouldn't have helped because it is a production DB and new data has been coming during all the time after the upgrade into all other tables of the DB.

HI,

We wrote:

"
Our Manual recommends backing up the original datadir prior to upgrading.
"

If I had a time machine, I would definitely to that. The current situation is that the table is inaccessible, and there is no way to do something with it.

I cannot even rename the table:

mysql> rename table t to t_broken;
ERROR 3995 (HY000): Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like.

The responsible team has verified the behaviour, and will take a look to see if we can be more user-friendly in this case.

Documented fix as follows in the MySQL 8.0.42, 8.4.5, and 9.3.0 changelogs:

  When an SQL function is improved from one release to the next,
  it may throw SQL errors in situations in which it previously did
  not. If this happens in a table's constraints, default
  expressions, partitioning expressions, or virtual columns, the
  table could not be opened. This prevented both analyzing the
  problem (using, for example, SHOW CREATE TABLE) and addressing
  it (such as with an ALTER TABLE ... DROP ... statement).

  Now, on server upgrade, we scan the data dictionary for tables
  that use any of the features just mentioned. We then try to open
  such tables, and if we fail to do so, we alert the user. This
  patch addresses this. The --check-table-functions server option
  introduced in this release helps to address this problem by
  making it possible to specify the server's behavior when
  encountering an error with such a function. Set this option to
  WARN in order to log a warning for each table which the server
  could not open; setting it to ABORT also logs these warnings as
  WARN, but aborts the server upgrade if any issues were found.

  ABORT is the default; this enables the user to fix the issue
  using the older version of the server before upgrading to the
  new one. WARN flags the issues, but allows the user to continue
  in interactive mode while addressing the problem.

Also noted the addition of the new startup option in the appropriate versions of the Manual.

Closed.

Thank you , Jon.

But I still cannot access the table in the already migrated database :(