Description:
Using the DATA DIRECTORY option, pointing to the same directory where the database is
already located, and converting to a different engine, will create a "corrupted" table.
For this example, assume that the data directory is '/usr/local/mysql/data' and the test
database is '/usr/local/mysql/data/test'. If you create a MyISAM partitioned table with
the DATA DIRECTORY option pointing to '/usr/local/mysql/data/test', the creation will be
successful. However, converting it to Archive will result in a corrupted table.
--------------
select version()
--------------
+-------------------+
| version() |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+
1 row in set (0.00 sec)
--------------
create schema if not exists test
--------------
Query OK, 0 rows affected, 1 warning (0.00 sec)
Note (Code 1007): Can't create database 'test'; database exists
--------------
drop table if exists t1, t2, t3
--------------
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Note (Code 1051): Unknown table 't2'
Note (Code 1051): Unknown table 't3'
--------------
create table t1 (id int not null) engine = myisam partition by range (id)
(
partition p0 values less than (1000) data directory =
'/usr/local/mysql/data/test/',
partition p1 values less than (2000) data directory =
'/usr/local/mysql/data/test/',
partition p2 values less than (MAXVALUE)
)
--------------
Query OK, 0 rows affected (0.04 sec)
--------------
show tables
--------------
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
--------------
show create table t1
--------------
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) (PARTITION p0
VALUES LESS THAN (1000) DATA DIRECTORY = '/usr/local/mysql/data/test/' ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2000) DATA DIRECTORY = '/usr/local/mysql/data/test/'
ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)
--------------
alter table t1 engine=archive
--------------
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
check table t1 extended
--------------
+---------+-------+----------+--------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+--------------------------------------------------------+
| test.t1 | check | error | Table 't1' is marked as crashed and should be repaired |
+---------+-------+----------+--------------------------------------------------------+
1 row in set (0.00 sec)
--------------
repair table t1
--------------
+---------+--------+----------+--------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+--------+----------+--------------------------------------------------------+
| test.t1 | repair | error | Table 't1' is marked as crashed and should be repaired |
+---------+--------+----------+--------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
Error (Code 1194): Table 't1' is marked as crashed and should be repaired
How to repeat:
select version();
create schema if not exists test;
use test;
drop table if exists t1, t2, t3;
create table t1 (id int not null) engine = myisam partition by range (id)
(
partition p0 values less than (1000) data directory =
'/usr/local/mysql/data/test/',
partition p1 values less than (2000) data directory =
'/usr/local/mysql/data/test/',
partition p2 values less than (MAXVALUE)
)
;
show tables;
show create table t1\G
alter table t1 engine=archive;
check table t1 extended;
repair table t1;
Suggested fix:
the DATA DIRECTORY option should not be allowed if it points to the same place where the
database is already located.
Description: Using the DATA DIRECTORY option, pointing to the same directory where the database is already located, and converting to a different engine, will create a "corrupted" table. For this example, assume that the data directory is '/usr/local/mysql/data' and the test database is '/usr/local/mysql/data/test'. If you create a MyISAM partitioned table with the DATA DIRECTORY option pointing to '/usr/local/mysql/data/test', the creation will be successful. However, converting it to Archive will result in a corrupted table. -------------- select version() -------------- +-------------------+ | version() | +-------------------+ | 5.1.12-beta-debug | +-------------------+ 1 row in set (0.00 sec) -------------- create schema if not exists test -------------- Query OK, 0 rows affected, 1 warning (0.00 sec) Note (Code 1007): Can't create database 'test'; database exists -------------- drop table if exists t1, t2, t3 -------------- Query OK, 0 rows affected, 2 warnings (0.00 sec) Note (Code 1051): Unknown table 't2' Note (Code 1051): Unknown table 't3' -------------- create table t1 (id int not null) engine = myisam partition by range (id) ( partition p0 values less than (1000) data directory = '/usr/local/mysql/data/test/', partition p1 values less than (2000) data directory = '/usr/local/mysql/data/test/', partition p2 values less than (MAXVALUE) ) -------------- Query OK, 0 rows affected (0.04 sec) -------------- show tables -------------- +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) -------------- show create table t1 -------------- *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000) DATA DIRECTORY = '/usr/local/mysql/data/test/' ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (2000) DATA DIRECTORY = '/usr/local/mysql/data/test/' ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ 1 row in set (0.00 sec) -------------- alter table t1 engine=archive -------------- Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 -------------- check table t1 extended -------------- +---------+-------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+--------------------------------------------------------+ | test.t1 | check | error | Table 't1' is marked as crashed and should be repaired | +---------+-------+----------+--------------------------------------------------------+ 1 row in set (0.00 sec) -------------- repair table t1 -------------- +---------+--------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+--------+----------+--------------------------------------------------------+ | test.t1 | repair | error | Table 't1' is marked as crashed and should be repaired | +---------+--------+----------+--------------------------------------------------------+ 1 row in set, 1 warning (0.01 sec) Error (Code 1194): Table 't1' is marked as crashed and should be repaired How to repeat: select version(); create schema if not exists test; use test; drop table if exists t1, t2, t3; create table t1 (id int not null) engine = myisam partition by range (id) ( partition p0 values less than (1000) data directory = '/usr/local/mysql/data/test/', partition p1 values less than (2000) data directory = '/usr/local/mysql/data/test/', partition p2 values less than (MAXVALUE) ) ; show tables; show create table t1\G alter table t1 engine=archive; check table t1 extended; repair table t1; Suggested fix: the DATA DIRECTORY option should not be allowed if it points to the same place where the database is already located.