在大数据量的业务系统中，int的最大值为21亿，这个看似很大的数值，但在各种日志系统中，经常出现溢出的情况，不管是在应用程序中，还是在数据库中，一定需要注意类型超长的情况。 作为mysql的dba,能掌控的就是保证表的自增字段在将要溢出之前，提前通知研发人员该如何处理。 下面模拟一下数据库自增字段溢出之后的现象：
mysql> insert into xcytest1 values(2147483645,1,'sd');
Query OK, 1 row affected (0.02 sec)
mysql> show create table xcytest1;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xcytest1 | CREATE TABLE "xcytest1" (
"id" int(11) NOT NULL AUTO_INCREMENT,
"age" int(11) DEFAULT NULL,
"name" varchar(200) DEFAULT NULL,
PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=2147483646 DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into xcytest1(age,name) values(2,'asd');
Query OK, 1 row affected (0.02 sec)
mysql> insert into xcytest1(age,name) values(2,'asd');
Query OK, 1 row affected (0.02 sec)
mysql> insert into xcytest1(age,name) values(2,'asd');
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
我们发现，当自增字段的自增值达到最大值，取下一个值的时候，还是取到了最大值，导致主键冲突报错。
为了提前发现这个现象，于是编写一个脚本，用来解析mysqldump导出的表结构的sql文件中关于自增字段的表的当前值是否快要溢出。
cat check_auto_incre.sh
sql_file_name=xcytest.sql
tmp_file_name=tmp.file.$$
out_put_file=out.file.sql
result_file=result.txt
warning_value=50
bigint_max=9223372036854775807
int_max=2147483647
unknown_result=unknow.autoinc.table.txt
cat /dev/null >$out_put_file
cat /dev/null >$result_file
cat /dev/null >$unknown_result
check_incr_value()
{
local max_value=$1
local cur_value=$2
local table_name="${3}"
local column_name="${4}"
local column_type="${5}"
used_pct=`expr $cur_value \* 100 `
used_pct=`expr $used_pct / $max_value`
if [ $used_pct -gt $warning_value ] ; then
echo "warning_pct:${used_pct} currunt_value:${cur_value} ${column_type} ${table_name} ${column_name} " >>$result_file
fi
}
while read line_str
do
begin_str=`echo "${line_str}" |grep "^CREATE TABLE" `
if [ "X" == "X${begin_str}" ] ; then
begin_strart=0
else
begin_start=1
cat /dev/null >$tmp_file_name
fi
end_str=` echo $line_str |grep "ENGINE=" `
if [ "X" == "X${end_str}" ]; then
char_end=0
else
char_end=1
fi
echo $line_str >>$tmp_file_name
if [ $char_end -eq 1 ]; then
ifexistauto=`grep "AUTO_INCREMENT" $tmp_file_name`
if [ ! "X" == "X${ifexistauto}" ] ;then
cat $tmp_file_name >>$out_put_file
table_name=`grep "^CREATE TABLE" $tmp_file_name|awk '{ print $3 }' `
column_define=`grep "AUTO_INCREMENT," $tmp_file_name`
column_name=`echo "$column_define" |awk '{print $1 } '`
column_type=`echo "$column_define" |awk '{print $2 } '`
column_incre_value=`grep "AUTO_INCREMENT=" $tmp_file_name|awk '{ print $3} ' |awk -F"=" '{ print $2 } '`
## new create table , no AUTO_INCREMENT value ###
if [ ! "X" == "X${column_incre_value}" ] ; then
if [ "${column_type}" == "bigint(20)" ] ;then
check_incr_value $bigint_max $column_incre_value "${table_name}" "${column_name}" "${column_type}"
return_res=$?
elif [ "${column_type}" == "int(11)" ];then
check_incr_value $int_max $column_incre_value "${table_name}" "${column_name}" "${column_type}"
else
cat $tmp_file_name >>$unknown_result
fi
else
cat $tmp_file_name >>$unknown_result
fi
fi
/bin/rm $tmp_file_name
fi
done < ${sql_file_name}
/bin/rm $tmp_file_name
echo "#######check result #############"
cat $result_file
if [ -s $unknown_result ] ;then
echo "unknow table "
cat $unknown_result
fi
脚本的运行结果如下：
[mysql@MYSQLTMP ~]$ bash check_auto_incr.sh
#######check result #############