MySQL Bugs: #6544: DATA LOSS - connection expired and not reported & joins loose TEXT fields

Bug #6544	DATA LOSS - connection expired and not reported & joins loose TEXT fields
Submitted:	10 Nov 2004 11:02	Modified:	14 Nov 2004 18:10
Reporter:	Paul Mihai	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.21	OS:	Windows (windows and linux)
Assigned to:		CPU Architecture:	Any

Description:

there are 2 bugs:
I'm using mysql 4.0.21 (both on Linux and Windows)
I've hit rough bottom at some complex scripts I needed to move data from 2 db's into a third one.
I used a php(4 with standard mysql connection) script in conjunction with a lot of sql. (php only does slight adjustments where necessary). 

First bug:

If the script runs for more then a certain nr of seconds (my script takes about 120 seconds to execute but problem appears even on execution times of ~60 seconds)  the script will continue to execute SQL's but filling the tables only with defaults (usually NULL's)

Second bug:

If I try joining (I tryed LEFT and INNER ) 2 tables after theyr respective primary keys (I have TEXT fields in both tables) the TEXT fields get lost (ie filled with NULL values)

Most of my script are INSERT INTO ... SELECT between db's (old tables-> temp tables -> new tables).

I know how to go round these problems but they're VERY ennoying. I would love it thow if mysql 5 wouldn't feature these bugs aswell.

How to repeat:
Do the precise things I did : 
try to move date from 2 db's to a third from php
ensure the execution time is somewhere over 60 seconds (maybe less I havn't tested it )
use INSERT INTO ... SELECT

for the second bug it's far simpler just make joins on 2 tables that contain text fields and see what happens to them in the resultset

Suggested fix:
I have fixed these things using php... but I would like to know if there is a SQL fix to this (changing DB distro is impossible)

Thank you for writting this report.

This bug report forum is only for repeatable bug reports. For bug
reports like this one you should use the MySQL mailing list or buy
commercial support from MySQL AB.  

What we need is fully repeatable test case.

This test case should consist of the series of SQL commands or actaions that will always lead to the problem, in your case to the crash.

Err... I doubt you have read the bug report.. as there is nothing mentioned about a crash...

And another thing.. the way you have answered the questions shows me you're totally incompetent (as you can't blank test the issue) I can't post here a 1300 lines script (80% SQL). And I won't buy mysql support if this is the kind of support you're giving... thanks but no thanks... might chose another db instead :)

Thank you for writting this report.

This bug report forum is only for repeatable bug reports. For bug
reports like this one you should use the MySQL mailing list or buy
commercial support from MySQL AB.  

What we need is fully repeatable test case.

This test case should consist of the series of SQL commands or actaions that
will always lead to the problem, in your case to the wrongly updated tables.

If your test case is too large, you can upload it to this issue, under "Files" section.

You sound like a robot... so I'm not going to send anything else .. you're just useless.. thank you :)

Dear Paul -

I am sorry for the miscommunications regarding your MySQL bug report #6544.   We value all bug 
reports very highly.   The volume of reports necessitates the use of standard replies, which may not 
have entirely fit your circumstances.

But #6544 really contains two bug reports.  

On the first, the two staff with whom I have discussed your report believe it is probably a problem 
related to PHP, not MySQL.   Without a repeatable test case or a paid support contract, we cannot 
pursue it further.

On the second, our staff did perform a test of what we thought your problem meant in using joins 
with text fields.   We did not see any problems in our results, but did not explicitly report this to 
you.

If we are to pursue this second bug further, we need a specific test case, which is a series of SQL 
statements that produces the bug, like this:

  1. create table a .......
  2. create table b ...
  3. insert into a (..........)
  4. insert into b (............)
  5. select .........

If you can provide such a test case, we can investigate further.  For more personal help on issues 
which are not strictly bugs, paid support assistance can be purchased for as little as $295 at https://
order.mysql.com/index.php?infopage=2.

Sincerely,

- Tom Basil
  MySQL, Inc. Director of Support

I traced the second bug and discovered that somewhere in my huge script things got tangled - sorry for that one.

The first is certainly NOT a php issue (or it's a php & mysql issue)

here is detailed description:
I run the script which takes about 100 seconds to execute
If I leave it do so it will fill some of the tables with default values (NULL's & 0's)
If I close the connection and re-open it every 20 seconds or so everything goes ok.
If I execute the script manually loading the sql's with a command line connection everything is ok.
Why I think it's not a php issue :
A: Mysql should report a loss of connection / expiration of connection to the client
B: Mysql should not execute any querryes after this conenction loss / expiration
C: Mysql should print out an error message 

The php client then would be anounced that something went wrong and (in my case) halt the script and print out an error - I do error checkings after each mysql query (to see all occured errors)

I can't post here the sql (db and sql are not my propertyes and I have to preserve confidentiality.. I think you can understand this). I will try to make a dummy db. But I tought you can do that too as you're supposed to have tousands if not millions of test cases. 

Anyway I solved the problem for my scripts but I sincerely hope you take this bug serious as it can affect the data integrity (hopefully mysql 5 will not have this bug - it will be the first thing I will test on it)

Anyway I consider mysql a fast and usually reliable db engine (tested myisam and innodb) and as such I hope you will take this bug and solve it somehow. I don't need support as I can overcome any issues as I did in this case, and the quality of a product is measured in the support needed to solve an issue (idealy none).

As I don't need a reply to this I'll leave it with you with the best hopes of a better connection handling :)

Ok, this is what can be the reason:

MySQL C API library (libmysql) has an auto-reconnect feature, it automatically reconnects if the connection is broken. Until recently is was ON by default.

php/mysql does not turns it off. New php/mysqli does.

That is you probably has auto-reconnect turned on, and when connection times out the library auto-reconnects for you, but all the session context - temporary tables, user variables, etc - is lost, so the rest of your scripts works incorrectly.

Workarounds are - use php/mysqli, recompile libmysql making auto-reconnect off by default, recompile php/mysql to disable auto-reconnect.