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
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
New Comment:
Even shorter one:
<?php
/*
CREATE TABLE type18 (
tripDay datetime NOT NULL
) ENGINE=InnoDB;
INSERT INTO type18 VALUES ('2014-04-03 00:00:00');
*/
$conn = mysqli_connect('127.0.0.1', '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);
?>
Previous Comments:
------------------------------------------------------------------------
[2016-07-06 14:45:29] andrey@php.net
Hi, I am able to reproduce with the following:
<?php
/*
CREATE TABLE type18 (
tripDay datetime NOT NULL,
KEY entity_index (tripDay),
KEY day_index (tripDay)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO type18 VALUES ('2014-04-03 00:00:00');
*/
$foo = mysqli_connect('127.0.0.1', 'root', '', 'test');
$query = "(SELECT
DATE_FORMAT(DATE_ADD(MAKEDATE(YEAR(tripDay), 1), INTERVAL MONTH(tripDay) - 1
MONTH),'%c/%e/%y'),
tripDay
FROM type18
ORDER BY YEAR(tripDay))
ORDER BY YEAR(tripDay)";
$foo->query("set sql_mode=''");
$x = $foo->prepare($query);
var_dump($foo->error);
?>
------------------------------------------------------------------------
[2016-07-03 04:22:21] 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-06-30 16:07:05] webart dot video at gmail dot com
Here's an insert statement you can test.
https://gist.github.com/NoMan2000/0ee7fbf41f69a7ae4cdc555241e2e5f6#file-insert-sql
------------------------------------------------------------------------
[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.
------------------------------------------------------------------------
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