Merge lp:~vlad-lesin/percona-server/sql_timeout into lp:percona-server/5.5

Proposed by Vlad Lesin on 2012-01-23
Status: Rejected
Rejected by: Alexey Kopytov on 2013-01-21
Proposed branch: lp:~vlad-lesin/percona-server/sql_timeout
Merge into: lp:percona-server/5.5
Diff against target: 1169 lines (+910/-4)
16 files modified
Percona-Server/mysql-test/r/mysqld--help-notwin.result (+2/-0)
Percona-Server/mysql-test/r/percona_server_variables_debug.result (+1/-0)
Percona-Server/mysql-test/r/percona_server_variables_release.result (+1/-0)
Percona-Server/mysql-test/r/percona_sql_timeout.result (+120/-0)
Percona-Server/mysql-test/suite/sys_vars/r/sql_timeout_basic.result (+164/-0)
Percona-Server/mysql-test/suite/sys_vars/t/sql_timeout_basic.test (+210/-0)
Percona-Server/mysql-test/t/percona_sql_timeout.test (+44/-0)
Percona-Server/sql/CMakeLists.txt (+1/-1)
Percona-Server/sql/mysqld.cc (+59/-2)
Percona-Server/sql/mysqld.h (+5/-1)
Percona-Server/sql/sql_class.cc (+5/-0)
Percona-Server/sql/sql_class.h (+6/-0)
Percona-Server/sql/sql_parse.cc (+3/-0)
Percona-Server/sql/sql_timeout.cc (+228/-0)
Percona-Server/sql/sql_timeout.h (+55/-0)
Percona-Server/sql/sys_vars.cc (+6/-0)
To merge this branch: bzr merge lp:~vlad-lesin/percona-server/sql_timeout
Reviewer Review Type Date Requested Status
Laurynas Biveinis (community) Needs Fixing on 2012-09-07
Vlad Lesin (community) Resubmit on 2012-02-17
Alexey Kopytov (community) 2012-01-23 Needs Fixing on 2012-02-11
Vadim Tkachenko Needs Fixing on 2012-01-23
Review via email: mp+89693@code.launchpad.net

Description of the change

This is draft implementation of https://blueprints.launchpad.net/percona-server/+spec/sql-timeout without tests.

  The QUERY_TIMEOUT keyword is used to point maximum query execution time. The example of using:

  SELECT SLEEP(20) /*! QUERY_TIMEOUT = 10 */;

  This keyword may be used in every statement except "begin".
  The keyword has a higher priority then SQL_TIMEOUT session variable. For example:

  SET SESSION SQL_TIMEOUT = 100;
  SELECT SLEEP(20) /*! QUERY_TIMEOUT = 10 */;

  The second query will be killed in 10 seconds.

  I haven't yet found the way of using the same name for keyword and session variable.

  The smoke tests may be done such way:
  NUM_PROCESSES=50; for i in `seq 1 $NUM_PROCESSES`; do TIMEOUT=$(( $RANDOM % 20 )); (mysql -uroot test -e "select sleep(20) query_timeout=$TIMEOUT" &); (mysql -uroot test -e "select sleep(20)" &); done

To post a comment you must log in.
Vadim Tkachenko (vadim-tk) wrote :

The name of option should be the same in all case.

If it is:
SET SESSION SQL_TIMEOUT = 100;

Then it should be:
  SELECT SLEEP(20) /*! SQL_TIMEOUT = 10 */;

review: Needs Fixing
Vlad Lesin (vlad-lesin) wrote :

> The name of option should be the same in all case.
>
> If it is:
> SET SESSION SQL_TIMEOUT = 100;
>
> Then it should be:
> SELECT SLEEP(20) /*! SQL_TIMEOUT = 10 */;

Ok. Done.

But when you use this variant of "set" statement you have to wrap the variable name with `` (SET SESSION `SQL_TIMEOUT` = 10) because this name coincides with the keyword. It's the common rule when a variable name coincides with a keyword.

Is it acceptable?

review: Needs Information
Vadim Tkachenko (vadim-tk) wrote :

Is it keyword because we use it in comment ?

On Tue, Jan 24, 2012 at 10:33 AM, Vladislav Lesin <email address hidden> wrote:
> Review: Needs Information
>
>> The name of option should be the same in all case.
>>
>> If it is:
>> SET SESSION SQL_TIMEOUT = 100;
>>
>> Then it should be:
>>   SELECT SLEEP(20) /*! SQL_TIMEOUT = 10 */;
>
> Ok. Done.
>
> But when you use this variant of "set" statement you have to wrap the variable name with `` (SET SESSION `SQL_TIMEOUT` = 10) because this name coincides with the keyword. It's the common rule when a variable name coincides with a keyword.
>
> Is it acceptable?
> --
> https://code.launchpad.net/~vlad-lesin/percona-server/sql_timeout/+merge/89693
> Your team Percona developers is subscribed to branch lp:percona-server.

--
Vadim Tkachenko, CTO, Percona Inc.
Phone +1-925-400-7377,  Skype: vadimtk153
Schedule meeting: http://tungle.me/VadimTkachenko

Join us at Percona Live: MySQL Conference And Expo 2012
http://www.percona.com/live/mysql-conference-2012/

Alexey Kopytov (akopytov) wrote :

On 24.01.12 22:37, Vadim Tkachenko wrote:
> Is it keyword because we use it in comment ?
>

Yes, /*! */ are executable comments. As in, it must be valid SQL code
inside, not just an arbitrary string.

Vadim Tkachenko (vadim-tk) wrote :

Ok, then it is fine.

On Tue, Jan 24, 2012 at 10:43 AM, Alexey Kopytov
<email address hidden> wrote:
> On 24.01.12 22:37, Vadim Tkachenko wrote:
>> Is it keyword because we use it in comment ?
>>
>
> Yes, /*! */ are executable comments. As in, it must be valid SQL code
> inside, not just an arbitrary string.
>
> --
> https://code.launchpad.net/~vlad-lesin/percona-server/sql_timeout/+merge/89693
> Your team Percona developers is subscribed to branch lp:percona-server.

--
Vadim Tkachenko, CTO, Percona Inc.
Phone +1-925-400-7377,  Skype: vadimtk153
Schedule meeting: http://tungle.me/VadimTkachenko

Join us at Percona Live: MySQL Conference And Expo 2012
http://www.percona.com/live/mysql-conference-2012/

Vlad Lesin (vlad-lesin) wrote :

Vadim proposed me to use kewpie for testing. I tried to work with it. But a lot of tests fail on the current branch of Percona-Server. May be there are some special ways to run this tool to have successful tests passing. I din't find them in google and documentation. I asked Alexey Kopytov about this and he answered that the integration with Percona-Server had not yet completed and he didn't recommend me to use this tool to avoid troubles with implementation and code review.

The question is should I use the kewpie or old good mysqltest may be used? If kewpie is the preferable solution whom I can contanct to clear some questions up?

review: Needs Information
Patrick Crews (patrick-crews) wrote :

I would recommend using mysqltest / MTR if you can express tests in the provided language.
If you have difficulty in testing your changes via what MTR provides, then one can use kewpie.

Out of curiosity - how were you trying to run the software? Could you share command lines / failure output? It is still a work in progress, but should be suitable for your needs here (provided MTR is unsuitable)

Vlad Lesin (vlad-lesin) wrote :

> Out of curiosity - how were you trying to run the software? Could you share
> command lines / failure output? It is still a work in progress, but should be
> suitable for your needs here (provided MTR is unsuitable)

Sorry, I used wrong command line agruments for building percona-server. That's why a lot of tests didn't work. It's ok now. I successfully used kewpie for testing this feature. Thank's.

Alexey Kopytov (akopytov) wrote :

Hi Vladislav,

On 23.01.12 18:43, Vladislav Lesin wrote:
> Vladislav Lesin has proposed merging lp:~vlad-lesin/percona-server/sql_timeout into lp:percona-server.
>
> Requested reviews:
> Alexey Kopytov (akopytov)
>
> For more details, see:
> https://code.launchpad.net/~vlad-lesin/percona-server/sql_timeout/+merge/89693
>
> This is draft implementation of https://blueprints.launchpad.net/percona-server/+spec/sql-timeout without tests.
>

The current implementation is better than your original design proposal,
but is still worse than what I have suggested (which is now described in
the blueprint).

There's still a lot of unnecessary work. Suppose you have 100 threads
started about the same time with a 600 seconds timeout. What the timeout
monitor thread will be doing with the current implementation is wake up
every 0.25 sec, walk through all 100 threads while holding the
LOCK_timeout mutex (so, for example, no new connections can be created
during this time). Around 4 * 600 * 100 loop iterations before it has to
do any real work. And then when it has to kill a thread, it will
possibly wait in LOCK_thd_data (while still holding LOCK_timeout, and
thus possibly blocking new connections).

Why not implement it as I written in the worklog, i.e. as a queue?

Other comments and questions in no particular order:

- why is sql_timeout a session-only variable? I don't see any reasons
for that.

- please implement the per-query syntax as a SELECT option.

- I don't see any comments except those in the copy-pasted code

- calling add_to_sql_timeout_monitor() from the parser is not a good
idea for a number of reasons. The most important one is that timeouts
will not work for prepared statements, and work not as one would expect
for stored routines. Besides, don't you call it twice in case of
per-query syntax?

- please don't use SLEEP() in cases. It always means trouble, because on
slow/overloaded test machines such tests behave unpredictably. For
example, on a slow/overloaded machine, SELECT SLEEP(3) may actually take
4 or more seconds, so the test case from your patch would fail.

I think we can simplify tests to make them reliable. For example, use
SLEEP(1000) and just verified it's interrupted (i.e. returns 1) when a
timeout is set.

- please remove the copy-pasted code related to Dec and IA64 from
sql_timeout_thread_func(), you don't really need that

- many lines with trailing whitespace, try for example:

grep '^+.* \+$' sql_timeout.patch

- please always put opening braces on separate lines, there's a few
cases where this rule is violated

Alexey Kopytov (akopytov) :
review: Needs Fixing
Vlad Lesin (vlad-lesin) wrote :

Everything is done except making SQL_TIMEOUT not only session but global variable too. Because blueprint describes it as a session variable. It's not an issue to do this but in this case we should change the blueprint too.

Each item in the queue has a pointer to the thread object to have
ability to invoke "kill query" function for that thread. Every time we use sql_timeout we push that pointer and expiration time to the queue. And after the query is executed we remove the item from the queue. The "remove" operation searches the queue item with certain thread pointer using hash table.

review: Resubmit
Vadim Tkachenko (vadim-tk) wrote :

Vlad,

We need to have GLOBAL variable also, that's my bad, I did not think
about it initially.
I updated blueprint.

On Tue, Feb 7, 2012 at 4:10 AM, Vladislav Lesin <email address hidden> wrote:
> Review: Resubmit
>
> Everything is done except making SQL_TIMEOUT not only session but global variable too. Because blueprint describes it as a session variable. It's not an issue to do this but in this case we should change the blueprint too.
>
> Each item in the queue has a pointer to the thread object to have
> ability to invoke "kill query" function for that thread. Every time we use sql_timeout we push that pointer and expiration time to the queue. And after the query is executed we remove the item from the queue. The "remove" operation searches the queue item with certain thread pointer using hash table.
> --
> https://code.launchpad.net/~vlad-lesin/percona-server/sql_timeout/+merge/89693
> You are reviewing the proposed merge of lp:~vlad-lesin/percona-server/sql_timeout into lp:percona-server.

--
Vadim Tkachenko, CTO, Percona Inc.
Phone +1-925-400-7377,  Skype: vadimtk153
Schedule meeting: http://tungle.me/VadimTkachenko

Join us at Percona Live: MySQL Conference And Expo 2012
http://www.percona.com/live/mysql-conference-2012/

Vlad Lesin (vlad-lesin) wrote :

> Vlad,
>
> We need to have GLOBAL variable also, that's my bad, I did not think
> about it initially.
> I updated blueprint.

Ok, done.

Alexey Kopytov (akopytov) wrote :

Vladislav,

I have reviewed the new version of patch and have a number of comments and questions:

   - why a thread is only added to the monitor if it's a SELECT query:

> + sql_timeout_monitor.add(thd);
> res= execute_sqlcom_select(thd, all_tables);
> + sql_timeout_monitor.remove(thd);

   What happens with non-SELECT statements when we have a global or a session timeout value?

   - please merge from trunk, there's a conflict in 'series'
   - sql_timeout_hand is defined but never used
   - init_thread_environment() seems to be a wrong place to initialize
     the monitor state. Why not do it in the sql_timeout_thread_func()?
   - clean_up_mutexes() seems to be a wrong place to destroy the monitor
     state. Why not do it in sql_timeout_thread_func()?
   - please add my_thread_init() to sql_timeout_thread_func()
   - http://jenkins.percona.com/view/Percona%20Server%205.5/job/percona-server-5.5-param/277/#showFailuresLink
     - please adjust main.mysqld--help-win, main.mysqld--help-win,
       main.percona_server_variables_release and
       main.percona_server_variables_debug
     - there's a compiler warning on 32-bit systems in sys_vars.cc
   - in sys_vars.cc the variable is declared as Sys_var_ulong, but the
     max value is ULONGLONG_MAX (which is the reason for the warning)
   - I don't like that we allocate/free the Sql_timeout_info structure in
     add()/remove(). What about adding a variable of that type to THD
     and then making add()/remove() use that variable instead?
   - I think it's a huge overkill to modify queues API in the server just to
     be able to use hash when removing. Just walking the queue to find
     the element to remove should be sufficient. It's a relatively rare
     event, right?
   - I wonder what's the reasoning behind making
     was_added_to_sql_timeout_monitor in THD and sql_timeout in LEX
     volatile
   - please rename was_added_to_sql_timeout_monitor to something
     shorter. 'has_sql_timeout' sounds descriptive enough to me.
   - there's a number of cases when assignments have a space before '='
   - please wrap the statements in QUEUE_ITEM_INDEX_CHANGED() macro into
     "do {...} while(0)". please also explain why it is needed :)
   - there's a number of cases when variable assignments do NOT have a
     space after '='
   - please don't use comma-separated assignments
   - add a space after a typecast
   - please use 'static inline' rather than just 'inline' in sql_timeout.cc
   - you don't have to use my_sleep() in ::exit(), if you do all
     destruction/deallocation in the thread itself. you just tell the
     montor thread to shutdown, and it will handle everything else
   - I still see a few cases when opening braces are not on a separate
     line
   - some lines are wider than 80 characters
   - please put declarations at the start of a block. it makes reading
     the code and finding variable types much easier, especially when
     declarations are separated from the code with a blank line
   - do you really need to separate tests sql_timeout_basic_32 and
     sql_timeout_basic_64?

review: Needs Fixing
Alexey Kopytov (akopytov) wrote :

Vladislav,

Please also remove support for per-query syntax. As we discussed previously, we will implement them in a different way later.

Vlad Lesin (vlad-lesin) wrote :

Done.

review: Resubmit
Vlad Lesin (vlad-lesin) wrote :

Jenkins build:

http://jenkins.percona.com/view/Percona%20Server%205.5/job/percona-server-5.5-param/318/

All remarks of Alexey Kopytov were taken into account.

Vlad Lesin (vlad-lesin) wrote :
Download full text (3.6 KiB)

   The blueprint has to be expanded with more details,
   i.e. parts of "Description of the Change" in the MP have to be
   moved there. Likewise, there is some discussion in bug 671227,
   which should be accounted for in the blueprint, (i.e. we decided to
   do this and not that etc).

   If there is no query option variable support, then this part should
   be moved to another blueprint, probably set as dependant on this
   one.

   Line 363: for reference can put the blueprint link.
   Line 368: unless I'm missing something, there is no need to include
   load_sysvars.inc. That file is for platform-specific variables and
   values, SQL_TIMEOUT is not such.
   Lines 399--410: I don't understand what does the
   "FN_DYNVARS_083_02" section do what the previous one doesn't.
   Lines 449--450, 460--461 seem redundant.

   Lines 462--463: why it's only a warning to set a negative time out
   value which then gets "truncated" to zero? IMHO it makes more sense
   to make it error instead, if that's possible of course.

   I don't like the SET @@global.sql_timeout = TRUE; SELECT
   @@global.sql_timeout test because of actual SQL timeout possibility
   on loaded Jenkins hosts. Since it's not sane (even if legal) to
   assign TRUE timeout, I'd leave the test only for FALSE.

   I am not sure if the test is stable on busy servers. Some of the
   connection threads might get starved and return results at
   unexpected times, breaking the test. I'd test the basic feature
   with a single connection test, and if there's need to have
   concurrent testing, I'd do it separately with the query log
   disabled (basically just to test that the server does not crash).
   Also, the timed-out statements produce any warnings? If yes, maybe
   it's possible to record only thesm with the query log disabled.
   Then the result file part for them would be a sequence of warnings,
   independent of their actual order.

   No need for extra empty line at 774.

   The sql_timeout_expire can be set in THD::THD initializer list.

   What about prepared statements? Is timeout honored for them? What
   about setting it inside of a PS? Perhaps makes sense to add test
   cases for it?

   Whitespace issue in lines 878--880. Also can just use return (a -
   b);

   Line 901 s/properly/proper

   For defensive programming I'd protect the queue_init_ex with
   LOCK_queue too.

   Please use C++ static_cast instead of C-style cast in
   Sql_timeout_monitor::deinit(). Perhaps elsewhere too, if I missed
   any C casts where C++ casts would make more sense.

   In the monitor function, I'd get rid of now variable and always
   invoke my_micro_time(). The reason is that THD data lock, kill,
   unlock might take a long time for busy queries or buggy code paths
   that do not check kill state very often.

   For the reason we need to release LOCK_queue around that code and
   move queue_remove to be the 1st statement in the loop so that
   queue_top and queue_remove happen atomically.

   Space after (%s) in line 993.

   In the add method, the LOCK_queue can be held for shorter time:
   thd->sql_timeout_expire and sql_print_error calls should happen
   outside it. Or, I'd set t...

Read more...

review: Needs Fixing
Alexey Kopytov (akopytov) wrote :

Closing, as sql_timeout will only be implemented in 5.6.

Unmerged revisions

237. By Vlad Lesin on 2012-04-26

Get SQL_TIMEOUT=X variable to make queries automatically be killed
when they are taking too long time.
Example:
SET SQL_TIMEOUT=10;
SELECT SLEEP(20);

The query will be killed in 10 seconds.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'Percona-Server/mysql-test/r/mysqld--help-notwin.result'
2--- Percona-Server/mysql-test/r/mysqld--help-notwin.result 2012-04-19 16:51:34 +0000
3+++ Percona-Server/mysql-test/r/mysqld--help-notwin.result 2012-04-26 09:57:20 +0000
4@@ -724,6 +724,7 @@
5 replication.
6 --sql-mode=name Syntax: sql-mode=mode[,mode[,mode...]]. See the manual
7 for the complete list of valid sql modes
8+ --sql-timeout=# Timeout in seconds to wait before killing query.
9 --stored-program-cache=#
10 The soft upper limit for number of cached stored routines
11 for one connection.
12@@ -1013,6 +1014,7 @@
13 sort-buffer-size 2097152
14 sporadic-binlog-dump-fail FALSE
15 sql-mode
16+sql-timeout 0
17 stored-program-cache 256
18 symbolic-links FALSE
19 sync-binlog 0
20
21=== modified file 'Percona-Server/mysql-test/r/percona_server_variables_debug.result'
22--- Percona-Server/mysql-test/r/percona_server_variables_debug.result 2012-04-18 23:26:11 +0000
23+++ Percona-Server/mysql-test/r/percona_server_variables_debug.result 2012-04-26 09:57:20 +0000
24@@ -344,6 +344,7 @@
25 SQL_SAFE_UPDATES
26 SQL_SELECT_LIMIT
27 SQL_SLAVE_SKIP_COUNTER
28+SQL_TIMEOUT
29 SQL_WARNINGS
30 SSL_CA
31 SSL_CAPATH
32
33=== modified file 'Percona-Server/mysql-test/r/percona_server_variables_release.result'
34--- Percona-Server/mysql-test/r/percona_server_variables_release.result 2012-04-18 23:26:11 +0000
35+++ Percona-Server/mysql-test/r/percona_server_variables_release.result 2012-04-26 09:57:20 +0000
36@@ -338,6 +338,7 @@
37 SQL_SAFE_UPDATES
38 SQL_SELECT_LIMIT
39 SQL_SLAVE_SKIP_COUNTER
40+SQL_TIMEOUT
41 SQL_WARNINGS
42 SSL_CA
43 SSL_CAPATH
44
45=== added file 'Percona-Server/mysql-test/r/percona_sql_timeout.result'
46--- Percona-Server/mysql-test/r/percona_sql_timeout.result 1970-01-01 00:00:00 +0000
47+++ Percona-Server/mysql-test/r/percona_sql_timeout.result 2012-04-26 09:57:20 +0000
48@@ -0,0 +1,120 @@
49+SLEEP(1)
50+0
51+SLEEP(1000)
52+1
53+SLEEP(2)
54+0
55+SLEEP(1)
56+0
57+SLEEP(1000)
58+1
59+SLEEP(2)
60+0
61+SLEEP(1)
62+0
63+SLEEP(1000)
64+1
65+SLEEP(2)
66+0
67+SLEEP(1)
68+0
69+SLEEP(1000)
70+1
71+SLEEP(2)
72+0
73+SLEEP(1)
74+0
75+SLEEP(1000)
76+1
77+SLEEP(2)
78+0
79+SLEEP(1)
80+0
81+SLEEP(1000)
82+1
83+SLEEP(2)
84+0
85+SLEEP(1)
86+0
87+SLEEP(1000)
88+1
89+SLEEP(2)
90+0
91+SLEEP(1)
92+0
93+SLEEP(1000)
94+1
95+SLEEP(2)
96+0
97+SLEEP(1)
98+0
99+SLEEP(1000)
100+1
101+SLEEP(2)
102+0
103+SLEEP(1)
104+0
105+SLEEP(1000)
106+1
107+SLEEP(2)
108+0
109+SLEEP(1)
110+0
111+SLEEP(1000)
112+1
113+SLEEP(2)
114+0
115+SLEEP(1)
116+0
117+SLEEP(1000)
118+1
119+SLEEP(2)
120+0
121+SLEEP(1)
122+0
123+SLEEP(1000)
124+1
125+SLEEP(2)
126+0
127+SLEEP(1)
128+0
129+SLEEP(1000)
130+1
131+SLEEP(2)
132+0
133+SLEEP(1)
134+0
135+SLEEP(1000)
136+1
137+SLEEP(2)
138+0
139+SLEEP(1)
140+0
141+SLEEP(1000)
142+1
143+SLEEP(2)
144+0
145+SLEEP(1)
146+0
147+SLEEP(1000)
148+1
149+SLEEP(2)
150+0
151+SLEEP(1)
152+0
153+SLEEP(1000)
154+1
155+SLEEP(2)
156+0
157+SLEEP(1)
158+0
159+SLEEP(1000)
160+1
161+SLEEP(2)
162+0
163+SLEEP(1)
164+0
165+SLEEP(1000)
166+1
167+SLEEP(2)
168+0
169
170=== added file 'Percona-Server/mysql-test/suite/sys_vars/r/sql_timeout_basic.result'
171--- Percona-Server/mysql-test/suite/sys_vars/r/sql_timeout_basic.result 1970-01-01 00:00:00 +0000
172+++ Percona-Server/mysql-test/suite/sys_vars/r/sql_timeout_basic.result 2012-04-26 09:57:20 +0000
173@@ -0,0 +1,164 @@
174+SET @start_global_value = @@global.sql_timeout;
175+SELECT @start_global_value;
176+@start_global_value
177+0
178+SET @start_session_value = @@session.sql_timeout;
179+SELECT @start_session_value;
180+@start_session_value
181+0
182+'#--------------------FN_DYNVARS_083_01-------------------------#'
183+SET @@global.sql_timeout = 100;
184+SET @@global.sql_timeout = DEFAULT;
185+SELECT @@global.sql_timeout;
186+@@global.sql_timeout
187+0
188+SET @@session.sql_timeout = 200;
189+SET @@session.sql_timeout = DEFAULT;
190+SELECT @@session.sql_timeout;
191+@@session.sql_timeout
192+0
193+'#--------------------FN_DYNVARS_083_02-------------------------#'
194+SET @@global.sql_timeout = DEFAULT;
195+SELECT @@global.sql_timeout = 0;
196+@@global.sql_timeout = 0
197+1
198+SET @@session.sql_timeout = DEFAULT;
199+SELECT @@session.sql_timeout = 0;
200+@@session.sql_timeout = 0
201+1
202+'#--------------------FN_DYNVARS_083_03-------------------------#'
203+SET @@global.sql_timeout = 100;
204+SELECT @@global.sql_timeout;
205+@@global.sql_timeout
206+100
207+SET @@global.sql_timeout = 200;
208+SELECT @@global.sql_timeout;
209+@@global.sql_timeout
210+200
211+SET @@global.sql_timeout = 65536;
212+SELECT @@global.sql_timeout;
213+@@global.sql_timeout
214+65536
215+SET @@global.sql_timeout = 4294967295;
216+SELECT @@global.sql_timeout;
217+@@global.sql_timeout
218+4294967295
219+SET @@global.sql_timeout = 4294967294;
220+SELECT @@global.sql_timeout;
221+@@global.sql_timeout
222+4294967294
223+'#--------------------FN_DYNVARS_083_04-------------------------#'
224+SET @@session.sql_timeout = 100;
225+SELECT @@session.sql_timeout;
226+@@session.sql_timeout
227+100
228+SET @@session.sql_timeout = 200;
229+SELECT @@session.sql_timeout;
230+@@session.sql_timeout
231+200
232+SET @@session.sql_timeout = 4294967295;
233+SELECT @@session.sql_timeout;
234+@@session.sql_timeout
235+4294967295
236+SET @@session.sql_timeout = 4294967294;
237+SELECT @@session.sql_timeout;
238+@@session.sql_timeout
239+4294967294
240+SET @@session.sql_timeout = 65535;
241+SELECT @@session.sql_timeout;
242+@@session.sql_timeout
243+65535
244+'#------------------FN_DYNVARS_083_05-----------------------#'
245+SET @@global.sql_timeout = 0;
246+SELECT @@global.sql_timeout;
247+@@global.sql_timeout
248+0
249+SET @@global.sql_timeout = -1024;
250+Warnings:
251+Warning 1292 Truncated incorrect sql_timeout value: '-1024'
252+SELECT @@global.sql_timeout;
253+@@global.sql_timeout
254+0
255+SET @@global.sql_timeout = 65530.34;
256+ERROR 42000: Incorrect argument type to variable 'sql_timeout'
257+SELECT @@global.sql_timeout;
258+@@global.sql_timeout
259+0
260+SET @@global.sql_timeout = test;
261+ERROR 42000: Incorrect argument type to variable 'sql_timeout'
262+SELECT @@global.sql_timeout;
263+@@global.sql_timeout
264+0
265+SET @@session.sql_timeout = 0;
266+SELECT @@session.sql_timeout;
267+@@session.sql_timeout
268+0
269+SET @@session.sql_timeout = -2;
270+Warnings:
271+Warning 1292 Truncated incorrect sql_timeout value: '-2'
272+SELECT @@session.sql_timeout;
273+@@session.sql_timeout
274+0
275+SET @@session.sql_timeout = 65530.34;
276+ERROR 42000: Incorrect argument type to variable 'sql_timeout'
277+SELECT @@session.sql_timeout;
278+@@session.sql_timeout
279+0
280+SET @@session.sql_timeout = test;
281+ERROR 42000: Incorrect argument type to variable 'sql_timeout'
282+SELECT @@session.sql_timeout;
283+@@session.sql_timeout
284+0
285+'#------------------FN_DYNVARS_083_06-----------------------#'
286+SELECT @@global.sql_timeout = VARIABLE_VALUE
287+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
288+WHERE VARIABLE_NAME='sql_timeout';
289+@@global.sql_timeout = VARIABLE_VALUE
290+1
291+'#------------------FN_DYNVARS_083_07-----------------------#'
292+SELECT @@session.sql_timeout = VARIABLE_VALUE
293+FROM INFORMATION_SCHEMA.SESSION_VARIABLES
294+WHERE VARIABLE_NAME='sql_timeout';
295+@@session.sql_timeout = VARIABLE_VALUE
296+1
297+'#------------------FN_DYNVARS_083_08-----------------------#'
298+SET @@global.sql_timeout = TRUE;
299+SELECT @@global.sql_timeout;
300+@@global.sql_timeout
301+1
302+SET @@global.sql_timeout = FALSE;
303+SELECT @@global.sql_timeout;
304+@@global.sql_timeout
305+0
306+'#---------------------FN_DYNVARS_083_09----------------------#'
307+SET @@global.sql_timeout = 100;
308+SELECT @@sql_timeout = @@global.sql_timeout;
309+@@sql_timeout = @@global.sql_timeout
310+0
311+'#---------------------FN_DYNVARS_083_10----------------------#'
312+SET @@sql_timeout = 1000;
313+SELECT @@sql_timeout = @@local.sql_timeout;
314+@@sql_timeout = @@local.sql_timeout
315+1
316+SELECT @@local.sql_timeout = @@session.sql_timeout;
317+@@local.sql_timeout = @@session.sql_timeout
318+1
319+'#---------------------FN_DYNVARS_083_11----------------------#'
320+SET sql_timeout = 100;
321+SELECT @@sql_timeout;
322+@@sql_timeout
323+100
324+SELECT local.sql_timeout;
325+ERROR 42S02: Unknown table 'local' in field list
326+SELECT session.sql_timeout;
327+ERROR 42S02: Unknown table 'session' in field list
328+SELECT sql_timeout = @@session.sql_timeout;
329+ERROR 42S22: Unknown column 'sql_timeout' in 'field list'
330+SET @@global.sql_timeout = @start_global_value;
331+SELECT @@global.sql_timeout;
332+@@global.sql_timeout
333+0
334+SET @@session.sql_timeout = @start_session_value;
335+SELECT @@session.sql_timeout;
336+@@session.sql_timeout
337+0
338
339=== added file 'Percona-Server/mysql-test/suite/sys_vars/t/sql_timeout_basic.test'
340--- Percona-Server/mysql-test/suite/sys_vars/t/sql_timeout_basic.test 1970-01-01 00:00:00 +0000
341+++ Percona-Server/mysql-test/suite/sys_vars/t/sql_timeout_basic.test 2012-04-26 09:57:20 +0000
342@@ -0,0 +1,210 @@
343+############mysql-test\suite\sysvars\t\sql_timeout_basic.test##################
344+# #
345+# Variable Name: sql_timeout #
346+# Scope: GLOBAL | SESSION #
347+# Access Type: Dynamic #
348+# Data Type: numeric #
349+# Default Value: 0 #
350+# Range: 1-4294967295 #
351+# #
352+# #
353+# Creation Date: 2012-02-08 #
354+# Author: Vlad Lesin #
355+# #
356+# Description: Test Cases of Dynamic System Variable sql_timeout #
357+# that checks the behavior of this variable in the following ways#
358+# * Default Value #
359+# * Valid & Invalid values #
360+# * Scope & Access method #
361+# * Data Integrity #
362+# #
363+# Reference: none #
364+# #
365+# #
366+###############################################################################
367+
368+--source include/load_sysvars.inc
369+
370+############################################################
371+# START OF sql_timeout TESTS #
372+############################################################
373+
374+
375+#############################################################
376+# Save initial value #
377+#############################################################
378+
379+SET @start_global_value = @@global.sql_timeout;
380+SELECT @start_global_value;
381+SET @start_session_value = @@session.sql_timeout;
382+SELECT @start_session_value;
383+
384+
385+--echo '#--------------------FN_DYNVARS_083_01-------------------------#'
386+#################################################################
387+# Display the DEFAULT value of sql_timeout #
388+#################################################################
389+
390+SET @@global.sql_timeout = 100;
391+SET @@global.sql_timeout = DEFAULT;
392+SELECT @@global.sql_timeout;
393+
394+SET @@session.sql_timeout = 200;
395+SET @@session.sql_timeout = DEFAULT;
396+SELECT @@session.sql_timeout;
397+
398+
399+--echo '#--------------------FN_DYNVARS_083_02-------------------------#'
400+#################################################################
401+# Check the DEFAULT value of sql_timeout #
402+#################################################################
403+
404+SET @@global.sql_timeout = DEFAULT;
405+SELECT @@global.sql_timeout = 0;
406+
407+SET @@session.sql_timeout = DEFAULT;
408+SELECT @@session.sql_timeout = 0;
409+
410+
411+--echo '#--------------------FN_DYNVARS_083_03-------------------------#'
412+###########################################################################
413+# Change the value of sql_timeout to a valid value for GLOBAL Scope #
414+###########################################################################
415+
416+SET @@global.sql_timeout = 100;
417+SELECT @@global.sql_timeout;
418+SET @@global.sql_timeout = 200;
419+SELECT @@global.sql_timeout;
420+SET @@global.sql_timeout = 65536;
421+SELECT @@global.sql_timeout;
422+SET @@global.sql_timeout = 4294967295;
423+SELECT @@global.sql_timeout;
424+SET @@global.sql_timeout = 4294967294;
425+SELECT @@global.sql_timeout;
426+
427+
428+--echo '#--------------------FN_DYNVARS_083_04-------------------------#'
429+############################################################################
430+# Change the value of sql_timeout to a valid value for SESSION Scope #
431+############################################################################
432+SET @@session.sql_timeout = 100;
433+SELECT @@session.sql_timeout;
434+SET @@session.sql_timeout = 200;
435+SELECT @@session.sql_timeout;
436+SET @@session.sql_timeout = 4294967295;
437+SELECT @@session.sql_timeout;
438+SET @@session.sql_timeout = 4294967294;
439+SELECT @@session.sql_timeout;
440+SET @@session.sql_timeout = 65535;
441+SELECT @@session.sql_timeout;
442+
443+
444+--echo '#------------------FN_DYNVARS_083_05-----------------------#'
445+#############################################################
446+# Change the value of sql_timeout to an invalid value #
447+#############################################################
448+
449+SET @@global.sql_timeout = 0;
450+SELECT @@global.sql_timeout;
451+SET @@global.sql_timeout = -1024;
452+SELECT @@global.sql_timeout;
453+--Error ER_WRONG_TYPE_FOR_VAR
454+SET @@global.sql_timeout = 65530.34;
455+SELECT @@global.sql_timeout;
456+--Error ER_WRONG_TYPE_FOR_VAR
457+SET @@global.sql_timeout = test;
458+SELECT @@global.sql_timeout;
459+
460+SET @@session.sql_timeout = 0;
461+SELECT @@session.sql_timeout;
462+SET @@session.sql_timeout = -2;
463+SELECT @@session.sql_timeout;
464+--Error ER_WRONG_TYPE_FOR_VAR
465+SET @@session.sql_timeout = 65530.34;
466+SELECT @@session.sql_timeout;
467+
468+--Error ER_WRONG_TYPE_FOR_VAR
469+SET @@session.sql_timeout = test;
470+SELECT @@session.sql_timeout;
471+
472+
473+--echo '#------------------FN_DYNVARS_083_06-----------------------#'
474+####################################################################
475+# Check if the value in GLOBAL Table matches value in variable #
476+####################################################################
477+
478+
479+SELECT @@global.sql_timeout = VARIABLE_VALUE
480+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
481+WHERE VARIABLE_NAME='sql_timeout';
482+
483+--echo '#------------------FN_DYNVARS_083_07-----------------------#'
484+####################################################################
485+# Check if the value in SESSION Table matches value in variable #
486+####################################################################
487+
488+SELECT @@session.sql_timeout = VARIABLE_VALUE
489+FROM INFORMATION_SCHEMA.SESSION_VARIABLES
490+WHERE VARIABLE_NAME='sql_timeout';
491+
492+
493+--echo '#------------------FN_DYNVARS_083_08-----------------------#'
494+####################################################################
495+# Check if TRUE and FALSE values can be used on variable #
496+####################################################################
497+
498+SET @@global.sql_timeout = TRUE;
499+SELECT @@global.sql_timeout;
500+SET @@global.sql_timeout = FALSE;
501+SELECT @@global.sql_timeout;
502+
503+
504+--echo '#---------------------FN_DYNVARS_083_09----------------------#'
505+#############################################################################
506+# Check if accessing variable with and without GLOBAL point #
507+# to same variable #
508+#############################################################################
509+
510+SET @@global.sql_timeout = 100;
511+SELECT @@sql_timeout = @@global.sql_timeout;
512+
513+
514+--echo '#---------------------FN_DYNVARS_083_10----------------------#'
515+#############################################################################
516+# Check if accessing variable with SESSION,LOCAL and without SCOPE points #
517+# to same session variable #
518+#############################################################################
519+
520+SET @@sql_timeout = 1000;
521+SELECT @@sql_timeout = @@local.sql_timeout;
522+SELECT @@local.sql_timeout = @@session.sql_timeout;
523+
524+
525+--echo '#---------------------FN_DYNVARS_083_11----------------------#'
526+############################################################################
527+# Check if sql_timeout can be accessed with and without @@ sign #
528+############################################################################
529+
530+SET sql_timeout = 100;
531+SELECT @@sql_timeout;
532+--Error ER_UNKNOWN_TABLE
533+SELECT local.sql_timeout;
534+--Error ER_UNKNOWN_TABLE
535+SELECT session.sql_timeout;
536+--Error ER_BAD_FIELD_ERROR
537+SELECT sql_timeout = @@session.sql_timeout;
538+
539+
540+####################################
541+# Restore initial value #
542+####################################
543+
544+SET @@global.sql_timeout = @start_global_value;
545+SELECT @@global.sql_timeout;
546+SET @@session.sql_timeout = @start_session_value;
547+SELECT @@session.sql_timeout;
548+
549+
550+#############################################################
551+# END OF sql_timeout TESTS #
552+#############################################################
553
554=== added file 'Percona-Server/mysql-test/t/percona_sql_timeout.test'
555--- Percona-Server/mysql-test/t/percona_sql_timeout.test 1970-01-01 00:00:00 +0000
556+++ Percona-Server/mysql-test/t/percona_sql_timeout.test 2012-04-26 09:57:20 +0000
557@@ -0,0 +1,44 @@
558+#
559+# SQL_TIMEOUT test
560+#
561+# SQL_TIMEOUT is a session variable or
562+# query keyword which sets the time
563+# limit for query execution.
564+
565+# Save the initial number of concurrent sessions
566+--source include/count_sessions.inc
567+
568+
569+--disable_query_log
570+
571+--let $sessions_count=20
572+
573+--let $i=$sessions_count
574+while ($i) {
575+--dec $i
576+
577+--let $connection_name=connection_$i
578+--connect ($connection_name,localhost,root,,)
579+--delimiter +++
580+ let $statement=
581+ SELECT SLEEP(1);
582+ SET @@SESSION.SQL_TIMEOUT = 1;
583+ SELECT SLEEP(1000);
584+ SET @@SESSION.SQL_TIMEOUT = 0;
585+ SELECT SLEEP(2);
586++++
587+--delimiter ;
588+--send_eval $statement
589+}
590+
591+--let $i=$sessions_count
592+while ($i) {
593+--dec $i
594+--let $connection_name=connection_$i
595+--connection $connection_name
596+--reap
597+--disconnect $connection_name
598+}
599+
600+--connection default
601+--source include/wait_until_count_sessions.inc
602
603=== modified file 'Percona-Server/sql/CMakeLists.txt'
604--- Percona-Server/sql/CMakeLists.txt 2012-04-19 16:51:34 +0000
605+++ Percona-Server/sql/CMakeLists.txt 2012-04-26 09:57:20 +0000
606@@ -76,7 +76,7 @@
607 sql_profile.cc event_parse_data.cc sql_alter.cc
608 sql_signal.cc rpl_handler.cc mdl.cc sql_admin.cc
609 transaction.cc sys_vars.cc sql_truncate.cc datadict.cc
610- sql_reload.cc
611+ sql_reload.cc sql_timeout.cc
612 ${GEN_SOURCES}
613 ${MYSYS_LIBWRAP_SOURCE})
614
615
616=== modified file 'Percona-Server/sql/mysqld.cc'
617--- Percona-Server/sql/mysqld.cc 2012-04-19 16:51:34 +0000
618+++ Percona-Server/sql/mysqld.cc 2012-04-26 09:57:20 +0000
619@@ -71,6 +71,8 @@
620
621 #include "query_response_time.h"
622
623+#include "sql_timeout.h"
624+
625 #ifdef WITH_PERFSCHEMA_STORAGE_ENGINE
626 #include "../storage/perfschema/pfs_server.h"
627 #endif /* WITH_PERFSCHEMA_STORAGE_ENGINE */
628@@ -671,6 +673,7 @@
629 mysql_rwlock_t LOCK_system_variables_hash;
630 mysql_cond_t COND_thread_count;
631 pthread_t signal_thread;
632+pthread_t sql_timeout_thread;
633 pthread_attr_t connection_attrib;
634 mysql_mutex_t LOCK_server_started;
635 mysql_cond_t COND_server_started;
636@@ -1439,6 +1442,7 @@
637 */
638 wait_for_signal_thread_to_end();
639 mysql_audit_finalize();
640+ sql_timeout_monitor.exit();
641 clean_up_mutexes();
642 clean_up_error_log_mutex();
643 #ifdef WITH_PERFSCHEMA_STORAGE_ENGINE
644@@ -4289,6 +4293,48 @@
645 }
646 #endif//DBUG_OFF
647
648+
649+pthread_handler_t sql_timeout_thread_func(void *arg __attribute__((unused)))
650+{
651+ my_thread_init();
652+ sql_timeout_monitor.init();
653+ /* SQL_TIMEOUT loop */
654+ sql_timeout_monitor.monitor();
655+ sql_timeout_monitor.deinit();
656+ my_thread_end();
657+ return(0);
658+}
659+
660+/**
661+ Start SQL_TIMEOUT monitor loop in a separate thread.
662+*/
663+static void start_sql_timeout_thread(void)
664+{
665+ int error;
666+ pthread_attr_t thr_attr;
667+ DBUG_ENTER("start_sql_timeout_thread");
668+ (void) pthread_attr_init(&thr_attr);
669+ (void) pthread_attr_setdetachstate(&thr_attr, PTHREAD_CREATE_DETACHED);
670+ pthread_attr_setstacksize(&thr_attr, my_thread_stack_size);
671+ mysql_mutex_lock(&LOCK_thread_count);
672+ if ((error= mysql_thread_create(key_thread_sql_timeout,
673+ &sql_timeout_thread,
674+ &thr_attr,
675+ sql_timeout_thread_func,
676+ 0)))
677+ {
678+ sql_print_error("Can't create sql_timeout-thread (error %d, errno: %d)",
679+ error,errno);
680+ exit(1);
681+ }
682+ /* Waiting for monitor thread start */
683+ mysql_cond_wait(&COND_thread_count, &LOCK_thread_count);
684+ mysql_mutex_unlock(&LOCK_thread_count);
685+
686+ (void) pthread_attr_destroy(&thr_attr);
687+ DBUG_VOID_RETURN;
688+}
689+
690 #ifdef __WIN__
691 int win_main(int argc, char **argv)
692 #else
693@@ -4579,6 +4625,7 @@
694 */
695 error_handler_hook= my_message_sql;
696 start_signal_handler(); // Creates pidfile
697+ start_sql_timeout_thread();
698
699 if (mysql_rm_tmp_tables() || acl_init(opt_noacl) ||
700 my_tz_init((THD *)0, default_tz_name, opt_bootstrap))
701@@ -7879,6 +7926,7 @@
702 key_LOCK_prepared_stmt_count,
703 key_LOCK_rpl_status, key_LOCK_server_started, key_LOCK_status,
704 key_LOCK_system_variables_hash, key_LOCK_table_share, key_LOCK_thd_data,
705+ key_LOCK_sql_timeout_monitor,
706 key_LOCK_user_conn, key_LOCK_uuid_generator, key_LOG_LOCK_log,
707 key_master_info_data_lock, key_master_info_run_lock,
708 key_master_info_sleep_lock,
709@@ -7931,6 +7979,9 @@
710 { &key_LOCK_prepared_stmt_count, "LOCK_prepared_stmt_count", PSI_FLAG_GLOBAL},
711 { &key_LOCK_rpl_status, "LOCK_rpl_status", PSI_FLAG_GLOBAL},
712 { &key_LOCK_server_started, "LOCK_server_started", PSI_FLAG_GLOBAL},
713+ { &key_LOCK_sql_timeout_monitor,
714+ "Sql_timeout_monitor::LOCK_timeouts",
715+ PSI_FLAG_GLOBAL},
716 { &key_LOCK_status, "LOCK_status", PSI_FLAG_GLOBAL},
717 { &key_LOCK_system_variables_hash, "LOCK_system_variables_hash", PSI_FLAG_GLOBAL},
718 { &key_LOCK_table_share, "LOCK_table_share", PSI_FLAG_GLOBAL},
719@@ -7991,6 +8042,7 @@
720 key_COND_thread_count, key_COND_thread_cache, key_COND_flush_thread_cache;
721 PSI_cond_key key_RELAYLOG_update_cond;
722 PSI_cond_key key_COND_wakeup_ready, key_COND_queue_busy;
723+PSI_cond_key key_COND_sql_timeout_monitor;
724
725 static PSI_cond_info all_server_conds[]=
726 {
727@@ -8010,6 +8062,9 @@
728 { &key_COND_queue_busy, "COND_queue_busy", PSI_FLAG_GLOBAL},
729 { &key_COND_rpl_status, "COND_rpl_status", PSI_FLAG_GLOBAL},
730 { &key_COND_server_started, "COND_server_started", PSI_FLAG_GLOBAL},
731+ { &key_COND_sql_timeout_monitor,
732+ "Sql_timeout_monitor::COND_timeouts",
733+ PSI_FLAG_GLOBAL},
734 { &key_COND_wakeup_ready, "THD::COND_wakeup_ready", 0},
735 { &key_delayed_insert_cond, "Delayed_insert::cond", 0},
736 { &key_delayed_insert_cond_client, "Delayed_insert::cond_client", 0},
737@@ -8032,7 +8087,8 @@
738
739 PSI_thread_key key_thread_bootstrap, key_thread_delayed_insert,
740 key_thread_handle_manager, key_thread_main,
741- key_thread_one_connection, key_thread_signal_hand;
742+ key_thread_one_connection, key_thread_signal_hand,
743+ key_thread_sql_timeout;
744
745 static PSI_thread_info all_server_threads[]=
746 {
747@@ -8057,7 +8113,8 @@
748 { &key_thread_handle_manager, "manager", PSI_FLAG_GLOBAL},
749 { &key_thread_main, "main", PSI_FLAG_GLOBAL},
750 { &key_thread_one_connection, "one_connection", 0},
751- { &key_thread_signal_hand, "signal_handler", PSI_FLAG_GLOBAL}
752+ { &key_thread_signal_hand, "signal_handler", PSI_FLAG_GLOBAL},
753+ { &key_thread_sql_timeout, "sql_timeout", PSI_FLAG_GLOBAL},
754 };
755
756 #ifdef HAVE_MMAP
757
758=== modified file 'Percona-Server/sql/mysqld.h'
759--- Percona-Server/sql/mysqld.h 2012-04-19 16:51:34 +0000
760+++ Percona-Server/sql/mysqld.h 2012-04-26 09:57:20 +0000
761@@ -269,6 +269,7 @@
762 key_LOCK_prepared_stmt_count,
763 key_LOCK_rpl_status, key_LOCK_server_started, key_LOCK_status,
764 key_LOCK_table_share, key_LOCK_thd_data,
765+ key_LOCK_sql_timeout_monitor,
766 key_LOCK_user_conn, key_LOCK_uuid_generator, key_LOG_LOCK_log,
767 key_master_info_data_lock, key_master_info_run_lock,
768 key_master_info_sleep_lock,
769@@ -302,10 +303,13 @@
770 key_COND_thread_count, key_COND_thread_cache, key_COND_flush_thread_cache;
771 extern PSI_cond_key key_RELAYLOG_update_cond;
772 extern PSI_cond_key key_COND_wakeup_ready, key_COND_queue_busy;
773+extern PSI_cond_key key_COND_sql_timeout_monitor;
774+
775
776 extern PSI_thread_key key_thread_bootstrap, key_thread_delayed_insert,
777 key_thread_handle_manager, key_thread_kill_server, key_thread_main,
778- key_thread_one_connection, key_thread_signal_hand;
779+ key_thread_one_connection, key_thread_signal_hand,
780+ key_thread_sql_timeout;
781
782 #ifdef HAVE_MMAP
783 extern PSI_file_key key_file_map;
784
785=== modified file 'Percona-Server/sql/sql_class.cc'
786--- Percona-Server/sql/sql_class.cc 2012-04-19 16:51:34 +0000
787+++ Percona-Server/sql/sql_class.cc 2012-04-26 09:57:20 +0000
788@@ -60,6 +60,7 @@
789 #include "debug_sync.h"
790 #include "sql_parse.h" // is_update_query
791 #include "sql_callback.h"
792+#include "sql_timeout.h"
793
794 /*
795 The following is used to initialise Table_ident with a internal
796@@ -871,6 +872,7 @@
797 lex->current_select= 0;
798 start_time=(time_t) 0;
799 start_utime= prior_thr_create_utime= 0L;
800+ sql_timeout_expire= 0;
801 utime_after_lock= 0L;
802 current_linfo = 0;
803 slave_thread = 0;
804@@ -1490,6 +1492,9 @@
805 {
806 THD_CHECK_SENTRY(this);
807 DBUG_ENTER("~THD()");
808+
809+ sql_timeout_monitor.remove(this);
810+
811 /* Ensure that no one is using THD */
812 mysql_mutex_lock(&LOCK_thd_data);
813 mysys_var=0; // Safety (shouldn't be needed)
814
815=== modified file 'Percona-Server/sql/sql_class.h'
816--- Percona-Server/sql/sql_class.h 2012-04-18 23:26:38 +0000
817+++ Percona-Server/sql/sql_class.h 2012-04-26 09:57:20 +0000
818@@ -492,6 +492,7 @@
819 ulong trans_prealloc_size;
820 ulong log_warnings;
821 ulong group_concat_max_len;
822+ ulong sql_timeout;
823
824 ulong binlog_format; ///< binlog format for this thd (see enum_binlog_format)
825 my_bool binlog_direct_non_trans_update;
826@@ -1653,6 +1654,11 @@
827 /* remote (peer) port */
828 uint16 peer_port;
829 time_t start_time, user_time;
830+ /*
831+ Expire time in microseconds for sql_timeout monitor thread. The 0 value
832+ means the current thread is not monitored by sql_timeout monitor.
833+ */
834+ ulonglong sql_timeout_expire;
835 // track down slow pthread_create
836 ulonglong prior_thr_create_utime, thr_create_utime;
837 ulonglong start_utime, utime_after_lock;
838
839=== modified file 'Percona-Server/sql/sql_parse.cc'
840--- Percona-Server/sql/sql_parse.cc 2012-04-19 16:51:34 +0000
841+++ Percona-Server/sql/sql_parse.cc 2012-04-26 09:57:20 +0000
842@@ -95,6 +95,7 @@
843 #include "debug_sync.h"
844 #include "probes_mysql.h"
845 #include "set_var.h"
846+#include "sql_timeout.h"
847
848 #define FLAGSTR(V,F) ((V)&(F)?#F" ":"")
849
850@@ -5801,7 +5802,9 @@
851 (char *) thd->security_ctx->host_or_ip,
852 0);
853
854+ sql_timeout_monitor.add(thd);
855 error= mysql_execute_command(thd);
856+ sql_timeout_monitor.remove(thd);
857 MYSQL_QUERY_EXEC_DONE(error);
858 }
859 }
860
861=== added file 'Percona-Server/sql/sql_timeout.cc'
862--- Percona-Server/sql/sql_timeout.cc 1970-01-01 00:00:00 +0000
863+++ Percona-Server/sql/sql_timeout.cc 2012-04-26 09:57:20 +0000
864@@ -0,0 +1,228 @@
865+#include "sql_timeout.h"
866+#include "my_sys.h"
867+#include "mysqld.h"
868+
869+#define INITIAL_QUEUE_SIZE 1000
870+
871+/**
872+ Is used in priority queue to compare THDs by expire time.
873+*/
874+
875+static int compare_thd(void *not_used __attribute__((unused)),
876+ uchar *a_ptr,uchar* b_ptr)
877+{
878+ ulonglong a= ((THD *) a_ptr)->sql_timeout_expire;
879+ ulonglong b= ((THD *) b_ptr)->sql_timeout_expire;
880+ return (a < b) ? -1 : (a == b) ? 0 : 1;
881+}
882+
883+
884+/**
885+ Initialize SQL_TIMEOUT monitor.
886+
887+ @note This function must be called before any actions with monitor.
888+*/
889+
890+void Sql_timeout_monitor::init()
891+{
892+ need_to_exit= false;
893+ completed= true;
894+ mysql_mutex_init(key_LOCK_sql_timeout_monitor,
895+ &LOCK_queue,
896+ MY_MUTEX_INIT_FAST);
897+ mysql_cond_init(key_COND_sql_timeout_monitor, &COND_queue, NULL);
898+ /*
899+ THD is not POD type. Thats why offsetof(...) is not used here to get
900+ priority member offset for the queue. The 0 offset is passed as an
901+ argument but compare_thd(...) function does properly comparison.
902+ */
903+ init_queue_ex(&queue, INITIAL_QUEUE_SIZE + 1, 0, 0,
904+ compare_thd, NULL, INITIAL_QUEUE_SIZE + 1);
905+}
906+
907+
908+/**
909+ Deinitialize SQL_TIMEOUT monitor.
910+
911+ Clean up and destroy all monitor data.
912+*/
913+
914+void Sql_timeout_monitor::deinit()
915+{
916+ mysql_mutex_lock(&LOCK_queue);
917+ for (size_t i= 0; i < queue.elements; ++i)
918+ ((THD *)queue_element(&queue, i))->sql_timeout_expire= 0;
919+ queue_remove_all(&queue);
920+ delete_queue(&queue);
921+ mysql_mutex_unlock(&LOCK_queue);
922+ mysql_cond_destroy(&COND_queue);
923+ mysql_mutex_destroy(&LOCK_queue);
924+}
925+
926+
927+/**
928+ SQL_TIMEOUT monitor loop.
929+
930+ If thread wants to be monitored it must add itself to the monitor
931+ before query execution and remove after. The monitor holds all
932+ threads in prority queue. The priority is an expiration time of
933+ query execution. The monitor idles till the top expiration time
934+ in the queue. Then it kills all theads with expired execution time.
935+ If there are no threads in the queue the monitor thread completely
936+ idles.
937+
938+ This function must be invoked in a separate thread. To exit from
939+ this function Sql_timeout_monitor::exit() must be called.
940+*/
941+
942+void Sql_timeout_monitor::monitor()
943+{
944+ completed= false;
945+
946+ /* Signal monitor is started */
947+ mysql_mutex_lock(&LOCK_thread_count);
948+ mysql_mutex_unlock(&LOCK_thread_count);
949+ mysql_cond_broadcast(&COND_thread_count);
950+
951+ mysql_mutex_lock(&LOCK_queue);
952+ while (!need_to_exit)
953+ {
954+ int error= 0;
955+
956+ if (queue.elements)
957+ {
958+ THD *thd;
959+ ulonglong sleep_time;
960+ struct timespec abstime;
961+ ulonglong now= my_micro_time();
962+
963+ while ( (thd= (THD *) queue_top(&queue))->sql_timeout_expire <= now)
964+ {
965+ mysql_mutex_lock(&thd->LOCK_thd_data);
966+ thd->awake(THD::KILL_QUERY);
967+ mysql_mutex_unlock(&thd->LOCK_thd_data);
968+ queue_remove(&queue, 0);
969+ /*
970+ Mark the thread as removed from SQL_TIMEOUT monitor.
971+ So there is no need to remove it in
972+ Sql_timeout_monitor::remove(THD *thd)
973+ after query execution.
974+ */
975+ thd->sql_timeout_expire= 0;
976+ if (!queue.elements)
977+ break;
978+ }
979+
980+ if (!queue.elements)
981+ continue;
982+
983+ sleep_time= thd->sql_timeout_expire - now;
984+ set_timespec_nsec(abstime, sleep_time);
985+ error= mysql_cond_timedwait(&COND_queue, &LOCK_queue, &abstime);
986+ }
987+ else
988+ error= mysql_cond_wait(&COND_queue, &LOCK_queue);
989+
990+ if (error && error != EINTR && error != ETIMEDOUT && error != ETIME)
991+ {
992+ if (!shutdown_in_progress && !abort_loop)
993+ sql_print_error("The error %d (%s)occured on wait condition variable "
994+ "in sql_timeout monitor. Exit thread.",
995+ error, strerror((ulong)error));
996+ break;
997+ }
998+ }
999+
1000+ mysql_mutex_unlock(&LOCK_queue);
1001+ completed= true;
1002+}
1003+
1004+
1005+/**
1006+ Add connection thread to the SQL_TIMEOUT monitor.
1007+
1008+ Add connection thread to the SQL_TIMEOUT monitor.
1009+ Do nothing if SQL_TIMEOUT is 0 or the thread has already
1010+ added.
1011+
1012+ @param thd pointer to the thread to add
1013+*/
1014+
1015+void Sql_timeout_monitor::add(THD *thd)
1016+{
1017+ DBUG_ASSERT(thd);
1018+
1019+ ulong timeout= thd->variables.sql_timeout;
1020+
1021+ if (thd->sql_timeout_expire || !timeout)
1022+ return;
1023+
1024+ mysql_mutex_lock(&LOCK_queue);
1025+ thd->sql_timeout_expire= my_micro_time() + (ulonglong)timeout*1000000;
1026+
1027+ if (queue_insert_safe(&queue, (uchar *)thd))
1028+ {
1029+ thd->sql_timeout_expire= 0;
1030+ sql_print_error("The memory is not enough to put the query into "
1031+ "SQL_TIMEOUT queue");
1032+ }
1033+ else
1034+ mysql_cond_broadcast(&COND_queue);
1035+
1036+ mysql_mutex_unlock(&LOCK_queue);
1037+}
1038+
1039+
1040+
1041+/**
1042+ Remove connection thread from the SQL_TIMEOUT monitor.
1043+
1044+ Remove connection thread from the SQL_TIMEOUT monitor.
1045+ Do nothing if the thread is not added.
1046+
1047+ @param thd pointer to the thread to remove
1048+*/
1049+
1050+void Sql_timeout_monitor::remove(THD *thd)
1051+{
1052+ size_t i;
1053+
1054+ DBUG_ASSERT(thd);
1055+
1056+ if (!thd->sql_timeout_expire)
1057+ return;
1058+
1059+ mysql_mutex_lock(&LOCK_queue);
1060+
1061+ for (i= 0; i < queue.elements; ++i)
1062+ if (((THD *) queue_element(&queue, i)) == thd)
1063+ break;
1064+ /*
1065+ It may be deleted in monitor thread between thd->sql_timeout_expire == 0
1066+ condition checking and mutex locking in current thread.
1067+ */
1068+ if (i < queue.elements)
1069+ queue_remove(&queue, i);
1070+
1071+ thd->sql_timeout_expire= 0;
1072+
1073+ mysql_mutex_unlock(&LOCK_queue);
1074+}
1075+
1076+
1077+/**
1078+ Signal to the monitor thread to exit .
1079+*/
1080+
1081+void Sql_timeout_monitor::exit()
1082+{
1083+ need_to_exit= true;
1084+ if (completed)
1085+ return;
1086+ mysql_mutex_lock(&LOCK_queue);
1087+ mysql_cond_broadcast(&COND_queue);
1088+ mysql_mutex_unlock(&LOCK_queue);
1089+}
1090+
1091+/* The global SQL_TIMEOUT monitor object */
1092+Sql_timeout_monitor sql_timeout_monitor;
1093
1094=== added file 'Percona-Server/sql/sql_timeout.h'
1095--- Percona-Server/sql/sql_timeout.h 1970-01-01 00:00:00 +0000
1096+++ Percona-Server/sql/sql_timeout.h 2012-04-26 09:57:20 +0000
1097@@ -0,0 +1,55 @@
1098+#ifndef SQL_TIMEOUT_INCLUDED
1099+#define SQL_TIMEOUT_INCLUDED
1100+#include "sql_class.h"
1101+#include "my_pthread.h"
1102+#include "queues.h"
1103+#include "hash.h"
1104+
1105+/**
1106+ @class Sql_timeout_monitor
1107+ The class to monitor threads for query execution time
1108+ in case of SQL_TIMEOUT session variable is set.
1109+
1110+ EXAMPLE
1111+ @verbatim
1112+ SET @@SESSION.SQL_TIMEOUT=5;
1113+ SELECT SLEEP(10);
1114+ @endverbatim
1115+*/
1116+
1117+class Sql_timeout_monitor
1118+{
1119+public:
1120+ void init();
1121+ void deinit();
1122+
1123+ void monitor();
1124+ void add(THD *thd);
1125+ void remove(THD *thd);
1126+ void exit();
1127+
1128+private:
1129+ int find(THD *thd);
1130+ /* Is locked during work with queue. */
1131+ mysql_mutex_t LOCK_queue;
1132+ /*
1133+ Condition variable to wake up monitor
1134+ thread when item is added to the queue
1135+ or exit from monitor() function
1136+ is requered.
1137+ */
1138+ mysql_cond_t COND_queue;
1139+ /*
1140+ The flag for notifying the monitor thread
1141+ about exit is requred.
1142+ */
1143+ volatile bool need_to_exit;
1144+ /* Shows that monitor thread is completed. */
1145+ volatile bool completed;
1146+ /* Priority queue of Sql_timeout_info objects */
1147+ QUEUE queue;
1148+};
1149+
1150+/* The global SQL_TIMEOUT monitor object */
1151+extern Sql_timeout_monitor sql_timeout_monitor;
1152+#endif /* SQL_TIMEOUT_INCLUDED */
1153
1154=== modified file 'Percona-Server/sql/sys_vars.cc'
1155--- Percona-Server/sql/sys_vars.cc 2012-04-19 16:51:34 +0000
1156+++ Percona-Server/sql/sys_vars.cc 2012-04-26 09:57:20 +0000
1157@@ -2825,6 +2825,12 @@
1158 SESSION_VAR(group_concat_max_len), CMD_LINE(REQUIRED_ARG),
1159 VALID_RANGE(4, ULONG_MAX), DEFAULT(1024), BLOCK_SIZE(1));
1160
1161+static Sys_var_ulong Sys_sql_timeout(
1162+ "sql_timeout",
1163+ "Timeout in seconds to wait before killing query.",
1164+ SESSION_VAR(sql_timeout), CMD_LINE(REQUIRED_ARG),
1165+ VALID_RANGE(0, ULONG_MAX), DEFAULT(0), BLOCK_SIZE(1));
1166+
1167 static char *glob_hostname_ptr;
1168 static Sys_var_charptr Sys_hostname(
1169 "hostname", "Server host name",

Subscribers

People subscribed via source and target branches