Bug #13520 Quoted partition options fail with syntax error
Submitted: 27 Sep 2005 11:34 Modified: 2 Apr 2006 6:19
Reporter: Jon Stephens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.2-a_drop5p4-log OS:Linux (Linux)
Assigned to: Mikael Ronström CPU Architecture:Any

[27 Sep 2005 11:34] Jon Stephens
Description:
Partition options taking quoted values raise ERROR 1064 (42000).

How to repeat:
(1)

mysql> CREATE TABLE t1 (id INT, name VARCHAR(25)) 
    -> PARTITION BY HASH (id) 
    -> ( 
    -> PARTITION p0 COMMENT='first partition', 
    -> PARTITION p1 COMMENT='second partition'
    -> );

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 'first partition ENGINE = MyISAM, PARTITION p1  COMMENT = second partition ENGINE' at line 1

(2)

mysql> CREATE TABLE t1 (id INT, name VARCHAR(25))
    -> PARTITION BY HASH (id)
    -> (
    -> PARTITION p0 DATA DIRECTORY='/home/jon/data/d0',
    -> PARTITION p1 DATA DIRECTORY='/home/jon/data/d1'
    -> );

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 '/home/jon/data/d0 ENGINE = MyISAM, PARTITION           p1  DATA DIRECTORY = /home/jon/data' at line 1

(3)

mysql> CREATE TABLE t1 (id INT, val INT) 
    -> PARTITION BY HASH(val) 
    -> ( 
    -> PARTITION p0 INDEX DIRECTORY = '/home/jon/data/d0', 
    -> PARTITION p1 INDEX DIRECTORY = '/home/jon/data/d1' 
    -> );
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 '/home/jon/data/d0 ENGINE = MyISAM, PARTITION p1  INDEX DIRECTORY = /home/jon/dat' at line 1

Options taking numeric values are not affected, e.g.

mysql> CREATE TABLE t1 (id INT, val INT) 
    -> PARTITION BY HASH(val) 
    -> ( 
    -> PARTITION p0 MAX_ROWS=200 MIN_ROWS=10, 
    -> PARTITION p1 MAX_ROWS=100 MIN_ROWS=20
    -> );

Query OK, 0 rows affected (0.15 sec)

Suggested fix:
Parse quoted PARTITION option values correctly.

Possibly related to Bug#13433? (Very similar error message.)
[18 Oct 2005 13:32] Mikael Ronström
Needed delimiters around key word strings when written in frm file
[19 Oct 2005 10:37] Jon Stephens
Changed category to Server/Partitioning.
[25 Oct 2005 14:56] Sergei Golubchik
duplicate of bug#13433
[26 Oct 2005 3:39] Jon Stephens
Why was this marked as a duplicate? Bug#13433 concerns identifiers, not option values. These are not the same thing at all. Also, this bug is much more serious - DATA DIRECTORY and INDEX DIRECTORY options for partitions don't work because of it, and these are essential. Partitioning is fairly useless if you can't control where the partitions are stored. Also I need to document these options with working examples.

Also, what happened to the patch that was pending for this bug?

Please change the status of this bug to what's appropriate for the patch status. Or else update Bug#13433 to indicate it's the same problem with the code and indicate that the same patch will fix both issues.
[26 Oct 2005 6:51] Mikael Ronström
Marked as duplicate by Serg since they were fixed by the same change set as
Bug #13433
[1 Apr 2006 10:56] Mikael Ronström
This patch will appear in 5.1.9
[2 Apr 2006 6:19] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.1.9 changelog. Closed.