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(&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.

The post Looking out for max values in integer-based columns in MySQL appeared first on MySQL Performance Blog.