max_user_connections issue

[Gor Martsen]

Hello

We are using max_user_connections=30 to prevent too many connections by user mistake.
Three weeks ago we upgraded 6 servers to Percona-Server-5.5 from 5.1.

And right after upgrade we noticed a problem with max_user_connections on all upgraded servers.

Users that connects to database very often, started to experience problem:
User ***** already has more than 'max_user_connections' active connections
But "SHOW PROCESSLIST" did not list any active connections for this user!

How you know, 5.5 has max_user_connections in mysql.user table.
We put max_user_connections=30 into this table, instead of global variable and nothing changes.

Known symptoms:
- Problem does not come up after mysql restart. It takes some time.
- "flush privileges" helps to reset counter, but after few hours issue is active again.

It fills like max_user_connections counter for specific (very active) users doesn't reset for some bug reason.

Server hardware:
32GB RAM. 4x4 core CPUs, SAS HDDS in RAID1 for /var/lib/mysql.
OS: centos5 x86_64

I am ready to provide any information that will help to find and resolve this issue.

Thank you.

Regards,
Gor

[Patrick Crews (patrick-crews)]

Hi. I'll be taking a look at this bug soon and will be in touch if I have issues duplicating the problem.

Thanks for the bug report,
Patrick

[Gor Martsen (gor-x)]

Hi Patrick

We are working on it too.
I will send you more details when be back home.

Regards,
Gor

Sent from my iPhone

On 2011-12-28, at 9:55 PM, Patrick Crews <email address hidden> wrote:

> Hi. I'll be taking a look at this bug soon and will be in touch if I
> have issues duplicating the problem.
>
> Thanks for the bug report,
> Patrick
>
> ** Changed in: percona-server
> Importance: Undecided => Medium
>
> ** Changed in: percona-server
> Assignee: (unassigned) => Patrick Crews (patrick-crews)
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/893348
>
> Title:
> max_user_connections issue
>
> Status in Percona Server with XtraDB:
> New
>
> Bug description:
> Hello
>
> We are using max_user_connections=30 to prevent too many connections by user mistake.
> Three weeks ago we upgraded 6 servers to Percona-Server-5.5 from 5.1.
>
> And right after upgrade we noticed a problem with max_user_connections
> on all upgraded servers.
>
> Users that connects to database very often, started to experience problem:
> User ***** already has more than 'max_user_connections' active connections
> But "SHOW PROCESSLIST" did not list any active connections for this user!
>
> How you know, 5.5 has max_user_connections in mysql.user table.
> We put max_user_connections=30 into this table, instead of global variable and nothing changes.
>
> Known symptoms:
> - Problem does not come up after mysql restart. It takes some time.
> - "flush privileges" helps to reset counter, but after few hours issue is active again.
>
> It fills like max_user_connections counter for specific (very active)
> users doesn't reset for some bug reason.
>
> Server hardware:
> 32GB RAM. 4x4 core CPUs, SAS HDDS in RAID1 for /var/lib/mysql.
> OS: centos5 x86_64
>
> I am ready to provide any information that will help to find and
> resolve this issue.
>
> Thank you.
>
> Regards,
> Gor
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-server/+bug/893348/+subscriptions

[Gor Martsen (gor-x)]

Hello Patrick.

Few more details about this bug.

On our servers we have [Warning] Too many connections.

Probably server does not decrease user connections when it happens.
But it is just a guess.

Let me know if you cannot repeat this bug, I will try to build it up for you.

[Gor Martsen (gor-x)]

Hello Patrick.

Right now I found a way to reproduce this bug.

You need two users with 3 grands.

test1 to database test1
test2 to databases test2 and mysqlslap

my.cnf settings:
[mysqld]
max_connections=50
max_user_connections=25
log-warnings=10

Changes to mysql.user table:

set max_user_connections for user test2 to 100.

Now you have to start 3 processes:

# it is help to have Too many connections error (max_connections field)
mysqlslap --concurrency=10 --iterations=1000 --auto-generate-sql --engine=innodb --number-of-queries=1000 --user=test1 -ptest1pass --create-schema=test1

# it is help to have 1226 User 'test2' has exceeded the 'max_user_connections' resource (max_user_connections field from mysql.user)
mysqlslap --concurrency=5 --iterations=1000 --auto-generate-sql --engine=innodb --number-of-queries=1000 --user=test2 -ptest2

# this one overloading all limits (max_connections and max_user_connections)
mysqlslap --concurrency=100 --iterations=100 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=100 --user=test2 -ptest2pass --create-schema=test2

It takes time, while bug will show itself.

You can speed it up by:
mysql> set global max_connections =20;

And right away you will see:
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)

Thats it.
Now you can interrupt all mysqlslaps and start new one: (don't restart mysql)

[root@nestor ~]# mysqlslap --concurrency=10 --iterations=1000 --auto-generate-sql --engi...

[Gor Martsen (gor-x)]

We resolved this issue.

In function acl_authenticate after check_for_max_user_connections (that increase uc->connections) it checks max_connections and if limit reached, return from function.

We need to decrease uc->connections before return.

Patch tested, it works how it expected.

Please confirm.

[Alexey Kopytov (akopytov)]

The problem analysis and the patch look correct to me. Let's fix it in 5.1.61 and 5.5.21? Whoever merges the patch should also report the bug upstream.

[Olivier Doucet (odoucet)]

I think I'm experiencing the same symptoms on a very large SQL server.
I was hitting a max_user_connections at 4000, when there are only ~ 400 new connections / second.

Server version is Percona 5.5.17-22.1

My problem is documented in internal Percona support as issue #23110

[Gor Martsen (gor-x)]

Try to use our patch. It should help.
We use it since January, no problem anymore.

I don't know why this simple patch is not applied yet.

[Olivier Doucet (odoucet)]

Have you opened a bug in MySQL bugtracker ? I'm sure this big is not limited to Percona version.

[Olivier Doucet (odoucet)]

I opened bug #65104 at http://bugs.mysql.com/bug.php?id=65104 ; I hope I have well explained this. Feel free to add details if you think it is needed.

[Gor Martsen (gor-x)]

You are right, it affects MySQL too.

I've tried but received strange message that they will contact me.
Probably I did something wrong.

Thank you. Will track it too.

Best Regards,
Gor

[Olivier Doucet (odoucet)]

Oracle contacted me on April 30th and said they were waiting for an Oracle Contributor Agreement signed from you Gor, to get your fix included directly in MySQL.

[Gor Martsen (gor-x)]

HI all,

I sent signed OCA.

I hope they will accept this patch soon.

[Gor Martsen (gor-x)]

Is there any plans to fix this issue ?

[Ivan Zahariev (famzah)]

If Oracle are not cooperating, please at least fix this in the Percona release. It's been 7 months now since this is a known bug.

[Raghavendra D Prabhu (raghavendra-prabhu)]

The upstream bug seems to have been fixed already in 5.5.27 release.

From http://dev.mysql.com/doc/refman/5.5/en/news-5-5-27.html

" If an account had a nonzero MAX_USER_CONNECTIONS value, that value was not always respected. (Bug #65104, Bug #14003080)"

So, it will be in upcoming PS 5.5.27 release as well.

[Raghavendra D Prabhu (raghavendra-prabhu)]

http://sprunge.us/KKCg?c is the commit message.

@Gor, they seem to have slightly changed your patch.

[Gor Martsen (gor-x)]

Finally!
Thank you for link. Looks like the added more code there.

[Alexey Kopytov (akopytov)]

Fixed upstream in the (later recalled) 5.5.26 release. Setting to Fix Released for Percona Server 5.5.27-28.0.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1227