Edit report at https://bugs.php.net/bug.php?id=72416&edit=1
ID: 72416
Updated by: andrey@php.net
Reported by: webart dot video at gmail dot com
Summary: mysqli_stmt::__construct(): Unknown type 18 sent by
the server.
-Status: Assigned
+Status: Feedback
Type: Bug
Package: MySQLi related
Operating System: Ubuntu 14.04 LTS
PHP Version: 7.0.7
Assigned To: mysql
Block user comment: N
Private report: N
Previous Comments:
------------------------------------------------------------------------
[2016-06-24 13:49:48] andrey@php.net
Hi again,
I just got time to look at the issue. I created the table and then decided to prepare the query from
the command line. Here is what I got :
-----
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'test.r.tripDay' which is not functionally dependent on columns in
GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
-----
By setting sql_mode to "" I am able to prepare the query. However, this is when I am using
a release build (MySQL 5.7.13). When I am trying to prepare in debug build the server crashes with
signal 6, which is an assert. To be able to isolate and be completely sure that things are really
broken in the server I need a bit of sample data, which means just 1-2 rows to fill into the table
so the in release build I can execute the statement and get some data back. Thank you very much in
advance!
If you need more details, here they are:
The code that asserts is:
Item::tmp_table_field_from_field_type()
6594 DBUG_ASSERT(0);
6595 /* If something goes awfully wrong, it's better to get a string than die */
Here is the stacktrace:
Program received signal SIGABRT, Aborted.
[Switching to Thread 0x7fffe14b9700 (LWP 11508)]
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=0x7fff70b18e58,
table=0x7fff70b1bbd0, 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=0x7fff70b18e58,
table=0x7fff70b1bbd0) at /work/mysql/mysql-5.7.13/sql/item.cc:10725
#6 0x00000000015a98eb in create_tmp_field (thd=0x7fff70000b70, table=0x7fff70b1bbd0,
item=0x7fff70b18e58, type=Item::TYPE_HOLDER, copy_func=0x7fff70038b40, from_field=0x7fff70b1cc48,
default_field=0x7fff70b1ca70, 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=0x7fff700075b0, 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=0x7fff70007590,
thd_arg=0x7fff70000b70, column_types=0x7fff70af2c80, 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=0x7fff70af2698, 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=0x7fff7002bae0, tables=0x7fff70b0eab8) at
/work/mysql/mysql-5.7.13/sql/sql_prepare.cc:1397
#11 0x000000000153b26a in check_prepared_statement (stmt=0x7fff7002bae0) at
/work/mysql/mysql-5.7.13/sql/sql_prepare.cc:1937
#12 0x000000000153e7ba in Prepared_statement::prepare (this=0x7fff7002bae0, query_str=0x7fff70024430
"(SELECT COUNT(id) AS numDays,\n", ' ' <repeats 12 times>,
"DATE_FORMAT(DATE_ADD(\n", ' ' <repeats 12 times>,
"MAKEDATE(YEAR(tripDay), 1),\n", ' ' <repeats 12 times>, "INTERVAL
MONTH(tripDay) - 1 MONTH),'%c/%e/%y') AS dWeek,\n", ' ' <repeats 12
times>, "ROUND(SUM(phone"..., query_length=2706) at
/work/mysql/mysql-5.7.13/sql/sql_prepare.cc:3327
#13 0x000000000153be35 in mysql_sql_stmt_prepare (thd=0x7fff70000b70) at
/work/mysql/mysql-5.7.13/sql/sql_prepare.cc:2313
#14 0x00000000014ff4d1 in mysql_execute_command (thd=0x7fff70000b70, first_level=true) at
/work/mysql/mysql-5.7.13/sql/sql_parse.cc:2769
#15 0x0000000001507010 in mysql_parse (thd=0x7fff70000b70, parser_state=0x7fffe14b81d0) at
/work/mysql/mysql-5.7.13/sql/sql_parse.cc:5525
#16 0x00000000014fc298 in dispatch_command (thd=0x7fff70000b70, com_data=0x7fffe14b8dd0,
command=COM_QUERY) at /work/mysql/mysql-5.7.13/sql/sql_parse.cc:1429
#17 0x00000000014fb1f1 in do_command (thd=0x7fff70000b70) at
/work/mysql/mysql-5.7.13/sql/sql_parse.cc:997
#18 0x0000000001633460 in handle_connection (arg=0x39bd590) at
/work/mysql/mysql-5.7.13/sql/conn_handler/connection_handler_per_thread.cc:301
#19 0x000000000187d11f in pfs_spawn_thread (arg=0x3b00ce0) at
/work/mysql/mysql-5.7.13/storage/perfschema/pfs.cc:2188
#20 0x00007ffff6f44184 in start_thread (arg=0x7fffe14b9700) at pthread_create.c:312
#21 0x00007ffff645137d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
------------------------------------------------------------------------
[2016-06-21 15:44:10] webart dot video at gmail dot com
Hey Andrey,
Check my gist:
https://gist.github.com/NoMan2000/0ee7fbf41f69a7ae4cdc555241e2e5f6
Has create table and select statement syntax and repro steps, and also a non-repro case.
Tested on Amazon RDS MySQL Databases, one is 5.1, one is 5.7. Haven't tested with 5.5 and 5.6.
------------------------------------------------------------------------
[2016-06-21 14:26:30] andrey@php.net
Here is what the MySQL documenation reads :
"The MYSQL_TYPE_TIME2, MYSQL_TYPE_DATETIME2, and MYSQL_TYPE_TIMESTAMP2) type codes are used
only on the server side. Clients see the MYSQL_TYPE_TIME, MYSQL_TYPE_DATETIME, and
MYSQL_TYPE_TIMESTAMP codes."
TIME2, DATETIME2 and TIMESTAMP2 are 17,18,19 . So it is interesting that you see on the client, as
they should not cross the wire.
------------------------------------------------------------------------
[2016-06-20 17:30:53] andrey@php.net
Hi,
can you tell us what MySQL version you are using and could you produce a bit minimal script that
generates the error and the crash.
I tried myself with 7.0 and 7.1 but no crash. Here is my table:
mysql> create table dt(a datetime(6));
Query OK, 0 rows affected (0,01 sec)
mysql> insert into dt values ('2014-09-08 17:51:04.123456');
Query OK, 1 row affected (0,01 sec)
mysql> select * from dt;
+----------------------------+
| a |
+----------------------------+
| 2014-09-08 17:51:04.123456 |
+----------------------------+
1 row in set (0,00 sec)
Then on the command line:
andrey@poohie:/work/dev/php/php-7.0$ ./php -r
'$c=mysqli_connect("127.0.0.1","root","","test");
$s=$c->prepare("select * from dt");
$s->execute();var_dump($s->get_result()->fetch_all());'
array(1) {
[0]=>
array(1) {
[0]=>
string(19) "2014-09-08 17:51:04"
}
}
And in wireshark I see that FIELD_DATETIME (12) is sent over the wire. Type 18 is DATETIME2, which
is DATETIME with microseconds. However, mysqlnd here doesn't see the microseconds as DATETIME
is sent. I am curious what triggered the server to send the microseconds and the new type. This is
why I would like to see a shorter example of your code that is self contained (or is one small PHP
and one small SQL dump file).
Thank you very much in advance!
Andrey
------------------------------------------------------------------------
[2016-06-19 07:00:55] laruence@php.net
5.6 is also affected
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
https://bugs.php.net/bug.php?id=72416
--
Edit this bug report at https://bugs.php.net/bug.php?id=72416&edit=1