I have a MariaDB 15.1 installed on a Debian system:
$ mariadb --version
mariadb Ver 15.1 Distrib 10.4.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
A script regularly loads data into a table with LOAD DATA LOCAL INFILE. The script won't try a new LOAD job, until the previous one is complete. It worked fine, but has recently failed (the process hangs with no result) without any outside changes.
Executing the query by hand or just running an insert in mysqlclient produced the same behaviour.
Looking at the process list, I see the following:
MariaDB [information_schema]> SHOW FULL PROCESSLIST;
+---------+-------------+-------------------------+--------------------+---------+-------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+---------+-------------+-------------------------+--------------------+---------+-------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------+----------+
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 1129656 | root | localhost | information_schema | Query | 0 | Init | SHOW FULL PROCESSLIST | 0.000 |
| 1130651 | monday | somehost:50688 | mydata | Query | 886 | Waiting for table metadata lock | LOAD DATA LOCAL INFILE 'measurements_2020_9_8.tmp' INTO TABLE apsdata FIELDS TERMINATED BY ',' | 0.000 |
| 1131149 | monday | somehost:50800 | mydata | Query | 503 | Waiting for table metadata lock | insert into apsdata values(0,0,0,0,0,0,0) | 0.000 |
...
+---------+-------------+-------------------------+--------------------+---------+-------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------+----------+
No locks or waits are present in the information_schema DB:
MariaDB [information_schema]> select * from INNODB_LOCK_WAITS;
Empty set (0.000 sec)
MariaDB [information_schema]> select * from INNODB_LOCKS;
Empty set (0.000 sec)
I have tried UNLOCK TABLES, with no success.
Why is my table locked? There is no blocking query and no lock I can remove. How do I remove the metadata lock?
