[#MDEV-30129] MySQL 5.7 --> MariaDB 10.8 switch: mysqlcheck --check --extended hangs on table

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

[MDEV-30129] MySQL 5.7 --> MariaDB 10.8 switch: mysqlcheck --check --extended hangs on table Created: 2022-11-29 Updated: 2023-02-05 Resolved: 2023-02-02
Status:	Closed
Project:	MariaDB Server
Component/s:	Documentation, Scripts & Clients, Server
Affects Version/s:	10.8.6
Fix Version/s:	10.6.11, 10.7.7, 10.8.6, 10.9.4

[MDEV-30129] MySQL 5.7 --> MariaDB 10.8 switch: mysqlcheck --check --extended hangs on table Created: 2022-11-29 Updated: 2023-02-05 Resolved: 2023-02-02