Bug #99461 Upgrade from 8.0.15 to 8.0.16 fails
Submitted: 6 May 2020 10:57 Modified: 14 Oct 2021 18:24
Reporter: Jehu Thomas Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S3 (Non-critical)
Version:8.0.15 OS:CentOS (7)
Assigned to: MySQL Verification Team CPU Architecture:x86

[6 May 2020 10:57] Jehu Thomas
Description:
The following error message is appearing during the upgrade but there's no additional error message as to why Data Dictionary initialization is failing. The below log was taken using "sudo /usr/sbin/mysqld-debug --user=mysql  --log-error-verbosity=3"

Getting the same error messages while trying to upgrade on a clone of our stage, UAT, and production servers. We were using 5.6 version 2 years ago, then it was upgraded as follows: 5.7=>8.0.13=>8.0.15. There's no error in upgrade till 8.0.15. 

Data dictionary upgrading from version '80014' to '80016'.
2020-05-05T13:47:35.303235Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
2020-05-05T13:47:35.498297Z 1 [Note] [MY-012356] [InnoDB] Validated 419/419  tablespaces
2020-05-05T13:47:40.964826Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-05-05T13:47:40.965749Z 0 [ERROR] [MY-010119] [Server] Aborting

How to repeat:
yum upgrade mysql-community-server-8.0.16
[6 May 2020 11:01] MySQL Verification Team
Please try the latest version 8.0.20 (there isn't fix for older versions). Thanks.
[6 May 2020 11:32] Jehu Thomas
Yes, we have tried that already and got the same error. So we thought of upgrading to 8.0.16 first. 

Error message while upgrading to 8.0.20
[Note] [MY-013327] [Server] MySQL server upgrading from version '80015' to '80020'.
[Note] [MY-012357] [InnoDB] Reading DD tablespace files
[Note] [MY-012356] [InnoDB] Validated 419/419  tablespaces
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
[ERROR] [MY-010119] [Server] Aborting
[6 May 2020 13:20] Terje Røsten
Can you try to run 

$ mysql_upgrade --force 

on the *8.0.15* server before doing upgrade to 8.0.16/20?
[6 May 2020 13:35] MySQL Verification Team
Please let us know what the result of mysql_upgrade --force on the last working version  (8.0.15) is.

Thanks
Bogdan
[11 May 2020 13:44] Jehu Thomas
I ran mysql_upgrade --force on 8.0.15 and the last few lines of the output are  pasted below:

information.ifimt_logs                       OK
information.ifimt_poi                        OK
information.ifimt_poi_category               OK
information.ifimt_pops                       Table is already up to date
information.ifimt_watchlist                  Table is already up to date
information.ip2location_db24                 Table is already up to date
information.messages                         OK
information.temp_zip_ref                     Table is already up to date
sys.sys_config                               Table is already up to date
Upgrade process completed successfully.
Checking if update is needed.
[root@Prod-MySQL01 ~]#
[11 May 2020 13:49] Terje Røsten
Thanks!

And upgrade to next version (8.0.20) still fails?
[11 May 2020 14:31] Jehu Thomas
Yes, upgrade to both 8.0.16 and 8.0.20 are failing with the "Data Dictionary initialization failed." error.
[12 May 2020 5:57] MySQL Verification Team
Hi,

I cannot reproduce this. 
Is there a way you can share the mysqldump --all --no-data ?

thanks
Bogdan
[18 May 2020 5:52] Jehu Thomas
Sorry, I can't share that because of our organization policy.
Kindly let me know what I have to check after taking the schema dump.

Other than that, what causes this Dictionary initialization error during the upgrade?
[18 May 2020 6:33] MySQL Verification Team
Hi,

It is understandable about the dump, I hoped the --no-data might be allowed as structure is not always that private but I understand.

I have no idea what can be causing the error you are seeing.

Can you, on some spare server try to import your mysqldump on the latest 8.0.20?

There's 2 issues I'd like to check. First, you make a mysqldump of your whole server. If the dump completes that means all tables an be 100% read. It is possible with InnoDB due to hardware error that table get corrupted and while it looks ok if you try to read the corrupted page mysql will not behave gracefully (normally it will die). So, if mysqldump finish ok we know all your tables are 100% ok. Secondly, importing that dump into fresh 8.0.20 on a spare server (or even some laptop, does not need performance you just need enough disk space to allow all data to be imported) will show that there is no some weird error in the create structure that messes with 8.0.20. That is what this dump/restore will show us.

Pay attention to your mysql logs during the dump and do it during "safe" hours as there is a chance your server will crash during the dump if there is an InnoDB corruption taking place. You can do it safer by 
 - shutdown mysql 
 - copy datadirectory
 - start mysql
 - move a copy of data directory to a secondary server with same mysql version and continue testing there (making a dump etc..).

in good health
Bogdan
[19 Jun 2020 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[17 Sep 2020 10:51] Jehu Thomas
Thanks for providing the steps. Actually there is no issue in taking dumps in all the 3 servers (stage, UAT and Production). A dump is happening on Production every week and there are no issues with it.

I have dumped all the databases on stage using the "--all-databases" option and then restored it successfully on 8.0.21 without any errors. But when I restarted MySQL service, it didn't start. I got the below error. I think the restore operation (with --all-databases) replaced mysql database of 8.0.21 with the previous server (8.0.15). Is that correct? 

I could start MySQL now only with the below option:
[mysqld]
skip-grant-tables

======================================
2020-09-17T10:34:47.755791Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2020-09-17T10:34:47.755814Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-09-17T10:34:47.755856Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2020-09-17T10:34:47.757038Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2020-09-17T10:34:47.757137Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 5296
2020-09-17T10:34:47.766935Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-09-17T10:34:48.984536Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-09-17T10:34:49.147977Z 1 [Warning] [MY-010005] [Server] Skip re-populating collations and character sets tables in InnoDB read-only mode.
2020-09-17T10:34:49.155019Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-09-17T10:34:49.155582Z 2 [Warning] [MY-011018] [Server] Skip updating information_schema metadata in InnoDB read-only mode.
2020-09-17T10:34:49.156905Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2020-09-17T10:34:49.157110Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2020-09-17T10:34:49.269033Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-09-17T10:34:49.269328Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-09-17T10:34:49.288760Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.user. The table is probably corrupted!
2020-09-17T10:34:49.289030Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.db. The table is probably corrupted!
2020-09-17T10:34:49.289253Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.tables_priv. The table is probably corrupted!
2020-09-17T10:34:49.289515Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.tables_priv. The table is probably corrupted!
2020-09-17T10:34:49.289775Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.columns_priv. The table is probably corrupted!
2020-09-17T10:34:49.290016Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.procs_priv. The table is probably corrupted!
2020-09-17T10:34:49.290258Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.procs_priv. The table is probably corrupted!
2020-09-17T10:34:49.290526Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.proxies_priv. The table is probably corrupted!
2020-09-17T10:34:49.290777Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.proxies_priv. The table is probably corrupted!
2020-09-17T10:34:49.291017Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.proxies_priv. The table is probably corrupted!
2020-09-17T10:34:49.291259Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.role_edges. The table is probably corrupted!
2020-09-17T10:34:49.291511Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.role_edges. The table is probably corrupted!
2020-09-17T10:34:49.291762Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.default_roles. The table is probably corrupted!
2020-09-17T10:34:49.292026Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.default_roles. The table is probably corrupted!
2020-09-17T10:34:49.292286Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.global_grants. The table is probably corrupted!
2020-09-17T10:34:49.292570Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.password_history. The table is probably corrupted!
2020-09-17T10:34:49.293757Z 0 [ERROR] [MY-013139] [Server] Cannot load from mysql.global_grants. The table is probably corrupted!
2020-09-17T10:34:49.294159Z 0 [ERROR] [MY-010952] [Server] The privilege system failed to initialize correctly. For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual.
2020-09-17T10:34:49.294875Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-09-17T10:34:50.575174Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.
[18 Sep 2020 10:39] MySQL Verification Team
> replaced mysql database of 8.0.21 with the previous server (8.0.15).

Yes. You need to run mysql_upgrade, or better yet, restore databases without mysql database
[14 Oct 2020 8:22] Jehu Thomas
I restored all the databases except the following and MySQL started without any issues
'mysql','information_schema','performance_schema'

For production, we can not dump and restore the databases as it's about 5TB in size.

So still no idea why the "Data Dictionary initialization failed" error is appearing during the upgrade.

Is it something to do with 'mysql','information_schema','performance_schema' databases?
[14 Oct 2020 15:39] MySQL Verification Team
Hi,

> For production, we can not dump and restore the databases as it's about 5TB in size.

Understandeable. What would be interesting is, if you can copy the datadir from the production to a stage system and try upgrade of the binary there, wether it would run ok or not. 

> So still no idea why the "Data Dictionary initialization failed" error is appearing during the upgrade.
>
> Is it something to do with 'mysql','information_schema','performance_schema' databases?

I cannot reproduce this so I doubt it's related to any of the generic tables (like PS or IS). You can try yourself, setup 8.0.15 and upgrade it to 8.0.21 and you will see it works ok, I recently (few days ago) upgraded my personal database from 8.0.16 to 8.0.21 without a problem, it's not 5T but around 1T but over 20000 tables and it went without a problem. It was .16 not .15 like yours but it was upgraded from .15 to .16 earlier without a hitch too so.. 

Without ability to reproduce there's zero way I can figure out what's going on there. This is most likely a corrupt tablespace, it can happen, a simple bit swap if you are using non-ecc memory is something that will happen few times a year, sometimes it gets recorded and ... In that case dump/restore is the only real solution.

all best
Bogdan
[15 Nov 2020 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[21 Aug 2021 8:41] MySQL Verification Team
Bug #104679 marked as duplicate of this one
[10 Sep 2021 10:14] Jehu Thomas
I have successfully upgraded MySQL to 8.0.26 from 8.0.15. The issue was with the stored procedures of a schema. It was not easy to find. 

I followed the below procedure:

1. Dropped all schema's except the system schemas and upgrade was successful.
2  Restored the server from backup. Then dropped the other 6 schemas one by one, restored the server in between, and found the schema that was causing the issue.
3. Then instead of dropping the tables I dropped all stored procedures and the upgrade was successful. If I upgrade with the stored procedures, the upgrade will fail.

Recommendation:
1. I think the 'mysql_upgrade --force' command only checks the tables and does not check stored procedures, functions, or triggers. Can it check these as well?
2. There's nothing in the log other than 'Data Dictionary initialization failed'. It doesn't show any other error even if '--log-error-verbosity=3'. Is it possible to add the error caused by stored procedures in the error logs?
[13 Sep 2021 7:30] MySQL Verification Team
Hi Jehu,

would it be possible for you to share the procedure that was preventing the upgrade?

thanks
[14 Sep 2021 7:07] Jehu Thomas
I have mentioned the procedure above. I am not sure which stored procedures are causing this issue as there are 288 stored procedures. This is difficult because I have to test it by dropping all 279 stored procedures and then upgrade the server and if the upgrade fails then I need to restore the server from the backup. This procedure should be repeated until I find the stored procedure that is causing this issue. But if there is a proper error message in the logs other than "Data Dictionary Initialization Failed" then it would have been easy. Also, it would be good if the 'mysql_upgrade --force' command could check there's anything in the stored procedures that are blocking the upgrade.
[14 Sep 2021 17:19] MySQL Verification Team
Hi,

Ah so you just removed all stored procedures and upgrade succeeded. I assumed you knew which one it is, I understand too well what you talk about finding the one out of N :(

Problem is that the error message does not give us enough data and without knowing what in the SP failed the upgrade we are not easily fixing it. If you can share "all" of them we can go through and find what's the problem but I don't know if you can do that or not (we'd need the dump without data with procedures) due to IP restrictions.

I'll see with our dev team what else we can do

Thanks
[14 Sep 2021 18:24] MySQL Verification Team
Hi Jehu,

A colleague reminded me that invalid UTF8 character can cause this, if you have it anywhere, can you check if you have any UTF8 characters in your stored procedures? Note that even if you have miss-formed character in the comment upgrade can fail (SP will work ok as it is in the comment but upgrade will not work).

Thanks
[15 Oct 2021 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".