[MDEV-16801] seg_fault on a query Created: 2018-07-23  Updated: 2018-08-04  Resolved: 2018-08-04

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update, Optimizer, Storage Engine - InnoDB
Affects Version/s: 10.3.8, 10.3
Fix Version/s: 10.3.9

Type: Bug Priority: Critical
Reporter: pablo arias Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Compatibility
Environment:

mariadb 10.3.8_1, centos7, innodbtables, upgrade from 10.2.

Issue Links:
Duplicate
duplicates MDEV-16802 seg_fault on a query Closed
is duplicated by MDEV-16419 crash Closed

 Description   

when executing the following query i get a seg_fault

UPDATE TASACION_buffer l,
 
                        (
 
                                select l.prefijooperador, l.compra_clave, c.clave from
 
                                TASACION_buffer l, 
                                ( select ck.localidad, ck.area, ck.destino, ck.clave, CONCAT(p.prefijo_saliente_2,'%') as prefijo
 
                                        from `clave-km_new` ck
 
                                        INNER JOIN prefijo_por_operadores p
 
                                        ON p.prefijo_saliente_new = LEFT(ck.prefijo, length(ck.prefijo)-1)
 
                                        Where ck.Area = '011'
 
                                  UNION
 
                                  select ck.localidad, ck.area, ck.destino, ck.clave, ck.prefijo
 
                                        from `clave-km_new` ck
 
                                        Where ck.Area = '011'
 
                                        and ck.destino = 'AMBA'
 
                                ) c
 
                                where l.prefijooperador like c.prefijo
 
                                and direccion in ('s','v','t')
 
                                and tipo in (1)
 
                                and dnis not Like '08%'
 
                                and prefijooperador like '011%'
 
                                and c.prefijo like '011%'
 
                                and outtrunkgroup in ( select nrotrunk from trunk 
                                                        where area_local = '11' )
 
                                and (compra_clave is null OR compra_clave = '')
 
 
 
                                group by prefijooperador, c.clave
 
                        ) s2
 
                        SET l.compra_clave = s2.clave
 
                        where l.direccion in ('s','v','t')
 
                        and l.tipo in (1)
 
                        and dnis not Like '08%'
 
                        and (l.compra_clave is null OR l.compra_clave = '')
 
                        and outtrunkgroup in ( select nrotrunk from trunk
 
                                where area_local = '11' )
 
                        and l.prefijooperador = s2.prefijooperador

information that should help you find out what is causing the crash.

2018-07-23 11:26:13 0 [Note] InnoDB: Using Linux native AIO
 
2018-07-23 11:26:13 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
2018-07-23 11:26:13 0 [Note] InnoDB: Uses event mutexes
 
2018-07-23 11:26:13 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
 
2018-07-23 11:26:13 0 [Note] InnoDB: Number of pools: 1
 
2018-07-23 11:26:13 0 [Note] InnoDB: Using SSE2 crc32 instructions
 
2018-07-23 11:26:13 0 [Note] InnoDB: Initializing buffer pool, total size = 8G, instances = 32, chunk size = 256M
 
2018-07-23 11:26:13 0 [Note] InnoDB: Completed initialization of buffer pool
 
2018-07-23 11:26:13 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 
2018-07-23 11:26:13 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=385445067830
 
2018-07-23 11:26:14 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
 
2018-07-23 11:26:14 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
 
2018-07-23 11:26:14 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2018-07-23 11:26:14 0 [Note] InnoDB: Setting file 'ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2018-07-23 11:26:14 0 [Note] InnoDB: File 'ibtmp1' size is now 12 MB.
 
2018-07-23 11:26:14 0 [Note] InnoDB: Waiting for purge to start
 
2018-07-23 11:26:14 0 [Note] InnoDB: 10.3.8 started; log sequence number 385445067839; transaction id 64084118
 
2018-07-23 11:26:14 0 [Note] InnoDB: Loading buffer pool(s) from /data/mariadb/ib_buffer_pool
 
2018-07-23 11:26:14 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2018-07-23 11:26:14 0 [Note] Recovering after a crash using tc.log
 
2018-07-23 11:26:14 0 [Note] Starting crash recovery...
 
2018-07-23 11:26:14 0 [Note] Crash recovery finished.
 
2018-07-23 11:26:14 0 [Note] Server socket created on IP: '::'.
 
2018-07-23 11:26:14 0 [Warning] 'proxies_priv' entry '@% root@newobelisk.cpsnet.com.ar' ignored in --skip-name-resolve mode.
 
2018-07-23 11:26:14 6 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
 
2018-07-23 11:26:14 0 [Note] Reading of all Master_info entries succeded
 
2018-07-23 11:26:14 0 [Note] Added new Master_info '' to hash table
 
2018-07-23 11:26:14 0 [Note] /usr/sbin/mysqld: ready for connections.
 
Version: '10.3.8-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
 
2018-07-23 11:26:16 0 [Note] InnoDB: Buffer pool(s) load completed at 180723 11:26:16
 
2018-07-23 11:47:09 0 [Note] /usr/sbin/mysqld (initiated by: unknown): Normal shutdown
 
2018-07-23 11:47:09 0 [Note] Event Scheduler: Purging the queue. 0 events
 
2018-07-23 11:47:09 0 [Note] InnoDB: FTS optimize thread exiting.
 
2018-07-23 11:47:09 0 [Note] InnoDB: Starting shutdown...
 
2018-07-23 11:47:09 0 [Note] InnoDB: Dumping buffer pool(s) to /data/mariadb/ib_buffer_pool
 
2018-07-23 11:47:09 0 [Note] InnoDB: Buffer pool(s) dump completed at 180723 11:47:09
 
2018-07-23 11:47:10 0 [Note] InnoDB: Shutdown completed; log sequence number 385445068900; transaction id 64084124
 
2018-07-23 11:47:10 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
 
2018-07-23 11:47:10 0 [Note] /usr/sbin/mysqld: Shutdown complete
 
 
 
2018-07-23 11:47:11 0 [Note] InnoDB: Using Linux native AIO
 
2018-07-23 11:47:11 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
2018-07-23 11:47:11 0 [Note] InnoDB: Uses event mutexes
 
2018-07-23 11:47:11 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
 
2018-07-23 11:47:11 0 [Note] InnoDB: Number of pools: 1
 
2018-07-23 11:47:11 0 [Note] InnoDB: Using SSE2 crc32 instructions
 
2018-07-23 11:47:11 0 [Note] InnoDB: Initializing buffer pool, total size = 8G, instances = 32, chunk size = 256M
 
2018-07-23 11:47:12 0 [Note] InnoDB: Completed initialization of buffer pool
 
2018-07-23 11:47:12 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 
2018-07-23 11:47:12 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
 
2018-07-23 11:47:12 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2018-07-23 11:47:12 0 [Note] InnoDB: Setting file 'ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2018-07-23 11:47:12 0 [Note] InnoDB: File 'ibtmp1' size is now 12 MB.
 
2018-07-23 11:47:12 0 [Note] InnoDB: Waiting for purge to start
 
2018-07-23 11:47:12 0 [Note] InnoDB: 10.3.8 started; log sequence number 385445068900; transaction id 64084126
 
2018-07-23 11:47:12 0 [Note] InnoDB: Loading buffer pool(s) from /data/mariadb/ib_buffer_pool
 
2018-07-23 11:47:12 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2018-07-23 11:47:12 0 [Note] Server socket created on IP: '::'.
 
2018-07-23 11:47:12 0 [Warning] 'proxies_priv' entry '@% root@newobelisk.cpsnet.com.ar' ignored in --skip-name-resolve mode.
 
2018-07-23 11:47:12 6 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
 
2018-07-23 11:47:12 0 [Note] Reading of all Master_info entries succeded
 
2018-07-23 11:47:12 0 [Note] Added new Master_info '' to hash table
 
2018-07-23 11:47:12 0 [Note] /usr/sbin/mysqld: ready for connections.
 
Version: '10.3.8-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
 
2018-07-23 11:47:14 0 [Note] InnoDB: Buffer pool(s) load completed at 180723 11:47:14
 
2018-07-23 11:49:16 9 [Warning] Aborted connection 9 to db: 'billing_telefonia' user: 'desarrollo' host: '190.12.96.11' (Got an error reading communication packets)
 
2018-07-23 11:50:11 18 [Warning] Aborted connection 18 to db: 'billing_telefonia' user: 'desarrollo' host: '190.12.96.11' (Got an error reading communication packets)
 
180723 12:30:28 [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 https://mariadb.com/kb/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.3.8-MariaDB
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=3
 
max_threads=802
 
thread_count=9
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1894121 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7efa080009a8
 
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 = 0x7efcbc7fed70 thread_stack 0x49000
 
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x5639b60f8ece]
 
/usr/sbin/mysqld(handle_fatal_signal+0x357)[0x5639b5b95577]
 
sigaction.c:0(__restore_rt)[0x7efcd6bde5e0]
 
sql/table.cc:8448(st_key::actual_rec_per_key(unsigned int))[0x5639b5a77fb1]
 
sql/opt_split.cc:920(st_join_table::choose_best_splitting(double, unsigned long long))[0x5639b5b033ed]
 
sql/sql_select.cc:6709(best_access_path(JOIN*, st_join_table*, unsigned long long, unsigned int, bool, double, st_position*, st_position*))[0x5639b59f4d82]
 
sql/sql_select.cc:8670(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5639b59f71da]
 
sql/sql_select.cc:8738(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5639b59f7541]
 
sql/sql_select.cc:8738(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5639b59f7541]
 
sql/sql_select.cc:7901(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x5639b59f77b1]
 
sql/sql_select.cc:7479(choose_plan(JOIN*, unsigned long long))[0x5639b59f7c5d]
 
sql/sql_select.cc:4978(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x5639b5a0f94c]
 
sql/sql_select.cc:1882(JOIN::optimize_inner())[0x5639b5a18412]
 
sql/sql_select.cc:1450(JOIN::optimize())[0x5639b5a18667]
 
sql/sql_select.cc:4206(mysql_select(THD*, 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*))[0x5639b5a19c34]
 
sql/sql_class.h:3858(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**))[0x5639b5a63f2e]
 
sql/sql_parse.cc:4645(mysql_execute_command(THD*))[0x5639b59c6914]
 
sql/sql_parse.cc:8074(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5639b59c925b]
 
sql/sql_parse.cc:1847(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5639b59cbe55]
 
sql/sql_parse.cc:1394(do_command(THD*))[0x5639b59cccbe]
 
sql/sql_connect.cc:1402(do_handle_one_connection(CONNECT*))[0x5639b5a9d2ba]
 
sql/sql_connect.cc:1310(handle_one_connection)[0x5639b5a9d3dd]
 
pthread_create.c:0(start_thread)[0x7efcd6bd6e25]
 
/lib64/libc.so.6(clone+0x6d)[0x7efcd51ae34d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7efa08011730): UPDATE TASACION_buffer l,    (     select l.prefijooperador, l.compra_clave, c.clave from     TASACION_buffer l,                                  ( select ck.localidad, ck.area, ck.destino, ck.clave, CONCAT(p.prefijo_saliente_2,'%') as prefijo                                         from `clave-km_new` ck                                         INNER JOIN prefijo_por_operadores p                                         ON p.prefijo_saliente_new = LEFT(ck.prefijo, length(ck.prefijo)-1)                                         Where ck.Area = '011'       UNION       select ck.localidad, ck.area, ck.destino, ck.clave, ck.prefijo      from `clave-km_new` ck      Where ck.Area = '011'      and ck.destino = 'AMBA'                                 ) c     where l.prefijooperador like c.prefijo     and direccion in ('s','v','t')     and tipo in (1)     and dnis not Like '08%'     and prefijooperador like '011%'     and c.prefijo like '011%'     and outtrunkgroup in ( select nrotrunk from trunk         where area_local = '11' )     and (compra_clave is null OR compra_clave = '')          group by prefijooperador, c.clave    ) s2    SET l.compra_clave = s2.clave    where l.direccion in ('s','v','t')    and l.tipo in (1)    and dnis not Like '08%'    and (l.compra_clave is null OR l.compra_clave = '')    and outtrunkgroup in ( select nrotrunk from trunk     where area_local = '11' )    and l.prefijooperador = s2.prefijooperador
 
Connection ID (thread ID): 112
 
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,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on



 Comments   
Comment by Alice Sherepa [ 2018-07-23 ]

Could you please add your .cnf file(s) and output of 

 SHOW CREATE TABLE TASACION_buffer;
 
 SHOW CREATE TABLE clave-km_new;
 
 SHOW CREATE TABLE prefijo_por_operadores;
 
 SHOW CREATE TABLE trunk;

Comment by Alice Sherepa [ 2018-07-31 ]

Please provide requested information, here or upload it to ftp.askmonty.org/private

Comment by pablo arias [ 2018-07-31 ]

show create table tasacion_buffer

CREATE TABLE `TASACION_buffer` (
`ani` varchar(50) NOT NULL DEFAULT '',
`dnis` varchar(50) NOT NULL DEFAULT '',
`InTrunkGroup` int(11) DEFAULT NULL,
`OutTrunkGroup` int(11) DEFAULT NULL,
`NewStartime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`NewEndtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`Duracion_en_Segs` int(11) DEFAULT NULL,
`Operador` varchar(50) DEFAULT NULL,
`NroDeLoteCPP` int(11) DEFAULT NULL,
`Duracion` double(8,4) DEFAULT NULL,
`Tipo` tinyint(4) DEFAULT NULL,
`Direccion` char(1) NOT NULL DEFAULT '',
`prefijoventa` varchar(15) DEFAULT NULL,
`PrecioVentaMinuto` float(8,5) DEFAULT NULL,
`PrecioCompraMinuto` float(8,5) DEFAULT NULL,
`prefijooperador` varchar(50) DEFAULT NULL,
`listaprecio` int(11) DEFAULT NULL,
`estado` tinyint(4) DEFAULT 0,
`periodo` varchar(10) DEFAULT NULL,
`nro_linea` int(11) DEFAULT NULL,
`interno` int(11) DEFAULT NULL,
`clave` char(4) NOT NULL,
`ticket_tipo` char(3) NOT NULL DEFAULT '',
`ticket_id` bigint(20) NOT NULL,
`prefijocompra` varchar(15) DEFAULT NULL,
`precioventamoneda` char(3) DEFAULT NULL,
`preciocompramoneda` char(3) DEFAULT NULL,
`compra_clave` varchar(4) DEFAULT NULL,
`preciotransito` float(8,5) DEFAULT 0.00000,
`preciotransporte` float(8,5) DEFAULT 0.00000,
PRIMARY KEY (`ani`,`dnis`,`NewStartime`,`NewEndtime`,`Direccion`,`ticket_tipo`,`ticket_id`),
KEY `idxani` (`ani`) USING BTREE,
KEY `idxdnis` (`dnis`) USING BTREE,
KEY `idxClave` (`clave`) USING BTREE,
KEY `idxTicket_t` (`Tipo`) USING BTREE,
KEY `idxLista` (`listaprecio`) USING BTREE,
KEY `idxOutTrunk` (`OutTrunkGroup`) USING BTREE,
KEY `idxPrefijoCompra` (`prefijocompra`) USING BTREE,
KEY `idxID` (`ticket_tipo`,`ticket_id`) USING BTREE,
KEY `idxTIpo` (`Tipo`) USING BTREE,
KEY `idxPrefijoVenta` (`prefijoventa`) USING BTREE,
KEY `idxPrefijoOp` (`prefijooperador`) USING BTREE,
KEY `idxNroLinea` (`nro_linea`) USING BTREE,
KEY `idxDireccion` (`Direccion`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

show create table clave-km_new

CREATE TABLE `clave-km_new` (
`Localidad` varchar(70) NOT NULL DEFAULT '',
`Area` varchar(40) DEFAULT NULL,
`Prefijo` varchar(40) NOT NULL DEFAULT '',
`Destino` varchar(255) DEFAULT NULL,
`Clave` int(3) NOT NULL,
`Hasta` varchar(255) DEFAULT NULL,
`Entre` varchar(255) DEFAULT NULL,
`orden` varchar(30) NOT NULL,
PRIMARY KEY (`Localidad`,`Prefijo`),
KEY `IdxPrefijo` (`Prefijo`),
KEY `IdxClave` (`Clave`),
KEY `IdxLocalidad` (`Localidad`),
KEY `IdxComp` (`Prefijo`,`Clave`),
KEY `IdxComp2` (`Localidad`,`Prefijo`,`Clave`),
KEY `Idxarea` (`Area`),
KEY `idxDestino` (`Destino`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

show create table prefijo_por_operadores

CREATE TABLE `prefijo_por_operadores` (
`indicativo` varchar(10) CHARACTER SET latin1 NOT NULL,
`bloque` varchar(10) CHARACTER SET latin1 NOT NULL,
`prefijo_saliente` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`prefijo_entrante` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`operador` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
`modalidad` varchar(25) CHARACTER SET latin1 DEFAULT NULL,
`localidad` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`prefijo_saliente_2` varchar(20) DEFAULT NULL,
`prefijo_saliente_new` varchar(20) DEFAULT NULL,
`provincia` varchar(50) DEFAULT NULL,
PRIMARY KEY (`indicativo`,`bloque`),
KEY `idxOperador` (`operador`),
KEY `idxPrefijoEnt` (`prefijo_entrante`),
KEY `idxLocalidad` (`localidad`),
KEY `idxIndi` (`indicativo`,`bloque`),
KEY `idxPrefijo` (`indicativo`),
KEY `idxBloque` (`bloque`),
KEY `idxprefSal` (`prefijo_saliente_2`),
KEY `idxprefSalnew` (`prefijo_saliente_new`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

show create trunk

CREATE TABLE `trunk` (
`nrotrunk` int(11) NOT NULL DEFAULT 0,
`proveedor` varchar(60) NOT NULL DEFAULT '',
`carrier` tinyint(4) NOT NULL DEFAULT 0,
`Color` varchar(10) NOT NULL,
`grupo_trunk` varchar(40) DEFAULT NULL,
`nro_linea` int(11) DEFAULT NULL,
`pectra` varchar(5) DEFAULT NULL,
`cuil` varchar(15) DEFAULT NULL,
`cant_canales` int(5) DEFAULT 30,
`area_local` int(11) DEFAULT 11,
`trunk_ssw` varchar(30) NOT NULL,
`compara_precio` int(1) NOT NULL DEFAULT 0,
`fact_entrantes` int(1) NOT NULL DEFAULT 1,
`activo` int(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`nrotrunk`),
KEY `idxTrunk` (`nrotrunk`),
KEY `idxCarrier` (`carrier`),
KEY `idxSsw` (`trunk_ssw`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Troncales del sistema no eliminar'

my.cnf

[mysqld]
datadir=/data/mariadb
symbolic-links=0
secure_auth=off

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#

  1. include all files from the config directory
    #
    !includedir /etc/my.cnf.d

server.cnf

[mysqld]

  1. LOG
    log_error=/var/log/mariadb/mariadb.log
    long_query_time=60
    sql_mode=""

query_cache_size=16M
key_buffer_size=256M

innodb_data_file_path="/data/mariadb/ibdata1:2000M;/data/mariadb/ibdata2:2000M:autoextend"
innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=32
innodb_buffer_pool_chunk_size=256M
innodb_log_group_home_dir="/data/mariadb"

innodb_log_file_size=2G
innodb_log_buffer_size=8M
innodb_log_files_in_group=4
innodb_file_per_table=1

innodb_flush_log_at_trx_commit=2

innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

max_connection = 800
max_user_connections = 500
max_connect_errors = 300

skip-name-resolve
skip-host-cache

Comment by Alice Sherepa [ 2018-08-01 ]

parias78, thanks!
Reproducible on 10.3 (commit 0c745c743c65ceb6533babdf)
test case:

 --source include/have_innodb.inc
 
CREATE TABLE t1 (
 
  i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8 int,
 
  c1 int, c2 int, p1 int,
 
  KEY (i1), KEY (i2), KEY (c2), KEY (i7), KEY (i4), KEY (i8), KEY (i6), KEY (p1) 
) ENGINE=InnoDB ;
 
 
 
CREATE TABLE t2 (l1 int, a1 int, p4 int, d1 int, c2 int) ENGINE=InnoDB;
 
CREATE TABLE t3 (l1 int, p2 int, p3 int) ENGINE=InnoDB ;
 
 
 
UPDATE  t1,
 
  (SELECT t1.p1, t1.c1, tmp1.c2 
    FROM  t1,
 
      (SELECT ck.c2,CONCAT(t3.p2,'%') AS p4
 
          FROM t2 ck 
      JOIN t3 ON t3.p3 = LEFT(ck.p4, length(ck.p4)-1)
 
      ) tmp1
 
   GROUP BY p1,tmp1.c2 
  ) tmp2
 
SET t1.c1 = tmp2.c2
 
WHERE t1.p1 = tmp2.p1 ;


Thread 1 (Thread 0x7f8e20c9f700 (LWP 28248)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x0000564772d648b5 in my_write_core (sig=11) at /git/10.3/mysys/stacktrace.c:481
 
#2  0x00005647725a815b in handle_fatal_signal (sig=11) at /git/10.3/sql/signal_handler.cc:305
 
#3  <signal handler called>
 
#4  0x00005647723db2b5 in st_key::actual_rec_per_key (this=0x0, i=0) at /git/10.3/sql/table.cc:8458
 
#5  0x00005647724c39df in st_join_table::choose_best_splitting (this=0x7f8dd0053fb8, record_count=1, remaining_tables=2) at /git/10.3/sql/opt_split.cc:919
 
#6  0x0000564772310216 in best_access_path (join=0x7f8dd0091ed0, s=0x7f8dd0053fb8, remaining_tables=2, idx=1, disable_jbuf=false, record_count=1, pos=0x7f8dd0054698, loose_scan_pos=0x7f8e20c9d0c0) at /git/10.3/sql/sql_select.cc:6716
 
#7  0x0000564772314fcc in best_extension_by_limited_search (join=0x7f8dd0091ed0, remaining_tables=2, idx=1, record_count=1, read_time=1.2, search_depth=61, prune_level=1, use_cond_selectivity=1) at /git/10.3/sql/sql_select.cc:8674
 
#8  0x00005647723154a9 in best_extension_by_limited_search (join=0x7f8dd0091ed0, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /git/10.3/sql/sql_select.cc:8745
 
#9  0x0000564772313590 in greedy_search (join=0x7f8dd0091ed0, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=1) at /git/10.3/sql/sql_select.cc:7908
 
#10 0x0000564772312a78 in choose_plan (join=0x7f8dd0091ed0, join_tables=3) at /git/10.3/sql/sql_select.cc:7486
 
#11 0x000056477230b95e in make_join_statistics (join=0x7f8dd0091ed0, tables_list=..., keyuse_array=0x7f8dd00921c0) at /git/10.3/sql/sql_select.cc:4985
 
#12 0x000056477230089f in JOIN::optimize_inner (this=0x7f8dd0091ed0) at /git/10.3/sql/sql_select.cc:1888
 
#13 0x00005647722feef5 in JOIN::optimize (this=0x7f8dd0091ed0) at /git/10.3/sql/sql_select.cc:1448
 
#14 0x0000564772308a2c in mysql_select (thd=0x7f8dd0000b00, tables=0x7f8dd0014f80, wild_num=0, fields=..., conds=0x7f8dd00907c8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x7f8dd0091e00, unit=0x7f8dd00049a8, select_lex=0x7f8dd0005118) at /git/10.3/sql/sql_select.cc:4213
 
#15 0x00005647723b706e in mysql_multi_update (thd=0x7f8dd0000b00, table_list=0x7f8dd0014f80, fields=0x7f8dd0005240, values=0x7f8dd0005740, conds=0x7f8dd00907c8, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x7f8dd00049a8, select_lex=0x7f8dd0005118, result=0x7f8e20c9de50) at /git/10.3/sql/sql_update.cc:1764
 
#16 0x00005647722be029 in mysql_execute_command (thd=0x7f8dd0000b00) at /git/10.3/sql/sql_parse.cc:4636
 
#17 0x00005647722c92a4 in mysql_parse (thd=0x7f8dd0000b00, rawbuf=0x7f8dd0014d08 "UPDATE  t1,\n(SELECT t1.p1, t1.c1, tmp1.c2 \nFROM  t1,\n(SELECT ck.c2,CONCAT(t3.p2,'%') AS p4\nFROM t2 ck \nJOIN t3 ON t3.p3 = LEFT(ck.p4, length(ck.p4)-1)\n) tmp1\nGROUP BY p1,tmp1.c2 \n) tmp2\nSET t1.c1 = tm"..., length=227, parser_state=0x7f8e20c9e470, is_com_multi=false, is_next_command=false) at /git/10.3/sql/sql_parse.cc:8074
 
#18 0x00005647722b62f2 in dispatch_command (command=COM_QUERY, thd=0x7f8dd0000b00, packet=0x7f8dd0125e51 "UPDATE  t1,\n(SELECT t1.p1, t1.c1, tmp1.c2 \nFROM  t1,\n(SELECT ck.c2,CONCAT(t3.p2,'%') AS p4\nFROM t2 ck \nJOIN t3 ON t3.p3 = LEFT(ck.p4, length(ck.p4)-1)\n) tmp1\nGROUP BY p1,tmp1.c2 \n) tmp2\nSET t1.c1 = tm"..., packet_length=228, is_com_multi=false, is_next_command=false) at /git/10.3/sql/sql_parse.cc:1847
 
#19 0x00005647722b4d0f in do_command (thd=0x7f8dd0000b00) at /git/10.3/sql/sql_parse.cc:1392
 
#20 0x000056477241adf5 in do_handle_one_connection (connect=0x5647757b7c30) at /git/10.3/sql/sql_connect.cc:1402
 
#21 0x000056477241ab46 in handle_one_connection (arg=0x5647757b7c30) at /git/10.3/sql/sql_connect.cc:1308
 
#22 0x0000564772cf4dca in pfs_spawn_thread (arg=0x5647757cb5d0) at /git/10.3/storage/perfschema/pfs.cc:1862
 
#23 0x00007f8e282e66ba in start_thread (arg=0x7f8e20c9f700) at pthread_create.c:333
 
#24 0x00007f8e2777b41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Comment by Igor Babaev [ 2018-08-04 ]

A fix for this bug was pushed into 10.3

Generated at Thu Feb 08 08:31:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.