Go connect MySQL Database error 1040:too many connections bug fix

Original: https://my.oschina.net/waknow/blog/205654

Summary: When using the Go link database, too many connections errors occur after a period of time due to improper connection release. Therefore, appropriate selection functions and timely release of database connections are required.

These days with go write a simple server, connect MySQL database, provide API to other program calls to implement and delete adaptation and other services. The go version is 1.2 and the driver used is go-sql-driver/mysql. However, after a certain amount of query results, will be too many connection error first.

Google a bit, many articles are suggested to modify the MySQL configuration file: My.ini. The article explains this: the default number of connections for MySQL is 100, and this error occurs when the number of queries is too large. So change the configuration:

max_connections=1000

The number behind this field is the number of connections allowed by MySQL, and a larger one will solve the problem. So on the computer directly changed to 10000, restart MySQL. Then the computer will crash on the card. Looked at the relevant document about knowing that this parameter is to control the number of threads established by MySQL. Change to 10000 will have 10,000 threads, the computer will naturally card. But even this appearance, after a certain period of time will still appear too many connections such errors, but the time will appear later. So this is only a palliative but not a cure, there is no solution to the problem.

Look at the SQL document of Go, which has a func (*db) Setmaxopenconns function, to see the name can control the maximum number of connections. Very happy to set an acceptable number in the program, and then compile to run. The problem remains unresolved, and the same error is reported. Can only be resolved by constantly restarting the server. I really don't know how to solve it. Even suspect that there is a problem with the driver package being used.

Inadvertently see this article Go's database/sql, the article explains the connection pool to the connection database in go: When you need to communicate with the database, you pull a connection from the connection pool and interact with the database. Unused connections that are exhausted go back to the connection pool and wait for the next call. If there is no idle connection in the connection pool, a new connection is created automatically. There is one paragraph:

An SQL. Row returns the connection when Scan () is called, SQL. Rows returns either when Close () are called or all rows are been iterated over with Next (), and SQL. Tx would return when Commit or Rollback () is called. If you forget to completely iterate an SQL. Rows and you forget to Close it, that connection'll never go back to the pool.

As you can see from above, SQL. If row does not traverse or call the close () method directly, the connection that executes this query will persist! When the available connections in the connection pool have been exhausted, a new connection is created. This is why calling Setmaxopenconns is useless because this function simply sets the number of connections in the connection pool! If the connection pool is killed because the connection is not released in time, a new connection will be created continuously until all the MySQL connections are exhausted and an error is made. After understanding, add the following function in all calls to Db.query:

Defer row. Close ()

This allows the query connection to be closed at the end of the function or in the event of an exception, and no new connections will continue to be created. I think this will solve the problem, but after the server is running, the same error will still occur over time. On the monitoring page in phpMyAdmin, you can see that the number of MySQL connections is soaring after the program runs. The problem becomes more and more solvable, and you can only check the code again in one line.

Functions in Go can have multiple return values, using underscores to ignore unwanted return values:

_, Err: = M.db. Query ("SQL")

SQL statements such as update and Del in the program do not require a return value and are ignored directly. Guess this is also impossible to release the connection, because even if you do not accept the return value, does not mean that the variable does not exist. That is, the SQL returned. Row still exists, but you do not receive it. Did not receive, not to mention the release of the connection, so the final generation of a large number of connections continue to error. Look back at the article and see this paragraph:

Ping and Exec would release the connection right before returning, but the others would pass ownership of the connection to The result, whether that's an SQL. Row, SQL. Rows, or SQL.TX.

This means that the ping and Exec methods will automatically release the connection after the call is complete. Change all the statements in the code that do not need the return value to be executed by the Exce method, go run, OK, the number of connections is finally normal!

The problem is solved, always up to pay attention to something:

• The program connected to the database will have a connection leak situation, need to release the connection in time

• In the Go SQL package, query and Queryrow(@qgymje mentioned in the comments that queryrow by calling the scan method will automatically close the connection) two methods of connection will not automatically release the connection. The connection is closed only if the result is traversed or the Close method is called

• The ping and Exec methods in Go SQL will automatically release the connection after the call is finished

• Ignoring a return value of a function does not mean that the value does not exist, and if the return value requires close to release the resource, ignoring it directly causes a resource leak.

• A variable with a close method, which is invoked immediately after use to release the resource

Go connect MySQL Database error 1040:too many connections bug fix