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

[7 Oct 2004 21:05] Josh Chamas
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.
[27 Apr 2005 20:14] 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/internals/24401
[28 Apr 2005 16:30] 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/internals/24461
[29 Apr 2005 8:59] 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/internals/24475
[29 Apr 2005 14:00] Ingo Strüwing
Fixed in 4.1.12 and 5.0.6.
[18 May 2005 2:31] Paul DuBois
Noted in 4.1.12, 5.0.6 changelogs.