MySQL Bugs: #12973: SHOW TABLE STATUS shows Autoincrement = 0 for InnoDB

Bug #12973	SHOW TABLE STATUS shows Autoincrement = 0 for InnoDB
Submitted:	3 Sep 2005 20:51	Modified:	8 Sep 2005 20:25
Reporter:	MySQL-Front Team	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.12-beta/BK source	OS:	Windows (Windows XP/Linux)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
SHOW TABLE STATUS shows Autoincrement = 0 for InnoDB engines.

How to repeat:
CREATE TABLE `foo` (
  `Id` int(11) NOT NULL auto_increment,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB;
INSERT INTO `foo` SET `Id`=1;
INSERT INTO `foo` SET `Id`=2;
SHOW TABLE STATUS LIKE 'foo';

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create jkl
miguel@hegel:~/dbs/5.0> bin/mysql -uroot jkl
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.13-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `foo` (
    ->   `Id` int(11) NOT NULL auto_increment,
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO `foo` SET `Id`=1;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `foo` SET `Id`=2;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW TABLE STATUS LIKE 'foo'\G
*************************** 1. row ***************************
           Name: foo
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 0
    Create_time: 2005-09-03 18:31:54
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 4096 kB
1 row in set (0.00 sec)

mysql> 

mysql> SHOW TABLE STATUS LIKE 'foo'\G
*************************** 1. row ***************************
           Name: foo
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 3
    Create_time: 2005-09-03 18:18:52
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 4096 kB
1 row in set (0.00 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.11-beta-nt |
+----------------+
1 row in set (0.01 sec)

Hi!

The following patch from August 8, 2005 probably introduced this bug.

Note the comment: "This allows to avoid locking
of tables which is needed when we use handler::update_auto_increment() method"

To init the auto-inc counter, InnoDB and BDB need to use some kind of locking, at least internally.

Regards,

Heikki

------------------------------------------------
D 1.253.2.1 05/08/08 17:46:01+04:00 dlenev@mysql.com 573 555 37/36/3873
P sql/sql_show.cc
C get_all_tables():
C   Now we use THD::reset_n_/restore_backup_open_tables_state() for
C   saving/restoring open tables state instead of working with it directly
C   (This also allows us to have proper content of I_S system tables in
C   statements with stored functions and in stored functions). We also
C   ignore possible flushes when opening tables (we may create deadlock
C   otherwise). Also we do all needed manipulations with LEX in this function
C   and not in get_schema_tables_result() now.
C get_schema_tables_record():
C   Let us use handler::info() method with HA_STATUS_AUTO flag for obtaining
C   information about table's auto-increment value. This allows to avoid locking
C   of tables which is needed when we use handler::update_auto_increment() metho
d.
C fill_schema_proc():
C   Now we use open_proc_table_for_read/close_proc_table() for access to
C   mysql.proc table (so we won't cause deadlock if we already have some
C   tables open and locked, this also allows us to have proper content in
C   ROUTINES system table in statements using stored functions/in stored
C   functions).
C get_schema_tables_result():
C   Moved all manipulations with Open_tables_state and LEX needed for
C   safe opening of tables to ST_SCHEMA_TABLE::fill_table functions
C   (i.e. get_all_tables() and fill_schema_proc()).
------------------------------------------------

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/internals/29291

The bug was introduced in 5.0.12 and fixed in 5.0.13.

Noted in 5.0.13 changelog.