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

[10 Aug 2005 22:02] R P
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.
[11 Aug 2005 5:19] R P
Sorry, but column_3's data type was actually VARCHAR(15). After changing it to CHAR(15) it didn't crash.
[11 Aug 2005 12:03] MySQL Verification Team
Hi,

Can you please provide the real query? I mean with forum_forums table.
[11 Aug 2005 12:37] R P
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.
[11 Aug 2005 20:09] Jorge del Conde
Hi!

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

Thanks
[11 Sep 2005 23:00] Bugs System
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".