Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.21
-
None
-
AWS Linux
Description
I don't have a testcase, I only have a customer's core file.
As far as I was able to investigate, the following has happened:
1. There is a big query with nested SELECTs and non-recursive CTEs. Some CTEs are used multiple times.
2. The query is submitted to server as a Prepared Statement. The prepared statement has many '?' parameters.
3. [At least] one of the parameters is inside in a CTE that's reused and it used like so:
WHERE |
table.key= ? |
We crash in optimize_keyuse(), when we are trying to process a KEYUSE element created from the above equality.
We crash somewhere here:
|
|
if (keyuse->used_tables & |
(map= (keyuse->used_tables & ~join->const_table_map &
|
~OUTER_REF_TABLE_BIT)))
|
{
|
uint n_tables= my_count_bits(map);
|
if (n_tables == 1) // Only one table |
{
|
Table_map_iterator it(map);
|
int tablenr= it.next_bit(); |
DBUG_ASSERT(tablenr != Table_map_iterator::BITMAP_END);
|
TABLE *tmp_table=join->table[tablenr];
|
if (tmp_table) // already created |
keyuse->ref_table_rows= MY_MAX(tmp_table->file->stats.records, 100);
|
}
|
The cause is that the keyuse has:
keyuse->used_tables = PARAM_TABLE_BIT
|
keyuse->val = Item_param
|
With PARAM_TABLE_BIT, the "if (n_tables == 1)" branch is taken, and we get a tablenr value, but join->table[tablenr] is not pointing to a valid TABLE object hence we get a poorly-reproducible crash.
We should not get {{ keyuse->used_tables = PARAM_TABLE_BIT}}. We do, because
$keyuse->val->state == Item_param::NO_VALUE
|
(which also shouldn't be happening).
There is Prepared_statement::param_array which is an array of "primary" parameter instances, and
$keyuse->val != $prepared_statement->param_array[$i] for any value of $i.
|
However, I can find $keyuse->val in
$prepared_statement->param_array[$a].m_clones.m_array[$b]
|
That is, it is a "secondary" Item_param object that was created for a CTE.
Further, I observe that
- The "primary" Item_param objects from $prepared_statement->param_array[i] all have state=Item_param::SHORT_DATA_VALUE or other similar states
- The "secondary" Item_param objects from "m_clones" arrays all have state=Item_param::NO_VALUE.
Attachments
Activity
Decoding the Client's packet
(gdb) x/1024xb $thd->net.read_pos
|
0x17 0x08 0x00 0x00 0x00 0x00 0x01 0x00
|
|=COM_=| |===== statement_id =======| |=flags| |== iteration_
|
0x00 0x00 0x00 0x00 0x00 0x01
|
_count======| |===== null-bitmap ==| |=send_type=|
|
|
0xfd 0x00 - parameter# 1
|
0xfd 0x00 - parameter# 2
|
0xfd 0x00 - parameter# 3
|
0xfd 0x00 - parameter# 4
|
0xfd 0x00 - parameter# 5
|
0xfd 0x00 - parameter# 6
|
0xfd 0x00 - parameter# 7
|
0xfd 0x00 - parameter# 8
|
0xfd 0x00 - parameter# 9
|
0xfd 0x00 - parameter# 10
|
0xfd 0x00 - parameter# 11
|
0xfd 0x00 - parameter# 12
|
0xfd 0x00 - parameter# 13
|
0xfd 0x00 - parameter# 14
|
0xfd 0x00 - parameter# 15
|
0xfd 0x00 - parameter# 16
|
0xfd 0x00 - parameter# 17
|
0xfd 0x00 - parameter# 18
|
0xfd 0x00 - parameter# 19
|
0xfd 0x00 - parameter# 20
|
0xfd=253=MYSQL_TYPE_VAR_STRING (see
https://mariadb.com/kb/en/resultset/#field-types)
Noe that send_type=1... This goes against the "exotic client didn't send the
types" hypothesis?
No, one doesn't need an "exotic" client after all. I was mistaken.
The steps to reproduce are:
Apply this patch:
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
|
index ae26458d451..ff9b024756c 100644
|
--- a/sql/sql_select.cc
|
+++ b/sql/sql_select.cc
|
@@ -6540,6 +6540,7 @@ void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array)
|
uint n_tables= my_count_bits(map);
|
if (n_tables == 1) // Only one table
|
{
|
+ DBUG_ASSERT(!(map & PSEUDO_TABLE_BITS)); // Must be a real table
|
Table_map_iterator it(map);
|
int tablenr= it.next_bit();
|
DBUG_ASSERT(tablenr != Table_map_iterator::BITMAP_END); |
Then, start the server with slow-query-log enabled.
Then, prepare the dataset:
create table t1(a int, b int, key(a)); |
insert into t1 (a) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
Then, prepare and execute this Prepared Statement. It must be done through client-server binary protocol.
explain
|
with T as |
(
|
select * from t1 where t1.a=? limit 2 |
)
|
select * from T as TA, T as TB;; |
Here is a program that does that using Connector/C: https://gist.github.com/spetrunia/a3b98df451f8a8e42df51319181a259a
This will cause the assert to fire on the server:
Thread 34 "mysqld" received signal SIGABRT, Aborted.
|
[Switching to Thread 0x7fffd981f700 (LWP 13761)]
|
__GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
|
51 ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
|
(gdb) wher
|
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
|
#1 0x00007ffff5225801 in __GI_abort () at abort.c:79
|
#2 0x00007ffff521539a in __assert_fail_base (fmt=0x7ffff539c7d8 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x555556876df8 "!(map & ((((table_map) 1) << (sizeof(table_map)*8-3)) | (((table_map) 1) << (sizeof(table_map)*8-2)) | (((table_map) 1) << (sizeof(table_map)*8-1))))", file=file@entry=0x555556876008 "/home/psergey/dev-git/10.3-cl/sql/sql_select.cc", line=line@entry=6543, function=function@entry=0x555556879a80 <optimize_keyuse(JOIN*, st_dynamic_array*)::__PRETTY_FUNCTION__> "void optimize_keyuse(JOIN*, DYNAMIC_ARRAY*)") at assert.c:92
|
#3 0x00007ffff5215412 in __GI___assert_fail (assertion=0x555556876df8 "!(map & ((((table_map) 1) << (sizeof(table_map)*8-3)) | (((table_map) 1) << (sizeof(table_map)*8-2)) | (((table_map) 1) << (sizeof(table_map)*8-1))))", file=0x555556876008 "/home/psergey/dev-git/10.3-cl/sql/sql_select.cc", line=6543, function=0x555556879a80 <optimize_keyuse(JOIN*, st_dynamic_array*)::__PRETTY_FUNCTION__> "void optimize_keyuse(JOIN*, DYNAMIC_ARRAY*)") at assert.c:101
|
#4 0x0000555555c8dd80 in optimize_keyuse (join=0x7fff68015078, keyuse_array=0x7fff68015368) at /home/psergey/dev-git/10.3-cl/sql/sql_select.cc:6543
|
#5 0x0000555555c8998d in make_join_statistics (join=0x7fff68015078, tables_list=..., keyuse_array=0x7fff68015368) at /home/psergey/dev-git/10.3-cl/sql/sql_select.cc:5118
|
#6 0x0000555555c7e67d in JOIN::optimize_inner (this=0x7fff68015078) at /home/psergey/dev-git/10.3-cl/sql/sql_select.cc:1942
|
(gdb) up
|
#4 0x0000555555c8dd80 in optimize_keyuse (join=0x7fff68015078, keyuse_array=0x7fff68015368) at /home/psergey/dev-git/10.3-cl/sql/sql_select.cc:6543
|
(gdb) p/x map
|
$89 = 0x2000000000000000
|
(gdb) p/x PARAM_TABLE_BIT
|
$91 = 0x2000000000000000
|
There is a call to Item_param::sync_clones made in EXECUTE:
#0 Item_param::sync_clones (this=0x7fff68073ad8) at /home/psergey/dev-git/10.3-cl/sql/item.cc:4138
|
#1 0x0000555555c5a048 in set_conversion_functions (stmt=0x7fff680708f0, data=0x7fffd981e268, data_end=0x7fff68008ee2 "") at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:974
|
#2 0x0000555555c5a139 in setup_conversion_functions (stmt=0x7fff680708f0, data=0x7fffd981e268, data_end=0x7fff68008ee2 "", bulk_protocol=false) at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:995
|
#3 0x0000555555c61491 in Prepared_statement::set_parameters (this=0x7fff680708f0, expanded_query=0x7fffd981e340, packet=0x7fff68008edc "\003\200\002", packet_end=0x7fff68008ee2 "") at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:4135
|
#4 0x0000555555c61624 in Prepared_statement::execute_loop (this=0x7fff680708f0, expanded_query=0x7fffd981e340, open_cursor=false, packet=0x7fff68008eda "", packet_end=0x7fff68008ee2 "") at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:4205
|
#5 0x0000555555c5eed5 in mysql_stmt_execute_common (thd=0x7fff68000d50, stmt_id=1, packet=0x7fff68008eda "", packet_end=0x7fff68008ee2 "", cursor_flags=0, bulk_op=false, read_types=false) at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:3235
|
#6 0x0000555555c5ea35 in mysqld_stmt_execute (thd=0x7fff68000d50, packet_arg=0x7fff68008ed1 "\001", packet_length=17) at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:3132
|
#7 0x0000555555c3072d in dispatch_command (command=COM_STMT_EXECUTE, thd=0x7fff68000d50, packet=0x7fff68008ed1 "\001", packet_length=17, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.3-cl/sql/sql_parse.cc:1798
|
But this one "distributes" the the no-value state:
(gdb) p this->state
|
$98 = Item_param::NO_VALUE
|
Then, Prepared_statement::set_parameters calls set_params
which points to insert_params_with_log().
insert_params_with_log doesn't have the
param->sync_clones();
|
call while insert_params() and other such functions do
Fix Version/s: 10.5.4, 10.2.33, 10.3.24, 10.4.14
A correction: the fix is in the source for these MariaDB versions: 10.5.5, 10.2.33, 10.3.24, 10.4.14 (none of them is released yet at the moment).
It is not available in 10.5.4.
Documentation about Execute in the protocol: https://mariadb.com/kb/en/com_stmt_execute/
According to Diego,