MySQL Bugs: #12511: Server crash with subsquery

Bug #12511	Server crash with subsquery
Submitted:	10 Aug 2005 22:02	Modified:	11 Sep 2005 20:09
Reporter:	R P	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.13	OS:	Windows (Windows Server 2003)
Assigned to:		CPU Architecture:	Any

Description:
Server crashes when updating rows with subquery.

How to repeat:
UPDATE
  table_1 AS t1
SET
  t1.column_1 = (SELECT id FROM table_2 WHERE column_2 = t1.column_3);

column_1 = INTEGER UNSIGNED NOT NULL
column_2 = CHAR(15) NOT NULL
column_3 = CHAR(15) NOT NULL

I basically added a new column (column_1) to store user id's instead of saving the actual username (column_2) and attempted to update rows with the query above.

I got a "MySQL has gone away" message a couple seconds after and the server had crashed / shutdown. I tried it a second time and happened again.

Sorry, but column_3's data type was actually VARCHAR(15). After changing it to CHAR(15) it didn't crash.

Hi,

Can you please provide the real query? I mean with forum_forums table.

Here it is:

UPDATE forum_forums t1 SET t1.last_post_user_id = (SELECT id FROM rp_user_account WHERE user_username = t1.last_post_username)

I'm not sure if it has anything to do with it being different data types. After going to another table to update it the same way, with a username field being VARCHAR(15), it worked fine.

Also, I hate to addon, but I figured since it also has to do with a subquery that it may be better to let you guys know here. This also crashed MySQL:

DELETE
  t1,
  t2
FROM
  forum_threads t1 LEFT JOIN forum_posts t2 ON t2.thread_id = t1.id
WHERE
  t1.id = (SELECT id FROM forum_threads WHERE last_post_user_id = 0)

A subquery really isn't needed there since that last line could easily be replaced with "t1.last_post_user_id = 0", but nonetheless MySQL doesn't like it.

Hi!

I was unable to reproduce this crash.  Can you please post all of your exact table definitions ?

Thanks

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".