Mysql (percona) does not start after xtrabackup restore

I’m moving to a new server. Did xtrabackup dump on machine 1, copied it to machine 2, prepared and restored (plus changed ownership to mysql.mysql).
All steps were successful.

But when I try to start mysqld ((mysqld 8.0.15-6) Percona Server (GPL), Release ‘6’, Revision ‘63abd08’) it tries to add foreign key constraints when starting- and fails:

2019-07-24T19:24:18.391720Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.15-6) starting as process 3697
2019-07-24T19:24:37.783978Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:24:44.781040Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:24:58.140755Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:25:04.669351Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:25:10.541926Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:25:15.943294Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:25:21.444415Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:25:26.642749Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:25:36.162198Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:25:41.015243Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:25:47.724146Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:25:58.505025Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:26:05.443897Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:26:11.788815Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:26:18.009204Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:26:23.537348Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:26:29.695161Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:26:37.746838Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’
2019-07-24T19:26:52.339164Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2019-07-24T19:26:52.340053Z 0 [ERROR] [MY-010119] [Server] Aborting

Any ideas how to make it run? Even just to fix the tables (add the missing index)?
Or is the only way to add the index on source machine, dump + copy + prepare + import again and hope that’s the only missing index?

Thanks

Hi,

I have Percona servers on two machines: 5.7 on machine A and 8.0 on B. I dumped the data using xtrabackup (as well as innobackupex wrapper with no more success) from A, copied it to the B, (successfully) prepared the data, (successfully) imported the data but when I try to run mysqld, I get the error:

[ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint ‘fk_srv_language_grid_spr_id_grd_id’ in the referenced table ‘srv_grid’

[ERROR] [MY-010022] [Server] Failed to Populate DD tables.

I checked the foreign key constraints for those particular tables on server A and they seem OK, app runs as it should…

How can I resolve this situation? I cannot disable foreign key checks if the server is not even running…

Hi May

Could you update with version information for MySQL/Percona Server for MySQL and Percona XtraBackup please? Are there any errors in the error log as the message suggests that indexes needed to support the foreign keys failed to build.

Meanwhile: in this article there’s mention of the error MY-010022 in case that provides any clue to the source of the issue https://mysqlserverteam.com/upgradin…-need-to-know/

It is also worth checking out this blog [URL=“MySQL Error Code 1215: "Cannot add foreign key constraint" - Percona Database Performance Blog”]https://www.percona.com/blog/2017/04...ey-constraint/[/URL] since it explains how to suspend fk checks

If you could check that and update with versions and if there are errors, I can see if I can find out more for you.