Bug #33414 Backup: DDL hangs indefinitely if ongoing backup
Submitted: 20 Dec 2007 18:29 Modified: 30 Sep 2008 17:27
Reporter: Lars Thalmann Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:6.0 source OS:Any
Assigned to: Chuck Bell
Triage: R3 (Medium) / E3 (Medium)

[20 Dec 2007 18:29] Lars Thalmann
Description:
One could expect that if DBA started BACKUP which would run for few hours and 
he issues CRATE TABLE then it should not hang for hours. Instead, he 
should get an error message so that he know what is going on.

So why not hang, and when a wait 'timeout period' expires, return an
error message?

(Note also WL#1272 "User timeouts on queries".)

How to repeat:
Use backup

Suggested fix:
Add option for timeout.

Hang, 
wait until 'timeout period' expires, and then
return an error message?
[4 Mar 2008 14:38] Lars Thalmann
SOLUTION:
1. Add a timeout as an option.
2. When timeout hits, then give error message.
[5 Mar 2008 14:54] Chuck Bell
Bug Resolution Plan
-------------------

1. Create new global variable 'DDL_blocker_timeout' with default 0.
2. Implement code to define range of values for the timeout (if needed).
3. Modify DDL_blocker_class::check_DDL_blocker() to timeout when timeout
   value is reached.
4. Write test for timeouts (min, max, etc.).
[5 Mar 2008 21:15] Chuck Bell
Explanation of Variable and Usage
---------------------------------
There is now a variable named ddl_wait_timeout. Setting this variable to a positive integer (ulong) will determine the number of seconds a blocked DDL statement will wait before aborting with an error.

The variable may be used as a session variable allowing the user to set the timeout for a DDL operation in that session.

For example, 

SET VARIABLE ddl_wait_timeout = 10;

will abort any DDL operation for that session which is blocked for more than 10 seconds.

The variable can also be used to set a timeout for all sessions, i.e. a global value. This will set a timeout for all DDLs blocked aborting them after the timeout.

For example,

SET GLOBAL VARIABLE ddl_wait_timeout = 100;

will abort all blocked DDL operations after 100 seconds.

It is also possible to set the timeout for some sessions and at the global level. In this case, the system will check to see if a session value is set and if so use that value for the current DDL operation. If there is no session value (it is set to 0), the system will then check the global value. If the global value is > 0, that value will be used for the timeout.

This flexibility gives the user the opportunity to set a higher global timeout for all blocked DDL operations and lower values for more time critical operations.

Notes
----- 
* This variable is set to default to 0 which means "do not timeout." 
* The maximum value is 1073741824.
* The DDL timeout counter is started from the point the DDL is first blocked, not from the point where the DDL blocker is fired.
[5 Mar 2008 22:05] Chuck Bell
Notes (continued)
-----------------
* The timeout (session or global) must be set before DDL is executed in order to have an effect.
[5 Mar 2008 22:08] Chuck Bell
Ready for review.

http://lists.mysql.com/commits/43491
[1 May 2008 17:34] Chuck Bell
The following is revised documentation of the solution to this bug based on collective rebutal of the original design/CONOPs.

Explanation of Variable and Usage
---------------------------------
There is now a variable named ddl_wait_timeout. Setting this variable to a positive integer (ulong) will determine the number of seconds a blocked DDL statement will wait before aborting with an error.

The variable is a session variable allowing the user to set the timeout for a
DDL operation per connection.

For example, 

SET VARIABLE ddl_wait_timeout = 10;

will abort any DDL operation for that session which is blocked for more than 10 seconds.

Notes
----- 
* This variable is session only.
* This variable is set to default to 50 seconds.
* Setting this variable to 0 means "timeout immediately." 
* The maximum value is (LONG_MAX/1000) = 2147483 which is in seconds.
* The DDL timeout counter is started from the point the DDL is first blocked, not from the point where the DDL blocker is fired.

New patch ready for review: http://lists.mysql.com/commits/46265
[7 Jul 2008 14:09] Chuck Bell
Will change variable name per Peter G's request.
[25 Jul 2008 18:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50533

2673 Chuck Bell	2008-07-25
      BUG#33414 : Backup: DDL hangs indefinitely if ongoing backup
      
      Added ability to set a timeout for blocked DDL operations. The
      timeout is the number of seconds a blocked DDL operation will 
      wait before aborting with an error message. The variable can be
      set only as a session variable.
      
      Note: timeout must be set before DDL is executed.
[25 Jul 2008 18:53] Chuck Bell
Patch ready for review.
[8 Aug 2008 14:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51197

2676 Chuck Bell	2008-08-08
      BUG#33414 : Backup: DDL hangs indefinitely if ongoing backup
      
      Added ability to set a timeout for blocked DDL operations. The
      timeout is the number of seconds a blocked DDL operation will 
      wait before aborting with an error message. The variable can be
      set only as a session variable.
      
      Note: timeout must be set before DDL is executed.
[8 Aug 2008 14:31] Jørgen Løland
Good to push
[12 Aug 2008 21:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51460

2683 Chuck Bell	2008-08-12
      BUG#33414 : Backup: DDL hangs indefinitely if ongoing backup
      
      Added ability to set a timeout for blocked DDL operations. The
      timeout is the number of seconds a blocked DDL operation will 
      wait before aborting with an error message. The variable can be
      set only as a session variable.
      
      Note: timeout must be set before DDL is executed.
[13 Aug 2008 12:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51524

2683 Chuck Bell	2008-08-13
      BUG#33414 : Backup: DDL hangs indefinitely if ongoing backup
      
      Added ability to set a timeout for blocked DDL operations. The
      timeout is the number of seconds a blocked DDL operation will 
      wait before aborting with an error message. The variable can be
      set only as a session variable.
      
      Note: timeout must be set before DDL is executed.
[15 Aug 2008 15:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51752

2675 Chuck Bell	2008-08-15
      BUG#33414 : Backup: DDL hangs indefinitely if ongoing backup
      
      Added ability to set a timeout for blocked DDL operations. The
      timeout is the number of seconds a blocked DDL operation will 
      wait before aborting with an error message. The variable can be
      set only as a session variable.
      
      Note: timeout must be set before DDL is executed.
[15 Aug 2008 21:27] Konstantin Osipov
From IRC discussion with Trudy I gathered this bug was not triaged by the triage team.
Triage, could you please triage it again?
Some items to keep in mind:
 - the bug only affect non-interactive applications. In an interactive application the user always has the option to interrupt a statement with Ctrl-C
 - the BACKUP statement is only one of the many SQL statements that may block a concurrent DDL, among them are:
   * a long-running SELECT
   * FLUSH TABLES WITH READ LOCK
   * LOCK TABLE/TABLES
Should the timeout be in effect for all statements, or only when BACKUP statement is running? Should there be a separate bug to address the above issues?
[18 Aug 2008 19:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51887

2675 Chuck Bell	2008-08-18
      BUG#33414 : Backup: DDL hangs indefinitely if ongoing backup
      
      Added ability to set a timeout for blocked DDL operations. The
      timeout is the number of seconds a blocked DDL operation will 
      wait before aborting with an error message. The variable can be
      set only as a session variable.
      
      Note: timeout must be set before DDL is executed.
[19 Aug 2008 7:25] Øystein Grøvlen
Patch http://lists.mysql.com/commits/51887 approved.
[19 Aug 2008 7:41] Lars Thalmann
See also: BUG#3654, BUG#9906, BUG#28481.
[19 Aug 2008 15:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51974

2677 Chuck Bell	2008-08-19
      BUG#33414 : Backup: DDL hangs indefinitely if ongoing backup
      
      Added ability to set a timeout for blocked DDL operations. The
      timeout is the number of seconds a blocked DDL operation will 
      wait before aborting with an error message. The variable can be
      set only as a session variable.
      
      Note: timeout must be set before DDL is executed.
[19 Aug 2008 15:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51975

2677 Chuck Bell	2008-08-19
      BUG#33414 : Backup: DDL hangs indefinitely if ongoing backup
      
      Added ability to set a timeout for blocked DDL operations. The
      timeout is the number of seconds a blocked DDL operation will 
      wait before aborting with an error message. The variable can be
      set only as a session variable.
      
      Note: timeout must be set before DDL is executed.
[19 Aug 2008 15:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51976

2677 Chuck Bell	2008-08-19
      BUG#33414 : Backup: DDL hangs indefinitely if ongoing backup
      
      Added ability to set a timeout for blocked DDL operations. The
      timeout is the number of seconds a blocked DDL operation will 
      wait before aborting with an error message. The variable can be
      set only as a session variable.
      
      Note: timeout must be set before DDL is executed.
[25 Aug 2008 20:59] Chuck Bell
Released in 6.0.7
[28 Aug 2008 19:23] Chuck Bell
Not pushed to main yet.
[10 Sep 2008 13:22] Øystein Grøvlen
Pushed to main (6.0.7) on Sep 5.
[10 Sep 2008 15:27] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080819153529-2q1xr9zeam8iltai) (version source revid:cbell@mysql.com-20080827130640-3q01lmazklr62bls) (pib:3)
[11 Sep 2008 22:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/53891

2693 He Zhenxing	2008-09-12 [merge]
      Merge 6.0 main -> 6.0-rpl
[13 Sep 2008 21:18] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080819153529-2q1xr9zeam8iltai) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)
[16 Sep 2008 9:11] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080819153529-2q1xr9zeam8iltai) (version source revid:konstantin@mysql.com-20080822124103-58z4hb7rxrxj590l) (pib:3)
[23 Sep 2008 11:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54481

2694 He Zhenxing	2008-09-22 [merge]
      Auto Merge
[30 Sep 2008 17:27] Paul Dubois
Noted in 6.0.7 changelog.

A new session system variable, backup_wait_timeout, controls the
number of seconds a BACKUP DATABASE or RESTORE operation waits for a
blocked DDL statements before aborting with an error.