Details

    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
      

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment - - edited

            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;
             

            alice Alice Sherepa added a comment - - edited 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;
            alice Alice Sherepa added a comment -

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

            alice Alice Sherepa added a comment - Please provide requested information, here or upload it to ftp.askmonty.org/private
            parias78 pablo arias added a comment -

            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

            parias78 pablo arias added a comment - 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 # include all files from the config directory # !includedir /etc/my.cnf.d server.cnf [mysqld] 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
            alice Alice Sherepa added a comment -

            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
            

            alice Alice Sherepa added a comment - 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

            A fix for this bug was pushed into 10.3

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 10.3

            People

              igor Igor Babaev (Inactive)
              parias78 pablo arias
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.