Description:
CHECK TABLE reports various errors and warnings, seems flakey and data-dependent:
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
Size of datafile is: 46 Should be: 39
got error: 137 when reading datafile at record: 3
The first four came from cloning a 1-column 2-row table. The last two from cloning a 3x3 table.
The destination table will report the same warnings and/or errors, but only after it is first read.
No such problems with MySQL version 4.1.18
Yes problems with 5.1.9
Similar results whether cloning via CREATE TEMPORARY TABLE ... SELECT ... ;
or CREATE TEMPORARY TABLE ...; INSERT ... SELECT;
Another symptom, later processing may generate this error:
PHP Warning: mysql_query(): Unable to save result set
How to repeat:
CREATE TEMPORARY TABLE t1Test (i INT);
INSERT INTO t1Test SET i=1;
INSERT INTO t1Test SET i=2;
CHECK TABLE t1Test;
CREATE TEMPORARY TABLE t2Test SELECT * FROM t1Test;
CHECK TABLE t1Test;
CHECK TABLE t2Test;
SELECT * FROM t2Test;
CHECK TABLE t2Test;
/* or */
CREATE TEMPORARY TABLE SuqTest (i INT, j INT, k INT);
INSERT INTO SuqTest SET i=11, j=22, k=33;
INSERT INTO SuqTest SET i=44, j=55, k=66;
INSERT INTO SuqTest SET i=77, j=88, k=99;
CREATE TEMPORARY TABLE BloTest (i INT, j INT, k INT);
CHECK TABLE SuqTest;
INSERT BloTest (i,j,k) SELECT i,j,k FROM SuqTest;
CHECK TABLE SuqTest;
CHECK TABLE BloTest;
SELECT * FROM BloTest;
CHECK TABLE BloTest;
====================== 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 9 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 t1Test (i INT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1Test SET i=1;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1Test SET i=2;
Query OK, 1 row affected (0.00 sec)
mysql> CHECK TABLE t1Test;
+-------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.t1test | check | status | OK |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> CREATE TEMPORARY TABLE t2Test SELECT * FROM t1Test;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CHECK TABLE t1Test;
+-------------+-------+----------+-------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+-------------------------------------------------+
| test.t1Test | check | warning | Size of datafile is: 21 Should be: 14 |
| test.t1Test | check | warning | Found 7 deleted space. Should be 0 |
| test.t1Test | check | warning | Found 1 deleted blocks Should be: 0 |
| test.t1Test | check | warning | Found 3 parts Should be: 2 parts |
| test.t1test | check | status | OK |
+-------------+-------+----------+-------------------------------------------------+
5 rows in set (0.02 sec)
mysql>
mysql> CHECK TABLE t2Test;
+-------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.t2test | check | status | OK |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2Test;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> CHECK TABLE t2Test;
+-------------+-------+----------+-------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+-------------------------------------------------+
| test.t2Test | check | warning | Size of datafile is: 21 Should be: 14 |
| test.t2Test | check | warning | Found 7 deleted space. Should be 0 |
| test.t2Test | check | warning | Found 1 deleted blocks Should be: 0 |
| test.t2Test | check | warning | Found 3 parts Should be: 2 parts |
| test.t2test | check | status | OK |
+-------------+-------+----------+-------------------------------------------------+
5 rows in set (0.00 sec)
mysql> CREATE TEMPORARY TABLE SuqTest (i INT, j INT, k INT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SuqTest SET i=11, j=22, k=33;
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO SuqTest SET i=44, j=55, k=66;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO SuqTest SET i=77, j=88, k=99;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TEMPORARY TABLE BloTest (i INT, j INT, k INT);
Query OK, 0 rows affected (0.00 sec)
mysql> CHECK TABLE SuqTest;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.suqtest | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> INSERT BloTest (i,j,k) SELECT i,j,k FROM SuqTest;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> CHECK TABLE SuqTest;
+--------------+-------+----------+---------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+---------------------------------------------------+
| test.SuqTest | check | warning | Size of datafile is: 46 Should be: 39 |
| test.SuqTest | check | error | got error: 137 when reading datafile at record: 3 |
| test.suqtest | check | error | Corrupt |
+--------------+-------+----------+---------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
mysql> CHECK TABLE BloTest;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.blotest | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM BloTest;
+------+------+------+
| i | j | k |
+------+------+------+
| 11 | 22 | 33 |
| 44 | 55 | 66 |
| 77 | 88 | 99 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> CHECK TABLE BloTest;
+--------------+-------+----------+---------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+---------------------------------------------------+
| test.BloTest | check | warning | Size of datafile is: 46 Should be: 39 |
| test.BloTest | check | error | got error: 137 when reading datafile at record: 3 |
| test.blotest | check | error | Corrupt |
+--------------+-------+----------+---------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
