Bug #82139 MySQL server sends internal column type over the wire
Submitted: 7 Jul 2016 8:41 Modified: 7 Jul 2016 10:13
Reporter: Andrey Hristov Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:5.7.13 OS:Any
Assigned to: CPU Architecture:Any

[7 Jul 2016 8:41] Andrey Hristov
Since MySQL 5.6 there is a new datatype with ordinal number 18 (decimal). According to notes about the C/S the 3 new datatypes: 0x11, 0x12, 0x13 should not appear on the wire. They are used internally to mark that the temporal value has microseconds. The C/S protocol supported microseconds before the server started to and thus it does not need an update.
A new datatype(or few datatypes) that should appear on the wire should be introduced with a corresponding CLIENT_ flag, so negotiation about the client capabilities during the handshake can be made. However, there is no such flag, as these types are private.

This bug breaks old clients!

How to repeat:
You need PHP (the example is pretty simple and can be translated to any language that has MySQL binding)
CREATE TABLE `type18` (`tripDay` datetime NOT NULL) ENGINE=InnoDB;
INSERT INTO `type18` VALUES ('2014-04-03 00:00:00');

$conn = mysqli_connect('', 'root', '', 'test');
$conn->query("set sql_mode=''");

$sql = "(SELECT tripDay FROM type18 ORDER BY YEAR(tripDay)) ORDER BY YEAR(tripDay)";
$stmt = $conn->prepare($sql);
var_dump($stmt, $conn->error);

The output is:
Warning: mysqli::prepare(): Unknown type 18 sent by the server. Please send a report to the developers in /work/dev/php/php-7.0/a.php on line 14
string(0) ""

A wireshark running during the execution also proves that the server is sending 0x12 as datatype. Here is the dump of the metadata packet about the column
0000   1d 00 00 02 03 64 65 66 00 00 00 07 74 72 69 70  .....def....trip
0010   44 61 79 00 0c 21 00 39 00 00 00 12 01 00 00 00  Day..!.9........
0020   00

When the script is run agains a debug build (the results above are from a release build) then the server crashes with an assert. The stacktrace is:
2016-07-07T08:16:04.362698Z 0 [Note] /work/mysql/mysql-5.7.13/sql/mysqld: ready for connections.
Version: '5.7.13-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 0x7fffe14b9700 (LWP 5391)]
mysqld: /work/mysql/mysql-5.7.13/sql/item.cc:6594: Field* Item::tmp_table_field_from_field_type(TABLE*, bool): Assertion `0' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 0x7fffe14b9700 (LWP 5391)]
0x00007ffff638dc37 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
(gdb) bt
#0  0x00007ffff638dc37 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
#1  0x00007ffff6391028 in __GI_abort () at abort.c:89
#2  0x00007ffff6386bf6 in __assert_fail_base (fmt=0x7ffff64d73b8 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x1dc4eed "0", file=file@entry=0x1dc5190 "/work/mysql/mysql-5.7.13/sql/item.cc", line=line@entry=6594, function=function@entry=0x1dc8560 <Item::tmp_table_field_from_field_type(TABLE*, bool)::__PRETTY_FUNCTION__> "Field* Item::tmp_table_field_from_field_type(TABLE*, bool)") at assert.c:92
#3  0x00007ffff6386ca2 in __GI___assert_fail (assertion=0x1dc4eed "0", file=0x1dc5190 "/work/mysql/mysql-5.7.13/sql/item.cc", line=6594, function=0x1dc8560 <Item::tmp_table_field_from_field_type(TABLE*, bool)::__PRETTY_FUNCTION__> "Field* Item::tmp_table_field_from_field_type(TABLE*, bool)") at assert.c:101
#4  0x0000000000f13d83 in Item::tmp_table_field_from_field_type (this=0x7fff700057e8, table=0x7fff701bdd20, fixed_length=false) at /work/mysql/mysql-5.7.13/sql/item.cc:6594
#5  0x0000000000f20449 in Item_type_holder::make_field_by_type (this=0x7fff700057e8, table=0x7fff701bdd20) at /work/mysql/mysql-5.7.13/sql/item.cc:10725
#6  0x00000000015a98eb in create_tmp_field (thd=0x7fff70000b70, table=0x7fff701bdd20, item=0x7fff700057e8, type=Item::TYPE_HOLDER, copy_func=0x7fff701bd910, from_field=0x7fff701bea98, default_field=0x7fff701bea78, group=false, modify_item=false, table_cant_handle_bit_fields=false, make_copy_field=false) at /work/mysql/mysql-5.7.13/sql/sql_tmp_table.cc:394
#7  0x00000000015ab2ee in create_tmp_table (thd=0x7fff70000b70, param=0x7fff70005730, fields=..., group=0x0, distinct=false, save_sum_fields=true, select_options=2416188160, rows_limit=18446744073709551615, table_alias=0x207c073 "") at /work/mysql/mysql-5.7.13/sql/sql_tmp_table.cc:961
#8  0x00000000015b601f in Query_result_union::create_result_table (this=0x7fff70005710, thd_arg=0x7fff70000b70, column_types=0x7fff70010f90, is_union_distinct=false, options=2416188160, table_alias=0x207c073 "", bit_fields_as_long=false, create_table=true) at /work/mysql/mysql-5.7.13/sql/sql_union.cc:132
#9  0x00000000015b72c2 in st_select_lex_unit::prepare (this=0x7fff700109a8, thd_arg=0x7fff70000b70, sel_result=0x0, added_options=0, removed_options=0) at /work/mysql/mysql-5.7.13/sql/sql_union.cc:599
#10 0x000000000153a0c5 in mysql_test_select (stmt=0x7fff7000f460, tables=0x7fff701ba868) at /work/mysql/mysql-5.7.13/sql/sql_prepare.cc:1397
#11 0x000000000153b26a in check_prepared_statement (stmt=0x7fff7000f460) at /work/mysql/mysql-5.7.13/sql/sql_prepare.cc:1937
#12 0x000000000153e7ba in Prepared_statement::prepare (this=0x7fff7000f460, query_str=0x7fff7000a331 "(SELECT tripDay FROM type18 ORDER BY YEAR(tripDay)) ORDER BY YEAR(tripDay)", query_length=74) at /work/mysql/mysql-5.7.13/sql/sql_prepare.cc:3327
#13 0x000000000153b809 in mysqld_stmt_prepare (thd=0x7fff70000b70, query=0x7fff7000a331 "(SELECT tripDay FROM type18 ORDER BY YEAR(tripDay)) ORDER BY YEAR(tripDay)", length=74) at /work/mysql/mysql-5.7.13/sql/sql_prepare.cc:2128
#14 0x00000000014fbfd3 in dispatch_command (thd=0x7fff70000b70, com_data=0x7fffe14b8dd0, command=COM_STMT_PREPARE) at /work/mysql/mysql-5.7.13/sql/sql_parse.cc:1383
#15 0x00000000014fb1f1 in do_command (thd=0x7fff70000b70) at /work/mysql/mysql-5.7.13/sql/sql_parse.cc:997
#16 0x0000000001633460 in handle_connection (arg=0x39a9870) at /work/mysql/mysql-5.7.13/sql/conn_handler/connection_handler_per_thread.cc:301
#17 0x000000000187d11f in pfs_spawn_thread (arg=0x39cd3d0) at /work/mysql/mysql-5.7.13/storage/perfschema/pfs.cc:2188
#18 0x00007ffff6f44184 in start_thread (arg=0x7fffe14b9700) at pthread_create.c:312
#19 0x00007ffff645137d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
[7 Jul 2016 10:13] Umesh Shastry
Hello Andrey,

Thank you for the report and test case.