Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30129

MySQL 5.7 --> MariaDB 10.8 switch: mysqlcheck --check --extended hangs on table

    XMLWordPrintable

Details

    Description

      This was originally posted as a question at
      https://dba.stackexchange.com/questions/320087/mysql-5-7-mariadb-10-8-switch-mysqlcheck-check-extended-hangs-on-table?noredirect=1#comment624082_320087
      but I'm filing it as bug report due to a recommendation there. I copied over the text of the stackexchange post along with some additional information

      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.

      I am working on getting a backtrace, but this is a bit tricky as it's a production server and I haven't had to do that before. A commenter thought that I should post it now anyway since the table structure alone might provide a clue.

      Note that this db started life as a mysql 5.7 db and was not upgraded from an early mysql version prior to the mariadb switch.

      Create table:

      CREATE TABLE `large_archive_table` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `a` datetime NOT NULL DEFAULT '1974-08-17 10:00:00',
        `b` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
        `c` varchar(256) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
        `d` varchar(256) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
        `e` varchar(256) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
        `f` tinyint(1) unsigned DEFAULT '0',
        `g` int(11) unsigned DEFAULT NULL,
        `h` varchar(100) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
        `i` int(11) unsigned DEFAULT NULL,
        `j` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
        `k` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
        `l` int(11) unsigned NOT NULL DEFAULT '0',
        `m` smallint(6) unsigned DEFAULT NULL,
        `n` smallint(6) unsigned DEFAULT NULL,
        `o` tinyint(4) unsigned DEFAULT NULL,
        `p` tinyint(4) unsigned DEFAULT NULL,
        `q` varchar(256) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
        `r` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
        `s` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `a` (`a`),
        KEY `i` (`i`),
        KEY `ip` (`b`),
        KEY `L` (`l`),
        KEY `q` (`q`(255)),
        KEY `c` (`c`(255)),
        KEY `g` (`g`) USING BTREE,
        KEY `r` (`r`) USING BTREE,
        KEY `k_r` (`k`,`r`) USING BTREE
      ) ENGINE=InnoDB AUTO_INCREMENT=9681472 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
      

      Attachments

        Issue Links

          Activity

            People

              danblack Daniel Black
              livingston@msp.org AJ Livingston
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.