| Bug #1812 | Table Corruption possibly due to "DELETE FROM TABLE" | ||
|---|---|---|---|
| Submitted: | 11 Nov 2003 22:54 | Modified: | 13 Dec 2003 16:43 |
| Reporter: | Paul Coldrey | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 4.0.16 | OS: | Windows (Windows XP) |
| Assigned to: | Michael Widenius | CPU Architecture: | Any |
[12 Nov 2003 13:10]
Paul Coldrey
Whoops, forgot to set the severity. Also, 1. I downgraded to 4.0.14b and the problem still ocurred. 2. As the first step in creating a simple test case, I altered the source of the data so that steps 4&5 don't occur and step 6 only inserts 1 record - problem still occurs.
[16 Nov 2003 19:24]
Farley Inglis
Miquel Soloranzo, Step #1: Open a CONNECTION to a DATABASE in (((lowercase))). Example: mysql test CREATE a TABLE and INSERT some records INTO it. SELECT * FROM this TABLE to verify that the records exist. Step #2: Open a 2nd-CONNECTION to the same DATABASE but use (((UPPERCASE))). Example: mysql TEST SELECT * FROM the TABLE you CREATED and INSERTED records to in 'Step 1:' above. NO RECORDS are returned; THE RECORDS ARE NOT THERE!!! Step #3: Open a 3rd-CONNECTION to the same DATABASE and use (((lowercase))). Example: mysql test SELECT * FROM the TABLE you CREATED and INSERTED records to in 'Step 1:' above. THE RECORDS ARE THERE. What's your take with the CASE-SENSITIVITIY relating to DATABASE-NAMES??? This may be the root-cause for the CORRUPTION-PROBLEM a lot of people seem to be reporting. Holler, Farley W. Inglis
[16 Nov 2003 20:40]
Paul Coldrey
Farley you are a sage fellow. Indeed I did create the database with a capital first letter but connected to it (via libmysql with a small first letter). If I capitalise the first letter in my C-Code then it all works fine. My attempts to create a nice test case have been hampered by the fact that every piece of code I run dies in a different fashion (which all seem to be related to corruption of the MYI file) and none of them fail in the same fashion as the large piece of code that caused me to report this bug. If Farley's example is not enough then let me know and I'll send in one of my test cases.
[17 Nov 2003 4:16]
MySQL Verification Team
Farley: Even I wasn't able for to repeat the issue with your sample, make sense to me a lot a chance it is bug because a similar case with the handle of database name with upper case was found with the bug report: http://bugs.mysql.com/bug.php?id=1736. So for to make my life easy please post the sql script how you got the behavior reported maybe in the way I am doing is the wrong way for to catch it.
[17 Nov 2003 13:03]
Farley Inglis
Miquel, I am using Win2K and lower_case_table_names=0. You need to open 3 DOS (Command Prompt) windows for this test. ==================================== ==================================== Window ONE: (LOWERCASE 'test' Database) C:\mysql\bin\mysql test <enter> >CREATE TABLE TESTTABLE (ID INT NOT NULL, F1 CHAR(10) NOT NULL, PRIMARY KEY(ID)) TYPE=MyISAM; <enter> >INSERT INTO TABLE TESTTABLE VALUES (1, 'Miquel'); <enter> >INSERT INTO TABLE TESTTABLE VALUES (2, 'Farley'); <enter> >SELECT * FROM TESTTABLE; <enter> 2 Records are returned. ==================================== Window TWO: (UPPERCASE 'TEST' Database) C:\mysql\bin\mysql TEST <enter> >SELECT * FROM TESTTABLE; <enter> 0 Records are returned. ==================================== Window THREE: (LOWERCASE 'test' Database) C:\mysql\bin\mysql test <enter> >SELECT * FROM TESTTABLE; <enter> 2 Records are returned. ==================================== ==================================== I hope that this makes the ISSUE more clear. Don't forget noticing the CASE-SENSITIVITY when referencing the 'test' or 'TEST' database. If you FLIP the UPPERCASEs to LOWERCASEs and the LOWERCASEs to UPPERCASEs, you will see the EXACT-SAME-RESULTS. Holler, Farley
[20 Nov 2003 15:03]
Paul Coldrey
I'm still struggling to get a small test case that show my original problem (although it is completely repeatable with my larger piece of code). Here is a small bit of code that exposes a problem which is probably related. Below is an SQL script that creates all the tables that are used to show the initial problem (a couple more than are needed for this issue - but I'm still hoping to tie down the other problem so I thought I'd send a full script). Below that is the C code I used to generate the issue - I linked this with libmysql using Visual C++ 6. Below that is my muy.ini file.
If I run the code and keep hitting keys (to repeat the loop) then it happily inserts 1000 rows at a time for as long as you care to keep trying. If however I connect to Bugme using a MySQL client tool and type "DELETE FROM Event" then the next time I run the code it doesn't insert any rows and it complains:
Query failed
Query: INSERT INTO Event(LoggerId, EventDate) VALUES(1, '2003-01-01')
Error: Duplicate entry '3001' for key 1
where the key value is always one higher that the last inserted key (and hence it does not exist - nor would it exist even if there were any rows in the table).
Also:
1. sometimes it reports simply "Table handler returned 22".... I haven't figured out what prompts this behaviour but it seems to happen sometimes the first time I come back to work on the test case after a turning on my PC and so it may be related to the server having shut down and started again
2. If I try to drop the Bugme database after the problem has occured I get the message: "Error on delete of '.\Bugme\Event.MYI' (Errcode: 13)"
I hope this helps :-)
-------------------------
-- SQL script below
-------------------------
CREATE DATABASE Bugme;
USE Bugme;
CREATE TABLE Download (
Id int(11) NOT NULL auto_increment,
LoggerId int(11) default NULL,
StartDate datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id),
KEY LoggerId (LoggerId),
KEY StartDate (StartDate)
);
CREATE TABLE Event (
Id INTEGER PRIMARY KEY AUTO_INCREMENT,
LoggerId INTEGER REFERENCES Logger(id),
Channel INTEGER,
ButtonId INTEGER,
MenuId INTEGER,
Temperature FLOAT (6,2),
Counter INTEGER,
Status INTEGER,
Flags smallint,
EventDate datetime NOT NULL default '0000-00-00 00:00:00',
DownloadId INTEGER,
DumpId INTEGER DEFAULT NULL,
INDEX(ButtonId),
INDEX(Channel),
INDEX(Temperature),
INDEX(Counter),
INDEX(Status),
INDEX(Flags),
INDEX(LoggerId),
INDEX(EventDate),
INDEX(DownloadId),
INDEX(DumpId)
) TYPE=MyISAM;
CREATE TABLE Logger (
Id INTEGER PRIMARY KEY AUTO_INCREMENT,
LoggerId varchar(16) NOT NULL default '',
Description varchar(255) NOT NULL default '',
LoggerTypeId integer references loggertypes(id),
Notes TEXT,
StartDate datetime NOT NULL,
EndDate datetime default NULL,
Active smallint default 1,
INDEX(LoggerId),
INDEX(Description),
INDEX(LoggerTypeId),
INDEX(StartDate),
INDEX(EndDate),
INDEX (Active)
) TYPE=MyISAM;
INSERT INTO Logger(LoggerId) VALUES ('480001');
-------------------------
-- C code below
-------------------------
#include <windows.h>
#include <stdio.h>
#include <conio.h>
#include "mysql.h"
MYSQL* db;
void query(const char* qry) {
if(mysql_query(db, qry)) {
printf("Query failed\n");
printf("Query: %s\n", qry);
printf("Error: %s\n", mysql_error(db));
//exit(0);
}
}
int main(void) {
char qry[1024];
MYSQL_ROW row;
MYSQL_RES* res;
while(1) {
db = mysql_init(NULL);
if(!mysql_real_connect(db, "localhost", "root", "", "bugme", 0, (const char*) NULL, 0)) {
printf("connection failed\n");
exit(0);
}
if(!db) {
printf("db still NULL after connection\n");
printf("Error: %s\n", mysql_error(db));
}
for(int i=0; i<1000; i++) {
sprintf(qry, "INSERT INTO Event(LoggerId, EventDate) VALUES(1, '2003-01-01')");
query(qry);
}
mysql_close(db);
printf("done\n");
getch();
}
}
-------------------------
-- my.ini below
-------------------------
[client]
port=3306
[mysqld]
port=3306
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
server-id = 1
set-variable = lower_case_table_names=OFF
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
set-variable = key_buffer=8M
set-variable = sort_buffer=8M
[myisamchk]
set-variable = key_buffer=8M
set-variable = sort_buffer=8M
[mysqlhotcopy]
interactive-timeout
[WinMySQLAdmin]
Server=C:/mysql/bin/mysqld-nt.exe
[23 Nov 2003 2:55]
Farley Inglis
Paul,
I have experienced TABLE-CORRUPTION to the point where NO-NORMAL-REPAIR-PROCESS would permanently-fix a TABLE in this condition. These were tables where there had been INSERTs, DELETEs and UPDATEs to a TABLE where the DATABASE-REFERENCE was with DIFFERING-(upper/lower)-CASEs. The problem is lying in the .MYD (data-structure) and not the .MYI (index-structure).
The only way that I was able to PERMANENTLY-REPAIR a table in this condition was to create a NEW-TABLE then RELOAD the data. The following are a couple of ways to correct this problem.
================================================
1. Do one more repair:
MYISAMCHK -c -f TBLNAME.MYI
2. MYSQLDUMP -hlocalhost -Ddbname -uusername -ppassword -d dbname TBLNAME > DumpFile.sql
3. MYSQL -B -hlocalhost -Ddbname -uusername -ppassword -N -e "DROP TABLE TBLNAME"
4 MYSQLADMIN REFRESH
5. Re-Create the TableStruct-And-DataRecords with:
MYSQL -hlocalhost -Ddbname -uusername -ppassword < DumpFile.sql
================================================
If Step 1 above fails because your table is TOO-BIG for your computer's resources to complete the MYSQLDUMP process then
1. STOP the MySQL-Service with:
NET STOP MySQL
1. Do one more repair:
MYISAMCHK -c -f TBLNAME.MYI
1. START the MySQL-Service with:
NET START MySQL
2. Export just the Table-Structure to a file with:
MYSQLDUMP -hlocalhost -Ddbname -uusername -ppassword -d dbname TBLNAME > DumpFile.sql
3. Use NOTEPAD to EDIT the DumpFile.sql and
CHANGE the TBLNAME to TBLNAMETMP. SAVE the DumpFile.sql to DISK.
4. Create the TBLNAMETMP data-structure with:
MYSQL -hlocalhost -Ddbname -uusername -ppassword < DumpFile.sql
5. Copy the data from TBLNAME to TBLNAMETMP with:
MYSQL -B -hlocalhost -Ddbname -uusername -ppassword -N -e "SELECT INTO TBLNAMETMP SELECT * FROM TBLNAME"
6. DROP the original TABLE with:
MYSQL -B -hlocalhost -Ddbname -uusername -ppassword -N -e "DROP TABLE TBLNAME"
7. FLUSH the TABLES with:
MYSQLADMIN REFRESH
8. RENAME TBLNAMETMP to the original TBLNAME with:
MYSQL -B -hlocalhost -Ddbname -uusername -ppassword -N -e "ALTER TABLE TBLNAMETMP RENAME TO TBLNAME"
9. FLUSH the TABLES with:
MYSQLADMIN REFRESH
================================================
After Re-Creating all of the Corrupted-Tables, REMEMBER not to mix UpperCase and LowerCase characters when referencing any Database-Names. I always use LOWERCASE for Database-Names and UPPERCASE for Table-Names. Since using these guidelines, I am not experiencing any more (unexplainable) Table-Corruptions.
Hope this helps,
Farley
[13 Dec 2003 16:43]
Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
I don't think this bugs is related to #1321 or #1736.
The 'bug' here is that if you set lower_case_table_names to OFF on a system where you have case insensitive file names (like windows) you WILL get table corruption if you access tables with different cases.
This is becasue MySQL doesn't know that the tables 'name' and 'NAME' are in fact the same table and will cache and lock each of these separately.
I have now added warnings about this in the MySQL manual.
I will also add a patch for 4.0.17 that if the file system were databases are stored is case sensitive, then we will force lower_case_table_names to 1.
[14 Dec 2003 14:00]
Paul Coldrey
Could I put in a feature request that at some stage this gets fixed by making the cache, table-locking, etc all work as case-insensitive on case-insensitive file systems. I know it seems petty, but I gets surprisingly annoying when you develop on both Windows and Linux platforms that mysqldump on Windows stuffs up the case of all the table names (as expected when lower_case_table_names is set). This is why I turned off lower_case_table_names. On the facecious side, people running Windows obviously aren't too concerned about performance so the extra function calls to remove case on-the-fly shouldn't cause a problem :-)
Description: It appears I may have found a bug which could be related to 1321 (or maybe not). Before I start let me apologise to the purists as this report doesn't contain a perfect test case, having said that I expect that it might contain enough information to allow the bug to be tracked down.... unfortunately I don't have the time to investigate it any further at the moment. If it proves hard to find then I will look at developing a small app which exploits the problem. Anyhoo, the situation is: I have some C++ code which uses libmysql to load data into a MySQL database as part of quite a complex system. What it does is: 1. SELECT Max and Min values from Event for a given condition (LoggerId = somevalue and EventDate > another value) 2. Create a record in a table called Download 3. Read the Id of the newly created record (using mysql_insert_id(...)) 4. Create a record in a table called Dump 5. Read the Id of the newly created record (using mysql_insert_id(...)) 6. Insert 1223 records in a table called Event When I run the first command I check that it returns a result and if not I show an error and use mysql_error(...) to get a little more info. On it's own I can run this over and over and it works fine. If however, I run the command "DELETE FROM Event" (using MySQLFront) in between each run of the software then after 3 or 4 iterations I get: "Got error 124 from table handler" And, if I run check table I get: mysql> check table Event -> ; +---------------+-------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------+-------+----------+--------------------------------------------------------+ | Daelibs.Event | check | warning | 2 clients is using or hasn't closed the table properly | | Daelibs.Event | check | status | OK | +---------------+-------+----------+--------------------------------------------------------+ 2 rows in set (0.00 sec) If I run the code again I get: "Incorrect key file for table: 'Event'. Try to repair it." When I run "Check Table Event" it says: mysql> check table Event; +---------------+-------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------+-------+----------+--------------------------------------------------------+ | Daelibs.Event | check | warning | Table is marked as crashed | | Daelibs.Event | check | warning | 3 clients is using or hasn't closed the table properly | | Daelibs.Event | check | error | Size of indexfile is: 1024 Should be: 124928 | | Daelibs.Event | check | error | Size of datafile is: 0 Should be: 63648 | | Daelibs.Event | check | error | Corrupt | +---------------+-------+----------+--------------------------------------------------------+ 5 rows in set (0.02 sec) Notably, if I change the DELETE command to: "DELETE FROM Event WHERE Id > 1" (to check whether it is the same problem as discussed in 1321), then I don't get the "Got error 124 from table handler" error message, it goes straight to the "Incorrect key file for table: 'Event'. Try to repair it." error. The tables in question look like this: CREATE TABLE Download ( Id int(11) NOT NULL auto_increment, LoggerId int(11) default NULL, StartDate datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (id), KEY LoggerId (LoggerId), KEY StartDate (StartDate) ); CREATE TABLE Dump ( Id int(11) NOT NULL auto_increment, Loggerid int(11) default NULL, ButtonId int(11) default NULL, EventDate datetime NOT NULL default '0000-00-00 00:00:00', StartDate datetime NOT NULL default '0000-00-00 00:00:00', EndDate datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (id), KEY LoggerId (LoggerId), KEY EventDate (EventDate), KEY StartDate (StartDate), KEY EndDate (EndDate) ); CREATE TABLE Event ( Id INTEGER PRIMARY KEY AUTO_INCREMENT, LoggerId INTEGER REFERENCES Logger(id), Channel INTEGER, ButtonId INTEGER, MenuId INTEGER, Temperature FLOAT (6,2), Counter INTEGER, Status INTEGER, Flags smallint, EventDate datetime NOT NULL default '0000-00-00 00:00:00', DownloadId INTEGER, DumpId INTEGER DEFAULT NULL, INDEX(ButtonId), INDEX(Channel), INDEX(Temperature), INDEX(Counter), INDEX(Status), INDEX(Flags), INDEX(LoggerId), INDEX(EventDate), INDEX(DownloadId), INDEX(DumpId) ) TYPE=MyISAM; How to repeat: See Description.