Looking Out for Max Values in Integer-Based Columns in MySQL

originally written by matthew boehm

yay! my first blog post! as long as at least 1 person finds it useful, i’ve done my job. recently, one of my long-term clients was noticing that while their inserts were succeeding, a particular column counter was not incrementing. a quick investigation determined the column was of type int(11) and they had reached the maximum value of 2147483647. we fixed this by using pt-online-schema-change to change the column to int(10) unsigned , thus allowing values up to 4294967295.

my client was now concerned about all his other integer-based columns and wanted me to check them all. so i wrote a quick-n-dirty script in go to check all integer-based columns on their current value compared to the maximum allowed for that column type.

you can find the full source code in my git repo .

here’s a quick overview; the code is pretty simple.

first we connect to mysql and verify the connection:

db, err := sql.open("mysql", fmt.sprintf("%s:%s@tcp(%s:3306)/%s", mysqlun, mysqlpw, hosttocheck, dbtocheck))
if err != nil {
	fmt.printf("error connecting to mysql on '%s': n", hosttocheck, err)
	db.close()
	os.exit(1)
}
// check connection is alive.
err = db.ping()
if err != nil {
	fmt.printf("unable to ping mysql at '%s': %sn", hosttocheck, err)
	db.close()
	os.exit(1)
}

next, we query the information_schema.columns table for the names of all integer-based columns and calculate what their maximum value can be (credit for the clever sql goes to peter boros ).

// construct our base i_s query
var tableextrasql string
if tabletocheck != "" {
	tableextrasql = fmt.sprintf("and table_name = '%s'", tabletocheck)
}
basesql := fmt.sprintf(`
	select table_name, column_name, column_type, (case data_type
   	  when 'tinyint' then 255
    	  when 'smallint' then 65535
    	  when 'mediumint' then 16777215
    	  when 'int' then 4294967295
    	  when 'bigint' then 18446744073709551615
   	end >> if(locate('unsigned', column_type) > 0, 0, 1)) as max_value
	from information_schema.columns
	where table_schema = '%s' %s
	and data_type in ('tinyint', 'int', 'mediumint', 'bigint')`, dbtocheck, tableextrasql)

now that we have this list of columns to check, we simply loop over this result set, get the max() of each column and print a pretty report.

// loop over rows received from i_s query above.
for columnstocheck.next() {
	err := columnstocheck.scan(&tablename, &columnname, &columntype, &maxvalue)
	if err != nil {
		log.fatal("scanning row error: ", err)
	}
	// check this column
	query := fmt.sprintf("select max(%s), round((max(%s)/%d)*100, 2) as ratio from %s.%s",
		columnname, columnname, maxvalue, dbtocheck, tablename)
	err = db.queryrow(query).scan(¤tvalue, &ratio)
	if err != nil {
		fmt.printf("couldn't get max(%s.%s): %sn", tablename, columnname, err)
		fmt.println("sql: ", query)
		continue
	}
	// print report
	if ratio.valid && ratio.float64 >= float64(reportpct) {
		fmt.printf("'%s'.'%s' - type: '%s' - ", tablename, columnname, columntype)
		fmt.printf("colummax: '%d'", maxvalue)
		fmt.printf(" - curval: '%d'", currentvalue.int64)
		fmt.printf(" - fillratio: '%.2f'n", ratio.float64)
	}
}

there are more options to the app that allow you to silence some of the verbosity and to only print report lines where the value-to-max ratio is > a user-defined threshold. if you have frequently changing schemas, this should allow you to cron the app and only receive email reports when there is a potential problem. otherwise, this tool could be useful to run once a month/quarter, just to verify things are in good standing.

like i said before, hopefully this helps at least 1 person catch a potential problem sooner rather than later.