Bug #1077 Problems with INDEX DIRECTORY in SHOW CREATE TABLE and in replication
Submitted: 18 Aug 2003 9:58 Modified: 30 Sep 2003 14:15
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 OS:Any (all)
Assigned to: Guilhem Bichot CPU Architecture:Any

[18 Aug 2003 9:58] Guilhem Bichot
From Georg Richter:

> I noticed some trouble when CREATE TABLE statement with INDEX DIRECTORY will 
> be replicated:
> a) If slave has a diffrent configuration (index directory doesn't exist), you 
> will recieve an error. (typical environment: Production master and slaves with
> 2 disks, backup and development with 1 disk).
> b) If you run master and slave on the same machine (like me for testing), you 
> will run in big trouble, cause slave will recreate the master index file.

a) and b) are fully right.
A simple solution could be ignoring the INDEX DIRECTORY and DATA DIRECTORY of the CREATE TABLE from the MASTER.
Another (not contradictory) approach is to have an option (--index-directory or --indexdir) where the indexes would be created. This way the CREATE TABLE needn't contain the index dir, is logged without it, and a different index-dir can be used on the slave.

> If you use LOAD DATA FROM MASTER index directories will be ignored.

Yes. But this is not the fault of this command, it's the fault
MASTER> create table uu(a int primary key) index directory='/tmp';
Query OK, 0 rows affected (0.05 sec)
MASTER> show create table uu;
| Table | Create
Table                                                                               |
| uu    | CREATE TABLE `uu` (
  `a` int(11) NOT NULL default '0',
  PRIMARY KEY  (`a`)
1 row in set (0.00 sec)

So it's a bug (or there may be a technical reason for this, that's to be decided) in SHOW CREATE TABLE. The first action on this bug is to know why
CREATE TABLE does this. Then we can turn to the first paragraph of this bug
report (--index-dir etc, which is more development task than a bugfix).

How to repeat:
See description.
[30 Sep 2003 14:15] Guilhem Bichot
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 

Fixed in 4.0.15 by adding a new SQL_MODE bit: NO_DIR_IN_CREATE.
When the server has SQL_MODE=NO_DIR_IN_CREATE, it will ignore DATA/INDEX DIRECTORY. So for a slave, one should start the slave with --sql-mode=no_dir_in_create.