Golang App Dying after sometime

I am working on a web project but it has a downtime of many hours/per week and it dies quite often now.

I checked all server logs and found the issue in MySQL connection:

2018-02-28T12:00:01.963958Z 402 [Note] Aborted connection 402 to db: ‘username’ user: ‘root’ host: ‘localhost’ (Got an error reading communication packets)
2018-02-28T12:00:01.963958Z 255 [Note] Aborted connection 255 to db: ‘username’ user: ‘root’ host: ‘localhost’ (Got an error reading communication packets)
2018-02-28T12:00:01.963992Z 252 [Note] Aborted connection 252 to db: ‘username’ user: ‘root’ host: ‘localhost’ (Got an error reading communication packets)
2018-02-28T12:00:01.964148Z 248 [Note] Aborted connection 248 to db: ‘username’ user: ‘root’ host: ‘localhost’ (Got an error reading communication packets)
2018-02-28T12:00:01.964018Z 249 [Note] Aborted connection 249 to db: ‘username’ user: ‘root’ host: ‘localhost’ (Got an error reading communication packets)

I am assuming this is the reason for dying app as MySQL connection is aborting.

Could you please suggest what I am doing wrong and why there are so many connections?

Thanks

im just an beginner .but in my experience my suggestion is kindly change
the port and try .Because sometimes using the same port wont connect to db

Does your GO code connect to the mysql directly?
If that is so can you tell us what sql packages you are using and post the relative code? (no passwords of course)

I am using this code to handle all my database CRUD.
Similar issue: https://github.com/go-sql-driver/mysql/issues/556

package funcs

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

var DbConn *sql.DB

func DbOpenConn() (*sql.DB, error) {
	connstr := GetDBConnString()
	DbConn, err := sql.Open("mysql", connstr)
	CheckErr(err)

	DbConn.SetMaxIdleConns(40)
	DbConn.SetMaxOpenConns(100)
	return DbConn, err
}

func FixConn() (*sql.DB) {
	checkping := DbConn.Ping()

	var err error
	if checkping != nil { //if error
		log.Println("DbConn.Ping failed here. Creating a new connection.")
		DbConn, err = DbOpenConn()
		CheckErr(err)
		return DbConn
	}
	return DbConn
}

func DbExecute(db *sql.DB, query string, params ...interface{}) (sql.Result, error) {
	db = FixConn()

	stmt, err := db.Prepare(query)
	CheckErr(err)

	res, err := stmt.Exec(params...)
	CheckErr(err)

	stmt.Close()
	return res, err
}

func DbInsert(db *sql.DB, query string, params ...interface{}) (int64, error) {
	db = FixConn()

	res, err := DbExecute(db, query, params...)
	CheckErr(err)

	id, err := res.LastInsertId()
	CheckErr(err)

	return id, err
}

func DbUpdate(db *sql.DB, query string, params ...interface{}) (int64, error) {
	db = FixConn()

	res, err := DbExecute(db, query, params...)
	CheckErr(err)

	affect, err := res.RowsAffected()
	CheckErr(err)

	return affect, err
}

func DbDelete(db *sql.DB, query string, params ...interface{}) (int64, error) {
	var affect int64
	var err error
	db = FixConn()

	affect, err = DbUpdate(db, query, params...)
	CheckErr(err)

	return affect, err
}

func DbQueryGetRows(db *sql.DB, query string) (*sql.Rows, error) {
	var rows *sql.Rows
	var err error

	db = FixConn()
	rows, err = db.Query(query)
	CheckErr(err)

	return rows, err
}

func DbQueryGetRowsParams(db *sql.DB, query string, params ...interface{}) (*sql.Rows, error) {
	db = FixConn()

	stmt, err1 := db.Prepare(query)
	CheckErr(err1)

	rows, err2 := stmt.Query(params...)
	CheckErr(err2)

	stmt.Close()
	return rows, err2
}

func DbQueryGetRowsParamsSingle(db *sql.DB, query string, params ...interface{}) (*sql.Rows, error) {
	var err error

	db = FixConn()
	CheckErr(err)

	stmt, err := db.Prepare(query)
	CheckErr(err)

	rows, err := stmt.Query(params...)
	CheckErr(err)

	stmt.Close()
	return rows, err
}

func FetchRowsByID(db *sql.DB, query string, param string) (*sql.Rows, error) {
	var rows *sql.Rows
	var err error

	db = FixConn()
	CheckErr(err)

	rows, err = db.Query(query, param)
	CheckErr(err)

	return rows, err
}

func DbCloseConn(db *sql.DB) {
	db.Close()
}

func CountRows(rows *sql.Rows) int {
	count := 0
	for rows.Next() {
		count++
	}
	return count
}

I created the above mentioned code and I don’t think that I am leaking anything in it.

Whenever I call these functions from other packages, I always use rows.Close() (doubled checked) after I am done using the returned rows. I also added a log message tracking if the connection is dying in FixConn() function (but it is not).

Another place where I am checking if DbConn is still alive is main function.

if funcs.DbConn == nil {
	log.Println("Creating new Connection. DbConn is Dead")
	var err error
	funcs.DbConn, err = funcs.DbOpenConn()
	funcs.CheckErr(err)
}

One more thing: I haven’t used db.Close() anywhere in my code. I am not sure whether it is a good idea to open & close the DB connection with each CRUD request.

Please correct me if I am doing something wrong.

Thanks

Hi andersk, I just want to share this case before you debug your code:

MySQL increments the status counter for Aborted_clients, which could mean:

  • The client connected successfully but terminated improperly (and may relate to not closing the connection properly)
  • The client slept for longer than the defined wait_timeout or interactive_timeout seconds (which ends up causing the connection to sleep for wait_timeout seconds and then the connection gets forcibly closed by the MySQL server)
  • The client terminated abnormally or exceeded the max_allowed_packet for queries

some other say it’s related to network/firewall.

Hello Adriel,

Thanks for the reply. I read this on MySQL website too.

I will check more.

Thanks

I found the solution for the issue:

Same issue here:


I was getting two MySQL errors:
Aborted Connection
Too Many Connections (after code changes)

So, I added this code to FIX the issue:

DbConn.SetMaxIdleConns(0) //No Idle connection
DbConn.SetMaxOpenConns(400) //Max connection to 400
DbConn.SetConnMaxLifetime(time.Second * 5) //Connection dies after 5 seconds

In MySQL default values are

MaxOpenConns : 150
MaxLifetime : 10

Also added the following in /etc/mysql/my.cnf //mysql config file:

[mysqld]
max_connections = 500

The overall solution is:
1 - Do not retain any connection (as mentioned in github links)
2 - Increase max connections in Go code & MySQL
3 - Reduce Connection Lifetime

Thanks

2 Likes

Ones Query is executed in database then that reference variable should be close
Ex :-. dB ,err := connection.Query(" ur query ")
If errs := dB.error ; errs!=nill{
dB.close()
}
u can handle err also