Re: Repeatable mysql 'update' bug - lockup / cpu drain. - Dana Powers - com.mysql.lists.bugs

10 messages in com.mysql.lists.bugsRe: Repeatable mysql 'update' bug - l...

From	Sent On	Attachments
Sander Pilon	18 May 2000 08:14
Sander Pilon	18 May 2000 10:00
Dana Powers	18 May 2000 10:33
Sander Pilon	18 May 2000 10:54
Thimble Smith	18 May 2000 14:35
sas...@mysql.com	18 May 2000 15:53
sas...@mysql.com	18 May 2000 15:57
Sander Pilon	18 May 2000 16:10
sas...@mysql.com	18 May 2000 16:21
Michael Widenius	22 May 2000 04:48

Subject:	Re: Repeatable mysql 'update' bug - lockup / cpu drain.
From:	Dana Powers (dana...@quicknet.net)
Date:	05/18/2000 10:33:51 AM
List:	com.mysql.lists.bugs

I can verify that this causes an error in 3.22.29, but it works just fine with 3.23.16.

So does this warrant a new release of 3.22, or is the solution to upgrade to 3.23 even though it isnt a fully stable system?

I havent had any problems with 3.23.16, and as has been said on this list before, it is equally or more stable than 3.22 for anything that 3.22 did, but maybe not production level on all new features (such as BDB transactions, binary replication etc).

SELECT 3-22.feature,3-22.stability,3-23.stability FROM 3-22 LEFT JOIN 3-23 ON 3-22.feature = 3-23.feature;

Dana

On Thu, 18 May 2000, Sander Pilon wrote:

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

To unsubscribe, send a message to: <mysql-unsubscribe-dana420=quic...@lists.mysql.com>