Bug #6701 Server crash on some queries
Submitted: 18 Nov 2004 17:36 Modified: 16 Jan 2005 0:48
Reporter: Gleb Paharenko Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:Windows (windows 2003)
Assigned to: CPU Architecture:Any

[18 Nov 2004 17:36] Gleb Paharenko
Description:
In one of mysql lists' posts was reported strange server crash.
The original message from Klaus Prückl - http://lists.mysql.com/mysql/175969.
More info in authors reply to my questions - http://lists.mysql.com/win32/16017.
Files at  ftp://ftp.mysql.com/pub/mysql/upload:
k.prueckl@itpro.at - mysql 4.1.7 crash - mysql.err
k.prueckl@itpro.at - mysql 4.1.7 crash - my.ini
k.prueckl@itpro.at - mysql 4.1.7 crash - ddl.sql

Query:
SELECT bs.ID, bs.Name,
  (SELECT convert(av.value, SIGNED)
    FROM (AttributeValues av JOIN Batches b ON av.FK_RowID = b.ID) JOIN Products p ON p.ID = b.FK_ProductID
    WHERE b.FK_BatchStorageID = bs.ID AND av.Deleted = 0 AND av.FK_AttributeID IN (77, 90, 161, 162) AND b.Deleted = 0 AND b.Completed = 0 limit 1) As Weight,
  (SELECT p.Name
    FROM Batches b JOIN Products p ON p.ID = b.FK_ProductID
    WHERE b.FK_BatchStorageID = bs.ID AND b.Deleted = 0 AND b.Completed = 0 limit 1) As ProductName
FROM BatchStorages bs
WHERE bs.Deleted = 0 AND bs.FK_OwnerPartyID = 2;

For more info you should go to Klaus: k.prueckl@itpro.at.

How to repeat:
Klaus was unable to repeat this bug on another machine.
[18 Nov 2004 22:35] MySQL Verification Team
Hi,

I don't see any files with such names. Please, ask user put all these files to the compressed archive and upload one more time.
[18 Nov 2004 23:05] Sergei Golubchik
sorry for confusion, files are there. no need to re-upload
[19 Nov 2004 0:06] MySQL Verification Team
I downloaded the files and noticed that isn't possible to create the 
tables with the ddl.sql script because there are FK constraints with
tables not existing there. Also maybe will be necessary a dump
for to populate the tables for to run the query.

Thanks
[19 Nov 2004 7:20] Gleb Paharenko
Sorry for this question, but should I work as a wrapper between you and Klaus?
Or you will contact him directly?
Thanks.
[19 Nov 2004 9:59] Klaus Prückl
Hi,

Today I have a new server running Windows Server 2003 (Standard Ed.) here for evaluating the problem. I'm trying to rebuild the error on this machine. (Maybe it is depending on the operation system).
[19 Nov 2004 12:11] Klaus Prückl
I was not able to reproduce the hanging connections on another machine. So this seems not to be a problem with Windows Server 2003. But the hardware of the two machines is not really comparable:
AMD Athlon XP 1,8GHz, 512 MB RAM (hanging connections)
vs
HP Proliant 2x Intel Xeon 2,8GHz, 2GB RAM (working)

I'm doing some more tests to ensure that the new server is not affected.

BTW, is the crash report (mysql.err) enough for tracking the "real crash"?
[22 Nov 2004 14:15] Klaus Prückl
OK, Update:
MySQL also crashes on the new Xeon-Server; so I've switched from mysqld-nt to mysqld-debug. Upload of the error files and memorydumps follows.
[22 Nov 2004 17:22] Klaus Prückl
Status:
The connections also hang on the new server (so seems to be clearly a MySQL bug - at least on Windows Server 2003).
This also happens with Connector .NET 1.0.2 (not only old clients).

I'm going to check if I'm allowed to upload a db dump or otherwise I'm trying to rebuild the bug with obfuscated data. BTW, are the uploads visible to the public?

What other files / information do you need (are the from Windows automatic generated memory dumps useful for you)?
[26 Nov 2004 14:27] MySQL Verification Team
No, only MySQL developers can see uploaded files.
[30 Nov 2004 14:39] Klaus Prückl
After verifying some legal questions, we are probably going to buy a single incident support case for solving this bug. Do you have separate platforms/web-portals to handle these commercial support cases or are these also solved on bugs.mysql.com?
[7 Dec 2004 9:17] Klaus Prückl
Update:
- The crashes also occur on Windows XP
- I've written a tool to obfuscate the data, but on my first attempts the server did not crash. So I guess, that after some queries the InnoDB tablespace gets corrupt and then the crashes always occur.

So would it be possible that you set up a MySQL instance with our database (with obfuscated data) and we configure our application to use your server?
Then (I hope) it would be possible to rebuild the crashes in your environment.
[9 Dec 2004 16:08] Klaus Prückl
Update:
Because of performance reasons we've added some indices on a table. With this change MySQL is more stable by far - it did not crash since we've made the changes. I will keep you up to date.
[13 Dec 2004 18:30] Klaus Prückl
Update:
Today MySQL crashed again. I guess that this happens in combination with concurrent connections. If the server crashes again, we are going to upload a dump.
[13 Dec 2004 19:51] MySQL Verification Team
Thank you for feedback.
Yes, please upload a dump file and let us know a file name.
[15 Dec 2004 9:37] Klaus Prückl
Update:
I've switched back to mysqld-debug again and uploaded the error files:
you can find them in "mysql server bug#6701 errorfiles 20041215.zip".
It seems that there is an error in memory allocation or in the usage of that allocated memory.
[17 Dec 2004 9:02] Klaus Prückl
I've switched to MySQL 4.1.8 today. I let you know if the bug still exists.
[23 Dec 2004 9:33] Klaus Prückl
Update:
MySQL 4.1.8 is also affected, so I've switched to the debug version.
Can you add server version 4.1.8 to the bug-description or (better) transfer the the bug to my account so I can do this myself?

BTW, I'm still waiting for a confirmation from our company to upload a dump to your server.
[23 Dec 2004 14:26] Klaus Prückl
Update:
I've uploaded the dump file as "mysql server bug#6701 dump.zip".
It is not possible to crash the server with a special select-statement, so the best way is that you set up a server with this dump and we configure our application to use that db.
BTW, is it possible to trace all sql statements that are executed on server-side?
[16 Jan 2005 0:48] MySQL Verification Team
Please follow the http://bugs.mysql.com/bug.php?id=7760.

It is the same case.