How to fix INSERT DELAYED Delayed_errors in MySQL.
Posted: June 29th, 2010 | Author: owocki | Filed under: Uncategorized | 2 Comments »
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:
ON DUPLICATE KEY UPDATE ID=ID+1;
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:
