0

I am upgrading a MySQL database from 5.6 to 5.7; it contains a few tables that are obscenely big, >500m rows in one case. I started the mysql_upgrade yesterday, and it has been chewing its way through the tables, until it reached the first, huge one. Now it shows:

mysql> show processlist;
+----+------+-----------+------+---------+-------+-------------------+----------------------------------------------------------------+-----------+---------------+
| Id | User | Host      | db   | Command | Time  | State             | Info                                                           | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+-------+-------------------+----------------------------------------------------------------+-----------+---------------+
|  2 | root | localhost | test | Query   | 69784 | copy to tmp table | REPAIR NO_WRITE_TO_BINLOG TABLE `somedatabase`.`whoppingtable` |         0 |             0 |
|  3 | root | localhost | NULL | Query   |     0 | starting          | show processlist                                               |         0 |             0 |
+----+------+-----------+------+---------+-------+-------------------+----------------------------------------------------------------+-----------+---------------+
2 rows in set (0.04 sec)

top show mysql running at high CPU pcts, but nothing is happens to diskspace - certainly not to the disk volume I use for /var/lib/mysql. How can I dig into this to see if there is any hope of it ever finishing?

Edit:

I been reading a bit, now. All my tables are InnoDB, and the manual seems to say that REPAIR TABLE only works on MyISAM tables, and that for InnoDB, one should use innodb_force_recovery=4 and restart the server. The table contains TIMESTAMP columns in the old format - I assume those are what particularly need upgrading. Still, for all that, is there any way to monitor the progress of this statement?

  • Please post results of SHOW FULL PROCESSLIST; – Wilson Hauck Dec 2 '19 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.