MySQL Bugs: #17310: Partitions: archive tables cause errors

Bug #17310	Partitions: archive tables cause errors
Submitted:	10 Feb 2006 19:02	Modified:	20 Jun 2006 11:41
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Archive storage engine	Severity:	S3 (Non-critical)
Version:	5.1.7-beta-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Mikael Ronström	CPU Architecture:	Any

Description:
If I have a partitioned archive table,

I can't create an index. That's okay, but the error message in 5.0 was:
ERROR 1069 (42000): Too many keys specified; max 0 keys allowed
The error message in 5.1 is different and not as clear:
ERROR 1005 (HY000): Can't create table 'db99.#sql-3764_11' (errno: 1)

I can't drop a partition. Maybe that's okay, but if it's disallowed then
the error message should say so.  It shouldn't be a syntax error,

I can't drop the database. That's not okay.

How to repeat:
/* user is root */

mysql> create database db99;
Query OK, 1 row affected (0.02 sec)

mysql> use db99;
Database changed
mysql> create table tnv (s1 int not null) engine=archive partition by list (s1) (partition p1 values in (1), partition p2 values in (2));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tnv values (1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create index inv on tnv (s1);
ERROR 1005 (HY000): Can't create table 'db99.#sql-3764_11' (errno: 1)
mysql> alter table tnv add partition (partition p3 values in (3));
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tnv drop partition p2;
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 ') (PARTITION p1 VALUES IN (1) ENGINE = ARCHIVE, PARTITION p2 VALUES IN (2) ENG' at line 1
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop database db99;
ERROR 1010 (HY000): Error dropping database (can't rmdir './db99', errno: 39)

Thank you for the bug report.

May be a bad error since Archive does have indexes, but only on one key.

The bug on drop database was due to that the error on index creation was
detected in the ha_archive::create method. This method had already created
the file when detecting the error and didn't clean up properly in the error cases.
There is a simple method of removing this problem by moving the checks before
the file creation. There are however more file creation errors that have the same
problem so in essence the method should be looked into.

The unclear error message comes from that archive now supports one special
key and then the error isn't detected until you try to create the new table, this
problem is the same if you do CREATE TABLE, ALTER TABLE and so forth using
archive tables even if there is no partitioning.

I made a patch for the drop database error from create table with index error + 
drop database

This will appear in 5.1.12

Independent of whether we support Archive for partitioning we can still fix bugs,
furthermore this was a bug in the archive engine not related to partitioning.

Mikael,

Correct but Jon still has to get the documentation correct regarding support for archive with partitioning.  That's all he was asking about.

If partitioning isn't germane to this bug, please move it into a more appropriate queue. Thanks!

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.12 changelog. Closed.