Hi,
We're looking at changing an existing database from MyISAM to InnoDB. The existing system is using MariaDB
5.5.56 on RHEL 7.4
The plan is to
- create a replication slave on the same host
- use ALTER TABLE .. ENGINE=InnoDB on the slave, with 4 ALTERs running concurrently
- replace the master with the "coverted" slave
Doing this with a small database (~ 3.5GB)
works okay with the largest table taking ~ 7 minutes to convert.
However, while trying to figure out how to replicate from the new master, we found that we need "FLUSH TABLES .. FOR EXPORT" which isn't available in MariaDB 5.5. So we updated to MariaDB 10.2.12 and started again.
Now when we run the "ALTER TABLE .. ENIGNE=InnoDB", it takes hours from the ALTERs to complete and we're getting the following log entries
2018-01-09 12:36:03 140132036818688 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_12#P#P20160101` /* Partition `P20160101` */: Lock wait timeout
2018-01-09 12:36:54 140132036515584 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_13#P#P20160101` /* Partition `P20160101` */: Lock wait timeout
2018-01-09 12:37:45 140132036818688 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_12#P#P20160601` /* Partition `P20160601` */: Lock wait timeout
2018-01-09 12:38:36 140132036818688 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_12#P#P20160701` /* Partition `P20160701` */: Lock wait timeout
2018-01-09 12:39:27 140132037728000 [ERROR] InnoDB: Cannot save table statistics for table `statsdb`.`#sql-6d1a_11#P#P20160701` /* Partition `P20160701` */: Lock wait timeout
...
Any ideas on what's wrong here?
Thanks,
Conor