Bug #96124 AUTO_INCREMENT out of sync
Submitted: 8 Jul 12:01 Modified: 8 Jul 13:00
Reporter: Björn N Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.16 OS:Mac OS X
Assigned to: CPU Architecture:x86

[8 Jul 12:01] Björn N
Description:
AUTO_INCREMENT out of sync in table status and in information_schema.

On a OSX system the following bug occurred where a tables AUTO_INCREMENT is out of sync when looking in `information_schema.tables` and in `TABLE STATUS`.

This output shows AUTO_INCREMENT as `28` while the correct is `AUTO_INCREMENT=44` is the output from a dump.

I am unaware on how to reproduce, but figured it would be better to report it to see if something comes of it. Below are some output from said commands.

mysql> SHOW TABLE STATUS LIKE '<table-name>'\G
*************************** 1. row ***************************
           Name: <table-name>
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 49152
      Data_free: 0
 Auto_increment: 28
    Create_time: 2019-06-27 23:18:24
    Update_time: 2019-07-05 00:43:24
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.tables WHERE (table_schema, table_name) = ('ticketsolve_dev', 'upload_attachments')\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: <schema_name>
     TABLE_NAME: <table_name>
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 49152
      DATA_FREE: 0
 AUTO_INCREMENT: 28
    CREATE_TIME: 2019-06-27 23:18:24
    UPDATE_TIME: 2019-07-05 00:43:24
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

CREATE TABLE `<table-name>` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `resource_type` varchar(255) NOT NULL,
  `resource_id` int(11) NOT NULL,
  `account_id` smallint(5) unsigned NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY ...,
  KEY ...,
  KEY ...
 (`resource_id`,`resource_type`),
  CONSTRAINT `fk_rails_52d76d763e` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

How to repeat:
Currently not able to reproduce.
[8 Jul 12:24] Umesh Shastry
Hello Björn N,

Thank you for the report.
Imho this is an expected behavior in 8.0 as default value
of information_schema_stats_expiry is 86400 secs (= 1 day) - https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc... 
This is introduced in 8.0 and not present in 5.7. 

Marking this as duplicate of Bug #91038, please see Bug #91038

regards,
Umesh
[8 Jul 13:00] Björn N
Thank you for the fast response!