DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Automating the Migration From JS to TS for the ZK Framework
  • 5 Key Concepts for MQTT Broker in Sparkplug Specification
  • File Upload Security and Malware Protection
  • From On-Prem to SaaS

Trending

  • Automating the Migration From JS to TS for the ZK Framework
  • 5 Key Concepts for MQTT Broker in Sparkplug Specification
  • File Upload Security and Malware Protection
  • From On-Prem to SaaS
  1. DZone
  2. Data Engineering
  3. Databases
  4. Looking Out for Max Values in Integer-Based Columns in MySQL

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

Peter Zaitsev user avatar by
Peter Zaitsev
·
Jul. 16, 14 · Interview
Like (0)
Save
Tweet
Share
4.21K Views

Join the DZone community and get the full member experience.

Join For Free

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.


sql Database MySQL

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Automating the Migration From JS to TS for the ZK Framework
  • 5 Key Concepts for MQTT Broker in Sparkplug Specification
  • File Upload Security and Malware Protection
  • From On-Prem to SaaS

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: