Bug #22634 converting where DATA DIRECTORY = table directory to ARCHIVE corrupts table
Submitted: 23 Sep 2006 23:03 Modified: 14 Dec 2006 1:56
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.12 OS:Linux (Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: data directory, partitioning

[23 Sep 2006 23:03] Giuseppe Maxia
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.
[25 Sep 2006 9:33] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.1.12-BK (ChangeSet@1.1810.1697.161, 2006-09-25 01:32:55+02:00) on Linux.
[16 Nov 2006 10:41] 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/15401

ChangeSet@1.2328, 2006-11-16 15:13:48+04:00, holyfoot@mysql.com +1 -0
  bug #22634 (partitioned tables with DATA DIRECTORY option corrupts table)
  
  In fact the problem is inside the ARCHIVE engine.
  It stores real datapath inside the ARM file, but doesn't implement
  specific ha_archive::rename_table function.
  As the ALTER TABLE statement first creates the table with the temporary
  name and then renames it to the normal one, we get the temporary name
  saved in ARM what leads to "can't open file" error.
  Code modified to store only path in the ARM, without filename.
[13 Dec 2006 12:33] Sergey Glukhov
Fixed in 5.1.15-beta
[14 Dec 2006 1:56] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented in 5.1.15 changelog.

Updated synopsis.