Bug #26325 TEMPORARY TABLE "corrupt" after first read, according to CHECK TABLE
Submitted: 13 Feb 2007 15:27 Modified: 31 Jul 2007 18:55
Reporter: Bob Stein (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.0,5.2.3-falcon, 5.2.0-falcon-alpha-community-nt OS:Microsoft Windows (Windows 2000, XP)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: CHECK TABLE, CREATE TEMPORARY TABLE, temporary

[13 Feb 2007 15:27] Bob Stein
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>
[13 Feb 2007 15:56] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.2.3-falcon-alpha on Windows XP SP2. Bug is repeatable if MyISAM is used as a storage engine for these temporary tables.
[19 Jul 2007 9:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31160

ChangeSet@1.2529, 2007-07-19 13:51:31+05:00, svoj@mysql.com +3 -0
  BUG#26325 - TEMPORARY TABLE "corrupt" after first read, according
              to CHECK TABLE
  
  CHECK/REPAIR TABLE reports "File not found" error when issued
  against temporary table.
  
  Fixed by disabling a brunch of code (in case it gets temporary table)
  that is responsible for updating frm version as it is not needed
  for temporary tables.
[27 Jul 2007 16:49] Bugs System
Pushed into 5.1.21-beta
[27 Jul 2007 16:50] Bugs System
Pushed into 5.0.48
[31 Jul 2007 18:55] Paul Dubois
Noted in 5.0.48, 5.1.21 changelog.

After the first read of a TEMPORARY table, CHECK TABLE could report
the table as being corrupt.