Bug #12644 Table is marked as crashed if modified from both statement and SP

• Submitted: 18 Aug 2005 12:44
• Modified: 30 Aug 2005 4:07
• Reporter: Sergey Petrunya
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Linux (Linux)
• Assigned to: Dmitry Lenev
• CPU Architecture: Any

[18 Aug 2005 12:44] Sergey Petrunya

Description:
A statement that A) modifies the table itself  and B) calls a stored function that modifies
the same table leaves the table in crashed state.

How to repeat:
Run the following:

create table t1 (a int);
insert into t1 values (1),(2),(3),(4);
create table t2 (a int);
delimiter //
create function write_t2(a int) RETURNS int MODIFIES SQL DATA DETERMINISTIC
begin
  insert into t2 values (a);
  return a;
end;//

delimiter ;

insert into t2 select write_t2(a) from t1;

alter table t2 add index(a); 

And get this error:
ERROR 1194 (HY000): Table 't2' is marked as crashed and should be repaired

[18 Aug 2005 13:14] MySQL Verification Team

I was able to repeat only on Linux. Windows not presents that behavior:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use db5;
Database changed
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter //
mysql> create function write_t2(a int) RETURNS int MODIFIES SQL DATA DETERMINISTIC
    -> begin
    ->   insert into t2 values (a);
    ->   return a;
    -> end;//
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> delimiter ;
mysql> 
mysql> insert into t2 select write_t2(a) from t1;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> alter table t2 add index(a); 
ERROR 1194 (HY000): Table 't2' is marked as crashed and should be repaired
mysql> 

c:\mysql\bin>mysql -uroot db5
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.12-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.22 sec)

mysql> insert into t1 values (1),(2),(3),(4);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.13 sec)

mysql> delimiter //
mysql> create function write_t2(a int) RETURNS int MODIFIES SQL DATA
    -> DETERMINISTIC
    -> begin
    ->   insert into t2 values (a);
    ->   return a;
    -> end;//
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql>
mysql> delimiter ;
mysql>
mysql>
mysql> insert into t2 select write_t2(a) from t1;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> alter table t2 add index(a);
Query OK, 8 rows affected (0.31 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>

[23 Aug 2005 6:29] Dmitry Lenev

Fixed in 5.0.12

See the comment for bug #11896 for more info.

[30 Aug 2005 4:07] Mike Hillyer

Added reference to this bug number to the changelog entry for Bug #11896.