Bug #32757 hang with sql_mode set when setting some global variables
Submitted: 27 Nov 2007 8:51 Modified: 31 Mar 2008 19:47
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.23-debug OS:Any
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any
Tags: SQL_MODE
Triage: D2 (Serious)

[27 Nov 2007 8:51] Shane Bester
Description:
I found some queries hang forever, but only when issued under a sql_mode of strict_all_tables:

mysql> set session sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_thread_concurrency = 10000;
 <hang>

see the uploaded debug traces to see what the difference is between having the sql_mode set and not having it set.

How to repeat:
set session sql_mode='STRICT_ALL_TABLES';
set global innodb_thread_concurrency = 10000;

Suggested fix:
Maybe some network buffer is not correctly setup? Hang occurs in the vio* routines.
[27 Nov 2007 8:54] Shane Bester
when a hang occurs

Attachment: bug32757_sqlmode_hang.log (application/octet-stream, text), 8.39 KiB.

[27 Nov 2007 8:54] Shane Bester
when no hang occurs

Attachment: bug32757_no_sqlmode_no_hang.log (application/octet-stream, text), 10.31 KiB.

[7 Jan 2008 1:20] Tatiana Azundris Nuernberg
fails here:
sql_plugin.cc:1895

static int check_func_long(THD *thd, struct st_mysql_sys_var *var,
                          void *save, st_mysql_value *value)
  ...
  return (thd->variables.sql_mode & MODE_STRICT_ALL_TABLES) &&
         (*(long *)save != (long) tmp);

sql_set_variables()  fails with
  =>   -1  ERROR, message not sent

returns to
mysql_execute_command (thd=0x8fd1f90) at sql_parse.cc:4463

lex->sql_command==SQLCOM_SET_OPTION (!)
res==TRUE (-1)
thd->net.report_error == 0

confirmed as not existent in 5.0 (5.0 corrects value silently),
introduced in sql_plugin.cc

return !(thd->variables.sql_mode & MODE_STRICT_ALL_TABLES) ? 0 :
       (fixed ? 1 : (*(long *)save != (long) tmp);
[7 Jan 2008 1:21] Tatiana Azundris Nuernberg
select @@session.sql_mode into @old_sql_mode;

set global innodb_thread_concurrency=500;
select @@global.innodb_thread_concurrency;
-- ULONG_MAX -- throw warning, set to maximum allowed value
set global innodb_thread_concurrency=-1;
select @@global.innodb_thread_concurrency;

set session sql_mode='STRICT_ALL_TABLES';
set global innodb_thread_concurrency=500;
select @@global.innodb_thread_concurrency;
-- ULONG_MAX -- throw warning, do NOT change value
set global innodb_thread_concurrency=-1;
select @@global.innodb_thread_concurrency;

set session sql_mode=@old_sql_mode;

set session old=bla;
show warnings;
[7 Jan 2008 1:42] 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/40611

ChangeSet@1.2654, 2008-01-07 02:42:12+01:00, tnurnberg@mysql.com +5 -0
  Bug#32757: hang with sql_mode set when setting some global variables
  
  If setting a system-variable provided by a plug-in failed, no OK or
  error was sent in some cases, hanging the client. We now send an error
  in the case from the ticket (integer-argument out of range in STRICT
  mode). We also provide a semi-generic fallback message for possible
  future cases like this where an error is signalled, but no message is
  sent to the client.
[7 Jan 2008 1:48] 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/40612

ChangeSet@1.2654, 2008-01-07 02:47:56+01:00, tnurnberg@mysql.com +5 -0
  Bug#32757: hang with sql_mode set when setting some global variables
  
  If setting a system-variable provided by a plug-in failed, no OK or
  error was sent in some cases, hanging the client. We now send an error
  in the case from the ticket (integer-argument out of range in STRICT
  mode). We also provide a semi-generic fallback message for possible
  future cases like this where an error is signalled, but no message is
  sent to the client.
[5 Feb 2008 11: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/41700

ChangeSet@1.2654, 2008-02-05 12:39:26+01:00, tnurnberg@blasphemy.intern.azundris.com +6 -0
  Bug#32757: hang with sql_mode set when setting some global variables
  
  If setting a system-variable provided by a plug-in failed, no OK or
  error was sent in some cases, hanging the client. We now send an error
  in the case from the ticket (integer-argument out of range in STRICT
  mode). We also provide a semi-generic fallback message for possible
  future cases like this where an error is signalled, but no message is
  sent to the client. The error/warning handling is unified so it's the
  same again for variables provided by plugins and those in the server
  proper.
[24 Feb 2008 12:45] 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/42917

ChangeSet@1.2669, 2008-02-24 13:45:01+01:00, tnurnberg@mysql.com +6 -0
  Bug#32757: hang with sql_mode set when setting some global variables
  
  If setting a system-variable provided by a plug-in failed, no OK or
  error was sent in some cases, hanging the client. We now send an error
  in the case from the ticket (integer-argument out of range in STRICT
  mode). We also provide a semi-generic fallback message for possible
  future cases like this where an error is signalled, but no message is
  sent to the client. The error/warning handling is unified so it's the
  same again for variables provided by plugins and those in the server
  proper.
[25 Feb 2008 2:06] Tatiana Azundris Nuernberg
pushed to 5.1.24-rc, 6.0.5-alpha in opt
[13 Mar 2008 19:28] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35] Bugs System
Pushed into 5.1.24-rc
[31 Mar 2008 19:47] Jon Stephens
Documetned in the 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:

        In some cases where setting a system variable failed, no error was sent to 
        the client, causing the client to hang.