A query on empty table with BLOBs may crash server

DROP DATABASE test;CREATE DATABASE test;USE test;
CREATE TABLE t1(a INT KEY,B TEXT)ENGINE=InnoDB;
explain extended select * FROM t1 x,t1 y where(x.a=2 or (x.a,x.b)in ((0,0),(5,0),(4,3))) and y.a=x.d and y.b=x.b;

http://bugs.mysql.com/bug.php?id=74644

Reproduces best in debug, but not a debug-only bug

I can reproduce the crash using the test case on PS 5.6.19 and PS 5.6.21, however, when I install jemalloc, no more crashing happens. So seems that using jemalloc may be a good enough workaround for now.

[root@vagrant-centos65 ~]# rpm -qa|grep Percona-Server
Percona-Server-shared-56-5.6.21-rel70.1.el6.x86_64
Percona-Server-shared-51-5.1.73-rel14.12.624.rhel6.x86_64
Percona-Server-client-56-5.6.21-rel70.1.el6.x86_64
Percona-Server-server-56-5.6.21-rel70.1.el6.x86_64

MySQL> DROP DATABASE test;CREATE DATABASE test;USE test;CREATE TABLE t1(a INT KEY,b TEXT)ENGINE=InnoDB;explain extended select * FROM t1 x,t1 y where(x.a=2 or (x.a,x.b)in ((0,0),(5,0),(4,3))) and y.a=x.a and y.b=x.b;
Query OK, 1 row affected (0.15 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.35 sec)

ERROR 2013 (HY000): Lost connection to MySQL server during query

Err log:
2014-12-02 11:47:39 10633 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.21-70.1' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 70.1, Revision 698
11:51:19 UTC - 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.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

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

Thread pointer: 0x2c3dfa0
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 = 7fcac40dfd40 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8bdcec]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x6512a1]
/lib64/libpthread.so.0(+0xf710)[0x7fcae304b710]
/lib64/libc.so.6(+0x89b78)[0x7fcae16feb78]
/lib64/libc.so.6(memmove+0x192)[0x7fcae16f8992]
/usr/sbin/mysqld(_ZN6String4copyERKS_+0x26)[0x716126]
/usr/sbin/mysqld(_ZN20cmp_item_sort_string11store_valueEP4Item+0x33)[0x5cdb43]
/usr/sbin/mysqld(_ZN12cmp_item_row11store_valueEP4Item+0x7c)[0x5c91ac]
/usr/sbin/mysqld(_ZN12Item_func_in18fix_length_and_decEv+0x8b4)[0x5c9c24]
/usr/sbin/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x1f5)[0x5e6d75]
/usr/sbin/mysqld(_ZN12Item_func_in10fix_fieldsEP3THDPP4Item+0x13)[0x5bfbd3]
/usr/sbin/mysqld(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0x106)[0x5bf576]
/usr/sbin/mysqld(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0x106)[0x5bf576]
/usr/sbin/mysqld(_Z11setup_condsP3THDP10TABLE_LISTS2_PP4Item+0x10a)[0x68a63a]
/usr/sbin/mysqld(_ZN4JOIN7prepareEP10TABLE_LISTjP4ItemjP8st_orderS5_S3_P13st_select_lexP18st_select_lex_unit+0x4de)[0x6ed...

Using Jemalloc might trade a clear crash for a silent server memory corruption elsewhere, I wouldn't consider it as a workaround.

I can finally reproduce this in debug and valgrind.

I can eliminate the EXPLAIN and just use this sequence shortened from Roels test above:
DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t1(a INT KEY,B TEXT)ENGINE=InnoDB; select * FROM t1 x,t1 y where(x.a=2 or (x.a,x.b)in ((0,0),(5,0),(4,3))) and y.a=x.d and y.b=x.b;

Look at the last two expressions of the where:
and y.a=x.d and y.b=x.b;

"and y.a=x.d and"

There is no 'd' in the table definition, this is causing the crash. Ideally the server shouldn't crash but return an error instead.

Valgrind+vgdb is giving me this:
==26044== Conditional jump or move depends on uninitialised value(s)
==26044== at 0x65206F: Item_field::val_str(String*) (item.cc:2673)
==26044== by 0x6864DD: cmp_item_sort_string::store_value(Item*) (item_cmpfunc.h:1166)
==26044== by 0x67BEEF: cmp_item_row::store_value(Item*) (item_cmpfunc.cc:4190)
==26044== by 0x67D296: Item_func_in::fix_length_and_dec() (item_cmpfunc.cc:4590)
==26044== by 0x69FF2D: Item_func::fix_fields(THD*, Item**) (item_func.cc:231)
==26044== by 0x67C6B8: Item_func_in::fix_fields(THD*, Item**) (item_cmpfunc.cc:4362)
==26044== by 0x67E101: Item_cond::fix_fields(THD*, Item**) (item_cmpfunc.cc:4838)
==26044== by 0x67E101: Item_cond::fix_fields(THD*, Item**) (item_cmpfunc.cc:4838)
==26044== by 0x76F8E2: setup_conds(THD*, TABLE_LIST*, TABLE_LIST*, Item**) (sql_base.cc:9003)
==26044== by 0x7FE8CB: setup_without_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, TABLE_LIST*, List<Item>&, List<Item>&, Item**, st_order*, st_order*, int*, int*) (sql_resolver.cc:952)
==26044== by 0x7FC1C0: JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, st_order*, Item*, st_select_lex*, st_select_lex_unit*) (sql_resolver.cc:177)
==26044== by 0x803D3B: mysql_prepare_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*, bool*) (sql_select.cc:1054)
==26044== Uninitialised value was created by a heap allocation
==26044== at 0x4C2AB80: malloc (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==26044== by 0xAA2918: my_malloc (my_malloc.c:38)
==26044== by 0xA9C14A: alloc_root (my_alloc.c:224)
==26044== by 0x899ED5: open_table_from_share(THD*, TABLE_SHARE*, char const*, unsigned int, unsigned int, unsigned int, TABLE*, bool) (table.cc:2168)
==26044== by 0x763CB1: open_table(THD*, TABLE_LIST*, Open_table_context*) (sql_base.cc:3205)
==26044== by 0x76635D: open_and_process_table(THD*, LEX*, TABLE_LIST*, unsigned int*, unsigned int, Prelocking_strategy*, bool, Open_table_context*) (sql_base.cc:4699)
==26044== by 0x7672C9: open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) (sql_base.cc:5213)
==26044== by 0x76843A: open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int) (sql_base.cc:5913)
==26044== by 0x7DA78F: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5589)
==26044== by 0x7D2E84: mysql_execute_command(THD*) (sql_parse.cc:2974)
==26044== by 0x7DD136: mysql_parse(THD*, char*, unsigned int, P...

My bad, changing the x.d to x.a and it still crashes, but still from the backtrace, memory probing and valgrind trace I can see easily that a field exists but is uninitialized/had totally bogus data within it.

Reduced further to:
DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t1(a INT KEY,B TEXT)ENGINE=InnoDB; select * FROM t1 x,t1 y where (x.a,x.b) in ((5,0),(4,3));

I tried with a few variations of the in set and it seems any 2 or more combinations of (x.a, x.b) will crash.

Ruling out type conversions and eliminating alias...still happens with:
DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t1(a INT KEY,b INT)ENGINE=InnoDB; select * FROM t1 where (t1.a,t1.b) in ((5,0),(4,3));

I am able to trigger the crash with:
DROP table t1; CREATE TABLE t1(a INT KEY,b text) ENGINE=InnoDB; select * FROM t1 where (a,b) in ((0,0),(5,0),(4,3));
but not with:
DROP table t1;CREATE TABLE t1(a INT KEY,b int)ENGINE=InnoDB; select * FROM t1 where (a,b) in ((0,0),(5,0),(4,3));

Tested on MySQL Community 5.6.21. Error log:
Thread pointer: 0x2bebb90
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 = 7f7cc321de50 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0x8efafb]
/usr/sbin/mysqld(handle_fatal_signal+0x491)[0x6841b1]
/lib64/libpthread.so.0[0x38e500f6d0]
/lib64/libc.so.6[0x38e494daf0]
/usr/sbin/mysqld(_ZN6String4copyERKS_+0x26)[0x743566]
/usr/sbin/mysqld(_ZN20cmp_item_sort_string11store_valueEP4Item+0x33)[0x5fed03]
/usr/sbin/mysqld(_ZN12cmp_item_row11store_valueEP4Item+0x7c)[0x5fa36c]
/usr/sbin/mysqld(_ZN12Item_func_in18fix_length_and_decEv+0x8b4)[0x5fade4]
/usr/sbin/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x21d)[0x61801d]
/usr/sbin/mysqld(_ZN12Item_func_in10fix_fieldsEP3THDPP4Item+0x13)[0x5f0b73]
/usr/sbin/mysqld(_Z11setup_condsP3THDP10TABLE_LISTS2_PP4Item+0x10a)[0x6bd1ca]
/usr/sbin/mysqld(_ZN4JOIN7prepareEP10TABLE_LISTjP4ItemjP8st_orderS5_S3_P13st_select_lexP18st_select_lex_unit+0x51b)[0x71bd6b]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x89e)[0x7254fe]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x175)[0x725745]
/usr/sbin/mysqld[0x58cd70]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x34a4)[0x701d14]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x3a8)[0x704fb8]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xf90)[0x7066d0]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x152)[0x6d48c2]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x6d4980]
/usr/sbin/mysqld(pfs_spawn_thread+0x143)[0xb355e3]
/lib64/libpthread.so.0[0x38e5007ee5]
/lib64/libc.so.6(clone+0x6d)[0x38e48f4b8d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f7c9c006cd0): select * FROM t1 where (a,b) in ((0,0),(5,0),(4,3))
Connection ID (thread ID): 1

Clarify, not crash, gets valgrind "Conditional jump or move depends on uninitialised value(s)". Only happens on a newly created, empty table or a truncated table. If you insert then delete rows to empty, doesn't happen and doesn't happen if there are any rows in the table.

Leaving this here in case anyone needs to pick this up while I am out next week...

So, during the select, something seems to not be properly parsing and setting up the item values for the JOIN comparison correctly...

With this simple query and a debug build:
DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t1(a INT KEY,b text) ENGINE=InnoDB; select * FROM t1 where (a,b) in ((0,0),(5,0),(4,3));

The crash is happening when an attempt is made to copy a string from a bogus memory address. That address lies within an Item_field->Field_blob and is copied into a String from within Field_blob::val_str. The thing is the Field_blob is bad and I'm still trying to figure out where that is getting set up from. The call stack where the bogus address is getting set on the object that is actually where the crash is on is:
#0 0x0000000000666350 in String::set (this=0x7fffa80058a0,
    str=0x7fffa8011b9000 <error: Cannot access memory at address 0x7fffa8011b9000>, arg_length=127,
    cs=0x16b76e0 <my_charset_latin1>) at /data/percona/ST-48128/5.6/sql/sql_string.h:258
#1 0x000000000091408f in Field_blob::val_str (this=0x7fffa8011c68, val_buffer=0x7fffa8006c90, val_ptr=0x7fffa80058a0)
    at /data/percona/ST-48128/5.6/sql/field.cc:7899
#2 0x00000000006520ce in Item_field::val_str (this=0x7fffa8005890, str=0x7fffa8006c90)
    at /data/percona/ST-48128/5.6/sql/item.cc:2676
#3 0x00000000006864de in cmp_item_sort_string::store_value (this=0x7fffa8006c28, item=0x7fffa8005890)
    at /data/percona/ST-48128/5.6/sql/item_cmpfunc.h:1166
#4 0x000000000067bef0 in cmp_item_row::store_value (this=0x7fffa8006b78, item=0x7fffa80059d0)
    at /data/percona/ST-48128/5.6/sql/item_cmpfunc.cc:4190
#5 0x000000000067d297 in Item_func_in::fix_length_and_dec (this=0x7fffa80061c0)
    at /data/percona/ST-48128/5.6/sql/item_cmpfunc.cc:4590
#6 0x000000000069ff2e in Item_func::fix_fields (this=0x7fffa80061c0, thd=0x209a950, ref=0x7fffa8006708)
    at /data/percona/ST-48128/5.6/sql/item_func.cc:231
#7 0x000000000067c6b9 in Item_func_in::fix_fields (this=0x7fffa80061c0, thd=0x209a950, ref=0x7fffa8006708)
    at /data/percona/ST-48128/5.6/sql/item_cmpfunc.cc:4362
#8 0x000000000076f8e3 in setup_conds (thd=0x209a950, tables=0x7fffa8005200, leaves=0x7fffa8005200, conds=0x7fffa8006708)
    at /data/percona/ST-48128/5.6/sql/sql_base.cc:9003
#9 0x00000000007fe8cc in setup_without_group (thd=0x209a950, ref_pointer_array=..., tables=0x7fffa8005200,
    leaves=0x7fffa8005200, fields=..., all_fields=..., conds=0x7fffa8006708, order=0x0, group=0x0,
    hidden_group_field_count=0x7fffa80065ec, hidden_order_field_count=0x7ffff04086c0)
    at /data/percona/ST-48128/5.6/sql/sql_resolver.cc:952
#10 0x00000000007fc1c1 in JOIN::prepare (this=0x7fffa80063c8, tables_init=0x7fffa8005200, wild_num=1, conds_init=0x7fffa80061c0,
    og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, select_lex_arg=0x209d5b0, unit_arg=0x209cf68)
    at /data/percona/ST-48128/5.6/sql/sql_resolver.cc:177
#11 0x0000000000803d3c in mysql_prepare_select (thd=0x209a950, tables=0x7fffa8005200, wild_num=1, fields=...,
    conds=0x7fffa80061c0, og_num=0, order=0x0, group=0x0, having=0x0, select_o...

OK, so this bogus value of ptr is getting set from table.cc:open_table_from_share during the call to move_field_offset:

2217 /* Setup copy of fields from share, but use the right alias and record */
2218 for (i=0 ; i < share->fields; i++, field_ptr++)
2219 {
2220 Field *new_field= share->field[i]->clone(&outparam->mem_root);
2221 *field_ptr= new_field;
2222 if (new_field == NULL)
2223 goto err;
2224 new_field->init(outparam);
2225 new_field->move_field_offset((my_ptrdiff_t) (outparam->record[0] -
2226 outparam->s->default_values));
2227 }

I don't know if this is valid or not but

(my_ptrdiff_t) (outparam->record[0] - outparam->s->default_values))

is calculating to

(gdb) p (my_ptrdiff_t)(outparam->record[0] - outparam->s->default_values)
$167 = -305392

which seems like a _really_ odd value to be moving an offset within a buffer.

Offset is OK. It is not offset within the buffer. It is offest to add to pointer for it to into another buffer after clone (memcpy) done.

The real reason for the crash is that outparam->record[0] is not initialized. It usually initialized later in JOIN::exec when records read from table or in many across the code with read_record to fill it with default values. JOIN::exec is too late for given SELECT query, because JOIN::prepare needs Field_blob::is_null which in case needs default values to be in place.

5.1 and 5.5 are not affected. The logic behind specific SELECT is different for these versions.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-176