Description:
Creating a temporary table, filling it with insert, at least 2 rows. At first CHECK reports OK. But after simply reading the table, CHECK reports warnings and/or errors.
From a 2x1 table:
Size of datafile is: 21 Should be: 14
Found 7 deleted space. Should be 0
Found 1 deleted blocks Should be: 0
Found 3 parts Should be: 2 parts
From a 3x3 table:
Size of datafile is: 46 Should be: 39
got error: 137 when reading datafile at record: 3
I found this tracking a different symptom, which might appear much later in processing with the table:
PHP Warning: mysql_query(): Unable to save result set
This error has been associated (google) with corrupt tables.
How to repeat:
CREATE TEMPORARY TABLE t (i INT);
INSERT INTO t SET i=1;
INSERT INTO t SET i=2;
CHECK TABLE t;
SELECT * FROM t;
CHECK TABLE t;
#################################################
CREATE TEMPORARY TABLE tx (i INT, j INT, k INT);
INSERT INTO tx SET i=11, j=22, k=33;
INSERT INTO tx SET i=44, j=55, k=66;
INSERT INTO tx SET i=77, j=88, k=99;
CHECK TABLE tx;
SELECT * FROM tx;
CHECK TABLE tx;
====================== interactive capture follows ============================
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.
C:\Documents and Settings\bobstein>mysql -uroot -p
Enter password: ************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15 to server version: 5.2.0-falcon-alpha-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Database changed
mysql> CREATE TEMPORARY TABLE t (i INT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SET i=1;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t SET i=2;
Query OK, 1 row affected (0.00 sec)
mysql> CHECK TABLE t;
+--------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.t | check | status | OK |
+--------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> CHECK TABLE t;
+--------+-------+----------+-------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+-------------------------------------------------+
| test.t | check | warning | Size of datafile is: 21 Should be: 14 |
| test.t | check | warning | Found 7 deleted space. Should be 0 |
| test.t | check | warning | Found 1 deleted blocks Should be: 0 |
| test.t | check | warning | Found 3 parts Should be: 2 parts |
| test.t | check | status | OK |
+--------+-------+----------+-------------------------------------------------+
5 rows in set (0.00 sec)
mysql>
mysql> #################################################
mysql>
mysql> CREATE TEMPORARY TABLE tx (i INT, j INT, k INT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tx SET i=11, j=22, k=33;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tx SET i=44, j=55, k=66;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tx SET i=77, j=88, k=99;
Query OK, 1 row affected (0.00 sec)
mysql> CHECK TABLE tx;
+---------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.tx | check | status | OK |
+---------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tx;
+------+------+------+
| i | j | k |
+------+------+------+
| 11 | 22 | 33 |
| 44 | 55 | 66 |
| 77 | 88 | 99 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> CHECK TABLE tx;
+---------+-------+----------+---------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+---------------------------------------------------+
| test.tx | check | warning | Size of datafile is: 46 Should be: 39 |
| test.tx | check | error | got error: 137 when reading datafile at record: 3 |
| test.tx | check | error | Corrupt |
+---------+-------+----------+---------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
