Table Crashes when INSERTing and DELETEing records

[JoachimChello]

Hello.

Could anyone help me with the following problem? I cannot believe that MySql cannot handle this test, but my table keeps crashing. Any help would be appreciated.

Problem description
My MySQL table gets corrupted when running a simple test: one thread
inserts a huge number of records in a 3-field table with one index,
while another thread deletes records from that same table. MySQL doesn't
die, it's just that the table gets corrupted quickly, during each test.

Applies to
- MySQL version 3.23.51
- MyODBC 2.50.39
- Windows 2000 SP2
- on a Pentium 550 with 128 Mb RAM and approx. 20 Gb free disk space.

Background
I'm running some MySQL tests, to see if it's a good alternative for
MS-Access 2000. My Access databases get corrupted too often. My largest
tables are not more than 45.000 records in size, but are expected to
grow until 500.000 or more records in the next few years. I'm looking
for a platform that can handle those bigger tables with multiple threads.

Test environment
In installed the MySQL / MyODBC binaries and applied all Windows 2000
updates mentioned in the MyODBC documentation. Mysqld-nt.exe is installed
on my Windows 2000 machine as a service.

The test was run on a table named 'test', with a primary index 'id' and
two 'tinytext' fields, 'Noidx' and 'idx'. 'Noidx' has no indexes, while
'idx' has a fulltext index. This is the query I used to create the table:

CREATE TABLE 'test' ( 'id' int(10) unsigned 
    NOT NULL auto_increment, 'Noidx' tinytext, 
    'idx' tinytext, PRIMARY KEY  ('id'),
    FULLTEXT KEY 'idxname' ('idx') TYPE=MyISAM;

Next, I wrote an (ASP) script that inserts several 1.000 records at a time,
with different values for 'Noidx' and 'idx' for each record. Each query is
generated at random and looks like:

INSERT INTO TEST 
   VALUES ( Null, 'lbtl pyji FjDOhKgNRu hcspJ fjGvto BbvRcSjl', 
   'LLKOBHNiGF ELCkfg OlgQyGH KwqxdpoC jQJQ aHcfhaw Dplb' );

(Both strings are actually 255 characters long).

The script works fine, inserting as many records as I define in the
FOR...NEXT loop (with 'Server.ScriptTimeOut = 1200' because the ASP
script needs some time to complete). But the table crashes almost
immediately when I start a second MySQL session in a cmd-window and
delete records with queries like 'DELETE FROM TEST WHERE ID=44;' or
'DELETE FROM TEST WHERE ID < 1000;'. 'SELECT' and other queries run
without problems, but each time I use 'DELETE' a crash occurs.
Subsequently, a 'REPAIR TABLE TEST' takes up to 29 minutes to complete
- with approx. 55.000 records in the table.

Here are some things I tried to solve the problem:

- Surrounded the inserting routine with 'LOCK TABLES TEST WRITE;' and
'UNLOCK TABLES;' - same result.
- 'OPTIMIZE TABLE TEST' - table keeps crashing.
- Connected to a DSN, checked and un-checked MyODBC 2.50.39 options
&quot;Return matching rows&quot; and/or &quot;Safety&quot; enabled. No difference.
- Connected without DSN (is this true?) with 'strConn = &quot;DRIVER={MySQL};
DATABASE=tPSC&quot; ' etc. No difference.

Having read the manuals, I cannot believe that MySQL can't handle
two threads both changing a table with only three fields and one
index. So I think that something else must be wrong. But what?

Some server output is included below. Any help would be greatly
appreciated.


Joachim Kruyswijk

SOME SERVER OUTPUT

mysql> delete from test where id < 1000;
ERROR 1034: Incorrect key file for table: 'test'. Try to repair it

mysql> check table test;
+-----------+-------+----------+----------------------------+
| Table     | Op    | Msg_type | Msg_text                   |
+-----------+-------+----------+----------------------------+
| tpsc.test | check | warning  | Table is marked as crashed |
| tpsc.test | check | error    | Found 55362 keys of 55369  |
| tpsc.test | check | error    | Corrupt                    |
+-----------+-------+----------+----------------------------+

mysql> REPAIR TABLE TEST
+-----------+--------+----------+-----------------------------------------------------------------------+
| tpsc.TEST | repair | info     | Wrong bytesec: 74-110-32 at 25056848; Skipped                         |
| tpsc.TEST | repair | info     | Duplicate key 1 for record at 25057180 against new record at 25055116 |
| tpsc.TEST | repair | info     | Duplicate key 1 for record at 25057704 against new record at 25055148 |
| tpsc.TEST | repair | info     | Duplicate key 1 for record at 25058228 against new record at 25055180 |
| tpsc.TEST | repair | info     | Duplicate key 1 for record at 25058752 against new record at 25055212 |
| tpsc.TEST | repair | info     | Duplicate key 1 for record at 25059276 against new record at 25055244 |
| tpsc.TEST | repair | warning  | Number of rows changed from 55369 to 55365                            |
| tpsc.TEST | repair | status   | OK                                                                    |
+-----------+--------+----------+-----------------------------------------------------------------------+
8 rows in set (32 min 58.28 sec) 

mysql> DESCRIBE TEST
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned |      | PRI | NULL    | auto_increment |
| Noidx | tinytext         | YES  |     | NULL    |                |
| idx   | tinytext         | YES  | MUL | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

mysql> SHOW INDEX FROM TEST;
+-------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+-------+------------+----------+--------------+-------------+
| test  |          0 | PRIMARY  |            1 | id          |
| test  |          1 | idxname  |            1 | idx         |
+-------+------------+----------+--------------+-------------+

-----------+-------------+----------+--------+----------+
 Collation | Cardinality | Sub_part | Packed | Comment  |
-----------+-------------+----------+--------+----------+
 A         |       55369 |     NULL | NULL   |          |
 A         |       55369 |        1 | NULL   | FULLTEXT |
-----------+-------------+----------+--------+----------+

 

[sleipnir214]

A couple of thoughts.

Have you tried InnoDB tables instead of MyISAM tables? They are more robust, though a little more work to implement.

Have you tried running the server on another platform? You may be having locking or latency problems running MySQL on Win2K. ______________________________________________________________________
Don't say thanks. Just award stars.
______________________________________________________________________