maria-discuss team mailing list archive

[mariadb@xxxxxxxxxxxxxx]

With this table:

CREATE TABLE t1 (
 label varchar(100) NOT NULL,
 dt datetime NOT NULL,
 success tinyint(1) NOT NULL,
 PRIMARY KEY (label,dt),
 KEY dt (dt),
 KEY success (success,dt)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

I have a DELETE query that has been stuck in Statistics for nearly an 
hour now:

DELETE
    MyDB.t1
FROM
    MyDB.t1
WHERE
    dt <= '2023-02-21 08:06:46'
    AND dt >= '2023-02-21 08:03:48'

This statement runs several times per hour to trim the table (and has 
for years). It has run fine against my 10.6.12 DB (this is a downstream 
replica DB) for a couple weeks now(when I upgraded the replica); but 
suddenly today produced the hang. Any ideas why? The table has nearly 15 
million rows; but the rows are more or less evenly spaced across datetimes.

Also I find that I cannot kill the query using KILL 12345; the KILL 
statement just comes back immediately without reporting an error and the 
statement continues. Is there no way to kill this statement without 
crashing the DB? Again, this is a replica statement that is executing. I 
had planned to stop the query, execute an ANALYZE TABLE t1 and then try 
it again, but I don't seem to be able to kill the query.

Thank you!

Dan