In MySQL, it’s very common and recommended to you an auto_increment as Primary Key.
However that integer can have different types each having their size limitation, and they can also be signed or not.
Many people when creating a table use by default the following syntax do define their auto_increment primary key:
id int auto_increment primary key
This is a signed integer where the maximum value is 2,147,483,647.
It’s recommended as a DBA to keep an eye on how fast your tables are filling up and how much records can still be added to your tables.
And this can happen even to the bests ! (see this Github Availability Report on May 5th at 00:45)
Once again, MySQL Shell comes to the rescue with an easy plugin: ext.innodb.getAutoincFill()
The plugin returns the information related to the auto_increments fields.
Of course it’s recommended to not use the information_schema cache in the session.
It provides live information, as when the auto_increment values are exhausted…
As you can see, it’s very important to monitor the tables using auto_increments and it’s also very important to choose a right data type for them. Using
unsigned is always a good practice !
MySQL Shell plugins are available here on github and contributions are welcome !