Getting error while upgrading from MySQL 8.0.12 to 8.0.32

Question

Background: first, we have upgraded from MySQL 5.6 to MySQL 8.0.12 (5.6 --> 5.7 --> 8.0.12) and it was working fine .

Now, we are trying to in-place upgrade from 8.0.12 to 8.0.32 and passed all preliminary checks mentioned in this link ( Prerequisites ).

Upgraded by replacing 8.0.12 binaries with 8.0.32 and tried to start the server. But getting the below error which is related to datetime value of one of data dictionary tables.

I have seen many questions which are similar to this error, but those errors are related to user tables. For that reason, I have made the title related to Upgrade process.

2023-02-28T13:28:00.305202Z 0 \[System\] \[MY-010116\] \[Server\] C:\\Program Files\\MySQL\\MySQL Server       5.6\\bin\\mysqld (mysqld 8.0.32) starting as process 13296

2023-02-28T13:28:00.326722Z 1 \[System\] \[MY-013576\] \[InnoDB\] InnoDB initialization has started.

2023-02-28T13:28:01.109930Z 1 \[System\] \[MY-013577\] \[InnoDB\] InnoDB initialization has ended.

2023-02-28T13:28:01.139462Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80012' to  '80023'.

2023-02-28T13:28:07.931896Z 1 \[ERROR\] \[MY-013178\] \[Server\] Execution of server-side SQL statement 'INSERT   INTO st_spatial_reference_systems SELECT \* FROM mysql.st_spatial_reference_systems' failed with error code = 1292, error message = 'Incorrect datetime value: '0000-00-00 00:00:00' for column 'last_altered' at row 2'.

2023-02-28T13:28:09.524427Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.

2023-02-28T13:28:09.524886Z 0 [ERROR] [MY-010119] [Server] Aborting

2023-02-28T13:28:11.167707Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server   5.6\bin\mysqld: Shutdown complete (mysqld 8.0.32)  MySQL Community Server - GPL.

I have tried to access this data dictionary table mysql.st_spatial_reference_systems but access is denied.

Is there any way to alter this column default value? Please suggest.

Answer 1

MySQL 8.0 enforces that dates like '0000-00-00' are not allowed, which is generally a good idea, since those are not valid date values. But zero dates are still in use in some system tables. It seems like an unfortunate oversight in the MySQL development.

mysql.st_spatial_reference_systems is a system table that is hidden. You can't access it unless you're running a debug version of MySQL Server.

So to correct this, you'd have to:

1. Uninstall 8.0.32
2. Reinstall a debug build of 8.0.12
3. UPDATE values in mysql.st_spatial_reference_systems that have zeroes in date columns, setting them to some valid nonzero value.
4. Try the upgrade to 8.0.32 again.

This issue with upgrading was reported as a bug here: https://bugs.mysql.com/bug.php?id=109075 It was closed as "not a bug" because they think it's your responsibility to fix any zero dates before you upgrade — apparently even if they exist in MySQL's on system tables.

The bug comments also suggest that an upgrade from 8.0.13 to 8.0.31 (in the case of the person reporting that bug) was too large, and that they should have upgraded step by step. Are they suggesting that the zero dates would be corrected in some intermediate upgrade version, but it was only a one-time fix? But they do not identify which version's upgrade may have fixed the date. Or maybe the comment is irrelevant.

All said, this is a poor response from the MySQL verification team, and a poor user experience to force such steps to do an upgrade.

---

Another solution might be to install 8.0.32 after changing your option file to set sql_mode to permit zero dates. Honestly, I don't know if this would work, because I've never tried to change the sql_mode during installation of 8.0.

Even if you get it to work this time, you'd still have to fix the zero dates so you can upgrade in the future. To update the zero dates in the system table manually, it requires you to run a debug build so you can access the hidden tables.

I have no idea if there are more system tables that also contain zero dates.