Looking out for max values in integer-based columns in MySQL

July 7, 2014

Author

Matthew Boehm

Insight for DBAs

MySQL

Share this Post:

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.

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))<br>if err != nil {<br>	fmt.Printf("Error connecting to MySQL on '%s': n", hostToCheck, err)<br>	db.Close()<br>	os.Exit(1)<br>}<br><br>// Check connection is alive.<br>err = db.Ping()<br>if err != nil {<br>	fmt.Printf("Unable to ping mysql at '%s': %sn", hostToCheck, err)<br>	db.Close()<br>	os.Exit(1)<br>}<br>

1

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<br>var tableExtraSql string<br>if tableToCheck != "" {<br>	tableExtraSql = fmt.Sprintf("AND TABLE_NAME = '%s'", tableToCheck)<br>}<br><br>baseSql := fmt.Sprintf(`<br>	SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, (CASE DATA_TYPE<br>   	  WHEN 'tinyint' THEN 255<br>    	  WHEN 'smallint' THEN 65535<br>    	  WHEN 'mediumint' THEN 16777215<br>    	  WHEN 'int' THEN 4294967295<br>    	  WHEN 'bigint' THEN 18446744073709551615<br>   	END &gt;&gt; IF(LOCATE('unsigned', COLUMN_TYPE) &gt; 0, 0, 1)) AS MAX_VALUE<br>	FROM information_schema.columns<br>	WHERE TABLE_SCHEMA = '%s' %s<br>	AND DATA_TYPE IN ('tinyint', 'int', 'mediumint', 'bigint')`, dbToCheck, tableExtraSql)<br>

1

// 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.<br>for columnsToCheck.Next() {<br>	err := columnsToCheck.Scan(&amp;tableName, &amp;columnName, &amp;columnType, &amp;maxValue)<br>	if err != nil {<br>		log.Fatal("Scanning Row Error: ", err)<br>	}<br><br>	// Check this column<br>	query := fmt.Sprintf("SELECT MAX(%s), ROUND((MAX(%s)/%d)*100, 2) AS ratio FROM %s.%s",<br>		columnName, columnName, maxValue, dbToCheck, tableName)<br>	err = db.QueryRow(query).Scan(&amp;currentValue, &amp;ratio)<br>	if err != nil {<br>		fmt.Printf("Couldn't get MAX(%s.%s): %sn", tableName, columnName, err)<br>		fmt.Println("SQL: ", query)<br>		continue<br>	}<br><br>	// Print report<br>	if ratio.Valid &amp;&amp; ratio.Float64 &gt;= float64(reportPct) {<br>		fmt.Printf("'%s'.'%s' - Type: '%s' - ", tableName, columnName, columnType)<br>		fmt.Printf("ColumMax: '%d'", maxValue)<br>		fmt.Printf(" - CurVal: '%d'", currentValue.Int64)<br>		fmt.Printf(" - FillRatio: '%.2f'n", ratio.Float64)<br>	}<br>}<br>

1

// 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(&currentValue, &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.