Bug #4795 Data removed from MyISAM tables when running SELECT queries
Submitted: 28 Jul 2004 20:47 Modified: 28 Jul 2004 20:52
Reporter: Greg Wolgemuth Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:4.1.3-beta-nt OS:Windows (Windows 2000 SP 4)
Assigned to: Matthew Lord CPU Architecture:Any

[28 Jul 2004 20:47] Greg Wolgemuth
Description:
A basic database was moved from a functioning MySQL 4.0.17-nt onto a test server using 4.1.3-beta-nt on Win2K SP 4. For approximately two weeks, SQL SELECT queries were run on the server using ODBC connections. However, at some point in time (presumably overnight, when the server was not in use), two things happened:
An entire column from a critical table was lost (all data from column was lost, column still present). Column was a non-indexed VARCHAR(15) field, which could be NULL and defaulted to NULL. The table had one primary key, which was of type int(11). The column lost was set completely to null.

The other occurence was an entire table was emptied. Any queries run against the table yielded an empty set, when the table previously contained data. The fields in the table were:
smallint(6)
double
int(11)
tinyint(4)
tinyint(4)
All of these fields could be NULL, and defaulted to being NULL.

Because the server in question was for development only, no log options were enabled. The mysql.err file does not show any log entries between when the server was started (two weeks ago) and when the data was lost (sometime between yesterday afternoon and this morning) Examining other logs on the server show that the only other actions which took place when the data was lost were automated virus definition file updates. 

The CHECK TABLE x and CHECK TABLE x EXTENDED commands were run against the affected tables, and both showed no changes/bad closes/errors. The describe table command also showed that the structure of both tables was intact. 

How to repeat:
Owing to the rather random nature of the bug, it hasn't been repeated and no attempt has been made. Here is how the SQL commands were being run (for those who wish to try to repeat this set of circumstances):

Server A would run IIS 5.0, with a VB 6 ActiveX DLL as a Web Service (SOAP Call)
The DLL is activated by the SOAP call, then initiates an ODBC connection using ADO.
The ODBC source is set to connect to localhost as root (we were just doing testing on dummy data) on port 3306. The ODBC connector used was 3.51. The only driver configuration options checked were Don't Optimize Column Width and Return Matching Rows. All other boxes were unchecked.
Once the connection goes through, any of the following queries could be made:
SELECT X FROM Y WHERE Z = V
SELECT DISTINCT X FROM Y
SELECT X, Y, Z FROM V WHERE U = T AND X BETWEEN A AND B
SELECT MIN(X) AS Y FROM Z
SELECT MAX(X) AS Y FROM Z

The only query which acted on the table which was deleted was of the form SELECT X, Y, Z FROM V ..... The query which ran on the table with the lost column was of the type SELECT X FROM Y WHERE Z = V

Suggested fix:
None that I am presently aware of.
[28 Jul 2004 20:52] Matthew Lord
Dear Sir,

We need to have a repeatable test case to proceed in this avenue.  This seems very strange.
I would enable the general log so that you can see if someone is executing these sql statements 
w/o your knowledge.

Best Regards
[28 Jul 2004 21:02] Greg Wolgemuth
For all future testing, we've enabled --log and --log-bin on the server daemon. However, I can guarantee that no other SQL commands were being run on this server - only about ten people know this 'server' (an old PIII 500) exists, then only four know that the database is loaded on it, and then only one system/person was ever running commands on the database.
[10 Jun 2005 3:52] Santhanam Veerasamy
Dear Friends,
This problem happens in MySQL 4.0.17 on Redhat Linux 9 / RHEL 3 AS also. Please suggest a solution. We are using MySQL for very critical and sensitive purpose.
Thanks in Advance
With Regards
SAnthanam