|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #72416 mysqli_stmt::__construct(): Unknown type 18 sent by the server.
Submitted: 2016-06-15 15:06 UTC Modified: 2020-12-20 04:22 UTC
Avg. Score:4.3 ± 0.9
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: webart dot video at gmail dot com Assigned:
Status: No Feedback Package: MySQLi related
PHP Version: 7.0.7 OS: Ubuntu 14.04 LTS
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2016-06-15 15:06 UTC] webart dot video at gmail dot com
PHP Information:

PHP (cli) ( NTS )
Copyright (c) 1997-2016 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2016 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies
    with Xdebug v2.4.0, Copyright (c) 2002-2016, by Derick Rethans
    with blackfire v1.10.6,, by Blackfireio Inc.

Pretty simple, using $mysqli->prepare($query) crashes the server with the message 
"mysqli_stmt::__construct(): Unknown type 18 sent by the server. Please send a report to the developers"

Test script:

Refer bug:

The difference is that user is using PDO, I'm using mysqli, but the underlying cause seems to be the same.


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2016-06-19 07:00 UTC]
-Status: Open +Status: Verified -Assigned To: +Assigned To: mysql
 [2016-06-19 07:00 UTC]
5.6 is also affected
 [2016-06-20 17:30 UTC]
-Status: Verified +Status: Feedback
 [2016-06-20 17:30 UTC]
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("","root","","test"); $s=$c->prepare("select * from dt"); $s->execute();var_dump($s->get_result()->fetch_all());'
array(1) {
  array(1) {
    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!

 [2016-06-21 14:26 UTC]
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-21 15:44 UTC] webart dot video at gmail dot com
-Status: Feedback +Status: Assigned
 [2016-06-21 15:44 UTC] webart dot video at gmail dot com
Hey Andrey, 

Check my gist:

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-24 13:49 UTC]
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:
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/", 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/", 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/
#5  0x0000000000f20449 in Item_type_holder::make_field_by_type (this=0x7fff70b18e58, table=0x7fff70b1bbd0) at /work/mysql/mysql-5.7.13/sql/
#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/
#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/
#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/
#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/
#10 0x000000000153a0c5 in mysql_test_select (stmt=0x7fff7002bae0, tables=0x7fff70b0eab8) at /work/mysql/mysql-5.7.13/sql/
#11 0x000000000153b26a in check_prepared_statement (stmt=0x7fff7002bae0) at /work/mysql/mysql-5.7.13/sql/
#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/
#13 0x000000000153be35 in mysql_sql_stmt_prepare (thd=0x7fff70000b70) at /work/mysql/mysql-5.7.13/sql/
#14 0x00000000014ff4d1 in mysql_execute_command (thd=0x7fff70000b70, first_level=true) at /work/mysql/mysql-5.7.13/sql/
#15 0x0000000001507010 in mysql_parse (thd=0x7fff70000b70, parser_state=0x7fffe14b81d0) at /work/mysql/mysql-5.7.13/sql/
#16 0x00000000014fc298 in dispatch_command (thd=0x7fff70000b70, com_data=0x7fffe14b8dd0, command=COM_QUERY) at /work/mysql/mysql-5.7.13/sql/
#17 0x00000000014fb1f1 in do_command (thd=0x7fff70000b70) at /work/mysql/mysql-5.7.13/sql/
#18 0x0000000001633460 in handle_connection (arg=0x39bd590) at /work/mysql/mysql-5.7.13/sql/conn_handler/
#19 0x000000000187d11f in pfs_spawn_thread (arg=0x3b00ce0) at /work/mysql/mysql-5.7.13/storage/perfschema/
#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-24 13:50 UTC]
-Status: Assigned +Status: Feedback
 [2016-06-30 16:07 UTC] webart dot video at gmail dot com
Here's an insert statement you can test.
 [2016-07-03 04:22 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 [2016-07-03 04:48 UTC]
-Status: No Feedback +Status: Open
 [2016-07-06 14:45 UTC]
Hi, I am able to reproduce with the following:

CREATE TABLE `type18` (
  `tripDay` datetime NOT NULL,
  KEY `entity_index` (`tripDay`),
  KEY `day_index` (`tripDay`)

INSERT INTO `type18` VALUES ('2014-04-03 00:00:00');

$foo = mysqli_connect('', 'root', '', 'test');
$query = "(SELECT
            DATE_FORMAT(DATE_ADD(MAKEDATE(YEAR(tripDay), 1), INTERVAL MONTH(tripDay) - 1 MONTH),'%c/%e/%y'),
            FROM type18
			ORDER BY YEAR(tripDay))
            ORDER BY YEAR(tripDay)";
$foo->query("set sql_mode=''");
$x = $foo->prepare($query);
 [2016-07-07 08:20 UTC]
Even shorter one:
CREATE TABLE `type18` (
  `tripDay` datetime NOT NULL

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);
 [2016-07-07 08:42 UTC]
Hi, I have opened a MySQL server bug report as this is a server bug
Bug #82139 MySQL server sends internal column type over the wire
 [2020-12-10 16:13 UTC]
-Status: Assigned +Status: Feedback -Assigned To: mysql +Assigned To:
 [2020-12-10 16:13 UTC]
Does anyone still experience this issue on a current version of MySQL and PHP?

At least using MySQL 8.0.22 this does not reproduce any more, so presumably the issue has been fixed on the server side, even though the bug report has not been closed.
 [2020-12-20 04:22 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
PHP Copyright © 2001-2023 The PHP Group
All rights reserved.
Last updated: Sun Dec 10 10:01:27 2023 UTC