Not the answer you need?
Register and ask your own question!

Mysql (percona) does not start after xtrabackup restore

MayMay EntrantCurrent User Role Novice
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

Comments

  • MayMay Entrant Current User Role Novice
    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...
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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 https://www.percona.com/blog/2017/04...ey-constraint/ 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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.