Bug #35111 Truncate a MyISAM partitioned table does not reset the auto_increment value.

• Submitted: 6 Mar 2008 14:16
• Modified: 12 Nov 2009 20:12
• Reporter: Mattias Jonsson
• Status: Closed
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: 5.1+
• OS: Any
• Assigned to: Satya B
• CPU Architecture: Any

[6 Mar 2008 14:16] Mattias Jonsson

Description:
When truncating a partitioned MyISAM table, its auto_increment value is not reseted. (It works in InnoDB and Falcon)

How to repeat:
CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY)
ENGINE=MyISAM
PARTITION BY HASH (c1)
PARTITIONS 2;
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
1
DELETE FROM t1;
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
2
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
3
DROP TABLE t1;
# the result should be 1,2,1 not 1,2,3.

Suggested fix:
Implement the ha_myisam::reset_auto_increment() function, which could be called from ha_partition::reset_auto_increment via handler::ha_reset_auto_increment.
Or do this in the ha_myisam::delete_all_rows (mi_delete_all_rows) if SQLCOM_TRUNCATE like InnoDB does.

[6 Mar 2008 22:29] MySQL Verification Team

Thank you for the bug report.

[23 Jan 2009 10:02] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63878

2750 Mattias Jonsson	2009-01-23
      Bug#35111: Truncate a MyISAM partitioned table does not reset
      the auto_increment value
      
      Problem was that a non partitioned table just did recreate of the table,
      overwriting the previous table. But a partitioned table could not indicate
      if the partitioning engine (or the partitions engines) did support recreate
      or not.
      
      Solution was to extend the 'peek at the table' function (mysql_frm_type) to also
      look at the default engine used by the table, resulting in the same handling of
      partitioned as of non partitioned tables.

[25 Mar 2009 15:46] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70370

2773 Mattias Jonsson	2009-03-25
      Bug#35111: Truncate a MyISAM partitioned table does not reset
      the auto_increment value
      
      This is an alternative patch that instead of allowing RECREATE TABLE
      on TRUNCATE TABLE it implements reset_auto_increment that is called
      after delete_all_rows.
     @ mysql-test/suite/parts/r/partition_auto_increment_memory.result
        Bug#35111: Truncate a MyISAM partitioned table does not reset
        the auto_increment value
        
        Now correct result
     @ mysql-test/suite/parts/r/partition_auto_increment_myisam.result
        Bug#35111: Truncate a MyISAM partitioned table does not reset
        the auto_increment value
        
        Now correct result
     @ sql/ha_partition.cc
        Bug#35111: Truncate a MyISAM partitioned table does not reset
        the auto_increment value
        
        Added reset_auto_increment after delete_all_rows if truncate.
     @ storage/heap/ha_heap.cc
        Bug#35111: Truncate a MyISAM partitioned table does not reset
        the auto_increment value
        
        Added reset_auto_increment, to be used after delete_all_rows
        to simulate truncate.
     @ storage/heap/ha_heap.h
        Bug#35111: Truncate a MyISAM partitioned table does not reset
        the auto_increment value
        
        Added reset_auto_increment, to be used after delete_all_rows
        to simulate truncate.
     @ storage/myisam/ha_myisam.cc
        Bug#35111: Truncate a MyISAM partitioned table does not reset
        the auto_increment value
        
        Added reset_auto_increment, to be used after delete_all_rows
        to simulate truncate.
     @ storage/myisam/ha_myisam.h
        Bug#35111: Truncate a MyISAM partitioned table does not reset
        the auto_increment value
        
        Added reset_auto_increment, to be used after delete_all_rows
        to simulate truncate.

[11 May 2009 11:43] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73749

2847 Mikael Ronstrom	2009-05-08
      WL#4444 Added TRUNCATE partition support, fixes bug#19405 and bug#35111
      modified:
        mysql-test/suite/parts/inc/partition_mgm.inc
        mysql-test/suite/parts/r/partition_auto_increment_memory.result
        mysql-test/suite/parts/r/partition_auto_increment_myisam.result
        mysql-test/suite/parts/r/partition_mgm_lc0_archive.result
        mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result
        mysql-test/suite/parts/r/partition_mgm_lc0_memory.result
        mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result
        mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result
        mysql-test/suite/parts/r/partition_mgm_lc1_archive.result
        mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result
        mysql-test/suite/parts/r/partition_mgm_lc1_memory.result
        mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result
        mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result
        mysql-test/suite/parts/r/partition_mgm_lc2_archive.result
        mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result
        mysql-test/suite/parts/r/partition_mgm_lc2_memory.result
        mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result
        mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result
        mysql-test/suite/parts/t/partition_mgm_lc0_archive.test
        mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test
        mysql-test/suite/parts/t/partition_mgm_lc1_archive.test
        mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test
        mysql-test/suite/parts/t/partition_mgm_lc2_archive.test
        mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test
        sql/ha_partition.cc
        sql/partition_element.h
        sql/sql_delete.cc
        sql/sql_partition.cc
        sql/sql_table.cc
        sql/sql_yacc.yy
        storage/heap/ha_heap.cc
        storage/heap/ha_heap.h
        storage/myisam/ha_myisam.cc
        storage/myisam/ha_myisam.h

[2 Jun 2009 8:54] Mattias Jonsson

Set svoj and satay as reviewers.

The proposed patch is:
  http://lists.mysql.com/commits/70370

2773 Mattias Jonsson	2009-03-25

[8 Jul 2009 12:14] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/78199

3013 Satya B	2009-07-08
      Bug#35111 - Truncate a MyISAM partitioned table does not reset
      the auto_increment value
            
      This is an alternative patch that instead of allowing RECREATE TABLE
      on TRUNCATE TABLE it implements reset_auto_increment that is called
      after delete_all_rows.
      
      Note: this bug was fixed by Mattias Jonsson:
      Pusing this patch: http://lists.mysql.com/commits/70370
      modified:
        mysql-test/suite/parts/r/partition_auto_increment_memory.result
        mysql-test/suite/parts/r/partition_auto_increment_myisam.result
        sql/ha_partition.cc
        storage/heap/ha_heap.cc
        storage/heap/ha_heap.h
        storage/myisam/ha_myisam.cc
        storage/myisam/ha_myisam.h

[10 Jul 2009 11:21] Bugs System

Pushed into 5.4.4-alpha (revid:[e-mail]-20090710111017-bnh2cau84ug1hvei) (version source revid:[e-mail]-20090708124636-dbzumhgzrm38hdue) (merge vers: 5.4.4-alpha) (pib:11)

[13 Jul 2009 17:49] Bugs System

Pushed into 5.1.37 (revid:[e-mail]-20090713174543-cd2x7q1gi1hzoand) (version source revid:[e-mail]-20090710151930-6e6kq5tp7ux1rtbh) (merge vers: 5.1.37) (pib:11)

[13 Jul 2009 20:08] Paul DuBois

Noted in 5.1.37, 5.4.4 changelogs.

Truncating a partitioned MyISAM table did not reset the
AUTO_INCREMENT value.

[21 Jul 2009 15:42] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/79100

2826 Mikael Ronstrom	2009-07-21
      WL#4444 Added TRUNCATE partition support, fixes bug#19405 and bug#35111
      modified:
        mysql-test/suite/parts/inc/partition_mgm.inc
        mysql-test/suite/parts/r/partition_mgm_lc0_archive.result
        mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result
        mysql-test/suite/parts/r/partition_mgm_lc0_memory.result
        mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result
        mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result
        mysql-test/suite/parts/r/partition_mgm_lc1_archive.result
        mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result
        mysql-test/suite/parts/r/partition_mgm_lc1_memory.result
        mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result
        mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result
        mysql-test/suite/parts/r/partition_mgm_lc2_archive.result
        mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result
        mysql-test/suite/parts/r/partition_mgm_lc2_memory.result
        mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result
        mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result
        mysql-test/suite/parts/t/partition_mgm_lc0_archive.test
        mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test
        mysql-test/suite/parts/t/partition_mgm_lc1_archive.test
        mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test
        mysql-test/suite/parts/t/partition_mgm_lc2_archive.test
        mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test
        sql/ha_partition.cc
        sql/partition_element.h
        sql/sql_delete.cc
        sql/sql_partition.cc
        sql/sql_table.cc
        sql/sql_yacc.yy

[12 Aug 2009 21:57] Paul DuBois

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

[14 Aug 2009 22:50] Paul DuBois

Ignore previous comment about 5.4.2.

[18 Aug 2009 20:03] Bugs System

Pushed into 5.4.4-alpha (revid:[e-mail]-20090818200211-fx1sq0284n261fb3) (version source revid:[e-mail]-20090721154436-z3jtxoycg9a68h58) (merge vers: 5.4.4-alpha) (pib:11)

[26 Aug 2009 13:46] Bugs System

Pushed into 5.1.37-ndb-7.0.8 (revid:[e-mail]-20090826132541-yablppc59e3yb54l) (version source revid:[e-mail]-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

[26 Aug 2009 13:46] Bugs System

Pushed into 5.1.37-ndb-6.3.27 (revid:[e-mail]-20090826105955-bkj027t47gfbamnc) (version source revid:[e-mail]-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

[26 Aug 2009 13:48] Bugs System

Pushed into 5.1.37-ndb-6.2.19 (revid:[e-mail]-20090825194404-37rtosk049t9koc4) (version source revid:[e-mail]-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

[27 Aug 2009 16:33] Bugs System

Pushed into 5.1.35-ndb-7.1.0 (revid:[e-mail]-20090827163030-6o3kk6r2oua159hr) (version source revid:[e-mail]-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

[10 Sep 2009 9:09] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82901

2877 Mikael Ronstrom	2009-09-10
      WL#4444 Added TRUNCATE partition support, fixes bug#19405 and bug #35111
      added:
        mysql-test/r/partition_truncate.result
        mysql-test/t/partition_truncate.test
      modified:
        BUILD/build_mccge.sh
        mysql-test/suite/parts/inc/partition_mgm.inc
        mysql-test/suite/parts/r/partition_mgm_lc0_archive.result
        mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result
        mysql-test/suite/parts/r/partition_mgm_lc0_memory.result
        mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result
        mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result
        mysql-test/suite/parts/r/partition_mgm_lc1_archive.result
        mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result
        mysql-test/suite/parts/r/partition_mgm_lc1_memory.result
        mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result
        mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result
        mysql-test/suite/parts/r/partition_mgm_lc2_archive.result
        mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result
        mysql-test/suite/parts/r/partition_mgm_lc2_memory.result
        mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result
        mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result
        mysql-test/suite/parts/t/partition_mgm_lc0_archive.test
        mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test
        mysql-test/suite/parts/t/partition_mgm_lc1_archive.test
        mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test
        mysql-test/suite/parts/t/partition_mgm_lc2_archive.test
        mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test
        sql/ha_partition.cc
        sql/handler.cc
        sql/partition_element.h
        sql/sql_delete.cc
        sql/sql_partition.cc
        sql/sql_table.cc
        sql/sql_yacc.yy

[30 Sep 2009 8:17] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20090929093622-1mooerbh12e97zux) (version source revid:[e-mail]-20090929093622-1mooerbh12e97zux) (merge vers: 6.0.14-alpha) (pib:11)

[5 Oct 2009 19:49] Paul DuBois

Noted in 6.0.14 changelog.

[12 Nov 2009 8:20] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091110093229-0bh5hix780cyeicl) (version source revid:[e-mail]-20091002121459-pvg0cs6lt298xo83) (merge vers: 5.4.5-beta) (pib:13)

[12 Nov 2009 20:12] Paul DuBois

Noted in 5.5.0 changelog.