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 Email Updates:
Status: Closed Impact on me:
None 
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:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:satya.bn@sun.com-20090708124636-dbzumhgzrm38hdue) (merge vers: 5.4.4-alpha) (pib:11)
[13 Jul 2009 17:49] Bugs System 
Pushed into 5.1.37 (revid:joro@sun.com-20090713174543-cd2x7q1gi1hzoand) (version source revid:staale.smedseng@sun.com-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:alik@sun.com-20090818200211-fx1sq0284n261fb3) (version source revid:mikael@mysql.com-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:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-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:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-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:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-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:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-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:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-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:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@sun.com-20091002121459-pvg0cs6lt298xo83) (merge vers: 5.4.5-beta) (pib:13)
[12 Nov 2009 20:12] Paul DuBois 
Noted in 5.5.0 changelog.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.