Summary:
Repeatable mysql bug where mysql is looping, eating CPU resources.
More detail:
I'm inserting / updating a table (layout below) in PHP. The 'update' goes
wrong.
The query: " UPDATE comment_headers SET thread_id = thread_id +1 WHERE
(root_id = 2) AND (thread_id > 12) "
keeps looping, keeps incrementing 'thread_id' in the same row over and over
again. The result is a lockup,
and upon inspection of the table an extremely large value of 'thread_id'.
(A value of 428503 where I expected a value of 14)
Anyone got a workaround, or perhaps a quick patch?
(Yes, I know I run 2 versions behind the latest Mysql, but changelog didnt
specify a fixed bug in this area.
And yes, I tried mysqlbug, but it gave me trouble. )
Regards,
Sander
------------
Details of query history, table layout and mysql version follow:
mysqladmin Ver 8.0 Distrib 3.22.32, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty
Server version 3.22.32
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 27 min 15 sec
Threads: 1 Questions: 12337 Slow queries: 7 Opens: 11 Flush tables: 2
Open tables: 2
#
# Mysqladmin output
#
mysqladmin processlist showed:
UPDATE comment_headers SET thread_id = thread_id +1 WHERE (root_id = 2) AND
(thread_id > 12)
#
# How to recreate
#
DROP TABLE comment_headers;
CREATE TABLE comment_headers (
comment_id int(11) DEFAULT '0' NOT NULL auto_increment,
root_id int(11) DEFAULT '0' NOT NULL,
parent_id int(11) DEFAULT '0' NOT NULL,
thread_id int(11) DEFAULT '0' NOT NULL,
article_id int(11) DEFAULT '0' NOT NULL,
article_type int(11) DEFAULT '0' NOT NULL,
author_id int(11) DEFAULT '0' NOT NULL,
posted datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
level int(11),
edit_nr int(11),
edit_date datetime,
PRIMARY KEY (comment_id),
KEY root_id (root_id),
KEY parent_id (parent_id),
KEY thread_id (thread_id),
KEY article_id (article_id),
KEY author_id (author_id)
);
INSERT INTO comment_headers VALUES( '1', '1', '0', '1', '0', '0', '0',
'2000-05-18 17:07:19', '0', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '2', '2', '0', '1', '0', '0', '0',
'2000-05-18 17:07:19', '0', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '3', '2', '2', '2', '0', '0', '0',
'2000-05-18 17:07:19', '1', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '4', '2', '3', '3', '0', '0', '0',
'2000-05-18 17:07:19', '2', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '5', '2', '4', '10', '0', '0', '0',
'2000-05-18 17:07:19', '3', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '6', '6', '0', '1', '0', '0', '0',
'2000-05-18 17:07:19', '0', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '7', '6', '6', '2', '0', '0', '0',
'2000-05-18 17:07:19', '1', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '8', '2', '3', '4', '0', '0', '0',
'2000-05-18 17:07:19', '2', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '9', '2', '8', '8', '0', '0', '0',
'2000-05-18 17:07:19', '3', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '10', '2', '8', '9', '0', '0', '0',
'2000-05-18 17:07:19', '3', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '11', '11', '0', '1', '0', '0', '0',
'2000-05-18 17:07:19', '0', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '12', '2', '3', '5', '0', '0', '0',
'2000-05-18 17:07:19', '2', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '13', '6', '6', '3', '0', '0', '0',
'2000-05-18 17:07:19', '1', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '14', '2', '4', '12', '0', '0', '0',
'2000-05-18 17:07:19', '3', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '15', '2', '3', '7', '0', '0', '0',
'2000-05-18 17:07:19', '2', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '16', '6', '6', '4', '0', '0', '0',
'2000-05-18 17:07:19', '1', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '17', '2', '5', '11', '0', '0', '0',
'2000-05-18 17:07:19', '4', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '19', '2', '12', '6', '0', '0', '0',
'2000-05-18 17:07:19', '3', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '20', '0', '0', '1', '0', '0', '0',
'2000-05-18 17:07:19', '0', '0', '0000-00-00 00:00:00');
INSERT INTO comment_headers VALUES( '18', '2', '4', '13', '0', '0', '0',
'2000-05-18 17:07:19', '3', '0', '0000-00-00 00:00:00');
UPDATE comment_headers SET thread_id = thread_id +1 WHERE (root_id = 2) AND
(thread_id > 12) ;
EOF
