[MDEV-17513] Impossible to optimize tables (TRANSACTIONAL option is handled inconsistently) Created: 2018-10-21  Updated: 2021-01-12  Resolved: 2021-01-12

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.0, 10.1, 10.2.18, 10.2, 10.3
Fix Version/s: 10.2.37, 10.3.28, 10.4.18, 10.5.9

Type: Bug Priority: Major
Reporter: Kai Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu 14.04

Issue Links:
Duplicate
duplicates MDEV-18428 Memory: If transactional=0 is specifi... Closed

 Description   

Attempting to optimize a table (existing or new) fails immediately.

 
 
MariaDB [my_db]> CREATE TABLE toku_bug ( 
  toku_bug_key int(10) unsigned not null auto_increment,
 
  primary key (toku_bug_key)
 
) ENGINE=TokuDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=0 `compression`=TOKUDB_LZMA;
 
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
 
 
MariaDB [my_db]> optimize table toku_bug;
 
+------------------+----------+----------+------------------------------------------------------------------------------------+
 
| Table            | Op       | Msg_type | Msg_text                                                                           |
 
+------------------+----------+----------+------------------------------------------------------------------------------------+
 
| zija_db.toku_bug | optimize | note     | Table does not support optimize, doing recreate + analyze instead                  |
 
| zija_db.toku_bug | optimize | error    | Table storage engine 'TokuDB' does not support the create option 'TRANSACTIONAL=1' |
 
| zija_db.toku_bug | optimize | status   | Operation failed                                                                   |
 
+------------------+----------+----------+------------------------------------------------------------------------------------+



 Comments   
Comment by Alice Sherepa [ 2018-10-29 ]

Thanks for the report!
Reproducible with InnoDB as well, on MariaDB 10.0-10.3

The cause is "TRANSACTIONAL" in the CREATE TABLE clause. (TRANSACTIONAL is only applicable for Aria tables)
In STRICT_TRANS_TABLES (which is default in versions >10.2.4) server returns the error and optimize fails. The behaviour is inconsistent, as it allows to create the table (with a warning), but returns the error while rebuilding.

Also the message in warning/error is incorrect (CREATE TABLE ... TRANSACTIONAL=0 -> "does not support the create option 'TRANSACTIONAL=1")

MariaDB [test]>  CREATE TABLE t1 (i int) ENGINE=InnoDB  TRANSACTIONAL=0;
 
Query OK, 0 rows affected, 1 warning (0.034 sec)
 
 
 
Warning (Code 1478): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'
 
MariaDB [test]>  optimize table t1;
 
+---------+----------+----------+------------------------------------------------------------------------------------+
 
| Table   | Op       | Msg_type | Msg_text                                                                           |
 
+---------+----------+----------+------------------------------------------------------------------------------------+
 
| test.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead                  |
 
| test.t1 | optimize | error    | Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1' |
 
| test.t1 | optimize | status   | Operation failed                                                                   |
 
+---------+----------+----------+------------------------------------------------------------------------------------+
 
3 rows in set, 1 warning (0.001 sec)
 
 
 
Error (Code 1478): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'
 
MariaDB [test]>  alter table t1 force;
 
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'
 
 
MariaDB [test]>  show variables like 'sql_mode';
 
+---------------+-------------------------------------------------------------------------------------------+
 
| Variable_name | Value                                                                                     |
 
+---------------+-------------------------------------------------------------------------------------------+
 
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 
+---------------+-------------------------------------------------------------------------------------------+
 
1 row in set (0.002 sec)
 
 
 
MariaDB [test]>  set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]>  optimize table t1;
 
+---------+----------+----------+-------------------------------------------------------------------+
 
| Table   | Op       | Msg_type | Msg_text                                                          |
 
+---------+----------+----------+-------------------------------------------------------------------+
 
| test.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
 
| test.t1 | optimize | status   | OK                                                                |
 
+---------+----------+----------+-------------------------------------------------------------------+
 
2 rows in set, 1 warning (0.076 sec)
 
 
 
Warning (Code 1478): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'

Comment by Elena Stepanova [ 2019-01-31 ]

Another report about the same problem with MEMORY tables: MDEV-18428.

Generated at Thu Feb 08 08:37:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.