ERT restore failed. Failed to backup database running restore on elastic-runtime

[cloudm9]

OpsManager 1.9.2 ERT 1.9.2

Trying to restore ERT. Getting below errors from cfops restore

017/02/08 16:53:06 E0208 16:53:06.296638 10271 execute_list.go:16] Process exited with status 1 2017/02/08 16:53:06 E0208 16:53:06.296778 10271 elasticruntime.go:69] Error backing up db Process exited with status 1 2017/02/08 16:53:06 E0208 16:53:06.29682 10271 createCliCommand.go:49] there was an error: failed to backup database running restore on elastic-runtime tile:tile

Here is the mysql log..

2017-02-08 21:53:04 139775647685376 [Note] WSREP: cluster conflict due to high priority abort for threads: 2017-02-08 21:53:04 139775647685376 [Note] WSREP: Winning thread: THD: 17029, mode: total order, state: executing, conflict: no conflict, seqno: 79655 SQL: DROP TABLE IF EXISTS scaling_events 2017-02-08 21:53:04 139775647685376 [Note] WSREP: Victim thread: THD: 17025, mode: local, state: idle, conflict: no conflict, seqno: -1 SQL: (null) 2017-02-08 21:53:04 139775647685376 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=4 solved by abort 2017-02-08 21:53:04 139779500526336 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=8 solved by abort 2017-02-08 21:53:04 139779500526336 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=8 solved by abort 2017-02-08 21:53:04 139775617952512 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=8 solved by abort 2017-02-08 21:53:04 139775618861824 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=8 solved by abort 2017-02-08 21:53:04 139775619771136 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=8 solved by abort 2017-02-08 21:53:04 139775647685376 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=4 solved by abort 2017-02-08 21:53:04 139775647685376 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=4 solved by abort 2017-02-08 21:53:04 139775647685376 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=4 solved by abort 2017-02-08 21:53:04 139775617952512 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=4 solved by abort 2017-02-08 21:53:04 139775617952512 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=4 solved by abort 2017-02-08 21:53:04 139775619771136 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=4 solved by abort 2017-02-08 21:53:04 139775624317696 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=7 solved by abort 2017-02-08 21:53:04 139775648291584 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=7 solved by abort 2017-02-08 21:53:04 139775618558720 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=7 solved by abort 2017-02-08 21:53:04 139775647685376 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=7 solved by abort 2017-02-08 21:53:04 139775647685376 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=7 solved by abort 2017-02-08 21:53:04 139775647685376 [Note] WSREP: MDL conflict db=autoscale table=scaling_events ticket=7 solved by abort InnoDB: Error: trying to create a MySQL system table mysql/db of type InnoDB. InnoDB: MySQL system tables must be of the MyISAM type!

Any idea?

Thanks,

[tinygrasshopper]

Hi @cloudm9,

Thanks for raising this issue. We'd advise you to contact Pivotal GSS for support - they will able to help you with this.

[cloudm9]

Ok, thank you!

[siennathesane]

@cloudm9 were you able to get assistance from Pivotal GSS for this issue? I'd like to go ahead and close out this issue.

[Murryy79]

@mxplusb has there been any update/fix on this issue? Just ran into this issue.

[Samze]

Came across this issue. Here are the notes from the investigation.

PCF 1.8 uses MariaDB 10.0.17

PCF 1.9.7 uses MariaDB 10.1.18 (checked on vm)

MariaDB 10.1.4 introduced a new property called enforce_storage_engine

This controls the ability to set engines beyond the default. In MariaDB 10.1.7 this includes the default sql_mode NO_ENGINE_SUBSTITUTION.

This appears to cause the command to fail.

To workaround this, I ran the cli sudo -i /var/vcap/packages/mariadb/bin/mysql -u root -h localhost --password=<pass> and then set global enforce_storage_engine=NULL;.

Rerunning the backup then seemed to work and all system tables were recreated successfully.

SHOW TABLES from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
30 rows in set (0.00 sec)

[Murryy79]

@Samze workaround worked!!! Thanks.

[henryaj]

This is now documented at http://www.cfops.io/#restoring-ert-on-pcf-1-9.

[anwarchk]

@henryaj Looks like the above work around breaks database replication. So, to make database replication work, you have to put the enforce_storage_engine value back what it was. It would be great to add this to the doc

[henryaj]

Thanks @anwarchk - that's noted in the docs on cfops.io:

From the same VM you used to perform the manual backup, or a similar VM, SSH into the MySQL Database VM. Use the MySQL password and IP address to enable the creation of tables using any storage engine. Run CF OPS restore. Use the MySQL password and IP address to restore original storage engine restriction. Log in to the MySQL client and flush privileges.