Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Checks AUTO_INCREMENT fields in MySQL to see if near MAXVALUE
/*
Copyright (c) 2014, Percona LLC and/or its affiliates. All rights reserved.
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Affero General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Affero General Public License for more details.
You should have received a copy of the GNU Affero General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>
*/
/*
mysql_int_checker.go
All integer-based fields in MySQL have a maximum possible value.
This application will check the MAX() value of all integer-based fields in a MySQL
database and print a report showing how close the column is to its maximum value.
Usage of ./mysql_int_checker:
-D: Database to check
-h: MySQL Host
-p: MySQL password
-q: Don't print summary or header lines.
-r: Print report only if ratio > this value.
-t: Table to check. If not specified, checks all tables in database.
-u: MySQL user. Must have SELECT priv on database.
Examples:
-- Check all columns on on tables in the 'foo' database, full summary:
./mysql_int_checker -D foo -h testhost1 -u myuser -p mypw
-- Check all columns on 'foo' database, only reporting if 90% to max
./mysql_int_checker -D foo -h testhost1 -u myuser -p mypw -q -r 90
-- Check all columns on table 'bar' in database 'foo', reporting > 75%
./mysql_int_checker -D foo -t bar -h testhost1 -u myuser -p mypw -q -r 75
*/
package main
import (
"database/sql"
"flag"
"fmt"
_ "github.com/go-sql-driver/mysql"
"log"
"os"
)
func main() {
var hostToCheck string
var dbToCheck string
var tableToCheck string
var summary bool
var reportPct int
var mysqlUn string
var mysqlPw string
// Set up and parse command-line flags
flag.StringVar(&hostToCheck, "h", "", "MySQL Host")
flag.StringVar(&dbToCheck, "D", "", "Database to check")
flag.StringVar(&tableToCheck, "t", "", "Table to check. If not specified, checks all tables in database.")
flag.StringVar(&mysqlUn, "u", "mysql_admin", "MySQL user. Must have SELECT priv on database.")
flag.StringVar(&mysqlPw, "p", "", "MySQL password")
flag.IntVar(&reportPct, "r", 0, "Print report only if ratio > this value.")
flag.BoolVar(&summary, "q", true, "Print summary and header lines.")
flag.Parse()
// Sanity check
if hostToCheck == "" || dbToCheck == "" {
fmt.Println("You must supply at least a MySQL host and a database.")
flag.PrintDefaults()
os.Exit(1)
}
// Print summary
if summary {
fmt.Print("\n-- Summary --\n")
fmt.Println("Host: ", hostToCheck)
fmt.Println("Database: ", dbToCheck)
if tableToCheck != "" {
fmt.Println("Table: ", tableToCheck)
} else {
fmt.Println("Table: -- All Tables --")
}
fmt.Println("Username: ", mysqlUn)
fmt.Println("Password: ", mysqlPw)
fmt.Println("Report %: ", reportPct)
}
// MySQL Connect
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)
}
// Make sure we close the connection when we exit for any reason
defer db.Close()
// Check connection is alive.
err = db.Ping()
if err != nil {
fmt.Printf("Unable to ping mysql at '%s': %s\n", hostToCheck, err)
db.Close()
os.Exit(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)
// Get a list of all the integer-type columns in the specified database
columnsToCheck, err := db.Query(baseSql)
if err != nil {
fmt.Println("Couldn't retrieve column information: ", err)
fmt.Println("SQL: ", baseSql)
// Because os.Exit() doesn't call defers
columnsToCheck.Close()
db.Close()
os.Exit(1)
}
// Call destructor on rows when we exit; mysql_free_result equiv
defer columnsToCheck.Close()
// Variables to store info from the rows
var tableName string
var columnName string
var columnType string
var maxValue uint64
// Special SQL types to handle the NULL case
var ratio sql.NullFloat64
var currentValue sql.NullInt64
if summary {
fmt.Print("\n-- Report --\n")
}
// 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(%[1]s.%[2]s), ROUND((MAX(%[1]s.%[2]s)/%[3]d)*100, 2) AS ratio FROM %[4]s.%[1]s",
tableName, columnName, maxValue, dbToCheck)
err = db.QueryRow(query).Scan(&currentValue, &ratio)
if err != nil {
fmt.Printf("Couldn't get MAX(%s.%s): %s\n", 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)
}
}
// Just to be safe and clean
columnsToCheck.Close()
db.Close()
if summary {
fmt.Print("\nFinished!\n")
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment