Help with query
Posted by: Dayton Jones
Date: January 24, 2019 09:29AM

I'm trying to get a get query to check any fields that are auto_increment with type int that are running out of ids to assign.

What I have right now is:
SELECT table_name FROM (SELECT table_schema,table_name,auto_increment, (2147483647 - auto_increment) values_left FROM information_schema.tables WHERE auto_increment IS NOT NULL) A WHERE values_left < 1000000

which works until it hits a field that is BIGINT:

mysql> SELECT table_name FROM (SELECT table_schema,table_name,auto_increment, (2147483647 - auto_increment) values_left FROM information_schema.tables WHERE auto_increment IS NOT NULL) A WHERE values_left < 1000000;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(2147483647 - `information_schema`.`tables`.`AUTO_INCREMENT`)'

How can I limit that query to only auto_increment fields that are INT and ignore BIGINT?

Options: ReplyQuote


Subject
Written By
Posted
Help with query
January 24, 2019 09:29AM
January 24, 2019 10:47AM


Sorry, only registered users may post in this forum.

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.