[MDEV-9455] [ERROR] mysqld got signal 11 Created: 2016-01-23  Updated: 2017-02-20  Resolved: 2017-02-20

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Optimizer
Affects Version/s: 10.0.19, 5.5, 10.0, 10.1
Fix Version/s: 5.5.55, 10.0.30, 10.1.22, 10.2.5

Type: Bug Priority: Major
Reporter: yangback Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS6.6

Sprint: 5.5.55

 Description   

Any pointers in debugging?

This is what I see in the error log:

160115 15:31:15 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or 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-bugs
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed,
 
something is definitely wrong and this may fail.
 
 
Server version: 10.0.19-MariaDB-log
 
key_buffer_size=134217728
 
read_buffer_size=1048576
 
max_used_connections=4
 
max_threads=501
 
thread_count=2
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1177387 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
Thread pointer: 0x0x7fa841866008
 
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 = 0x7fa82cbfecd8 thread_stack 0x48000
 
mysys/stacktrace.c:247(my_print_stacktrace)[0xbca37e]
 
sql/signal_handler.cc:153(handle_fatal_signal)[0x71fe5c]
 
/lib64/libpthread.so.0[0x344740f710]
 
sql/sql_lex.cc:3945(st_select_lex::update_correlated_cache())[0x5875b5]
 
sql/sql_lex.cc:3505(st_select_lex::optimize_unflattened_subqueries(bool))[0x589553]
 
sql/opt_subselect.cc:4972(JOIN::optimize_unflattened_subqueries())[0x6a8ff2]
 
sql/sql_select.cc:1782(JOIN::optimize_inner())[0x5ebc8e]
 
sql/sql_select.cc:1035(JOIN::optimize())[0x5edb92]
 
sql/sql_select.cc:373(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5f195d]
 
sql/sql_parse.cc:5275(execute_sqlcom_select)[0x5957d0]
 
sql/sql_parse.cc:2562(mysql_execute_command(THD*))[0x598427]
 
sql/sql_parse.cc:6529(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x59f866]
 
sql/sql_parse.cc:1310(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x5a1797]
 
sql/sql_parse.cc:999(do_command(THD*))[0x5a1ed9]
 
sql/threadpool_common.cc:233(threadpool_process_request(THD*))[0x6b6798]
 
sql/threadpool_unix.cc:1448(handle_event)[0x6f3818]
 
perfschema/pfs.cc:1863(pfs_spawn_thread)[0xa83f49]
 
/lib64/libpthread.so.0[0x34474079d1]
 
/lib64/libc.so.6(clone+0x6d)[0x34470e8b6d]
 
 

Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fa82a820020): SELECT A.IMORY_ID,        A.NUMBER,        A.NAME,        DATE_FORMAT(A.DATETIME, '%Y-%m-%d') AS TARGET_DATE,        SUM(                        CASE                          WHEN A.DATA_TYPE='1' THEN 1                          ELSE 0                        END) AS CALL_CNT,        SUM(                        CASE                          WHEN A.DATA_TYPE IN ('2',                        '3') THEN 1                          ELSE 0                        END) AS SMS_CNT,        SUM(CAST(A.DURATION AS INT)) AS DURATION,        (SELECT COUNT(*)         FROM   TB_DIARY_TOTAL_DAY         WHERE  IMORY_ID=A.IMORY_ID         AND    NUMBER=A.NUMBER         AND    NAME=A.NAME         AND    DATETIME = DATE_FORMAT(A.DATETIME, '%Y-%m-%d')) STATS_COUNT FROM   TB_DIARY A WHERE  A.IMORY_ID = 55094102 AND    A.DATETIME LIKE (SELECT                CONCAT (DATE_FORMAT(DATETIME, '%Y-%m-%d') ,'%')           FROM   TB_DIARY         WHERE  IMORY_ID=55094102         AND    DIARY_SEQ IN ( 608351221,                        608351225,                        608351229 )          group by  DATE_FORMAT(DATETIME, '%Y-%m-%d') )  GROUP BY A.IMORY_ID, A.NUMBER, A.NAME, DATE_FORMAT(A.DATETIME, '%Y-%m-%d')
 
Connection ID (thread ID): 5635152
 
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=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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
 
 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
information that should help you find out what is causing the crash.
 
160115 15:31:16 mysqld_safe Number of processes running now: 0



 Comments   
Comment by Elena Stepanova [ 2016-01-25 ]

If you can provide the data dump of TB_DIARY_TOTAL_DAY and TB_DIARY, it would be perfect (you can upload it to ftp.askmonty.org/private so that only MariaDB developers will have access to it). If you can't provide the dump, please paste the output of

SHOW CREATE TABLE TB_DIARY_TOTAL_DAY;
 
SHOW CREATE TABLE TB_DIARY;
 
SHOW INDEX IN TB_DIARY_TOTAL_DAY;
 
SHOW INDEX IN TB_DIARY;

.

In either case, please also attach your cnf file(s).
Thanks.

Comment by yangback [ 2016-01-25 ]

check plz..

SHOW CREATE TABLE TB_DIARY_TOTAL_DAY;
 
 
 
CREATE TABLE `TB_DIARY_TOTAL_DAY` (
 
  `DIARY_TOTAL_DAY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `IMORY_ID` bigint(20) NOT NULL,
 
  `NAME` varchar(75) DEFAULT NULL,
 
  `DATETIME` varchar(10) NOT NULL DEFAULT '',
 
  `DAILY_CALL_CNT` int(11) DEFAULT NULL,
 
  `DAILY_SMS_CNT` int(11) DEFAULT NULL,
 
  `NUMBER` varchar(64) DEFAULT NULL,
 
  `DURATION` varchar(16) DEFAULT NULL,
 
  PRIMARY KEY (`DIARY_TOTAL_DAY_SEQ`,`DATETIME`),
 
  KEY `IDX_TB_DIARY_TOTAL_DAY_01` (`IMORY_ID`,`DATETIME`)
 
) ENGINE=InnoDB AUTO_INCREMENT=328702514 DEFAULT CHARSET=utf8mb4
 
/*!50500 PARTITION BY RANGE  COLUMNS(`DATETIME`)
 
(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
 
 PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
 
 PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
 
 PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
 
 PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
 
 PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
 
 PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
 
 PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
 
 PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB,
 
 PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
 
 PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) */ |
 

SHOW CREATE TABLE TB_DIARY;
 
CREATE TABLE `TB_DIARY` (
 
  `DIARY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `IMORY_ID` bigint(20) NOT NULL,
 
  `CALL_TYPE` varchar(1) DEFAULT NULL,
 
  `DATA_TYPE` varchar(1) DEFAULT NULL,
 
  `FEATURES` varchar(1) DEFAULT NULL,
 
  `NAME` varchar(75) DEFAULT NULL,
 
  `NUMBER` varchar(64) DEFAULT NULL,
 
  `DATETIME` datetime NOT NULL,
 
  `REG_DATE` datetime NOT NULL,
 
  `TITLE` varchar(50) DEFAULT NULL,
 
  `BODY` varchar(4200) DEFAULT NULL,
 
  `MIME_TYPE` varchar(32) DEFAULT NULL,
 
  `DURATION` varchar(16) DEFAULT NULL,
 
  `DEVICE_ID` varchar(64) DEFAULT NULL,
 
  `DEVICE_NAME` varchar(32) DEFAULT NULL,
 
  PRIMARY KEY (`DIARY_SEQ`,`DATETIME`,`REG_DATE`),
 
  KEY `IDX_TB_DIARY_01` (`IMORY_ID`,`DATETIME`,`CALL_TYPE`,`NUMBER`),
 
  KEY `IDX_TB_DIARY_02` (`REG_DATE`)
 
) ENGINE=InnoDB AUTO_INCREMENT=688799006 DEFAULT CHARSET=utf8mb4
 
/*!50500 PARTITION BY RANGE  COLUMNS(REG_DATE)
 
(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
 
 PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
 
 PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
 
 PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
 
 PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
 
 PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
 
 PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
 
 PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
 
 PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB,
 
 PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
 
 PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) */ |
 

SHOW INDEX IN TB_DIARY_TOTAL_DAY;
 
+--------------------+------------+---------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| Table              | Non_unique | Key_name                  | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 
+--------------------+------------+---------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| TB_DIARY_TOTAL_DAY |          0 | PRIMARY                   |            1 | DIARY_TOTAL_DAY_SEQ | A         |    67300277 |     NULL | NULL   |      | BTREE      |         |               |
 
| TB_DIARY_TOTAL_DAY |          0 | PRIMARY                   |            2 | DATETIME            | A         |    67300277 |     NULL | NULL   |      | BTREE      |         |               |
 
| TB_DIARY_TOTAL_DAY |          1 | IDX_TB_DIARY_TOTAL_DAY_01 |            1 | IMORY_ID            | A         |      542744 |     NULL | NULL   |      | BTREE      |         |               |
 
| TB_DIARY_TOTAL_DAY |          1 | IDX_TB_DIARY_TOTAL_DAY_01 |            2 | DATETIME            | A         |    67300277 |     NULL | NULL   |      | BTREE      |         |               |
 
+--------------------+------------+---------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
4 rows in set (0.00 sec)
 

(root) [CLOUD_MDB]>SHOW INDEX IN TB_DIARY;
 
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| Table    | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| TB_DIARY |          0 | PRIMARY         |            1 | DIARY_SEQ   | A         |   163362519 |     NULL | NULL   |      | BTREE      |         |               |
 
| TB_DIARY |          0 | PRIMARY         |            2 | DATETIME    | A         |   163362519 |     NULL | NULL   |      | BTREE      |         |               |
 
| TB_DIARY |          0 | PRIMARY         |            3 | REG_DATE    | A         |   163362519 |     NULL | NULL   |      | BTREE      |         |               |
 
| TB_DIARY |          1 | IDX_TB_DIARY_01 |            1 | IMORY_ID    | A         |     1666964 |     NULL | NULL   |      | BTREE      |         |               |
 
| TB_DIARY |          1 | IDX_TB_DIARY_01 |            2 | DATETIME    | A         |   163362519 |     NULL | NULL   |      | BTREE      |         |               |
 
| TB_DIARY |          1 | IDX_TB_DIARY_01 |            3 | CALL_TYPE   | A         |   163362519 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| TB_DIARY |          1 | IDX_TB_DIARY_01 |            4 | NUMBER      | A         |   163362519 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| TB_DIARY |          1 | IDX_TB_DIARY_02 |            1 | REG_DATE    | A         |     3267250 |     NULL | NULL   |      | BTREE      |         |               |
 
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

my.cnf

 
 
 
############################################
 
[mysqld_multi]
 
mysqld=/MARIA/mariadb/bin/mysqld_safe
 
mysqladmin=/MARIA/mariadb/bin/mysqladmin
 
log=/MARIA/mariadb/mysqld_multi.log
 
user=root
 
password=root
 
 
 
############################################
 
 
 
[mysqld]
 
 
 
# Character set Config
 
character-set-client-handshake=FALSE
 
character-set-server=utf8mb4
 
init_connect=SET collation_connection = utf8mb4_general_ci
 
init_connect=SET NAMES utf8mb4
 
 
 
# Common Config
 
#lower_case_table_names=1
 
back_log=1024
 
binlog_cache_size=1M
 
ft_min_word_len=4
 
interactive_timeout=31536000
 
wait_timeout=31536000
 
join_buffer_size=2M
 
max_allowed_packet=128M
 
max_heap_table_size=2G
 
max_connections=1024
 
performance_schema
 
read_buffer_size=1M
 
read_rnd_buffer_size=8M
 
skip-external-locking
 
skip-name-resolve=1
 
sort_buffer_size=1M
 
max_length_for_sort_data=1024
 
tmp_table_size=2G
 
table_open_cache=10240
 
transaction-isolation=READ-COMMITTED
 
group_concat_max_len=10240
 
open_files_limit=8192
 
read_only=1
 
slave_skip_errors=all
 
 
 
# Query Cache Disable
 
query_cache_type=0
 
query_cache_size=0
 
 
 
# Log Config
 
expire_logs_days=7     
long_query_time=2
 
max_binlog_size=1G
 
slow_query_log=1
 
sync_binlog=1
 
binlog_format=mixed
 
relay_log_purge=1
 
log_slave_update=1
 
 
 
#innodb
 
innodb_data_file_path=ibdata1:1024M;ibdata2:1024M;ibdata3:10M:autoextend
 
innodb_file_per_table=1
 
innodb_flush_method=ALL_O_DIRECT
 
innodb_lock_wait_timeout=50
 
innodb_log_buffer_size=128M
 
innodb_log_file_size= 512M
 
innodb_open_files=8192
 
innodb_log_files_in_group=8
 
innodb_thread_sleep_delay=0
 
innodb_fast_shutdown
 
innodb_autoinc_lock_mode=1
 
innodb_thread_concurrency = 24
 
#innodb_use_native_aio=0
 
autocommit=false
 
innodb_sort_buffer_size=1M
 
 
 
#Thread Pool
 
thread_handling=pool-of-threads
 
thread_pool_stall_limit = 60
 
thread_pool_idle_timeout = 120
 
 
 
 
 
# MyISAM Config
 
bulk_insert_buffer_size =       64M
 
key_buffer_size =      128M
 
myisam_max_sort_file_size       =       10G
 
myisam_recover
 
myisam_repair_threads   =       1
 
myisam_sort_buffer_size =       64M
 
 
 
# Replication related settings
 
log_bin_trust_function_creators = 1
 
 
 
# auto_increment
 
auto_increment_increment=4
 
 
 
############################################
 
 
 
[mysqld1]
 
basedir=/MARIA/mariadb
 
port=3301  
socket=/MARIA_DATA/db0/data/mysql.sock
 
pid-file=/MARIA_DATA/db0/data/mysql.pid
 
datadir=/MARIA_DATA/db0/data
 
tmpdir=/MARIA_DATA/db0/tmpdir
 
innodb_data_home_dir=/MARIA_DATA/db0/data
 
innodb_log_group_home_dir=/MARIA_DATA/db0/data
 
log-bin=/MARIA_LOG/db0/binary/mysql-bin
 
user=maria
 
log-error=/MARIA_LOG/db0/error/mysql.err
 
relay-log=/MARIA_LOG/db0/relay/relay-log
 
slow_query_log_file=/MARIA_LOG/db0/slow/mysql-slow-query.log
 
innodb_buffer_pool_size = 4G  
innodb_buffer_pool_instances = 8
 
#rpl_semi_sync_master_enabled=1 
#rpl_semi_sync_slave_enabled=1 
server-id=101
 
slave_parallel_threads=8
 
auto_increment_offset=1
 
 
 
 
 
[mysqld2]
 
basedir=/MARIA/mariadb
 
port=3302  
socket=/MARIA_DATA/db1/data/mysql.sock
 
pid-file=/MARIA_DATA/db1/data/mysql.pid
 
datadir=/MARIA_DATA/db1/data
 
tmpdir=/MARIA_DATA/db1/tmpdir
 
innodb_data_home_dir=/MARIA_DATA/db1/data
 
innodb_log_group_home_dir=/MARIA_DATA/db1/data
 
log-bin=/MARIA_LOG/db1/binary/mysql-bin
 
user=maria
 
log-error=/MARIA_LOG/db1/error/mysql.err
 
relay-log=/MARIA_LOG/db1/relay/relay-log
 
slow_query_log_file=/MARIA_LOG/db1/slow/mysql-slow-query.log
 
innodb_buffer_pool_size = 4G  
innodb_buffer_pool_instances = 8
 
#rpl_semi_sync_master_enabled=1 
#rpl_semi_sync_slave_enabled=1 
server-id=102
 
slave_parallel_threads=8
 
auto_increment_offset=2
 
 
 
 
 
[mysqld3]
 
basedir=/MARIA/mariadb
 
port=3303  
socket=/MARIA_DATA/db2/data/mysql.sock
 
pid-file=/MARIA_DATA/db2/data/mysql.pid
 
datadir=/MARIA_DATA/db2/data
 
tmpdir=/MARIA_DATA/db2/tmpdir
 
innodb_data_home_dir=/MARIA_DATA/db2/data
 
innodb_log_group_home_dir=/MARIA_DATA/db2/data
 
log-bin=/MARIA_LOG/db2/binary/mysql-bin
 
user=maria
 
log-error=/MARIA_LOG/db2/error/mysql.err
 
relay-log=/MARIA_LOG/db2/relay/relay-log
 
slow_query_log_file=/MARIA_LOG/db2/slow/mysql-slow-query.log
 
innodb_buffer_pool_size = 4G  
innodb_buffer_pool_instances = 8
 
#rpl_semi_sync_master_enabled=1 
#rpl_semi_sync_slave_enabled=1 
server-id=103
 
slave_parallel_threads=8
 
auto_increment_offset=3
 
 
 
 
 
[mysqld4]
 
basedir=/MARIA/mariadb
 
port=3304  
socket=/MARIA_DATA/db3/data/mysql.sock
 
pid-file=/MARIA_DATA/db3/data/mysql.pid
 
datadir=/MARIA_DATA/db3/data
 
tmpdir=/MARIA_DATA/db3/tmpdir
 
innodb_data_home_dir=/MARIA_DATA/db3/data
 
innodb_log_group_home_dir=/MARIA_DATA/db3/data
 
log-bin=/MARIA_LOG/db3/binary/mysql-bin
 
user=maria
 
log-error=/MARIA_LOG/db3/error/mysql.err
 
relay-log=/MARIA_LOG/db3/relay/relay-log
 
slow_query_log_file=/MARIA_LOG/db3/slow/mysql-slow-query.log
 
innodb_buffer_pool_size = 4G  
innodb_buffer_pool_instances = 8
 
#rpl_semi_sync_master_enabled=1 
#rpl_semi_sync_slave_enabled=1 
server-id=104
 
slave_parallel_threads=8
 
auto_increment_offset=4
 
 
 
[mysqldump]
 
quick
 
max_allowed_packet=512M

Comment by Elena Stepanova [ 2016-01-25 ]

Thanks! It helped a lot.

Comment by Elena Stepanova [ 2016-01-25 ]

Below is the MTR-style test case, otherwise everything is exactly the same as provided in the comment above.

No crash on MySQL 5.5, 5.7 (to run it on MySQL, one of lines with CAST needs to be commented, but it does not affect the crash on MariaDB). 

--source include/have_partition.inc
 
--source include/have_innodb.inc
 
 
 
CREATE TABLE `TB_DIARY_TOTAL_DAY` (
 
  `DIARY_TOTAL_DAY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `IMORY_ID` bigint(20) NOT NULL,
 
  `NAME` varchar(75) DEFAULT NULL,
 
  `DATETIME` varchar(10) NOT NULL DEFAULT '',
 
  `DAILY_CALL_CNT` int(11) DEFAULT NULL,
 
  `DAILY_SMS_CNT` int(11) DEFAULT NULL,
 
  `NUMBER` varchar(64) DEFAULT NULL,
 
  `DURATION` varchar(16) DEFAULT NULL,
 
  PRIMARY KEY (`DIARY_TOTAL_DAY_SEQ`,`DATETIME`),
 
  KEY `IDX_TB_DIARY_TOTAL_DAY_01` (`IMORY_ID`,`DATETIME`)
 
) ENGINE=InnoDB AUTO_INCREMENT=328702514 DEFAULT CHARSET=utf8mb4
 
/*!50500 PARTITION BY RANGE  COLUMNS(`DATETIME`)
 
(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
 
 PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
 
 PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
 
 PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
 
 PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
 
 PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
 
 PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
 
 PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
 
 PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB,
 
 PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
 
 PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) */
 
;
 
 
 
CREATE TABLE `TB_DIARY` (
 
  `DIARY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `IMORY_ID` bigint(20) NOT NULL,
 
  `CALL_TYPE` varchar(1) DEFAULT NULL,
 
  `DATA_TYPE` varchar(1) DEFAULT NULL,
 
  `FEATURES` varchar(1) DEFAULT NULL,
 
  `NAME` varchar(75) DEFAULT NULL,
 
  `NUMBER` varchar(64) DEFAULT NULL,
 
  `DATETIME` datetime NOT NULL,
 
  `REG_DATE` datetime NOT NULL,
 
  `TITLE` varchar(50) DEFAULT NULL,
 
  `BODY` varchar(4200) DEFAULT NULL,
 
  `MIME_TYPE` varchar(32) DEFAULT NULL,
 
  `DURATION` varchar(16) DEFAULT NULL,
 
  `DEVICE_ID` varchar(64) DEFAULT NULL,
 
  `DEVICE_NAME` varchar(32) DEFAULT NULL,
 
  PRIMARY KEY (`DIARY_SEQ`,`DATETIME`,`REG_DATE`),
 
  KEY `IDX_TB_DIARY_01` (`IMORY_ID`,`DATETIME`,`CALL_TYPE`,`NUMBER`),
 
  KEY `IDX_TB_DIARY_02` (`REG_DATE`)
 
) ENGINE=InnoDB AUTO_INCREMENT=688799006 DEFAULT CHARSET=utf8mb4
 
/*!50500 PARTITION BY RANGE  COLUMNS(REG_DATE)
 
(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
 
 PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
 
 PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
 
 PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
 
 PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
 
 PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
 
 PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
 
 PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
 
 PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB,
 
 PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
 
 PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) */
 
;
 
 
 
SELECT 
  A.IMORY_ID, 
  A.NUMBER, 
  A.NAME, 
  DATE_FORMAT(A.DATETIME, '%Y-%m-%d') AS TARGET_DATE, 
  SUM( CASE WHEN A.DATA_TYPE='1' THEN 1 ELSE 0 END) AS CALL_CNT, 
  SUM( CASE WHEN A.DATA_TYPE IN ('2', '3') THEN 1 ELSE 0 END) AS SMS_CNT, 
  SUM(CAST(A.DURATION AS INT)) AS DURATION, 
  ( SELECT COUNT(*) 
    FROM TB_DIARY_TOTAL_DAY 
    WHERE IMORY_ID=A.IMORY_ID 
      AND NUMBER=A.NUMBER 
      AND NAME=A.NAME 
      AND DATETIME = DATE_FORMAT(A.DATETIME, '%Y-%m-%d')
 
  ) STATS_COUNT 
FROM TB_DIARY A 
WHERE A.IMORY_ID = 55094102 
  AND A.DATETIME LIKE (
 
    SELECT CONCAT (DATE_FORMAT(DATETIME, '%Y-%m-%d') ,'%') 
    FROM TB_DIARY 
    WHERE IMORY_ID=55094102 
      AND DIARY_SEQ IN ( 608351221, 608351225, 608351229 ) 
    group by DATE_FORMAT(DATETIME, '%Y-%m-%d') 
  ) 
GROUP BY A.IMORY_ID, A.NUMBER, A.NAME, DATE_FORMAT(A.DATETIME, '%Y-%m-%d')
 
;

Stack trace from 5.5 commit 9c9d10b441fe79b111509949ca40afe1a6284c0f

 
#3  <signal handler called>
 
#4  0x0000000000619514 in st_select_lex::update_correlated_cache (this=0x7f94889bb3f8) at /src/5.5/sql/sql_lex.cc:3959
 
#5  0x00000000006183d6 in st_select_lex::optimize_unflattened_subqueries (this=0x7f948d3d39b0, const_only=false) at /src/5.5/sql/sql_lex.cc:3531
 
#6  0x0000000000767bca in JOIN::optimize_unflattened_subqueries (this=0x7f9488881510) at /src/5.5/sql/opt_subselect.cc:4973
 
#7  0x00000000006544ac in JOIN::optimize (this=0x7f9488881510) at /src/5.5/sql/sql_select.cc:1665
 
#8  0x00000000006593dc in mysql_select (thd=0x7f948d3cffa0, rref_pointer_array=0x7f948d3d3c20, tables=0x7f94889ba9b0, wild_num=0, fields=..., conds=0x7f94888a4328, og_num=4, order=0x0, group=0x7f94888a4580, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f94888814f0, unit=0x7f948d3d32d0, select_lex=0x7f948d3d39b0) at /src/5.5/sql/sql_select.cc:3080
 
#9  0x000000000064fd0d in handle_select (thd=0x7f948d3cffa0, lex=0x7f948d3d3220, result=0x7f94888814f0, setup_tables_done_option=0) at /src/5.5/sql/sql_select.cc:319
 
#10 0x00000000006291a0 in execute_sqlcom_select (thd=0x7f948d3cffa0, all_tables=0x7f94889ba9b0) at /src/5.5/sql/sql_parse.cc:4689
 
#11 0x00000000006224d3 in mysql_execute_command (thd=0x7f948d3cffa0) at /src/5.5/sql/sql_parse.cc:2234
 
#12 0x000000000062bc80 in mysql_parse (thd=0x7f948d3cffa0, rawbuf=0x7f94888a0078 "SELECT \nA.IMORY_ID, \nA.NUMBER, \nA.NAME, \nDATE_FORMAT(A.DATETIME, '%Y-%m-%d') AS TARGET_DATE, \nSUM( CASE WHEN A.DATA_TYPE='1' THEN 1 ELSE 0 END) AS CALL_CNT, \nSUM( CASE WHEN A.DATA_TYPE IN ('2', '3') T"..., length=788, parser_state=0x7f94980a7650) at /src/5.5/sql/sql_parse.cc:5914
 
#13 0x000000000061fa72 in dispatch_command (command=COM_QUERY, thd=0x7f948d3cffa0, packet=0x7f9490ba66a1 "SELECT \nA.IMORY_ID, \nA.NUMBER, \nA.NAME, \nDATE_FORMAT(A.DATETIME, '%Y-%m-%d') AS TARGET_DATE, \nSUM( CASE WHEN A.DATA_TYPE='1' THEN 1 ELSE 0 END) AS CALL_CNT, \nSUM( CASE WHEN A.DATA_TYPE IN ('2', '3') T"..., packet_length=789) at /src/5.5/sql/sql_parse.cc:1079
 
#14 0x000000000061ec05 in do_command (thd=0x7f948d3cffa0) at /src/5.5/sql/sql_parse.cc:793
 
#15 0x00000000007214e1 in do_handle_one_connection (thd_arg=0x7f948d3cffa0) at /src/5.5/sql/sql_connect.cc:1269
 
#16 0x000000000072126e in handle_one_connection (arg=0x7f948d3cffa0) at /src/5.5/sql/sql_connect.cc:1185
 
#17 0x0000000000c8acbd in pfs_spawn_thread (arg=0x7f948d573cc0) at /src/5.5/storage/perfschema/pfs.cc:1015
 
#18 0x00007f9497d210a4 in start_thread () from /lib64/libpthread.so.0

Comment by Sergei Petrunia [ 2016-01-26 ]

This seems to be about non-semi-join subqueries.

Comment by Oleksandr Byelkin [ 2017-02-16 ]

It is an attempt to use item allocated in change_to_use_tmp_fields()

Comment by Oleksandr Byelkin [ 2017-02-16 ]

And freed on prune_partitions() exit because it was allocated in the prune_partitions mem_root

Comment by Oleksandr Byelkin [ 2017-02-16 ]

must be discussed with psergey

Comment by Oleksandr Byelkin [ 2017-02-17 ]

revision-id: d1a37eab2b353a0475da26fd4157a37e76fa4808 (mariadb-5.5.54-25-gd1a37ea)
parent(s): 29d78dbb44ee9890b6bc28873344f20fc9157928
committer: Oleksandr Byelkin
timestamp: 2017-02-17 13:37:18 +0100
message:

MDEV-9455: [ERROR] mysqld got signal 11

Switch MEM_ROOT to non-prune_partitions() during optimizing subselect.

—

Comment by Oleksandr Byelkin [ 2017-02-17 ]

github tree bb-5.5-MDEV-9455

Comment by Sergei Petrunia [ 2017-02-20 ]

Ok to push.

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