1

In show processlist I have (from time to time) lot of unauthenticated user.

How it looks like?

| 187743 | unauthenticated user | connecting host    | NULL               | Connect          |  39173 | login                                                         | NULL             |         0 |             0 |
| 187767 | unauthenticated user | connecting host    | NULL               | Connect          |  39098 | login                                                         | NULL             |         0 |             0 |
| 187791 | unauthenticated user | connecting host    | NULL               | Connect          |  39037 | login                                                         | NULL             |         0 |             0 |
| 187815 | unauthenticated user | connecting host    | NULL               | Connect          |  38941 | login                                                         | NULL             |         0 |             0 |
| 187839 | unauthenticated user | connecting host    | NULL               | Killed           |  38885 | login                                                         | NULL             |         0 |             0 |
| 187863 | unauthenticated user | connecting host    | NULL               | Connect          |  38799 | login                                                         | NULL             |         0 |             0 |
| 187887 | unauthenticated user | connecting host    | NULL               | Connect          |  38701 | login                                                         | NULL             |         0 |             0 |
| 187911 | unauthenticated user | connecting host    | NULL               | Connect          |  38583 | login                                                         | NULL             |         0 |             0 |

If I kill a thread (ie. kill 187839) it turns from Connect to Killed but it still hangs.

skip_name_resolve is set to ON so its not a DNS problem.

In further investigation I found some some server variables that can help in this problem like:

connect_timeout - The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds.

wait_timeout - The number of seconds the server waits for activity on a noninteractive connection before closing it.

interactive_timeout - The number of seconds the server waits for activity on an interactive connection before closing it.

But they are set to:

+-------------------------------------+----------+
| Variable_name                       | Value    |
+-------------------------------------+----------+
| connect_timeout                     | 10       |
| interactive_timeout                 | 28800    |
| wait_timeout                        | 28800    |
+-------------------------------------+----------+

As You can see those variables do not affect those processes, they are active lot more than 28800. Are there any other variables then can affect this processes?

If I look at strace of client process (PHP script) it looks like:

connect(8, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("10.3.24.110")}, 16) = -1 EINPROGRESS (Operacja jest właśnie wykonywana)
poll([{fd=8, events=POLLIN|POLLOUT|POLLERR|POLLHUP}], 1, 60000) = 1 ([{fd=8, revents=POLLOUT}])
getsockopt(8, SOL_SOCKET, SO_ERROR, [0], [4]) = 0
fcntl(8, F_SETFL, O_RDWR)               = 0
setsockopt(8, SOL_TCP, TCP_NODELAY, [1], 4) = 0
setsockopt(8, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
poll([{fd=8, events=POLLIN|POLLERR|POLLHUP}], 1, 86400000) = ? ER
poll([{fd=8, events=POLLIN|POLLERR|POLLHUP}], 1, 86400000) = ? ERESTART_RESTARTBLOCK (Interrupted by signal)
--- SIGWINCH {si_signo=SIGWINCH, si_code=SI_KERNEL} ---
restart_syscall(<... resuming interrupted poll ...>) = ? ERESTART_RESTARTBLOCK (Interrupted by signal)
--- SIGWINCH {si_signo=SIGWINCH, si_code=SI_KERNEL} ---
restart_syscall(<... resuming interrupted restart_syscall ...>

and if hangs forever. This behaviour of MySQL can be seen from time to time, not all the time. After MySQL restart problem is "solved" for some time, after few hour/days it returns. Server where I can observe this behaviour is SLAVE (out MASTER is not affected by this issue). Some analitical processes want to connect to this instance and read some data.

In image below You can see how it looks like (MySQL connection and Thread Active): enter image description here enter image description here

2
  • May be I misunderstand what you've said, but if your PHP application "hangs forever", why do you think it's a MySQL problem?
    – mustaccio
    May 7, 2021 at 12:30
  • it can be basically be everysthing, first is your server accesibke from elsewhere besides localhost, if yes disable it, if not check you application, if it tries to connect multiple times without any authentification
    – nbk
    May 7, 2021 at 13:47

1 Answer 1

1

Your program, or some hacker, is swamping the system. "Unauthenticated" means "Login is in progress, but have not finished yet."

Lower those 8-hour numbers.

Check max_connections; dozens or hundreds is OK. Thousands is probably excessive.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.