Description:
The definition of global_connection_memory_limit and connection_memory_limit is incorrect in the documentation.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
global_connection_memory_limit:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set the total amount of memory that can be used by all user connections; that is, Global_connection_memory should not exceed this amount. Any time that it does, any new queries from users are rejected with ER_GLOBAL_CONN_LIMIT.
Memory used by the system users such as the MySQL root user is included in this total, but is not counted towards the disconnection limit; such users are never disconnected due to memory usage.
Memory used by the InnoDB buffer pool is excluded from the total.
You must have the SYSTEM_VARIABLES_ADMIN or SUPER privilege to set this variable.
connection_memory_limit:
~~~~~~~~~~~~~~~~~~~~~~~~
Set the maximum amount of memory that can be used by a single user connection. If any user connection uses more than this amount, any new queries from this connection are rejected with ER_CONN_LIMIT.
The limit set by this variable does not apply to system users, or to the MySQL root account. Memory used by the InnoDB buffer pool is also not included.
You must have the SYSTEM_VARIABLES_ADMIN or SUPER privilege to set this variable.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The point to be noted here is, "any new queries from users are rejected with respective errors". However, based on my testing, the current running query is also aborted. As you can see in the below repro, there is only one query running in the server. The connection on which the query got aborted due to exceeding limits. As per documentation, the behaviour should be aborting the connection on the *new* queries.
I see the commit uses more appropriate wording here: https://github.com/mysql/mysql-server/commit/dfdd36ceb897dd865c31c522442155b6457bfe4c mentioning "queries that try to allocate
more memory will be aborted"
How to repeat:
REPRO STEPS:
###########
1. Create a MySQL Server with 8.0.32 community binary
2. Connect using system user and execute the following:
set global global_connection_memory_tracking=ON;
set global global_connection_memory_limit=250000000;
drop database test;
create database test;
use test;
CREATE TABLE t1 (f1 LONGTEXT , f2 INTEGER);
INSERT INTO t1 VALUES
(REPEAT('a', 1024), 0), (REPEAT('b', 1024), 1),
(REPEAT('c', 1024), 2), (REPEAT('d', 1024), 3),
(REPEAT('e', 1024), 4), (REPEAT('f', 1024), 5);
INSERT INTO t1 SELECT f1, f2 + 6 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 12 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 24 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 48 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 96 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 192 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 384 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 768 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 1536 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 3072 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 6144 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 12288 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 6 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 12 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 24 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 48 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 96 FROM t1;
CREATE USER 'user1'@'%';
GRANT USAGE ON *.* TO 'user1'@'%';
GRANT RELOAD ON *.* TO 'user1'@'%';
GRANT SELECT,DROP,INSERT ON test.* TO 'user1'@'%';
exit
### Login using the created user `user1` and execute the below query ####
SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM test.t1;
Result:
#######
/home/ec2-user/opt/mysql/8.0.32/bin/mysql -uroot -p -P8032 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set global global_connection_memory_tracking=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> set global global_connection_memory_limit=250000000;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE t1 (f1 LONGTEXT , f2 INTEGER);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t1 VALUES
-> (REPEAT('a', 1024), 0), (REPEAT('b', 1024), 1),
-> (REPEAT('c', 1024), 2), (REPEAT('d', 1024), 3),
-> (REPEAT('e', 1024), 4), (REPEAT('f', 1024), 5);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 6 FROM t1;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 12 FROM t1;
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 24 FROM t1;
Query OK, 24 rows affected (0.01 sec)
Records: 24 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 48 FROM t1;
Query OK, 48 rows affected (0.01 sec)
Records: 48 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 96 FROM t1;
Query OK, 96 rows affected (0.02 sec)
Records: 96 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 192 FROM t1;
Query OK, 192 rows affected (0.02 sec)
Records: 192 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 384 FROM t1;
Query OK, 384 rows affected (0.06 sec)
Records: 384 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 768 FROM t1;
Query OK, 768 rows affected (0.02 sec)
Records: 768 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 1536 FROM t1;
Query OK, 1536 rows affected (0.04 sec)
Records: 1536 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 3072 FROM t1;
Query OK, 3072 rows affected (0.08 sec)
Records: 3072 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 6144 FROM t1;
Query OK, 6144 rows affected (0.17 sec)
Records: 6144 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 12288 FROM t1;
Query OK, 12288 rows affected (0.29 sec)
Records: 12288 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 6 FROM t1;
Query OK, 24576 rows affected (0.68 sec)
Records: 24576 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 12 FROM t1;
Query OK, 49152 rows affected (1.61 sec)
Records: 49152 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 24 FROM t1;
Query OK, 98304 rows affected (7.25 sec)
Records: 98304 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 48 FROM t1;
Query OK, 196608 rows affected (10.16 sec)
Records: 196608 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 96 FROM t1;
Query OK, 393216 rows affected (20.63 sec)
Records: 393216 Duplicates: 0 Warnings: 0
mysql> CREATE USER 'user1'@'%';
Query OK, 0 rows affected (0.05 sec)
mysql> GRANT USAGE ON *.* TO 'user1'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT RELOAD ON *.* TO 'user1'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT,DROP,INSERT ON test.* TO 'user1'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
## Test with the created user
/home/ec2-user/opt/mysql/8.0.32/bin/mysql -uuser1 -h127.0.0.1 -P8032 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM test.t1;
ERROR 4081 (HY000): Connection closed. Global connection memory limit 250000000 bytes exceeded. Consumed 291538256 bytes.
mysql> \s
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 20
Current database: test
--------------
/home/ec2-user/opt/mysql/8.0.32/bin/mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 20
Current database: test
Current user: user1@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.32 MySQL Community Server - GPL
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 8032
Binary data as: Hexadecimal
SSL session reused: true
Uptime: 18 min 38 sec
Threads: 2 Questions: 121 Slow queries: 2 Opens: 227 Flush tables: 3 Open tables: 143 Queries per second avg: 0.108
--------------
Suggested fix:
The documentation needs to be updated with more appropriate wording.
