[MDEV-7413] optimizer_use_condition_selectivity > 2 crashes 10.0.15+maria-1~wheezy Created: 2015-01-06  Updated: 2015-02-25  Resolved: 2015-02-25

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.15
Fix Version/s: 10.0.17

Type: Bug Priority: Major
Reporter: Christian Winther Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: eits, optimizer
Environment:

debian 7.7

Attachments: File confluence-2015-01-08.sql     File crowd-2015-01-08.sql    
Issue Links:
Relates
relates to MDEV-6308 Server crashes in table_multi_eq_cond... Closed
relates to MDEV-7316 Assertion `join->best_read < double(.... Closed

 Description   

Ref MDEV-6308 which was my previous ticket

When increasing optimizer_use_condition_selectivity above 2, my server crashes completely - I'm not sure what query causes the crash, as its a live production environment with many many qps.

A list of my variables can be found here: https://gist.githubusercontent.com/jippi/81c2b80087308e11c767/raw/9f02e5fde3062925e98fd4996b6a9b30667160c2/gistfile1.txt

The list is with a working copy, it crashes if optimizer_use_condition_selectivity is increased above "2"

The stack trace is as below:

Server version: 10.0.15-MariaDB-1~wheezy-log
 
key_buffer_size=25165824
 
read_buffer_size=131072
 
max_used_connections=522
 
max_threads=1511
 
thread_count=522
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3343761 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x7f97b2260008
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7f97417c2e10 thread_stack 0x40000
 
/usr/sbin/mysqld(my_print_stacktrace+0x2b)[0x7fc89d5cab9b]
 
/usr/sbin/mysqld(handle_fatal_signal+0x422)[0x7fc89d15ae92]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0xf0a0)[0x7fc89c7ad0a0]
 
/usr/sbin/mysqld(_Z45fix_semijoin_strategies_for_picked_join_orderP4JOIN+0x68)[0x7fc89d0f3788]
 
/usr/sbin/mysqld(_Z20get_best_combinationP4JOIN+0x60)[0x7fc89d0302e0]
 
/usr/sbin/mysqld(+0x374104)[0x7fc89cf50104]
 
/usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x84a)[0x7fc89d04bdba]
 
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xd8)[0x7fc89d04e418]
 
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x28d)[0x7fc89d051c0d]
 
/usr/sbin/mysqld(+0x41b491)[0x7fc89cff7491]
 
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4b8a)[0x7fc89d001a1a]
 
/usr/sbin/mysqld(+0x4275ba)[0x7fc89d0035ba]
 
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x14df)[0x7fc89d004b7f]
 
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x47b)[0x7fc89d0c673b]
 
/usr/sbin/mysqld(handle_one_connection+0x47)[0x7fc89d0c6817]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x6b50)[0x7fc89c7a4b50]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc89aeb17bd]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f97ab53b020): is an invalid pointer
 
Connection ID (thread ID): 591
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on



 Comments   
Comment by Elena Stepanova [ 2015-01-08 ]

If you can afford crashing the server once more, it should be fairly easy to find the exact query that causes the problem. I'll describe the algorithm just in case you can do it (I realize that it's a production server, so you probably can't).

  • on the running server, execute set global general_log_file=<absolute path and file name of your choice>, general_log=1;
  • execute set global optimizer_use_condition_selectivity = 3;
  • wait till the server crashes;
  • in the error log after the stack trace, there is the informational block like this (taken from your description): 

    Trying to get some variables.
    		 
    Some pointers may be invalid and cause the dump to abort.
    		 
    Query (0x7f97ab53b020): is an invalid pointer
    		 
    Connection ID (thread ID): 591
    		 
    Status: NOT_KILLED

    Note that number in the 'Connection ID' string, lets call it NNNN (above NNNN = 591).
    Open the general log which was created at the path that you chose before; go to the end of it; search backwards for Query NNNN. Make sure it's the last query with this connection ID in the log.
    This should be the query that caused the crash.

If you happen to do it, please paste the query and output of SHOW CREATE TABLE ..., SHOW TABLE STATUS LIKE ... and SHOW INDEX IN ... for all tables involved in the query (or upload the information to our ftp.askmonty.org/private if it's confidential).

Comment by Christian Winther [ 2015-01-08 ]

Hi!

I took the box down for another crash, and this is the cause, I've attached the full schema for the database, as its the Atlassian Crowd product - (the previous bug report for crash was from an Atlassian product as well).

As it turns out, using the selectivity hurts our performance quite terrible for other applications, the query plan it executes look good, but its quite terrible in select speed.. up to 100 times slower than selectivity = 1

-> grep '16574 Query' /tmp/mysql_general.log
 
		16574 Query	SET SESSION storage_engine=InnoDB
 
		16574 Query	/* mysql-connector-java-5.1.27 ( Revision: alexander.soklakov@oracle.com-20131021093118-gtm1bh1vb450xipt ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
 
		16574 Query	/* mysql-connector-java-5.1.27 ( Revision: alexander.soklakov@oracle.com-20131021093118-gtm1bh1vb450xipt ) */SELECT @@session.auto_increment_increment
 
		16574 Query	SHOW COLLATION
 
		16574 Query	SET NAMES utf8
 
		16574 Query	SET character_set_results = NULL
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET sql_mode='STRICT_TRANS_TABLES'
 
		16574 Query	SELECT @@session.tx_isolation
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SHOW FULL TABLES FROM `confluence` LIKE 'PROBABLYNOT'
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
150108 11:22:35	16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
150108 11:22:36	16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	select confluence0_.BANDANAID as BANDANAID, confluence0_.BANDANACONTEXT as BANDANAC2_, confluence0_.BANDANAKEY as BANDANAKEY, confluence0_.BANDANAVALUE as BANDANAV4_ from BANDANA confluence0_ where (confluence0_.BANDANACONTEXT='_GLOBAL' )and(confluence0_.BANDANAKEY='com.atlassian.upm.SysPersisted:properties:upm.pac.disable' )
 
		16574 Query	commit
 
		16574 Query	rollback
 
		16574 Query	SET autocommit=1
 
		16574 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
		16574 Query	SET autocommit=0
 
		16574 Query	select internalus0_.user_name as x0_0_, internalus0_.lower_user_name as x1_0_ from cwd_user internalus0_ where (internalus0_.directory_id=98306 )AND(internalus0_.active='T' ) order by  internalus0_.lower_user_name
 
		16574 Query	select internalus0_.user_name as x0_0_, internalus0_.lower_user_name as x1_0_ from cwd_user internalus0_ where (internalus0_.directory_id=98305 )AND(internalus0_.active='T' ) order by  internalus0_.lower_user_name
 
		16574 Query	select spacepermi0_.PERMID as PERMID, spacepermi0_.SPACEID as SPACEID, spacepermi0_.PERMTYPE as PERMTYPE, spacepermi0_.PERMGROUPNAME as PERMGROU4_, spacepermi0_.PERMUSERNAME as PERMUSER5_, spacepermi0_.CREATOR as CREATOR, spacepermi0_.CREATIONDATE as CREATION7_, spacepermi0_.LASTMODIFIER as LASTMODI8_, spacepermi0_.LASTMODDATE as LASTMODD9_ from SPACEPERMISSIONS spacepermi0_ where (spacepermi0_.SPACEID is null )and(spacepermi0_.PERMTYPE='USECONFLUENCE' )
 
		16574 Query	select this.id as id0_, this.group_name as group_name0_, this.lower_group_name as lower_gr3_0_, this.active as active0_, this.local as local0_, this.created_date as created_6_0_, this.updated_date as updated_7_0_, this.description as descript8_0_, this.group_type as group_type0_, this.directory_id as directo10_0_ from cwd_group this where this.lower_group_name='confluence-users' and this.directory_id=98306
 
		16574 Query	select internalus2_.user_name as x0_0_ from cwd_membership hibernatem0_, cwd_group internalgr1_, cwd_user internalus2_ where hibernatem0_.child_user_id=internalus2_.id and ((hibernatem0_.child_group_id is null )and(internalgr1_.lower_group_name='confluence-users'  and hibernatem0_.parent_id=internalgr1_.id)and(internalgr1_.directory_id=98306  and hibernatem0_.parent_id=internalgr1_.id)and(internalgr1_.group_type='GROUP'  and hibernatem0_.parent_id=internalgr1_.id)) order by  internalus2_.lower_user_name
 
		16574 Query	select internalus2_.user_name as x0_0_ from cwd_membership hibernatem0_, cwd_group internalgr1_, cwd_user internalus2_ where hibernatem0_.child_user_id=internalus2_.id and ((hibernatem0_.child_group_id is null )and(internalgr1_.lower_group_name='confluence-users'  and hibernatem0_.parent_id=internalgr1_.id)and(internalgr1_.directory_id=98305  and hibernatem0_.parent_id=internalgr1_.id)and(internalgr1_.group_type='GROUP'  and hibernatem0_.parent_id=internalgr1_.id)) order by  internalus2_.lower_user_name

from

Server version: 10.0.15-MariaDB-1~wheezy-log
 
key_buffer_size=25165824
 
read_buffer_size=131072
 
max_used_connections=978
 
max_threads=1511
 
thread_count=954
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3343761 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x7f8022f7a008
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7f7fbaa48e10 thread_stack 0x40000
 
(my_addr_resolve failure: fork)
 
/usr/sbin/mysqld(my_print_stacktrace+0x2b) [0x7fb17b661b9b]
 
/usr/sbin/mysqld(handle_fatal_signal+0x422) [0x7fb17b1f1e92]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0xf0a0) [0x7fb17a8440a0]
 
/usr/sbin/mysqld(get_best_combination(JOIN*)+0x24f) [0x7fb17b0c74cf]
 
/usr/sbin/mysqld(+0x374104) [0x7fb17afe7104]
 
/usr/sbin/mysqld(JOIN::optimize_inner()+0x84a) [0x7fb17b0e2dba]
 
/usr/sbin/mysqld(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*)+0xd8) [0x7fb17b0e5418]
 
/usr/sbin/mysqld(handle_select(THD*, LEX*, select_result*, unsigned long)+0x28d) [0x7fb17b0e8c0d]
 
/usr/sbin/mysqld(+0x41b491) [0x7fb17b08e491]
 
/usr/sbin/mysqld(mysql_execute_command(THD*)+0x4b8a) [0x7fb17b098a1a]
 
/usr/sbin/mysqld(+0x4275ba) [0x7fb17b09a5ba]
 
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x14df) [0x7fb17b09bb7f]
 
/usr/sbin/mysqld(do_handle_one_connection(THD*)+0x47b) [0x7fb17b15d73b]
 
/usr/sbin/mysqld(handle_one_connection+0x47) [0x7fb17b15d817]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x6b50) [0x7fb17a83bb50]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7fb178f487bd]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f8098cce020): is an invalid pointer
 
Connection ID (thread ID): 16574
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on

Comment by Elena Stepanova [ 2015-01-08 ]

Strangely, I am getting ER_BAD_FIELD_ERROR when I'm trying to run the last query (with or without selectivity):

MariaDB [test]> select internalus2_.user_name as x0_0_ from cwd_membership hibernatem0_, cwd_group internalgr1_, cwd_user internalus2_ where hibernatem0_.child_user_id=internalus2_.id and ((hibernatem0_.child_group_id is null )and(internalgr1_.lower_group_name='confluence-users'  and hibernatem0_.parent_id=internalgr1_.id)and(internalgr1_.directory_id=98305  and hibernatem0_.parent_id=internalgr1_.id)and(internalgr1_.group_type='GROUP'  and hibernatem0_.parent_id=internalgr1_.id)) order by  internalus2_.lower_user_name;
 
ERROR 1054 (42S22): Unknown column 'hibernatem0_.child_user_id' in 'where clause'

And indeed, there is no child_user_id anywhere in the attached dump. Do you get the same error?

Regarding performance, which selectivity value give you bad results? Can you provide an example (better in another bug report)? The query, the plan, execution time, etc.

Comment by Christian Winther [ 2015-01-08 ]

oops, I uploaded the wrong SQL file

it was actually in Confluence (like last time) the error happened in

Comment by Elena Stepanova [ 2015-01-08 ]

Thanks a lot, I was able to reproduce the crash with this information.

Comment by Elena Stepanova [ 2015-01-08 ]

Did you run ANALYZE TABLE on involved tables (cwd_membership, cwd_group, cwd_user)?
Does your mysql.table_stats, mysql.index_stats and mysql.column_stats have any rows related to these tables?

Comment by Elena Stepanova [ 2015-01-08 ]

It looks like the same problem as filed in MDEV-7316 – at least it has the same stack trace on the non-debug version, and the same assertion failure on the debug version. Below is the simplified test case, I'll keep it open to make sure it is fixed along with MDEV-7316.

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (
 
  parent_id int,
 
  child_group_id int,
 
  child_user_id int,
 
  KEY (parent_id,child_group_id,child_user_id)
 
) ENGINE=InnoDB;
 
 
 
CREATE TABLE t2 (
 
  id int,
 
  lower_group_name varchar(255),
 
  directory_id int(20),
 
  UNIQUE KEY (directory_id)
 
) ENGINE=InnoDB;
 
 
 
CREATE TABLE t3 (id int) ENGINE=InnoDB;
 
 
 
insert into t1 values (1,1,1),(2,2,2);
 
insert into t2 values (10,'foo',10),(20,'bar',20);
 
insert into t3 values (101),(102);
 
 
 
set use_stat_tables = PREFERABLY, optimizer_use_condition_selectivity = 3;
 
 
 
select * from t1, t2, t3 
where t1.child_user_id=t3.id and t1.child_group_id is null and t2.lower_group_name='foo' and t1.parent_id=t2.id and t2.directory_id=10;

Crash on 10.0.15 release

 
#2  <signal handler called>
 
#3  fix_semijoin_strategies_for_picked_join_order (join=0x7f3a915a5720) at sql/opt_subselect.cc:3298
 
#4  0x00000000005cabea in get_best_combination (join=0x7f3a915a5720) at sql/sql_select.cc:8423
 
#5  0x00000000005e5cab in make_join_statistics (join=0x7f3a915a5720, tables_list=..., conds=0x7f3a91602ad0, keyuse_array=0x7f3a915a5a28) at sql/sql_select.cc:4070
 
#6  0x00000000005e82ea in JOIN::optimize_inner (this=0x7f3a915a5720) at sql/sql_select.cc:1339
 
#7  0x00000000005eb262 in optimize (this=<optimized out>) at sql/sql_select.cc:1024
 
#8  mysql_select (thd=0x7f3a93253008, rref_pointer_array=0x7f3a93257540, tables=0x7f3a91601320, wild_num=1, fields=..., conds=0x7f3a91602ad0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f3a91602fe0, unit=0x7f3a93256be0, select_lex=0x7f3a932572c8) at sql/sql_select.cc:3294
 
#9  0x00000000005ef00d in handle_select (thd=0x7f3a93253008, lex=0x7f3a93256b18, result=0x7f3a91602fe0, setup_tables_done_option=0) at sql/sql_select.cc:373
 
#10 0x0000000000590710 in execute_sqlcom_select (thd=0x7f3a93253008, all_tables=0x7f3a91601320) at sql/sql_parse.cc:5261
 
#11 0x0000000000596127 in mysql_execute_command (thd=0x7f3a93253008) at sql/sql_parse.cc:2545
 
#12 0x000000000059d50a in mysql_parse (thd=0x7f3a93253008, rawbuf=0x7f3a91601020 "select * from t1, t2, t3 \nwhere t1.child_user_id=t3.id and t1.child_group_id is null and t2.lower_group_name='foo' and t1.parent_id=t2.id and t2.directory_id=10", length=<optimized out>, parser_state=0x7f3a9a8cebe0) at sql/sql_parse.cc:6407
 
#13 0x000000000059f2b7 in dispatch_command (command=COM_QUERY, thd=0x7f3a93253008, packet=<optimized out>, packet_length=160) at sql/sql_parse.cc:1299
 
#14 0x000000000059f9c8 in do_command (thd=0x7f3a93253008) at sql/sql_parse.cc:996
 
#15 0x0000000000668974 in do_handle_one_connection (thd_arg=0x7f3a93253008) at sql/sql_connect.cc:1375
 
#16 0x0000000000668ab2 in handle_one_connection (arg=0x7f3a93253008) at sql/sql_connect.cc:1289
 
#17 0x0000000000a7c579 in pfs_spawn_thread (arg=<optimized out>) at storage/perfschema/pfs.cc:1860
 
#18 0x00007f3a9a4b9b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#19 0x00007f3a9943620d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Assertion failure on 10.0 revno 4549

 
mysqld: sql/sql_select.cc:6950: bool greedy_search(JOIN*, table_map, uint, uint, uint): Assertion `join->best_read < double(1.79769313486231570815e+308L)' failed.
 
150108 18:29:53 [ERROR] mysqld got signal 6 ;
 
 
 
#6  0x00007f6e3650c6f1 in *__GI___assert_fail (assertion=0xf11768 "join->best_read < double(1.79769313486231570815e+308L)", file=<optimized out>, line=6950, function=0xf14760 "bool greedy_search(JOIN*, table_map, uint, uint, uint)") at assert.c:81
 
#7  0x00000000006c17e5 in greedy_search (join=0x7f6e2e85b788, remaining_tables=5, search_depth=62, prune_level=1, use_cond_selectivity=3) at sql/sql_select.cc:6950
 
#8  0x00000000006c0d6f in choose_plan (join=0x7f6e2e85b788, join_tables=5) at sql/sql_select.cc:6519
 
#9  0x00000000006ba73d in make_join_statistics (join=0x7f6e2e85b788, tables_list=..., conds=0x7f6e2e815b38, keyuse_array=0x7f6e2e85ba90) at sql/sql_select.cc:4038
 
#10 0x00000000006b13a3 in JOIN::optimize_inner (this=0x7f6e2e85b788) at sql/sql_select.cc:1339
 
#11 0x00000000006b033a in JOIN::optimize (this=0x7f6e2e85b788) at sql/sql_select.cc:1024
 
#12 0x00000000006b7ebf in mysql_select (thd=0x7f6e3132f070, rref_pointer_array=0x7f6e313336e0, tables=0x7f6e2e814388, wild_num=1, fields=..., conds=0x7f6e2e815b38, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f6e2e816048, unit=0x7f6e31332d80, select_lex=0x7f6e31333468) at sql/sql_select.cc:3294
 
#13 0x00000000006ae549 in handle_select (thd=0x7f6e3132f070, lex=0x7f6e31332cb8, result=0x7f6e2e816048, setup_tables_done_option=0) at sql/sql_select.cc:373
 
#14 0x0000000000683244 in execute_sqlcom_select (thd=0x7f6e3132f070, all_tables=0x7f6e2e814388) at sql/sql_parse.cc:5261
 
#15 0x000000000067b542 in mysql_execute_command (thd=0x7f6e3132f070) at sql/sql_parse.cc:2545
 
#16 0x00000000006859bd in mysql_parse (thd=0x7f6e3132f070, rawbuf=0x7f6e2e814088 "select * from t1, t2, t3 \nwhere t1.child_user_id=t3.id and t1.child_group_id is null and t2.lower_group_name='foo' and t1.parent_id=t2.id and t2.directory_id=10", length=160, parser_state=0x7f6e38719610) at sql/sql_parse.cc:6407
 
#17 0x00000000006787b2 in dispatch_command (command=COM_QUERY, thd=0x7f6e3132f070, packet=0x7f6e323f6071 "select * from t1, t2, t3 \nwhere t1.child_user_id=t3.id and t1.child_group_id is null and t2.lower_group_name='foo' and t1.parent_id=t2.id and t2.directory_id=10", packet_length=160) at sql/sql_parse.cc:1299
 
#18 0x0000000000677b57 in do_command (thd=0x7f6e3132f070) at sql/sql_parse.cc:996
 
#19 0x0000000000794877 in do_handle_one_connection (thd_arg=0x7f6e3132f070) at sql/sql_connect.cc:1375
 
#20 0x00000000007945ca in handle_one_connection (arg=0x7f6e3132f070) at sql/sql_connect.cc:1289
 
#21 0x0000000000ccdb42 in pfs_spawn_thread (arg=0x7f6e30767bf0) at storage/perfschema/pfs.cc:1860
 
#22 0x00007f6e38306b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#23 0x00007f6e365bd20d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Comment by Christian Winther [ 2015-01-08 ]

No, i've not run any analyze or optimize on the table - the database is blackbox to me, since it's an Atlassian product, so I don't ever touch it directly in mysql

Comment by Elena Stepanova [ 2015-01-08 ]

Then maybe it explains the performance problem that you mentioned – if you are trying to use persistent statistics without actually having the statistical data, I suppose results can be non-optimal. psergey who this issue has been assigned to will probably be able to say more on the subject.

Comment by Christian Winther [ 2015-01-08 ]

Hi, sorry, the complain about bad performance, yes, I did run a full analyze and optimize on the table before testing it out

Comment by Christian Winther [ 2015-01-08 ]

I've uploaded the following files to the FTP for thats statistics:

MDEV-7413-stats-column-2015-01-08.csv
MDEV-7413-stats-index-2015-01-08.csv
MDEV-7413-stats-table-2015-01-08.csv
MDEV-7413-mysql-output.txt

with the stats tables and explain + query execution for each level of optimizer_use_condition_selectivity

Comment by Christian Winther [ 2015-01-27 ]

the fix didn't make it to 10.0.16 ? that's sad

Comment by Sergei Petrunia [ 2015-02-20 ]

Sorry about that. But now, there is progress. The issue seems to be similar (or the same to) MDEV-7316, tentative fix for MDEV-7316 makes this crash go away, too.

Comment by Sergei Petrunia [ 2015-02-25 ]

Ok the fix for MDEV-7316 that was pushed into bzr tree, fixes this bug, too (checked on Elena's, simplified testcase). I've committed Elena's testcase into 10.0-bzr.

Comment by Sergei Petrunia [ 2015-02-25 ]

Fix for MDEV-7316 is pushed, so closing this one too. The fix will be in 10.0.17 release.

Thanks for your patience.

Generated at Thu Feb 08 07:19:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.