From: paul.dubois Date: February 23 2010 9:36pm Subject: svn commit - mysqldoc@docsrva: r19249 - in trunk: . dynamic-docs/changelog dynamic-docs/command-optvars refman-4.1 refman-5.0 refman-5.1 refman-5.4 refman-5.5 refman-6.0 List-Archive: http://lists.mysql.com/commits/101259 Message-Id: <201002232136.o1NLaeNk000539@docsrva.mysql.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Author: paul Date: 2010-02-23 22:36:39 +0100 (Tue, 23 Feb 2010) New Revision: 19249 Log: r49697@frost: paul | 2010-02-23 15:17:46 -0500 User resource limits general revisions Modified: trunk/dynamic-docs/changelog/mysqld.xml trunk/dynamic-docs/command-optvars/mysqld.xml trunk/refman-4.1/dba-mysqld-server-core.xml trunk/refman-4.1/dba-user-management-core.xml trunk/refman-4.1/sql-syntax-server-administration.xml trunk/refman-5.0/dba-mysqld-server-core.xml trunk/refman-5.0/dba-user-management-core.xml trunk/refman-5.0/sql-syntax-server-administration.xml Property changes on: trunk ___________________________________________________________________ Name: svk:merge - 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/mysqldoc/trunk:35498 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/trunk:36808 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:49696 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39546 + 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/mysqldoc/trunk:35498 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/trunk:36808 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:49697 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39546 Modified: trunk/dynamic-docs/changelog/mysqld.xml =================================================================== --- trunk/dynamic-docs/changelog/mysqld.xml 2010-02-23 21:36:32 UTC (rev 19248) +++ trunk/dynamic-docs/changelog/mysqld.xml 2010-02-23 21:36:39 UTC (rev 19249) Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 598 bytes @@ -124282,7 +124282,7 @@ - DROP DATABASE failed check for + DROP DATABASE failed to check for all referencing InnoDB tables from other databases before dropping any tables. Modified: trunk/dynamic-docs/command-optvars/mysqld.xml =================================================================== --- trunk/dynamic-docs/command-optvars/mysqld.xml 2010-02-23 21:36:32 UTC (rev 19248) +++ trunk/dynamic-docs/command-optvars/mysqld.xml 2010-02-23 21:36:39 UTC (rev 19249) Changed blocks: 1, Lines Added: 2, Lines Deleted: 2; 897 bytes @@ -14583,12 +14583,12 @@ - + - + Modified: trunk/refman-4.1/dba-mysqld-server-core.xml =================================================================== --- trunk/refman-4.1/dba-mysqld-server-core.xml 2010-02-23 21:36:32 UTC (rev 19248) +++ trunk/refman-4.1/dba-mysqld-server-core.xml 2010-02-23 21:36:39 UTC (rev 19249) Changed blocks: 1, Lines Added: 9, Lines Deleted: 0; 752 bytes @@ -5782,6 +5782,15 @@ max_user_connections + + resource limits + user accounts + + + resource limits + user accounts + + max_user_connections Modified: trunk/refman-4.1/dba-user-management-core.xml =================================================================== --- trunk/refman-4.1/dba-user-management-core.xml 2010-02-23 21:36:32 UTC (rev 19248) +++ trunk/refman-4.1/dba-user-management-core.xml 2010-02-23 21:36:39 UTC (rev 19249) Changed blocks: 5, Lines Added: 17, Lines Deleted: 11; 2121 bytes @@ -733,7 +733,15 @@
- Limiting Account Resources + Setting Account Resource Limits + + resource limits + user accounts + + + resource limits + user accounts + MAX_QUERIES_PER_HOUR @@ -747,10 +755,6 @@ MAX_CONNECTIONS_PER_HOUR - - MAX_USER_CONNECTIONS - - Before MySQL 4.0.2, the only available method for limiting use of MySQL server resources is to set the @@ -867,7 +871,7 @@ - This statement leaves the account's existing privileges unchanged + The statement leaves the account's existing privileges unchanged and modifies only the limit values specified. @@ -883,6 +887,13 @@ + The server stores resource limits for an account in the user table row corresponding to the account. The + max_questions, max_updates, +and + max_connections columns store the per-hour limits. + + + Resource-use counting takes place when any account has a nonzero limit placed on its use of any of the resources. @@ -934,11 +945,6 @@ - Counter resets do not affect the - MAX_USER_CONNECTIONS limit. - - - All counts begin at zero when the server starts; counts are not carried over through a restart. Modified: trunk/refman-4.1/sql-syntax-server-administration.xml =================================================================== --- trunk/refman-4.1/sql-syntax-server-administration.xml 2010-02-23 21:36:32 UTC (rev 19248) +++ trunk/refman-4.1/sql-syntax-server-administration.xml 2010-02-23 21:36:39 UTC (rev 19249) Changed blocks: 3, Lines Added: 11, Lines Deleted: 3; 1713 bytes @@ -974,6 +974,14 @@ particular privileges, see . + + resource limits + user accounts + + + resource limits + user accounts + The MAX_QUERIES_PER_HOUR @@ -981,8 +989,8 @@ MAX_UPDATES_PER_HOUR count, and MAX_CONNECTIONS_PER_HOUR - count options are - implemented in MySQL 4.0.2. They limit the number of queries, + count limits were + implemented in MySQL 4.0.2. They restrict the number of queries, updates, and logins a user can perform during one hour. (Queries for which results are served from the query cache do not count against the MAX_QUERIES_PER_HOUR limit.) If @@ -991,7 +999,7 @@ - To specify any of these resource-limit options for an existing + To specify resource limits for an existing user without affecting existing privileges, use GRANT USAGE ON *.* ... WITH MAX_.... Modified: trunk/refman-5.0/dba-mysqld-server-core.xml =================================================================== --- trunk/refman-5.0/dba-mysqld-server-core.xml 2010-02-23 21:36:32 UTC (rev 19248) +++ trunk/refman-5.0/dba-mysqld-server-core.xml 2010-02-23 21:36:39 UTC (rev 19249) Changed blocks: 2, Lines Added: 40, Lines Deleted: 7; 2398 bytes @@ -6528,6 +6528,15 @@ max_user_connections + + resource limits + user accounts + + + resource limits + user accounts + + max_user_connections @@ -6535,17 +6544,41 @@ The maximum number of simultaneous connections allowed to any - given MySQL account. A value of 0 means no + given MySQL account. A value of 0 (the default) means no limit. - Before MySQL 5.0.3, this variable has only global scope. - Beginning with MySQL 5.0.3, it also has a read-only session - scope. The session variable has the same value as the global - variable unless the current account has a nonzero - MAX_USER_CONNECTIONS resource limit. In - that case, the session value reflects the account limit. + Before MySQL 5.0.3, this variable has only a global value. + Beginning with MySQL 5.0.3, this variable also has a (read-only) session + value that reflects the value that applies to the account +associated with the current session. +The session value is initialized as follows: + + + + + +If the user account has a nonzero MAX_USER_CONNECTIONS resource limit, the session + max_user_connections +value is set to that limit. + + + + + +Otherwise, the session + max_user_connections +value is set to the global value. + + + + + +Account resource limits are specified using the GRANT statement. +See +, and +. Modified: trunk/refman-5.0/dba-user-management-core.xml =================================================================== --- trunk/refman-5.0/dba-user-management-core.xml 2010-02-23 21:36:32 UTC (rev 19248) +++ trunk/refman-5.0/dba-user-management-core.xml 2010-02-23 21:36:39 UTC (rev 19249) Changed blocks: 7, Lines Added: 65, Lines Deleted: 49; 8111 bytes @@ -717,7 +717,15 @@
- Limiting Account Resources + Setting Account Resource Limits + + resource limits + user accounts + + + resource limits + user accounts + MAX_QUERIES_PER_HOUR @@ -737,11 +745,14 @@ One means of limiting use of MySQL server resources is to set the +global max_user_connections system - variable to a nonzero value. However, this limits only the number - of simultaneous connections made using a single account, and not - what a client can do once connected. In addition, this method is - strictly global, and does not allow for management of individual + variable to a nonzero value. This limits the number + of simultaneous connections that can be made by any given account, but places no limits on + what a client can do once connected. In addition, setting + max_user_connections globally + does not allow for management of individual + max_user_connections system accounts. Both types of control are of interest to many MySQL administrators, particularly those working for Internet Service Providers. @@ -789,16 +800,32 @@ - Before MySQL 5.0.3, an account in this context is - assessed against the actual host from which a user connects. - Suppose that there is a row in the user table - that has User and Host - values of usera and - %.example.com, to allow - usera to connect from any host in the - example.com domain. If usera - connects simultaneously from host1.example.com - and host2.example.com, the server applies the + An account in this context corresponds to a single + row in the mysql.user table. That is, connections are + assessed against the User and + Host values in the user + table row that applies to the connection. +For example, an account +'usera'@'%.example.com' corresponds to a + row in the user table that has + User and Host values of + usera and %.example.com, to + allow usera to connect from any host in the + example.com domain. In this case, the server + applies resource limits in this row collectively to all connections by + usera from any host in the + example.com domain because all such connections + use the same account. + + + + Before MySQL 5.0.3, an account was assessed against + the actual host from which a user connects. This older method + accounting may be selected by starting the server with the + option. In + this case, if usera connects simultaneously + from host1.example.com and + host2.example.com, the server applies the account resource limits separately to each connection. If usera connects again from host1.example.com, the server applies the @@ -807,35 +834,12 @@ - As of MySQL 5.0.3, an account corresponds to a - single row in the user table. That is, - connections are assessed against the User and - Host value in the user table - row that applies to the connection. In this case, the server - applies resource limits collectively to all connections by - usera from any host in the - example.com domain because all such connections - use the same account. The pre-5.0.3 method of accounting may be - selected by starting the server with the - option. - - - - The server limits account resources based on the resource-related - columns of the user table in the - mysql database: - max_questions, max_updates, - max_connections, and - max_user_connections. If your - user table does not have these columns, it must - be upgraded; see . - - - To set resource limits, use the GRANT statement and provide a WITH clause that names each resource to be - limited. For example, to create a new account that can access the + limited. +The default value for each limit is zero (no limit). +For example, to create a new account that can access the customer database, but only in a limited fashion, issue these statements: @@ -852,18 +856,20 @@ The limit types need not all be named in the WITH clause, but those named can be present in - any order. The value for each per-hour limit should be an integer - representing a count per hour. If the - GRANT statement has no - WITH clause, the limits are each set to the - default value of zero (that is, no limit). For + any order. +The value for each per-hour limit should be an integer + representing a count per hour. +For MAX_USER_CONNECTIONS, the limit is an integer representing the maximum number of simultaneous connections the - account can make at any one time. If the limit is set to the - default value of zero, the + account can make at any one time. If this limit is set to the + zero, the max_user_connections system variable determines the number of simultaneous connections for the account. +If + max_user_connections is also zero, +there is no limit for the account. @@ -880,7 +886,7 @@ - This statement leaves the account's existing privileges unchanged + The statement leaves the account's existing privileges unchanged and modifies only the limit values specified. @@ -896,6 +902,16 @@ + The server stores resource limits for an account in the user table row corresponding to the account. The + max_questions, max_updates, +and + max_connections columns store the per-hour limits, and the + max_user_connections column stores the MAX_USER_CONNECTIONS limit. If your + user table does not have these columns, it must + be upgraded; see . + + + Resource-use counting takes place when any account has a nonzero limit placed on its use of any of the resources. Modified: trunk/refman-5.0/sql-syntax-server-administration.xml =================================================================== Changed blocks: 0, Lines Added: 0, Lines Deleted: 0; 130 bytes