howto restore "mysql" database (MyISAM) with enforce_storage_engine = InnoDB? InnoDB: Error: trying to create a MySQL system table mysql/db of type InnoDB. InnoDB: MySQL system tables must be of the MyISAM type!

[GETandSELECT]

Hello

we make backup with Shield (mysqldump plugin).

I can't restore the "mysql" database. I can't create "MyISAM" tables.

mysql --show-warnings -u root -p -h xxx < 2017-11-08-020000-2f081d14-90c9-451a-a260-b7135a7e2816.out --protocol=tcp -f                     [18/3472]
ERROR 1005 (HY000) at line 5720: Can't create table `mysql`.`db` (errno: -1 "Internal error < 0 (Not system error)")
Error (Code 1005): Can't create table `mysql`.`db` (errno: -1 "Internal error < 0 (Not system error)")
Warning (Code 1030): Got error -1 "Internal error < 0 (Not system error)" from storage engine InnoDB
ERROR 1146 (42S02) at line 5752: Table 'mysql.db' doesn't exist
ERROR 1146 (42S02) at line 5753: Table 'mysql.db' doesn't exist
ERROR 1146 (42S02) at line 5754: Table 'mysql.db' doesn't exist
ERROR 1146 (42S02) at line 5755: Table 'mysql.db' doesn't exist
ERROR 1005 (HY000) at line 5963: Can't create table `mysql`.`host` (errno: -1 "Internal error < 0 (Not system error)")
Error (Code 1005): Can't create table `mysql`.`host` (errno: -1 "Internal error < 0 (Not system error)")
Warning (Code 1030): Got error -1 "Internal error < 0 (Not system error)" from storage engine InnoDB
ERROR 1146 (42S02) at line 5992: Table 'mysql.host' doesn't exist
ERROR 1146 (42S02) at line 5993: Table 'mysql.host' doesn't exist
ERROR 1146 (42S02) at line 5994: Table 'mysql.host' doesn't exist
ERROR 1005 (HY000) at line 6439: Can't create table `mysql`.`user` (errno: -1 "Internal error < 0 (Not system error)")
Error (Code 1005): Can't create table `mysql`.`user` (errno: -1 "Internal error < 0 (Not system error)")
Warning (Code 1030): Got error -1 "Internal error < 0 (Not system error)" from storage engine InnoDB
ERROR 1146 (42S02) at line 6494: Table 'mysql.user' doesn't exist
ERROR 1146 (42S02) at line 6495: Table 'mysql.user' doesn't exist
ERROR 1146 (42S02) at line 6496: Table 'mysql.user' doesn't exist
ERROR 1146 (42S02) at line 6497: Table 'mysql.user' doesn't exist
ERROR 1146 (42S02) at line 6507: Table 'mysql.user' doesn't exist
Error (Code 1146): Table 'mysql.user' doesn't exist
Warning (Code 1405): Failed to revoke all privileges to dropped routine
ERROR 1146 (42S02) at line 6526: Table 'mysql.user' doesn't exist
Error (Code 1146): Table 'mysql.user' doesn't exist
Warning (Code 1405): Failed to revoke all privileges to dropped routine
ERROR 1146 (42S02) at line 6797: Table 'mysql.user' doesn't exist
Error (Code 1146): Table 'mysql.user' doesn't exist
Warning (Code 1405): Failed to revoke all privileges to dropped routine
ERROR 1146 (42S02) at line 14231: Table 'mysql.user' doesn't exist
Error (Code 1146): Table 'mysql.user' doesn't exist
Warning (Code 1405): Failed to revoke all privileges to dropped routine
ERROR 1146 (42S02) at line 14276: Table 'mysql.user' doesn't exist
Error (Code 1146): Table 'mysql.user' doesn't exist
Warning (Code 1405): Failed to revoke all privileges to dropped routine
ERROR 1146 (42S02) at line 14308: Table 'mysql.user' doesn't exist
Error (Code 1146): Table 'mysql.user' doesn't exist
Warning (Code 1405): Failed to revoke all privileges to dropped routine
ERROR 1146 (42S02) at line 14343: Table 'mysql.user' doesn't exist
Error (Code 1146): Table 'mysql.user' doesn't exist
Warning (Code 1405): Failed to revoke all privileges to dropped routine
ERROR 1146 (42S02) at line 15301: Table 'mysql.user' doesn't exist
Error (Code 1146): Table 'mysql.user' doesn't exist
Warning (Code 1405): Failed to revoke all privileges to dropped routine
ERROR 1146 (42S02) at line 15336: Table 'mysql.user' doesn't exist
Error (Code 1146): Table 'mysql.user' doesn't exist
Warning (Code 1405): Failed to revoke all privileges to dropped routine
ERROR 1146 (42S02) at line 16275: Table 'mysql.user' doesn't exist
Error (Code 1146): Table 'mysql.user' doesn't exist
Warning (Code 1405): Failed to revoke all privileges to dropped routine
(...)

How to restore "mysql" database with service broker created credentials? Enforce InnoDB is hardcoded, can't temp disable that

cf-mysql-release/jobs/mysql/templates/my.cnf.erb

Line 101 in 12e45db

enforce_storage_engine = InnoDB

Thank you

[cf-gitbot]

We have created an issue in Pivotal Tracker to manage this:

https://www.pivotaltracker.com/story/show/152953559

The labels on this github issue will be updated when the story is started.

[GETandSELECT]

Fixed with SET GLOBAL enforce_storage_engine = NULL; on all 3 nodes

[GETandSELECT]

After monit stop/start this error in logs and it fails

2017-11-17  7:51:40 140587228006272 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
171117 07:51:40 mysqld_safe mysqld from pid file /var/vcap/sys/run/mysql/mysql.pid ended

this error during restore

2017-11-17  7:40:43 140497832590080 [ERROR] Slave SQL: Error 'Table 'mysql.host' doesn't exist' on query. Default database: 'mysql'. Query: '/*!40000 ALTER TABLE `host` ENABLE KEYS */', Internal MariaDB error code: 1146
2017-11-17  7:40:43 140497832590080 [Warning] WSREP: RBR event 1 Query apply warning: 1, 2380
2017-11-17  7:40:43 140497832590080 [Warning] WSREP: Ignoring error for TO isolated action: source: 491c01bc-cb68-11e7-864e-e775a2b68149 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 65 trx_id: -1 seqnos (l: 4035, g: 2380, s: 2
379, d: 2379, ts: 1318193349497)
InnoDB: Error: trying to create a MySQL system table mysql/user of type InnoDB.
InnoDB: MySQL system tables must be of the MyISAM type!
2017-11-17  7:40:44 140497832590080 [ERROR] Slave SQL: Error 'Can't create table `mysql`.`user` (errno: -1 "Internal error < 0 (Not system error)")' on query. Default database: 'mysql'. Query: 'CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` en
2017-11-17  7:40:44 140497832590080 [Warning] WSREP: RBR event 1 Query apply warning: 1, 2446
2017-11-17  7:40:44 140497832590080 [Warning] WSREP: Ignoring error for TO isolated action: source: 491c01bc-cb68-11e7-864e-e775a2b68149 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 65 trx_id: -1 seqnos (l: 4101, g: 2446, s: 2
445, d: 2445, ts: 1318798207383)
2017-11-17  7:40:44 140497832590080 [ERROR] Slave SQL: Error 'Table 'mysql.user' doesn't exist' on query. Default database: 'mysql'. Query: '/*!40000 ALTER TABLE `user` DISABLE KEYS */', Internal MariaDB error code: 1146
2017-11-17  7:40:44 140497832590080 [Warning] WSREP: RBR event 1 Query apply warning: 1, 2447
2017-11-17  7:40:44 140497832590080 [Warning] WSREP: Ignoring error for TO isolated action: source: 491c01bc-cb68-11e7-864e-e775a2b68149 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 65 trx_id: -1 seqnos (l: 4102, g: 2447, s: 2
446, d: 2446, ts: 1318808573875)
2017-11-17  7:40:44 140497832590080 [ERROR] Slave SQL: Error 'Table 'mysql.user' doesn't exist' on query. Default database: 'mysql'. Query: '/*!40000 ALTER TABLE `user` ENABLE KEYS */', Internal MariaDB error code: 1146
2017-11-17  7:40:44 140497832590080 [Warning] WSREP: RBR event 1 Query apply warning: 1, 2448
2017-11-17  7:40:44 140497832590080 [Warning] WSREP: Ignoring error for TO isolated action: source: 491c01bc-cb68-11e7-864e-e775a2b68149 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 65 trx_id: -1 seqnos (l: 4103, g: 2448, s: 2
447, d: 2447, ts: 1318817205822)

any idea?

[cf-gitbot]

We have created an issue in Pivotal Tracker to manage this:

https://www.pivotaltracker.com/story/show/152954314

The labels on this github issue will be updated when the story is started.

[ldangeard-orange]

If you want to restore FULL backup mysqlDump, you must :
on the restore node :

set global enforce_storage_engine=NULL;
set global general_log=OFF;
set global slow_query_log=OFF;

And other node , stop MySQL
monit stop mariadb_ctrl

After restore, one the restore node :

set global enforce_storage_engine=InnoDB;
set global general_log=OFF;
set global slow_query_log=ON;

You must resync galera node, so on the other node :

rm -rf /var/vcap/store/mysql
/var/vcap/jobs/mysql/bin/pre-start
monit start mariadb_ctrl

[menicosia]

Say @ldangeard-orange, in your solution, I notice that you turn the general_log off. We don't use the general log, deferring instead to the MariaDB Audit Plugin.

Do you all need or prefer the general log in your deployments? Are you doing something to enable general log in cf-mysql?

--
Marco Nicosia
Product Manager
Pivotal Software, Inc.

[menicosia]

I've added a doc, backup-restore.md.

What do you think?

--
Marco Nicosia
Product Manager
Pivotal Software, Inc.

[GETandSELECT]

thanks @menicosia very good

Missing is PITR. We use mysqldump options --flush-logs --master-data=2 for PITR. This will generate this line in dump

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000302', MASTER_LOG_POS=366;

I use this command for apply the bin log:

/var/vcap/data/packages/mariadb/*/bin/mysqlbinlog --start-position=366 mysql-bin.000302 | mysql --show-warnings --defaults-file=/var/vcap/jobs/mysql/config/mylogin.cnf

Also note the option --show-warnings for mysql CLI client.

Also very interesting in you docs why you use -h MYSQL-NODE-IP as destination for mysqldump and not the virtual switchboard address? At the moment we use the switchboard address, but I wonder if we should switch to direct node. Thanks.

[ldangeard-orange]

Say @menicosia, i don't want to enable general_log , because it's not recommended (overhead).
But perhaps in future releases of cf-mysql it's possible to enable it.

So before restore, you turn off log, and after you set system variables with initial value.

Doc is ok for me