[MDEV-640] LP:997397 - TRUNCATE on a partitioned Aria table does not reset AUTO_INCREMENT Created: 2012-05-09  Updated: 2020-05-28

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.9, 10.0.22, 10.1.8
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Michael Widenius
Resolution: Unresolved Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug997397.xml    

 Description   

TRUNCATE TABLE on an Aria table does not reset auto-increment value as it does for other engines, including MyISAM and InnoDB.
(see http://kb.askmonty.org/en/truncate-table or http://dev.mysql.com/doc/refman/5.5/en/truncate-table.html)

Reproducible on current MariaDB 5.1 - 5.5.

Test case:

CREATE TABLE t ( i INT NOT NULL AUTO_INCREMENT, KEY(i) )
 
  ENGINE=Aria
 
  PARTITION BY HASH(i) PARTITIONS 2;
 
INSERT INTO t VALUES (NULL),(NULL),(NULL);
 
 
 
SHOW CREATE TABLE t;
 
SELECT * FROM t;
 
TRUNCATE TABLE t;
 
SHOW CREATE TABLE t;
 
INSERT INTO t VALUES (NULL),(NULL);
 
SELECT * FROM t;
 
 
 
DROP TABLE t;
 
 

Expected output (after truncate):

TRUNCATE TABLE t;
 
SHOW CREATE TABLE t;
 
Table	Create Table
 
t	CREATE TABLE `t` (
 
  `i` int(11) NOT NULL AUTO_INCREMENT,
 
  KEY `i` (`i`)
 
) ENGINE=Aria DEFAULT CHARSET=latin1
 
/*!50100 PARTITION BY HASH (i)
 
PARTITIONS 2 */
 
INSERT INTO t VALUES (NULL),(NULL);
 
SELECT * FROM t;
 
i
 
1
 
2

Actual output (after truncate): 

TRUNCATE TABLE t;
 
SHOW CREATE TABLE t;
 
Table	Create Table
 
t	CREATE TABLE `t` (
 
  `i` int(11) NOT NULL AUTO_INCREMENT,
 
  KEY `i` (`i`)
 
) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
 
/*!50100 PARTITION BY HASH (i)
 
PARTITIONS 2 */
 
INSERT INTO t VALUES (NULL),(NULL);
 
SELECT * FROM t;
 
i
 
4
 
5



 Comments   
Comment by Rasmus Johansson (Inactive) [ 2012-05-09 ]

Launchpad bug id: 997397

Comment by Daniel Black [ 2015-11-13 ]

10.0.22

MariaDB [test]> select @@version;
 
+------------------------------+
 
| @@version                    |
 
+------------------------------+
 
| 10.0.22-MariaDB-1~jessie-log |
 
+------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> CREATE TABLE t ( i INT NOT NULL AUTO_INCREMENT, KEY(i) )
 
    ->   ENGINE=Aria
 
    ->   PARTITION BY HASH(i) PARTITIONS 2;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> INSERT INTO t VALUES (NULL),(NULL),(NULL);
 
Query OK, 3 rows affected (0.00 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> 
MariaDB [test]> SHOW CREATE TABLE t;
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                   |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t     | CREATE TABLE `t` (
 
  `i` int(11) NOT NULL AUTO_INCREMENT,
 
  KEY `i` (`i`)
 
) ENGINE=Aria AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
 
/*!50100 PARTITION BY HASH (i)
 
PARTITIONS 2 */ |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT * FROM t;
 
+---+
 
| i |
 
+---+
 
| 1 |
 
| 3 |
 
| 5 |
 
+---+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [test]> TRUNCATE TABLE t;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SHOW CREATE TABLE t;
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                   |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t     | CREATE TABLE `t` (
 
  `i` int(11) NOT NULL AUTO_INCREMENT,
 
  KEY `i` (`i`)
 
) ENGINE=Aria AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
 
/*!50100 PARTITION BY HASH (i)
 
PARTITIONS 2 */ |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> INSERT INTO t VALUES (NULL),(NULL);
 
Query OK, 2 rows affected (0.00 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT * FROM t;
 
+---+
 
| i |
 
+---+
 
| 7 |
 
| 9 |
 
+---+
 
2 rows in set (0.00 sec)

10.1.9 (before release)

MariaDB [test]> CREATE TABLE t ( i INT NOT NULL AUTO_INCREMENT, KEY(i) )
 
    ->   ENGINE=Aria
 
    ->   PARTITION BY HASH(i) PARTITIONS 2;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> INSERT INTO t VALUES (NULL),(NULL),(NULL);
 
Query OK, 3 rows affected (0.00 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> 
MariaDB [test]> SHOW CREATE TABLE t;
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                   |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t     | CREATE TABLE `t` (
 
  `i` int(11) NOT NULL AUTO_INCREMENT,
 
  KEY `i` (`i`)
 
) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
 
/*!50100 PARTITION BY HASH (i)
 
PARTITIONS 2 */ |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT * FROM t;
 
+---+
 
| i |
 
+---+
 
| 1 |
 
| 2 |
 
| 3 |
 
+---+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [test]> TRUNCATE TABLE t;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> SHOW CREATE TABLE t;
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                   |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t     | CREATE TABLE `t` (
 
  `i` int(11) NOT NULL AUTO_INCREMENT,
 
  KEY `i` (`i`)
 
) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
 
/*!50100 PARTITION BY HASH (i)
 
PARTITIONS 2 */ |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> INSERT INTO t VALUES (NULL),(NULL);
 
Query OK, 2 rows affected (0.00 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT * FROM t;
 
+---+
 
| i |
 
+---+
 
| 4 |
 
| 5 |
 
+---+
 
2 rows in set (0.00 sec)

Generated at Thu Feb 08 06:30:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.