MySQL Bugs: #29325: create table overwrites .MYD file of other table (datadir)

Bug #29325	create table overwrites .MYD file of other table (datadir)
Submitted:	24 Jun 2007 19:47	Modified:	5 Sep 2007 17:41
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.20bk, 5.0.44	OS:	FreeBSD
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	CREATE TABLE, datadir, qc

Description:
Scenario:
A table in db2 has a DATA DIRECTOR wich points into db1.
If a table of the same name is created in db1 (and no datadir option is used for this), then this table will overwrite the existing MYD file.

There will be no warning or error. The original table will be marked as crashed and the original data is gone.

I agree, it is a very bad practise to put files cross databases in this way, but mysql should still check for existing files, and rather fail creating a new table than overwriting data of an existing table. 

In mysql 5.1 this also applies to partition data, if partitions where created with such an datdir option, and a new table with the same partition structure is later created.
With partition the tables are not marked as crashed (assuming same row structure) and will *share* data. That is existing data is deleted, but new data is mixed (needs flush tables). This makes exploits even worse.

How to repeat:
create database db1; create database db2;

# assuming datadir = DATA

use db2;
CREATE TABLE t1 (b INT) engine myisam
DATA DIRECTORY = '/DATA/db1/';

insert into db2.t1 select 1;
select * from db2.t1;
reset query cache;

use db1;
drop table if exists t1; 

#no warning from create table
CREATE TABLE t1 (a INT) engine myisam;

select * from db2.t1; # fails table crashed

------
# with partitions

se db2;
drop table if exists t1; 

CREATE TABLE t1 (b INT) engine myisam PARTITION BY RANGE ( b )
( PARTITION p0 VALUES LESS THAN (10)  DATA DIRECTORY = '/DATA/db1/',
 PARTITION p1 VALUES LESS THAN (20)   
);

insert into db2.t1 select 1;
insert into db2.t1 select 11;

use db1;
drop table if exists t1; 

CREATE TABLE t1 (a INT) engine myisam  PARTITION BY RANGE ( a )
( PARTITION p0 VALUES LESS THAN (10)  ,
 PARTITION p1 VALUES LESS THAN (20)   
);

select * from db2.t1;
select * from db1.t1;

insert into db2.t1 select 2;
insert into db2.t1 select 12;

flush tables; reset query cache;
select * from db2.t1;
select * from db1.t1;

# insert into table in db1
insert into db1.t1 select 3;
insert into db1.t1 select 13;

flush tables; reset query cache;

select * from db2.t1; # show data from both tables
select * from db1.t1;

Suggested fix:
Table creation should check that no file will be overwritten.

I can't reproduce the "crashed table" result with 5.0 on linux, 
and as long as i don't do a FLUSH TABLES i even get correct
results for the overwritten table as the mysqld still has the
old file open. Only after a FLUSH TABLES the table data file 
gets reopened by the server and the query now reads from the
now empty t1.MYI file in /DATA/db1, the result now is 

mysql> select * from db2.t1; # fails table crashed
+-------------+
| b           |
+-------------+
| -1515870811 | 
+-------------+
1 row in set (0.01 sec)

A ls -l on the database directories shows that
the t1.MYD file has indeed been recreated as an
empty file

linux:/usr/local/mysql-5.0/var # ls -l db1 db2
db1:
total 28
drwx------  2 mysql mysql 4096 2007-06-25 12:56 .
drwx------  6 mysql root  4096 2007-06-25 12:56 ..
-rw-rw----  1 mysql mysql   65 2007-06-25 12:56 db.opt
-rw-rw----  1 mysql mysql 8554 2007-06-25 12:56 t1.frm
-rw-rw----  1 mysql mysql    0 2007-06-25 12:56 t1.MYD
-rw-rw----  1 mysql mysql 1024 2007-06-25 12:56 t1.MYI

db2:
total 28
drwx------  2 mysql mysql 4096 2007-06-25 12:56 .
drwx------  6 mysql root  4096 2007-06-25 12:56 ..
-rw-rw----  1 mysql mysql   65 2007-06-25 12:56 db.opt
-rw-rw----  1 mysql mysql 8554 2007-06-25 12:56 t1.frm
lrwxrwxrwx  1 mysql mysql   35 2007-06-25 12:56 t1.MYD -> /usr/local/mysql-5.0/var/db1/t1.MYD
-rw-rw----  1 mysql mysql 1024 2007-06-25 12:59 t1.MYI

Thank you for a bug report. Verified just as described with latest 5.1.20-BK on  Linux (using partiotion-based test case).

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/30452

ChangeSet@1.2521, 2007-07-06 18:57:51+03:00, gkodinov@magare.gmz +9 -0
  Bug #29325: 
  By default MyISAM overwrites .MYD and .MYI files no 
  DATA DIRECTORY option is used. This can lead to two tables
  using the same .MYD and .MYI files (that can't be dropped).
  
  To prevent CREATE TABLE from overwriting a file a new option
  is introduced : 
  dont_overwrite_files_if_data_directory_is_current_database
  When this is on the CREATE TABLE throws an error if either
  the .MYD or .MYI exists for a MyISAM table.
  The option is off by default (resulting in compatible behavior).

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/30653

ChangeSet@1.2521, 2007-07-11 10:40:45+03:00, gkodinov@magare.gmz +9 -0
  Bug #29325: 
  By default MyISAM overwrites .MYD and .MYI files no 
  DATA DIRECTORY option is used. This can lead to two tables
  using the same .MYD and .MYI files (that can't be dropped).
  
  To prevent CREATE TABLE from overwriting a file a new option
  is introduced : keep_files_on_create
  When this is on the CREATE TABLE throws an error if either
  the .MYD or .MYI exists for a MyISAM table.
  The option is off by default (resulting in compatible behavior).

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/30654

ChangeSet@1.2521, 2007-07-11 10:49:54+03:00, gkodinov@magare.gmz +9 -0
  Bug #29325: 
  By default MyISAM overwrites .MYD and .MYI files no 
  DATA DIRECTORY option is used. This can lead to two tables
  using the same .MYD and .MYI files (that can't be dropped).
  
  To prevent CREATE TABLE from overwriting a file a new option
  is introduced : keep_files_on_create
  When this is on the CREATE TABLE throws an error if either
  the .MYD or .MYI exists for a MyISAM table.
  The option is off by default (resulting in compatible behavior).

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/30921

ChangeSet@1.2552, 2007-07-14 01:34:46+05:00, gshchepa@gleb.loc +2 -0
  symlink.test, symlink.result:
    Minor fix for test case of bug #29325 to make emb test happy.

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/31083

ChangeSet@1.2534, 2007-07-18 15:33:41+03:00, gkodinov@magare.gmz +1 -0
  Bug #29325: create table overwrites .MYD file of other 
  table (datadir)
  Set errno when retunring an error in my_create_with_symlink.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Paul,

There is a new option : --keep_files_on_create
This tells the server not to overwrite existing .MYI and MYD files in the default directory (and return an error instead just as it would if there is a DATA DIRECTORY specified).
This option is off by default (to get a compatible behavior).

So think of this option as an additional precaution that will implement somewhat paranoid rules to not overwrite stale .MYD and MYI (something that is apparently considered a "feature" by the majority of users).

Note that (as demonstrated by the test case) this can leave to certain exploits, so this may be considered higher security option as well.

Note, that MyISAM never overwrite files when one uses DATA (or INDEX) DIRECTORY options. It only overwrite MYD/MYI files that have no corresponding .frm file and that are in the same datadir/db directory where frm file is created - if MyISAM wouldn't do it, a stale MYD or MYI file would prevent anybody from creating a table with this name.

Pushed into 5.1.23-beta

Noted in 5.0.48, 5.1.21 changelogs.

If a MyISAM table is created with no DATA DIRECTORY option, the .MYD
file is created in the database directory. By default, if MyISAM
finds an existing .MYD file in this case, it overwrites it. The same
applies to .MYI files for tables created with no INDEX DIRECTORY
option. To suppress this behavior, start the server with the new
--keep_files_on_create option, in which case MyISAM will not
overwrite existing files and returns an error instead.

Changed the 5.1 version number from 5.1.21 to 5.1.23 because the startup option is not exposed until then.