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 23:05	Modified:	18 May 2005 4:31
Reporter:	Josh Chamas
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Microsoft Windows (windows & linux -not OS specific)
Assigned to:	Ingo Strüwing	Target Version:

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.