Mysql check auto_increment values

Posted by KsI on January 10, 2013 Leave a comment (0) Go to comments

We get old project, with braindead architecture.
Database structure provides surprises regularly. Yesterday we had a problem with maximum integer value reached at one column.

‘id’ at one table was auto_increment and “INT” not UNSIGNED INT or BIG INT, so after reaching id value 2147483647 application stop working, I change type to UNSIGNED BIGINT.

ALTER TABLE ad_campaigns_rates MODIFY id BIGINT UNSIGNED not null auto_increment, AUTO_INCREMENT = 2147483648;

But Issue to check all similar fields at DB structure occur. How to solve it. We have 300+ tables.
lets select db,table name,column name WITH DATA_TYPE like ‘%int%’ and auto incremented.

SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS where DATA_TYPE like '%int%' AND TABLE_SCHEMA='smi20' and EXTRA='auto_increment' limit 500;

I pipe output to file, parse via bash and exec sql for each row.
output looks like:

smi20    ad_model                        id     int       
smi20    ad_model_filter                 id     int       
smi20    ad_model_setting                id     int       
smi20    ad_model_setting_value          id     int       
smi20    ad_model_settings_set           id     int       
smi20    ad_preview_page                 id     int       
smi20    ad_strategy                     id     bigint     
smi20    alerts                          id     int

How to get values:
Sorry I was in hurry and create Hindu bash code again :(.

#!/bin/bash

for i in `cat ./feelds.txt | awk '{print $2"+"$3"+"$4}'`;
do

res=`echo $i | sed 's/+/ /g'`

words=( $res )

echo "table_name ${words[0]} column_name ${words[1]} column_type ${words[2]} max_value 2 147 483 648"

mysql -u root -pMEGAPASS -h localhost DBNAME -e "select max(${words[1]}) from ${words[0]};"

done