{"id":352,"date":"2011-03-05T01:09:05","date_gmt":"2011-03-05T01:09:05","guid":{"rendered":"http:\/\/10sa.com\/sql_stories\/?p=352"},"modified":"2011-03-05T01:10:04","modified_gmt":"2011-03-05T01:10:04","slug":"max_connections_per_hour-mysql-user-max_connections-consternation","status":"publish","type":"post","link":"http:\/\/10sa.com\/sql_stories\/?p=352","title":{"rendered":"MAX_CONNECTIONS_PER_HOUR | mysql.user max_connections, Consternation."},"content":{"rendered":"<p>Option (and column) max_connections in the user grants stored in table user is used to represent the maximum of user connections per hour (conforming to the <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/user-resources.html\">documentation<\/a>).<br \/>\nIn order to fulfill the data in this column I use SQL syntax:<\/p>\n<pre lang=SQL>\r\nmysql&gt; GRANT USAGE ON *.* TO 'test'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 5;\r\nQuery OK, 0 rows affected (0.01 sec);\r\nmysql> flush user_resources;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n<\/pre>\n<p>Next, I open session with command line:<\/p>\n<pre lang=bash>\r\n# Session 1\r\nmysql -utest -p;\r\nEnter password:\r\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\r\nYour MySQL connection id is 1951229\r\nServer version: 5.1.43-log Source distribution\r\n<\/pre>\n<p>Then, I execute php script wich opens 10 sessions at once:<\/p>\n<pre lang=\"php\">\r\n<?php\r\nfor ( $i=(integer)0; $i < 10; $i++ ):\r\n    mysql_connect($host,$user,$pass, true \/* new_link *\/);\r\n    print mysql_error() . $i;\r\nendfor;\r\n<\/pre>\n<p>As a result I see 0123456789. Successfuly I opened 10 connections to MySQL.<br \/>\nI reexecute my php script. Failure, each time I receive an error:<br \/>\n<span style=\"color: #cd3030\">User 'test' has exceeded the 'max_connections_per_hour' resource (current value: 5)<\/span>;<br \/>\nI go to my Session 1 and execute a query:<\/p>\n<pre lang=SQL>\r\nmysql> select 1 from dual;\r\n+---+\r\n| 1 |\r\n+---+\r\n| 1 |\r\n+---+\r\n1 row in set (0.00 sec)\r\n<\/pre>\n<p>I execute my php script and... all connections are successful! That means that any SELECT made by the user who was limited (obviously logged before limit)  works like FLUSH RESOURCES;<br \/>\nTested on WINDOWS 5.1.51-community and CENTOS 5.1.43-log.<\/p>\n<p>My interpretation of MAX_CONNECTIONS_PER_HOUR (mysql.user max_connections) is as follows:<br \/>\nBlock access for a hour to the user who executed more connections at once than MAX_CONNECTIONS_PER_HOUR. The question is, it is \"The number of times an account can connect to the server per hour\"?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Option (and column) max_connections in the user grants stored in table user is used to represent the maximum of user connections per hour (conforming to the documentation). In order to fulfill the data in this column I use SQL syntax: mysql&gt; GRANT USAGE ON *.* TO &#8216;test&#8217;@&#8217;localhost&#8217; WITH MAX_CONNECTIONS_PER_HOUR 5; Query OK, 0 rows affected [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,3],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/352"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=352"}],"version-history":[{"count":17,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/352\/revisions"}],"predecessor-version":[{"id":370,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/352\/revisions\/370"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=352"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}