# 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)
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.
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: connections= 25
[mysqld]
max_connections=50
max_user_
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) of-queries= 1000 --user=test1 -ptest1pass --create- schema= test1
mysqlslap --concurrency=10 --iterations=1000 --auto-generate-sql --engine=innodb --number-
# it is help to have 1226 User 'test2' has exceeded the 'max_user_ connections' resource (max_user_ connections field from mysql.user) of-queries= 1000 --user=test2 -ptest2
mysqlslap --concurrency=5 --iterations=1000 --auto-generate-sql --engine=innodb --number-
# this one overloading all limits (max_connections and max_user_ connections) 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
mysqlslap --concurrency=100 --iterations=100 --number-int-cols=2 --number-
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: connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
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 connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100) connections' resource (current value: 100)
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
mysqlslap: Error when connecting to server: 1226 User 'test2' has exceeded the 'max_user_
[root@nestor ~]# mysql --user=test2 -ptest2test -e 'show processlist' connections' resource (current value: 100)
ERROR 1226 (42000): User 'test2' has exceeded the 'max_user_
and process list by root: -+----- --+---- ------- +------ -----+- ------- -+----- -+----- ------- --+---- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- +------ -----+- ------- ------- +------ -----+ -+----- --+---- ------- +------ -----+- ------- -+----- -+----- ------- --+---- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- +------ -----+- ------- ------- +------ -----+ 'naQuzhMt1IrZIJ MkbLAKBNNKKK2sC knzI5uHeGAgQuDd 5SLgpN0smODyc7q orTo1Qa | 0 | 0 | 1 | -+----- --+---- ------- +------ -----+- ------- -+----- -+----- ------- --+---- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- +------ -----+- ------- ------- +------ -----+
[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,
| 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. connection set up to 100 by mysql.user)
And only ~10 active connections (max_user_
I am still looking for source of this issue.
I will keep you up2date.
Regards,
Gor Martsen