MySQL Bugs: #14354: Partitions: data directory clause fails

Bug #14354	Partitions: data directory clause fails
Submitted:	26 Oct 2005 20:14	Modified:	7 Aug 2006 11:43
Reporter:	Peter Gulutzan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.2-alpha-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I put "DATA DIRECTORY 'a'" in a partition clause, and I get
back a message about "ENGINE = MYISAM" -- which wasn't part
of what I wrote. So, at least, the error message is wrong.

How to repeat:
mysql> create table tj (id int) partition by range (id) (partition p1 values less than (1) data directory = 'a');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a ENGINE = MyISAM)' at line 1

it seems that ENGINE = MyISAM is being appended by default.

mysql> create table tj (id int) engine=myisam partition by range (id) (partition p1 values less than (1) data directory = `a`);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a`)' at line 1

mysql> create table tj (id int) engine=myisam data directory = '/home/eric/foo' partition by range (id) (partition p1 values less than (1));
Query OK, 0 rows affected (0.00 sec)

However, the DATA DIRECTORY clause is being ignored in the previous 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/1478

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

Documented in 5.1.6 changelog:

      <listitem>
        <para>
          The <literal>DATA DIRECTORY</literal> and <literal>INDEX
            DIRECTORY</literal> clauses of a <literal>CREATE
              TABLE</literal> statement involving partitions did not
          work. (Bug #14354)
        </para>
      </listitem>

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

In the original complaint, I said: "So, at least, the error message is wrong."
The error message still is wrong. Notice in this example that the
table name is 'tj', not 'a'. Incidentally, I expected an error, not a warning.

mysql> create table tj (id int) partition by range (id) (partition p1 values
    -> less than (1) data directory = 'a');
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Error | 1103 | Incorrect table name 'a' |
+-------+------+--------------------------+
1 row in set (0.00 sec)

I managed to compile mysql-5.1 without support for symlinks and that unfortunately breaks the testcase for this bug. It seems to be a global flag "my_disable_symlinks" that are set in mysqld.cc and checked in 'my_create_with_symlink'. It will simply ignore the filename and instead create the files in db directory. In this case the files end up in ./test/t1#P#p1.MYD.

Also, I noticed that it says "myisam" and NOT "myisam", is that intentional?

Please use the "$MYSQLTEST_VARDIR" variable and create the temp files in $MYSQLTEST_VARDIR/tmp. 

I will disable that part of the testcase for now.

I works when symlink are available, so will not disable anything, but check in a version that uses $MYSQLTEST_VARDIR

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

There is a bug #19107 handling problems with DATA DIRECTORY on Windows.
There is a bug #21350 handling problems that DATA DIRECTORY erroneusly
provided doesn't report an error.
Thus this is an duplicate of those