1

Recently I switched from MySQL 5.7 to MariaDB 10.8 and it's been generally great as a drop-in replacement, but I've run into a problem: the process for a cron job that daily runs

/usr/bin/nice /usr/bin/ionice -c3 \
    /usr/bin/mysqlcheck \
        --all-databases --check --extended --check-only-changed --silent  2>&1

hangs indefinitely on one of the larger tables (981 MB). Here's the relevant output from showing the process list after four days since server restart (there's one process for each day the cron job runs):

MariaDB [(none)]> show full processlist;
+-------+--------------+-----------------+-----------+---------+--------+-----------+----------------------------------------------+----------+
| Id    | User         | Host            | db        | Command | Time   | State     | Info                                         | Progress |
+-------+--------------+-----------------+-----------+---------+--------+-----------+----------------------------------------------+----------+
|  7719 | <cron user>  | localhost       | <db name> | Query   | 305690 | Executing | CHECK TABLE `<table name>`  EXTENDED CHANGED |    0.000 |
| 23567 | <cron user>  | localhost       | <db name> | Query   | 219929 | Executing | CHECK TABLE `<table name>`  EXTENDED CHANGED |    0.000 |
| 38185 | <cron user>  | localhost       | <db name> | Query   | 133529 | Executing | CHECK TABLE `<table name>`  EXTENDED CHANGED |    0.000 |
| 57790 | <cron user>  | localhost       | <db name> | Query   |  47127 | Executing | CHECK TABLE `<table name>`  EXTENDED CHANGED |    0.000 |

Nothing about this appears in /var/log/mysql/ error logs.

What next?

I'm hoping someone can point me to other things I can check to help diagnose the problem. If someone is kind enough to do so I'll update the question. Thanks!

OS and mariadb info

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 18.04.6 LTS
Release:    18.04
Codename:   bionic

$ mariadb --version
mariadb  Ver 15.1 Distrib 10.8.6-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
11
  • Are the tables MyISAM? Or InnoDB?
    – Rick James
    Nov 22, 2022 at 21:23
  • Can you install the debug symbol packages for 10.8.6 and gdb, and using that grab a backtrace and create a bug report. Include the structure of show create table <tablename>.
    – danblack
    Nov 22, 2022 at 21:27
  • Also sorry to say the nice/ionice will have no effect as the execution of CHECK TABLE is in the database itself. Looking forward to seeing your bug report.
    – danblack
    Nov 22, 2022 at 21:29
  • 1
    @AJLivingston - Regardless of the outcome of this thread, you should change the cron script to avoid letting a new copy start before any previous ones finish.
    – Rick James
    Nov 22, 2022 at 23:10
  • 1
    @danblack I created an issue: jira.mariadb.org/browse/MDEV-30129 while I'm working on getting the backtrace. The server was always 5.7, and I've noted that in the issue. Thanks! Nov 29, 2022 at 20:33

1 Answer 1

0

MariaDB Engineers in MDEV-24402 added the implementation of EXTENDED for CHECK TABLES in InnoDB and this was included in the releases:

  • MariaDB 10.6.11,
  • MariaDB 10.7.7,
  • MariaDB 10.8.6 and
  • MariaDB 10.9.4.

And every stable version above those.

MySQL-5.7 and previous MariaDB versions parsed the --extended flag, but it had no effect on InnoDB tables.

2
  • Thanks @danblack! Can you clarify in your answer that the --extended flag did nothing when --check was present even in MySQL 5.7? That would address the original question completely. Feb 22, 2023 at 18:44
  • Thanks! Accepted! And thanks again for all your help Feb 24, 2023 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.