MySQL Bugs: #5964: 4.1 MERGE tables regression from 4.0

Bug #5964	4.1 MERGE tables regression from 4.0
Submitted:	7 Oct 2004 21:05	Modified:	18 May 2005 2:31
Reporter:	Josh Chamas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Windows (windows & linux -not OS specific)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
Running the test SQL script in this bug report generates this error:

$ mysql --user=root -f  < merge-break.sql
ERROR 1017 at line 11: Can't find file: 'tm.MRG' (errno: 2)

The reason for this is that the MERGE tables now contain an absolute file path to the tables being merged on disk, and this is the regression in a couple use cases.

$ cat /cygdrive/c/mysql/data/test_production/tm.MRG
c:\mysql\data\test_stage/t1

There are 2 use cases here that worked in 4.0, but no longer work in 4.1 because of this changed behavior.  First, a user that publishing to a staging area database/schema, and then does a rename to publish the files to a production/live database/schema can no longer do so as demonstrated by this bug, as the MERGE paths are no longer schema relative.  2nd, consider the scenario when a customer changes their datadir parameter because they have just installed a new disk array.  All of the MERGE tables will become invalid because they store full OS paths.

On a 4.0 system the output one gets with the below test script is:

$ mysql --user=root -f < merge-break.sql
count(*)
0

How to repeat:
$ mysql --user=root -f  < merge-break.sql

### merge-break.sql script here

create database if not exists test_stage;
create database if not exists test_production;

use test_stage;
create table if not exists t1 (c1 int);
create table if not exists tm (c1 int) TYPE=MERGE UNION=(t1);
rename table t1 to test_production.t1, tm to test_production.tm;

use test_production;
select count(*) from tm;

drop database if exists test_stage;
drop database if exists test_production;

Suggested fix:
The solution is two-fold I believe.  First, if relative tables are referenced in a MERGE definition, then keep the references in the MRG file relative also for backwards compatibility with 4.0 method.  Then, instead of specifiying full file path names for absolute db.table type reference, just specify the database/schema part as

db/file
  - or -
db.file

such that if a user changes their datadir in the future, the paths can be relative to the datadir path, not some absolute path in the system.

Actually, I think db.file should be written by the system as this is the SQL syntax anyway, but support should be for reading db/file, file, or full file path also if it exists & that would also be backwards compatible with MRG tables created in this more recent 4.1 method.

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/internals/24401

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/internals/24461

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/internals/24475

Fixed in 4.1.12 and 5.0.6.

Noted in 4.1.12, 5.0.6 changelogs.