Comment 5 for bug 893348

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 --engine=innodb --number-of-queries=1000 --user=test2 -ptest2test
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)

[root@nestor ~]# mysql --user=test2 -ptest2test -e 'show processlist'
ERROR 1226 (42000): User 'test2' has exceeded the 'max_user_connections' resource (current value: 100)

and process list by root:
[root@nestor ~]# mysqladmin pr
+-------+-------+-----------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+-------+-------+-----------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
| 14395 | test2 | localhost | mysqlslap | Sleep | 14 | | | 0 | 0 | 2 |
| 14568 | test2 | localhost | mysqlslap | Query | 1 | Sending data | SELECT intcol1,charcol1 FROM t1 | 242 | 0 | 243 |
| 14569 | test2 | localhost | mysqlslap | Query | 1 | Sending data | SELECT intcol1,charcol1 FROM t1 | 242 | 0 | 243 |
| 14570 | test2 | localhost | mysqlslap | Query | 1 | Sending data | SELECT intcol1,charcol1 FROM t1 | 336 | 0 | 337 |
| 14571 | test2 | localhost | mysqlslap | Query | 0 | update | INSERT INTO t1 VALUES (866596855,'naQuzhMt1IrZIJMkbLAKBNNKKK2sCknzI5uHeGAgQuDd5SLgpN0smODyc7qorTo1Qa | 0 | 0 | 1 |
| 14572 | test2 | localhost | mysqlslap | Query | 1 | Sending data | SELECT intcol1,charcol1 FROM t1 | 251 | 0 | 252 |
| 14573 | test2 | localhost | mysqlslap | Query | 1 | Sending data | SELECT intcol1,charcol1 FROM t1 | 288 | 0 | 289 |
| 14615 | root | localhost | | Query | 0 | | show processlist | 0 | 0 | 1 |
+-------+-------+-----------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+

How you can see - it generate 1226 error. There is no '1040 Too many connections' errors anymore.
And only ~10 active connections (max_user_connection set up to 100 by mysql.user)

I am still looking for source of this issue.
I will keep you up2date.

Regards,
Gor Martsen