truncate table on partitioned Aria table fails with ER_ILLEGAL_HA

[Scott Feldstein]

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)]

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.

[Scott Feldstein (scottmf76)]

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

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

[Elena Stepanova (elenst)]

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)]

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)]

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)]

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)]

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)]

# 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

[Elena Stepanova (elenst)]

Also filed in JIRA as https://mariadb.atlassian.net/browse/MDEV-276

[Michael Widenius (monty)]

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.