How to fix INSERT DELAYED Delayed_errors in MySQL.

How to fix INSERT DELAYED Delayed_errors in MySQL.

I”m posting this because I had such a hard time fixing this (and Google didn”t seem to have any articles on how to fix it).

If you are trying to add INSERT DELAYED queries to your application, and your rows are not getting inserted into your table, then you should check out your global status and variables:

mysql> show status LIKE ''%Delay%'';
| Variable_name | Value |
| Delayed_errors | xxxxx |
| Delayed_insert_threads | xxxx |
| Delayed_writes | xxxx |
| Not_flushed_delayed_rows | xxxx |
4 rows in set (0.00 sec)

mysql> show variables like ”%delay%”;+—————————-+——-+
| Variable_name | Value |
| delay_key_write | xxx |
| delayed_insert_limit | xxx |
| delayed_insert_timeout | xxx |
| delayed_queue_size | xxx |
| innodb_thread_sleep_delay | xxxx |
| max_delayed_threads | xxx |
| max_insert_delayed_threads | xxx |
7 rows in set (0.00 sec)


If you are seeing that Delayed_errors is being incremented, and your query works without the ”Delayed” element of the statement, then you might want to look into your table structure. Something about having an ”auto_increment” table and replication enabled caused my delayed inserts to fail.

Turns out if you add:


to the end of

INSERT DELAYED INTO `Table` VALUES (NULL, ''val'', ''val'', ''val'', ''val'', ''val'', ''val'', ''val'', ''val'' , ''val'');

to get

INSERT DELAYED INTO `Table` VALUES (NULL, ''val'', ''val'', ''val'', ''val'', ''val'', ''val'', ''val'', ''val'' , ''val'') ON DUPLICATE KEY UPDATE ID=ID+1;

it works. Turns out there was a duplicate key on the autoincremented ”ID” column of the table.

Did you find this useful? Extremely boring? Leave a comment and let me know:

Comments are closed.