Re: truncate table on aria storage engine fails inconsistently
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.

Re: truncate table on aria storage engine fails inconsistently
that may be a problem. My datadir is 91GB.

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

Re: truncate table on aria storage engine fails inconsistently
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)?

Re: truncate table on aria storage engine fails inconsistently
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.

Re: truncate table on aria storage engine fails inconsistently
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.

Re: truncate table on aria storage engine fails inconsistently
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;
--------------------

--------------------

--------------------
1 row in set (0.00 sec)

Let me know if you need anymore info.

Re: truncate table on aria storage engine fails inconsistently
Hi Scott,

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

Re: truncate table on aria storage engine fails inconsistently

1. Test case:

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

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

Re: truncate table on partitioned Aria table fails with ER_ILLEGAL_HA
Also filed in JIRA as https://mariadb.atlassian.net/browse/MDEV-276

Re: truncate table on partitioned Aria table fails with ER_ILLEGAL_HA
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.

Launchpad bug id: 997460