What reasons can there be for a MySQL server to crash? [closed]

Question

What reasons can there be for a MySQL server to crash? [closed]

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I am working on a project for a company in my country about 5 months ago. In that time, there have been problems with the Mysql Database Engine services. Sometimes the DB is hidden or does not allow access to it. I would like to know your opinions regarding this. Why could the Mysql database engine be dropped? Beforehand thank you very much.

Postdata: One of the common errors that occurs when this happens is the error # 1203 The MYSQL server did not authorize your entry

mysql

asked by Andres Galeano 31.03.2017 в 14:06

source

2 answers

Django urls.py error Crashea program when using scanf on float matrix

score 2 · Answer 1

According to the official documentation ( link ) it could be a problem by the number of users that you have connected to the database (by default 151). What I would do is:

As a containment measure the value of connections would increase
In the meantime, I would review the processes that consume data from that server and I would check that they do not remain active over the account. Usually it happens that there are processes that take more time than usual and, when you have more users than expected, they begin to occupy all the available connections and stop the server.

In any case I would recommend the use of some server monitoring tool to really know the load your server has and if you are really having more traffic than you expect.

If everything is within the expected, you have no choice but to increase connections and monitor server resources (ram, disk ...).

score 1 · Answer 2

As I read error # 1203 occurs because the maximum number of users connected to the database is exceeded.

Error: 1203 SQLSTATE: 42000 (ER_TOO_MANY_USER_CONNECTIONS)

Message: User% s already has more than 'max_user_connections' active connections

In Spanish:

Error: 1203 SQLSTATE: 42000 (ER_TOO_MANY_USER_CONNECTIONS)

Message: User% s already has more active connections than 'max_user_connections'

The databases have two values that limit the connections:

1) max_connections : maximum allowable connections, default 150

2) max_user_connections : maximum allowed users, by default max_connections + 1

By default the maximum connection of mysql is 151 for reasons of efficiency, and most likely is that they are occupying all the maximum connections allowed by the DB, that is why you should find out which elements are occupying all the connections, or otherwise, modify these values to expand capacity.

1st Solution:

Therefore you should go to: MYSQL CONSOLE

Then place these commands to verify what happens:

show variables like "max_connections";

show variables like "max_user_connections";

And then modify the value of the connection with:

set global max_connections = 200;

(The semicolon can cause you or not error)

EYE:

Take into account that the maximum number of connection is based on your physical equipment and depends on the RAM the maximum amount, you can use this formula:

max.connection=(available RAM-global buffers)/thread buffers

2nd Solution:

limit persistent connections with the following script in the "php.ini":

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent=Off

The maximum number of simultaneous connections corresponds to the type of Operating System and RAM available, as well as the PHP and MySQL version.

With this script you can limit persistent connections (Persistent connections are links that do not close at the end of the execution of a script.), which could be saturating the DB.