MySQL Bugs: #41437: Value stored in 'case' lacks charset, causes segfault

Bug #41437	Value stored in 'case' lacks charset, causes segfault
Submitted:	12 Dec 2008 15:03	Modified:	28 Jan 2009 21:34
Reporter:	Adam Erickson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0/5.1/6.0	OS:	Any (2.6.18-53.1.14.el5 (SMP, 64 bit))
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	case, character set, crash, mysqld, segfault, strict_trans_tables

Description:
It looks like 'safe_charset_converter' may not be so safe.  When comparing a variable that was initialized by @var := CASE... the server crashes.

081212  8:27:45 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=2147483648
read_buffer_size=67108864
max_used_connections=1
max_threads=300
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 41421801 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x826daa0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x4510b110 thread_stack 0x40000
/opt/mysql/bin/mysqld(my_print_stacktrace+0x20) [0x9fa460]
/opt/mysql/bin/mysqld(handle_segfault+0x368) [0x5fa992]
/lib64/libpthread.so.0 [0x3f64e0de70]
/opt/mysql/bin/mysqld(Item_func_sysconst::safe_charset_converter(charset_info_st*)+0x69) [0x5792b7]
/opt/mysql/bin/mysqld(agg_item_charsets(DTCollation&, char const*, Item**, unsigned int, unsigned int, int)+0x46d) [0x528597]
/opt/mysql/bin/mysqld(Item_bool_func2::fix_length_and_dec()+0xa9) [0x561c81]
/opt/mysql/bin/mysqld(Item_func::fix_fields(THD*, Item**)+0x19b) [0x541fa1]
/opt/mysql/bin/mysqld(Item_func::fix_fields(THD*, Item**)+0xa5) [0x541eab]
/opt/mysql/bin/mysqld(Item_func_if::fix_fields(THD*, Item**)+0x39) [0x55e595]
/opt/mysql/bin/mysqld(setup_fields(THD*, Item**, List<Item>&, enum_mark_columns, List<Item>*, bool)+0x121) [0x64c04f]
/opt/mysql/bin/mysqld(JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*)+0x228) [0x665c3e]
/opt/mysql/bin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x6fc) [0x6659e4]
/opt/mysql/bin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x16c) [0x68dce8]
/opt/mysql/bin/mysqld [0x6109e4]
/opt/mysql/bin/mysqld(mysql_execute_command(THD*)+0x49e6) [0x60aca0]
/opt/mysql/bin/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x20a) [0x610f7a]
/opt/mysql/bin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x1344) [0x60576a]
/opt/mysql/bin/mysqld(do_command(THD*)+0x114) [0x604422]
/opt/mysql/bin/mysqld(handle_one_connection+0xd31) [0x5ff799]
/lib64/libpthread.so.0 [0x3f64e062f7]
/lib64/libc.so.6(clone+0x6d) [0x3f642ce85d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8283c90 = SELECT IF(@testMe != database(), 'True','False') as 'Check'
thd->thread_id=3
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
081212 08:27:45 mysqld_safe Number of processes running now: 0
081212 08:27:45 mysqld_safe mysqld restarted
081212  8:27:45 [Warning] The syntax '--log' is deprecated and will be removed in MySQL 7.0. Please use '--general_log'/'--general_log_file' instead.
081212  8:27:45 [Warning] The syntax '--log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '--slow_query_log'/'--slow_query_log_file' instead.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
081212  8:27:46  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 242273, file name ./mysql-bin.000216
081212  8:27:47  InnoDB: Started; log sequence number 133 3829118559
081212  8:27:47 [Note] Recovering after a crash using mysql-bin
081212  8:27:47 [Note] Starting crash recovery...
081212  8:27:47 [Note] Crash recovery finished.
081212  8:27:47 [Note] Event Scheduler: Loaded 0 events
081212  8:27:47 [Note] /opt/mysql/bin/mysqld: ready for connections.
Version: '5.1.30-log'  socket: '/tmp/mysql.dbnode2.sock'  port: 3306  MySQL Community Server (GPL)

How to repeat:

Execute:
SELECT @testMe := case @@hostname when 'this' then 'that' end as 'Foo';
SELECT IF(@testMe != database(), 'True','False') as 'Check';

We have crashed two different machines with this statement now.

Suggested fix:
Not segfault.

Thank you for the bug report. On my first test on Windows I was able to crash 5.1.29 but not 5.1.30. Could you please provide your my.cnf?. Thanks in advance.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.29-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT @testMe := case @@hostname when 'this' then 'that' end as
    -> 'Foo';
+------+
| Foo  |
+------+
| NULL |
+------+
1 row in set (0.05 sec)

mysql> SELECT IF(@testMe != database(), 'True','False') as 'Check';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

C:\temp\mysql-5.1.30-win32>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT @testMe := case @@hostname when 'this' then 'that' end as 'Foo';
+------+
| Foo  |
+------+
| NULL |
+------+
1 row in set (0.03 sec)

mysql> SELECT IF(@testMe != database(), 'True','False') as 'Check';
+-------+
| Check |
+-------+
| False |
+-------+
1 row in set (0.03 sec)

mysql> exit
Bye

Thank you for the feedback. Now I was able to repeat adding to my.cnf file:

sql_mode=strict_trans_tables

Repeatable om 5.0 and 6.0 too.

c:\dbs>c:\dbs\5.1\bin\mysqld --defaults-file=c:\dbs\5.1\my.ini --standalone --console
081212 15:04:42  InnoDB: Started; log sequence number 0 57253
081212 15:04:42 [Note] Event Scheduler: Loaded 0 events
081212 15:04:42 [Note] c:\dbs\5.1\bin\mysqld: ready for connections.
Version: '5.1.31-nt-debug-log'  socket: ''  port: 3510  Source distribution
081212 15:05:17 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388572
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337709 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x1eb0f78
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
004483E1    mysqld.exe!String::charset()[sql_string.h:92]
004F9BE7    mysqld.exe!Item_func_sysconst::safe_charset_converter()[item_strfunc.cc:1785]
0047FE17    mysqld.exe!agg_item_charsets()[item.cc:1709]
004AF8D0    mysqld.exe!Item_func::agg_arg_charsets()[item_func.h:186]
0049ADAB    mysqld.exe!Item_bool_func2::fix_length_and_dec()[item_cmpfunc.cc:481]
004D5226    mysqld.exe!Item_func::fix_fields()[item_func.cc:198]
004D5065    mysqld.exe!Item_func::fix_fields()[item_func.cc:171]
004A0FE4    mysqld.exe!Item_func_if::fix_fields()[item_cmpfunc.cc:2295]
00617153    mysqld.exe!setup_fields()[sql_base.cc:7398]
006B385E    mysqld.exe!JOIN::prepare()[sql_select.cc:502]
006B9658    mysqld.exe!mysql_select()[sql_select.cc:2351]
006B3219    mysqld.exe!handle_select()[sql_select.cc:269]
00679B49    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4890]
00672578    mysqld.exe!mysql_execute_command()[sql_parse.cc:2184]
0067BBF1    mysqld.exe!mysql_parse()[sql_parse.cc:5789]
006706CE    mysqld.exe!dispatch_command()[sql_parse.cc:1200]
0066FDB7    mysqld.exe!do_command()[sql_parse.cc:857]
00780AF4    mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
008498A6    mysqld.exe!pthread_start()[my_winthread.c:85]
009B93B7    mysqld.exe!_threadstart()[thread.c:196]
7C80B713    kernel32.dll!GetModuleFileNameA()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 01EFEC58=SELECT IF(@testMe != database(), 'True','False') as 'Check'
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Can't reproduce with the latest 5.0-main. 
Tried : 
set SQL_MODE=strict_trans_tables;
SELECT @testMe := case @@hostname when 'this' then 'that' end as 'Foo';
Foo
NULL
SELECT IF(@testMe != database(), 'True','False') as 'Check';
Check
False

Can't repeat it on Fedora 10 with 5.0 compiled from 

bk-internal.mysql.com/bzrroot/server/mysql-5.0/ 

revno: 2724
committer: Georgi Kodinov <joro@sun.com>
branch nick: fix-5.0-bugteam
timestamp: Mon 2009-01-05 18:04:14 +0200

simpler testcase:

mysql -uroot -e"select @z:='1',@z=database()"

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/62682

2725 Georgi Kodinov	2009-01-08
      Bug #41437: Value stored in 'case' lacks charset, causes segfault
      
      Character set conversion functions were not expecting NULL
      values returned when converting to string.
      As a result they were dereferencing the NULL returned by ::val_str()
      in such cases.
      Fixed by checking and returning a constant Item_null instead of 
      Item_string.

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/62803

2725 Georgi Kodinov	2009-01-09
      Bug #41437: Value stored in 'case' lacks charset, causes segfault
      
      When substituting system constant functions with a constant result
      the server was not expecting that the function may return NULL.
      Fixed by checking for NULL and returning Item_null (in the relevant
      collation) if the result of the system constant function was NULL.

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/63197

2748 He Zhenxing	2009-01-14 [merge]
      Auto merge

Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:davi.arnaut@sun.com-20090113150631-y84w12o2zmh7j3qd) (merge vers: 5.1.31) (pib:6)

Pushed into 5.0.77 (revid:joro@sun.com-20090115043518-cn6jeeg78j6mupp3) (version source revid:azundris@mysql.com-20090109220607-5ygzffhznqomnf8k) (merge vers: 5.0.76) (pib:6)

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:igor@mysql.com-20090110022839-s9xt3acu2yq33n26) (merge vers: 6.0.10-alpha) (pib:6)

Noted in 5.0.78, 5.1.31, 6.0.10 changelogs.

When substituting system constant functions with a constant result,
the server was not expecting NULL function return values and could
crash.

Noted in 5.0.74sp1 changelog.

another testcase:

mysql -uroot -p

drop table if exists test.t1,test.v1;
create table test.t1(id varchar(255))engine=myisam;
create or replace view test.v1 as select id from test.t1 where id=database();
show create table test.v1; #crashes on <5.0.78