| Bug #4059 | problem with indexes, after it, MYI destroyed | ||
|---|---|---|---|
| Submitted: | 8 Jun 2004 15:43 | Modified: | 13 Aug 2004 22:14 |
| Reporter: | András Bártházi | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S1 (Critical) |
| Version: | 4.1.1 | OS: | Linux (Debian Linux) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[16 Jun 2004 20:10]
Hartmut Holzgraefe
I ran a test script for about an hour that uses your table definition and adds, modifies and deletes entries at random. After 10.000 INSERTS, 1.000.000 UPDATES and 5.000 DELETES i still see no corruption. Could you please try 4.1.2 and see if the problem persists?
[16 Jun 2004 21:16]
Hartmut Holzgraefe
I now stopped the test run at 160.000 INSERTS and 16.000.000 UPDATES and checked file integrity with myisamchk -> still no problems.
[16 Jun 2004 22:19]
András Bártházi
The bug may be in binary table files?
Attachment: problem.zip (application/zip, text), 1.20 KiB.
[16 Jun 2004 22:28]
András Bártházi
I've installed 4.1.2, and I created a table with the script I sent to you. And the problem wasn't there, but it was strange to me, that the re-creation (inserting the data into this "shadow" table) was so fast. So I started 4.1.1 again. And the problem wasn't there. Strange. Then I've truncated the real, live table, and started to inserting the data again. And it's there. The problem is the same: server:/backup# mysql -u username -p id <digests.sql Enter password: ********* ERROR 1062 at line 800: Duplicate entry '%s' for key 5 I've truncated the table again, and attached the zip. I hope, it'll help you. I guess comparing the bytes of table's binaries created by you, and what I sent, maybe tell you what can be a problem... If I can help you, just tell me. :) Anyway, I used MySQL 4.1.1 for about a week, and it seem'd to be OK (except this problem).
[17 Jun 2004 13:14]
Hartmut Holzgraefe
can you please upload the digests.sql script, too?
Description: I have a real world database about news digests, interviews, etc. When my program tried to add a new item to it, it gave me an error, something like (sorry, I have no opporunity to repeat it and get the right message again): "Error with '%s' on index 5". I'm sure that the "'%s'" and "index 5" was in the message. It was about, that it cannot inserts the new row into the database, because there is duplication on index 5. After some minutes (database was under use during this time on a web page, just querys on this table), I've got that cannot open digests.MYI. It was there, but myisamchk reported there are problems with it. I had a dump, so I tried to restored the whole table, but the problem came back again. The some problem, another situation: all started, when I found, that the digests.MYI corrupted, and my program cannot insert a record into it. I learned, that myisamchk usually help this time, so I started it. I had about 3000 records in it, and it gave me: "there were ~2000 duplication, so I removed it". Ooops, my database is nowhere. :( I had a dump, so I'm very very lucky. ;) When I went back to MySQL 4.0.x (alpha, too), it's OK. How to repeat: create a table: CREATE TABLE digests ( id int(11) NOT NULL auto_increment, enabled enum('Y','N') DEFAULT 'Y' NOT NULL, date date DEFAULT '0000-00-00' NOT NULL, category char(1) DEFAULT '' NOT NULL, name varchar(250), medium varchar(250), mediumtype varchar(16), author varchar(250), text longtext DEFAULT '' NOT NULL, ctype enum('T','R') DEFAULT 'T' NOT NULL, link1 varchar(250), linktxt1 varchar(250), link2 varchar(250), linktxt2 varchar(250), link3 varchar(250), linktxt3 varchar(250), link4 varchar(250), linktxt4 varchar(250), pics varchar(250), PRIMARY KEY (id), KEY DATE (date), KEY ENABLED (enabled), KEY CATEGORY (category), KEY SEARCHFULL (name(64),author(64),text(255)) ); Fill it with 3000-4000 articles. I guess, the problem will be there, because of the SEARCHFULL key. I'm not able to give you the dump, because it is one of my client's database. If you don't know, what is the problem, and unable to find it (no one else reported it), maybe I could ask them to allow me to send you the database. It you need this, tell me (it's 42MB). Suggested fix: Look around the code. I don't know, what is the exact problem, so I'm not able to give a fix idea. I guess, that the problem is with the triple key index. The database is under 'heavy use', so I can't test the problem. :( If I can help you, just tell me, how?