MySQL – Root Max Connections Per Hour Exceeded

connectivityMySQLusers

I made a mistake and set the max_connections column for root to '1', so now every time I try to connect to change it I get:

ERROR 1226 (42000): User 'root' has exceeded the 'max_connections_per_hour' resource (current value: 1)

It was my personal server, so I don't have any other user.

I've tried:

  • Resetting the server
  • Resetting the mysql process

But nothing works. Does anyone know how to change it?

Best Answer

You have to remove that attribute via MySQL

APPROACH #1

This only works for MySQL 5.7

ALTER USER root@localhost MAX_CONNECTIONS_PER_HOUR 0;

If you have other root users (like root@'127.0.0.1' and root@'%'), you will have to run a separate ALTER USER for each root user.

APPROACH #2

UPDATE mysql.user SET max_connections = 0 WHERE user='root';
FLUSH PRIVILEGES;

Log out and login to MySQL and you should notice the difference

NOTES

If you are logged out, you have to come back in an hour and try again.

If you can't wait one hour and you can restart mysql, you could enable --skip-grant-tables

service mysql restart --skip-grant-tables --skip-networking

Next, you can login and perform APPROACH #2

Then, restart mysql normally

service mysql restart

UPDATE 2016-11-25 14:49 EDT

With Windows, it's a little more involved.

Assuming you installed MySQL in Windows using the MSI Installer, there is no my.ini.

Here is where it gets crazy, but here we go ...

STEP 01

Login to the Windows Command Line as Administrator and run

C:\> net stop mysql

STEP 02

Find the folder where mysqld.exe is located on the Windows.

Usually, it would be something like C:\ProgramData\MySQL\MySQL Server 5.6

Start mysqld and login (no password needed)

cd "C:\ProgramData\MySQL\MySQL Server 5.6"
start mysqld --skip-grant-tables --skip-networking
mysql

STEP 03

Apply APPROACH #2

UPDATE mysql.user SET max_connections = 0 WHERE user='root';
FLUSH PRIVILEGES;
exit

STEP 04

Shutdown mysqld manually

mysqladmin shutdown

STEP 05

Start mysqld as a service

net start mysql

GIVE IT A TRY !!!