truncate table on partitioned Aria table fails with ER_ILLEGAL_HA

Bug #997460 reported by Scott Feldstein on 2012-05-10
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
Medium
Michael Widenius

Bug Description

Hi,
I am running Ver 5.5.23-MariaDB-log for Linux on x86_64 (MariaDB Server).

My application uses lots of the "truncate table" command on tables located in the aria storage engine.

Every hour I run a series of "truncate table" commands on the tables and am getting these errors:

Table storage engine for 'TABLE' doesn't have this option

This happens about 50% of the time when truncate is executed. When I run the command directly on the db, via the mysql client, I don't see the issue. Additionally I don't see any errors in the mysqld log.

This did not occur on previous versions of mariadb / aria engine, I was actually having other issues that I didn't log a bug for, but they seem to have subsided in favor of this issue in this release.

Please let me know what I can give you to better diagnose the error. It is occurring all the time in my env so I shouldn't have a problem giving you any data that you need.

thanks.

Elena Stepanova (elenst) wrote :

Hi,

If possible, could you please backup the datadir when the server is down, start server with the general log ON (to file) and provide the datadir backup along with the general log + error log up to the moment when the error occurs?

If you can't shutdown the server, please provide the database dump instead (still, along with the general log).

Thank you.

Elena Stepanova (elenst) on 2012-05-10
tags: added: aria
Scott Feldstein (scottmf76) wrote :

that may be a problem. My datadir is 91GB.

Do you have a site that I could upload this to?

Elena Stepanova (elenst) wrote :

Could you then upload only the smallest table where the problem occurs (but still with the general log and error log from the start and till the error)?

Scott Feldstein (scottmf76) wrote :

Hi Elena,
It turns out that this was a false alarm. In my code I set autocommit = false and then I ran truncate table. When I set autocommit = true, the error message goes away and the truncate works as it should.

I understand why this is an invalid scenario since truncate table is a ddl command, but this didn't occur in earlier mariadb releases. The only potential bug that I see is to make the error message more clear. Up to you if you want to do anything about that.

thanks again for the quick response.

Elena Stepanova (elenst) wrote :

Hi Scott,

It still sounds strange. Being a DDL operation, TRUNCATE is supposed to implicitly commit a transaction, not to cause an error.
No rush, but if you ever have a chance to collect and upload the data and the general logs, please do so. I'll keep the bug open for now.

Scott Feldstein (scottmf76) wrote :

Hey Elena,
I reproduced it very simply by running this set of sql:

MariaDB [mydb]> CREATE TABLE IF NOT EXISTS MYTABLE (
    -> TIMESTAMP bigint(20) NOT NULL,
    -> MEASUREMENT_ID int(11) NOT NULL,
    -> VALUE decimal(24,5) DEFAULT NULL,
    -> MINVALUE decimal(24,5) DEFAULT NULL,
    -> MAXXVALUE decimal(24,5) DEFAULT NULL,
    -> KEY MYTABLE_IDX (MEASUREMENT_ID)
    -> ) ENGINE=Aria
    -> PARTITION BY HASH(MEASUREMENT_ID)
    -> PARTITIONS 8;
Query OK, 0 rows affected (0.03 sec)

MariaDB [mydb]> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mydb]> truncate table MYTABLE;
ERROR 1031 (HY000): Table storage engine for 'MYTABLE' doesn't have this option

MariaDB [hqdb]> select @@version;
+--------------------+
| @@version |
+--------------------+
| 5.5.23-MariaDB-log |
+--------------------+
1 row in set (0.00 sec)

Let me know if you need anymore info.

Elena Stepanova (elenst) wrote :

Hi Scott,

Thank you, the fact that the table is partitioned was the missing bit of info (although, I could have guessed).

Elena Stepanova (elenst) wrote :

# Test case:

CREATE TABLE t ( i INT )
  ENGINE=Aria
  PARTITION BY HASH(i) PARTITIONS 2;
SET AUTOCOMMIT = 0;
TRUNCATE TABLE t;

# 'TRUNCATE TABLE t' failed: 1031: Table storage engine for 't' doesn't have this option

Changed in maria:
importance: Undecided → Medium
assignee: nobody → Michael Widenius (monty)
milestone: none → 5.5
summary: - truncate table on aria storage engine fails inconsistently
+ truncate table on partitioned Aria table fails with ER_ILLEGAL_HA
Michael Widenius (monty) on 2012-05-18
Changed in maria:
status: New → In Progress
Michael Widenius (monty) wrote :

The problem was that in MySQL 5.5 the old code for handling truncate by delete-row-one-by-one was removed()
This was used in Aria for the case when one didn't have auto-commit and we wanted to be able to rollback a TRUNCATE.

Have now fixed this by forcing the Aria internal truncate call to do an explicit commit.
The disadvantage is that we can't rollback TRUNCATE anymore in Aria.

Changed in maria:
status: In Progress → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers