MySQL Bugs: #32757: hang with sql_mode set when setting some global variables

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

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.

when a hang occurs

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

when no hang occurs

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

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);

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;

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.

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.

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.

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.

pushed to 5.1.24-rc, 6.0.5-alpha in opt

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

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.