max_user_connections issue

Bug #893348 reported by Gor Martsen on 2011-11-21
30
This bug affects 6 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Medium
Unassigned
5.1
Won't Fix
Medium
Unassigned
5.5
Fix Released
Medium
Unassigned

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

Gor Martsen (gor-x) wrote :
Patrick Crews (patrick-crews) 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
assignee: nobody → Patrick Crews (patrick-crews)

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) wrote :

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) wrote :
Download full text (6.7 KiB)

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...

Read more...

Changed in percona-server:
status: New → Triaged
assignee: Patrick Crews (patrick-crews) → nobody
Changed in percona-server:
status: Triaged → Confirmed
Gor Martsen (gor-x) wrote :

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.

yinfeng (yinfeng-zwx) on 2012-01-16
description: updated
Alexey Kopytov (akopytov) wrote :

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) wrote :

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) wrote :

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) wrote :

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

Olivier Doucet (odoucet) wrote :

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) wrote :

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) wrote :

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) wrote :

HI all,

I sent signed OCA.

I hope they will accept this patch soon.

tags: added: i23110
Gor Martsen (gor-x) wrote :

Is there any plans to fix this issue ?

Ivan Zahariev (famzah) wrote :

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.

tags: added: contribution

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.

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

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

Gor Martsen (gor-x) wrote :

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

Alexey Kopytov (akopytov) wrote :

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

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Bug attachments

Remote bug watches

Bug watches keep track of this bug in other bug trackers.