Details
Critical Description
Several crashes reported on update table left join with a view
Attachments
Issue Links
Activity
| Field | Original Value | New Value |
|---|---|---|
| Labels | join optimizer update view |
| Component/s | Views [ 10111 ] |
| Comment | [ A comment with security level 'Users' was removed. ] |
| Due Date | 2015-02-27 |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 5.5.40 [ 17100 ] | |
| Assignee | Sergei Petrunia [ psergey ] | |
| Due Date | 2015-02-27 | |
| Labels | join optimizer update view | join optimizer update verified view |
| Assignee | Sergei Petrunia [ psergey ] | Oleksandr Byelkin [ sanja ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
Looks like duplicate of https://mariadb.atlassian.net/browse/MDEV-6892
(I'll make testing on modern tree...)
Let it stay here:
--no-defaults --basedir=/home/bell/maria/git/server/ --datadir=/home/bell/maria/git/server/mysql-test/restoregit_3306/data/ --tmpdir=/home/bell/maria/git/server/mysql-test/var/tmp/ --core-file --lc-messages-dir=/home/bell/maria/git/server/sql/share/ --character-sets-dir=/home/bell/maria/git/server/sql/share/charset --innodb-log-file-size=536870912 --port=16000 --skip-grant-tables
difference from "short" variant: join_tab ls not initialized at all:
gdb) p join->join_tab[0].table
$7 = (TABLE *) 0xa5a5a5a5a5a5a5a5
(gdb) p join->join_tab[1].table
$8 = (TABLE *) 0xa5a5a5a5a5a5a5a5
(gdb) p join->join_tab[2].table
$9 = (TABLE *) 0xa5a5a5a5a5a5a5a5
Difference with Sergey observation is that after mysql_derived_merge() call 'merged' flag is set and merge really is done
(gdb) p derived->merged
$1 = true
It looks like tables with the same map are not temporary tables:
DBUG_PRINT("XXX", ("table '%s' 0x%lx join_tab: 0x%lx map: 0x%lx",
table->s->path.str, (ulong) table,
(ulong) s, (ulong) table->map));
in make_join_statistics() output:
T@4 : | | | | | | | | | | | XXX: table './kzmdb/tb_user' 0x7fff841859d0 join_tab: 0x7fff841e5f78 map: 0x1
T@4 : | | | | | | | | | | | XXX: table '/home/bell/maria/git/server/mysql-test/var/tmp/#sql_1968_0' 0x7fff841c35b8 join_tab: 0x7fff841e6298 map: 0x2
T@4 : | | | | | | | | | | | XXX: table './kuzudb/tb_team_bereich' 0x7fff8417c340 join_tab: 0x7fff841e65b8 map: 0x1
setup_tables() set it correctly:
T@4 : | | | | | | | | | | | XXX: table './kzmdb/tb_user' 0x7fff84185a90 map: 0x1
T@4 : | | | | | | | | | | | XXX: table '/home/bell/maria/git/server/mysql-test/var/tmp/#sql_2370_0' 0x7fff841c3678 map: 0x2
T@4 : | | | | | | | | | | | XXX: table './kuzudb/tb_team_bereich' 0x7fff8417c400 map: 0x4
then it become:
T@4 : | | | | | | | | | | | XXX: table './kzmdb/tb_user' 0x7fff84185a90 join_tab: 0x7fff841e6038 map: 0x1
T@4 : | | | | | | | | | | | XXX: table '/home/bell/maria/git/server/mysql-test/var/tmp/#sql_2370_0' 0x7fff841c3678 join_tab: 0x7fff841e6358 map: 0x2
T@4 : | | | | | | | | | | | XXX: table './kuzudb/tb_team_bereich' 0x7fff8417c400 join_tab: 0x7fff841e6678 map: 0x1
Above was not the last setup tables, the last one process only 2 tables
The problem with UPDATE (in difference with SELECT) is that in st_select_lex::remap_tables() it appeared that derived table temporary table which we are going to merge has no table map but underlying table already has (and it was included as leaf table (not temporary one)).
The difference between SELECT & UPDATE is that full_table_list is set to true for so underlying table get in to the leaf table list.
| Assignee | Oleksandr Byelkin [ sanja ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Oleksandr Byelkin [ sanja ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Elena Stepanova [ elenst ] |
Regarding the patch:
it didn't apply smoothly due to line breaks in long lines in the test case. I removed them (locally) and then it worked.
Regarding the test case:
It creates InnoDB tables, but InnoDB is disabled for the test case. Please either enable it or switch tables to MyISAM, it just doesn't make sense to generate warnings about the missing engine.
Otherwise, I've run a bunch of tests, didn't get any regressions. Please push.
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Assignee | Elena Stepanova [ elenst ] | Oleksandr Byelkin [ sanja ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
It is good, but if engine is not important it is better do not mention it at all (my fault)
Daniel, thank you a lot, but I think more changes should be done so I've made changes from my tree and push after test run.
| Fix Version/s | 10.0.18 [ 18702 ] | |
| Fix Version/s | 5.5.43 [ 18601 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v2 [ 59729 ] | MariaDB v3 [ 65732 ] |
| Workflow | MariaDB v3 [ 65732 ] | MariaDB v4 [ 148822 ] |
150220 10:53:15 [ERROR] mysqld got signal 11 ;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.To report this bug, see http://kb.askmonty.org/en/reporting-bugsWe will try our best to scrape up some info that will hopefully helpdiagnose the problem, but since we have already crashed,something is definitely wrong and this may fail.Server version: 5.5.40-MariaDB-logkey_buffer_size=134217728read_buffer_size=131072max_used_connections=61max_threads=202thread_count=23It is possible that mysqld could use up tokey_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 574210 K bytes of memoryHope that's ok; if not, decrease some variables in the equation.Thread pointer: 0x0x7f57720f7000Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = 0x7f58b616ce50 thread_stack 0x48000mysys/stacktrace.c:247(my_print_stacktrace)[0xaee3ee]sql/signal_handler.cc:153(handle_fatal_signal)[0x6dad2c]/lib64/libpthread.so.0(+0xf7c0)[0x7f58b87e97c0]sql/opt_subselect.cc:3290(fix_semijoin_strategies_for_picked_join_order(JOIN*))[0x67ffa2]sql/sql_list.h:34(Sql_alloc::operator new(unsigned long))[0x5bfd4f]sql/sql_select.cc:3817(make_join_statistics)[0x5c88ae]sql/sql_select.cc:1229(JOIN::optimize())[0x5c9207]sql/sql_derived.cc:789(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x55e24d]sql/sql_derived.cc:192(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55db98]sql/table.cc:6629(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x6140ba]sql/sql_lex.cc:3554(st_select_lex::handle_derived(LEX*, unsigned int))[0x570730]sql/sql_select.cc:991(JOIN::optimize())[0x5c8be5]sql/sql_select.cc:3080(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5d3927]sql/sql_update.cc:1441(mysql_multi_update(THD*, TABLE_LIST*, List<Item>*, List<Item>*, Item*, unsigned long long, enum_duplicates, bool, st_select_lex_unit*, st_select_lex*, multi_update**))[0x60aa53]sql/sql_parse.cc:2915(mysql_execute_command(THD*))[0x5850b7]sql/sql_parse.cc:5799(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x586bf1]sql/sql_parse.cc:1081(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x58831c]sql/sql_parse.cc:793(do_command(THD*))[0x5888c2]sql/sql_connect.cc:1266(do_handle_one_connection(THD*))[0x6432b3]sql/sql_connect.cc:1183(handle_one_connection)[0x6433fc]/lib64/libpthread.so.0(+0x77b6)[0x7f58b87e17b6]/lib64/libc.so.6(clone+0x6d)[0x7f58b7971c5d]