MySQL Bugs: #104581: AUTOINCREMENT value from information

Bug #104581	AUTOINCREMENT value from information_schema not returning correct value
Submitted:	10 Aug 2021 13:43	Modified:	12 Aug 2021 15:00
Reporter:	Tariq K	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	autoincrement, BEFORE INSERT, trigger

Description:
Query:
SELECT AUTO_INCREMENT FROM information_schema.`TABLES` s WHERE s.`TABLE_SCHEMA` = DATABASE() AND s.`TABLE_NAME` = 'dummy';

gives correct value when run as a separate query, but when the same query is being executed from a BEFORE INSERT TRIGGER, even after INSERT is committed, it keeps on returning an old value.

Note: already set information_schema_stats_expiry = 0.

The solution being implemented is to save the value of autoincrement (id) column in another column (display_id) at the time of insertion.

How to repeat:
#Table

CREATE TABLE `dummy` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `display_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#Trigger

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `generateDisplayDummy`$$

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `generateDisplayDummy` BEFORE INSERT 
ON `dummy` FOR EACH ROW 
BEGIN
  IF (new.display_id IS NULL) 
  THEN SET new.display_id = 
  (SELECT 
    `tables`.`AUTO_INCREMENT` AS `AUTO_INCREMENT` 
  FROM
    `information_schema`.`TABLES` 
  WHERE (
      (
        `tables`.`TABLE_SCHEMA` = DATABASE()
      ) 
      AND (RAND() <> RAND()) 
      AND (`tables`.`TABLE_NAME` = 'dummy')
    )) ;
  END IF ;
END ;
$$

DELIMITER ;

#Test queries

mysql> INSERT INTO `dummy`(`id`,`name`,`display_id`) VALUES ( NULL,'test',NULL);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql> SELECT * FROM dummy;
+----+------+------------+
| id | name | display_id |
+----+------+------------+
| 66 | test |         66 |
+----+------+------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> SELECT tables.`AUTO_INCREMENT` AS `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE ((`tables`.`TABLE_SCHEMA` = DATABASE()))
    ->       AND (`tables`.`TABLE_NAME` = 'dummy');
+----------------+
| AUTO_INCREMENT |
+----------------+
|             67 |
+----------------+
1 row in set (0.00 sec)

mysql>
mysql> INSERT INTO `dummy`(`id`,`name`,`display_id`) VALUES ( NULL,'test',NULL);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql> SELECT * FROM dummy;
+----+------+------------+
| id | name | display_id |
+----+------+------------+
| 66 | test |         66 |
| 67 | test |         66 |
+----+------+------------+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> SELECT tables.`AUTO_INCREMENT` AS `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE ((`tables`.`TABLE_SCHEMA` = DATABASE()))
    ->       AND (`tables`.`TABLE_NAME` = 'dummy');
+----------------+
| AUTO_INCREMENT |
+----------------+
|             68 |
+----------------+
1 row in set (0.00 sec)

Suggested fix:
It should return latest value if trigger is executed 2nd time

Hi Mr. K,

Thank you for your bug report.

However, it is not a bug.

if you read our Reference Manual on the topic, you will find out that this value, in the I_S table,  for InnoDB tables provide only informational and not exact value.

This is due to the fact that InnoDB is a MVCC storage engine and it adheres to the standard of multi-versioning.

The only exact manner of obtaining exact number of rows in InnoDB, is to get count(*). However, since it has to scan all rows, it is very, very slow on large tables.

Hence, we recommend that, after insertion, you simply get the next auto_increment column, for what ever purposes that you need.

Not a bug.

I understand that information_schema is not reliable in the sense to get the exact value everytime. But one of the main concern here is the difference in behavior when the query to get auto_increment separately vs when it is executed from inside the trigger. Behavior should be consistent