Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9674

Server crash on large transaction combined with multi-update inside stored procedure

    XMLWordPrintable

Details

    Description

      We execute this query

      SET FOREIGN_KEY_CHECKS = 0;
      REPLACE INTO Boeken ( Feedcode, Ean, NaamUitgever, Titel, Ondertitel, Titelserie, Bindwijze,  onixbindwijze, Samenvatting, PublishingStatus, ProductAvailability, BoekStatus, Verschijningsdatum, Taalvermelding )
      	SELECT 'gardners', Ean, B.`naamuitgever`, titel, ondertitel, B.`titelserie`, F.`Bindwijze`, B.`onixbindwijze`, B.`samenvatting`,
      	A.`PublishingStatus` , A.`ProductAvailability`,A.`BoekStatus`, CONCAT( SUBSTR(verschijningsdatum,1,4),'-',SUBSTR(verschijningsdatum,5,2),'-',SUBSTR(verschijningsdatum,7,2) ),
      	COALESCE( Taalvermelding, 'English' )
      	FROM Feed_Gardners_Boeken B
      	JOIN ONIX2Availability A ON ( A.`AvailabilityCode` = B.`productavailability` )
      	JOIN ONIX2ProductForm F ON ( B.`onixbindwijze` = F.`ProductForm` )
      	LEFT JOIN bic ON ( bic.`_Value` = B.`biccode` )
      	WHERE Ean LIKE "9%" AND NOT Bindwijze IS NULL;
      

      And the server crashes:

      Server version: 10.1.12-MariaDB-1~trusty
      key_buffer_size=16777216
      read_buffer_size=131072
      max_used_connections=181
      max_threads=502
      thread_count=46
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1118998 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x0x7f0d137c5008
      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 = 0x7f0d2bbc3df0 thread_stack 0x80000
      /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7f1b0443805e]
      /usr/sbin/mysqld(handle_fatal_signal+0x38d)[0x7f1b03f651bd]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f1b024bb340]
      /usr/sbin/mysqld(+0x75241d)[0x7f1b0410741d]
      /usr/sbin/mysqld(+0x75f69d)[0x7f1b0411469d]
      /usr/sbin/mysqld(+0x374476)[0x7f1b03d29476]
      /usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x818)[0x7f1b03e379e8]
      /usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x1b)[0x7f1b03e3a1bb]
      /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x8f)[0x7f1b03e3a30f]
      /usr/sbin/mysqld(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lexPP12multi_update+0x122)[0x7f1b03e81fc2]
      /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6bc6)[0x7f1b03dea236]
      /usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x15)[0x7f1b040717c5]
      /usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x83)[0x7f1b04078373]
      /usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x204)[0x7f1b04078924]
      /usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x786)[0x7f1b040747f6]
      /usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x617)[0x7f1b04075ea7]
      /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6889)[0x7f1b03de9ef9]
      /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x26d)[0x7f1b03decfed]
      /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x2460)[0x7f1b03df0330]
      /usr/sbin/mysqld(_Z10do_commandP3THD+0x169)[0x7f1b03df0ae9]
      /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x18a)[0x7f1b03eb40fa]
      /usr/sbin/mysqld(handle_one_connection+0x40)[0x7f1b03eb42d0]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f1b024b3182]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f1b01bd647d]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7f0d5ec72260): is an invalid pointer
      Connection ID (thread ID): 3267
      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,materi
      alization=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 transaction had, at the point of the crash, about 10M undo entries.

      Table information:

      CREATE TABLE `Feed_Gardners_Boeken` (
        `Ean` char(13) NOT NULL,
        `titel` varchar(255) DEFAULT NULL,
        `ondertitel` varchar(255) DEFAULT NULL,
        `prijs` decimal(6,2) DEFAULT NULL,
        `titelserie` varchar(255) DEFAULT NULL,
        `reeksnummer` varchar(255) DEFAULT NULL,
        `samenvatting` varchar(255) DEFAULT NULL,
        `inhoudsopgave` varchar(255) DEFAULT NULL,
        `onixbindwijze` varchar(255) DEFAULT NULL,
        `aantalblz` varchar(255) DEFAULT NULL,
        `auteur1` varchar(255) DEFAULT NULL,
        `auteur2` varchar(255) DEFAULT NULL,
        `auteur3` varchar(255) DEFAULT NULL,
        `biccode` varchar(255) DEFAULT NULL,
        `taalvermelding` varchar(255) DEFAULT NULL,
        `naamuitgever` varchar(255) DEFAULT NULL,
        `herdrukdatum` varchar(255) DEFAULT NULL,
        `verschijningsdatum` varchar(255) DEFAULT NULL,
        `publishingstatus` varchar(20) DEFAULT NULL,
        `productavailability` varchar(20) DEFAULT NULL,
        `fysiek_boek` char(13) DEFAULT NULL,
        `eanebook` char(13) DEFAULT NULL,
        `vervangt` char(13) DEFAULT NULL,
        `vervangen_door` char(13) DEFAULT NULL,
        `ebook` char(13) DEFAULT NULL,
        `hoogte` int(11) DEFAULT NULL,
        `breedte` int(11) DEFAULT NULL,
        `dikte` int(11) DEFAULT NULL,
        `gewicht` int(11) DEFAULT NULL,
        PRIMARY KEY (`Ean`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
       
      Index:
      Table                 Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  Index_comment  
      Feed_Gardners_Boeken  0           PRIMARY   1             Ean          A          6201104                              BTREE                               
       
       
      CREATE TABLE `ONIX2Availability` (
        `AvailabilityCode` char(2) NOT NULL,
        `AvailabilityText` varchar(255) DEFAULT NULL,
        `Leverbaar` tinyint(4) DEFAULT NULL,
        `BoekStatus` tinyint(4) DEFAULT NULL,
        `PublishingStatus` tinyint(2) DEFAULT NULL,
        `ProductAvailability` tinyint(2) DEFAULT NULL,
        PRIMARY KEY (`AvailabilityCode`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
       
      CREATE TABLE `ONIX2ProductForm` (
        `ProductForm` char(2) NOT NULL,
        `ProductFormDescription` varchar(255) DEFAULT NULL,
        `Bindwijze` varchar(50) DEFAULT NULL,
        PRIMARY KEY (`ProductForm`),
        KEY `Bindwijze` (`Bindwijze`),
        CONSTRAINT `ONIX2ProductForm_ibfk_1` FOREIGN KEY (`Bindwijze`) REFERENCES `Manpo`.`Bindwijze` (`Bindwijze`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
       
      CREATE TABLE `Boeken` (
        `FeedCode` char(8) NOT NULL COMMENT 'Feed code. BV ''wiley''',
        `Ean` char(13) NOT NULL COMMENT 'EAN',
        `NaamUitgever` varchar(50) DEFAULT NULL COMMENT 'Tekstuele naam uitgever',
        `Imprint` varchar(50) DEFAULT NULL,
        `CommercieleImprint` varchar(50) DEFAULT NULL,
        `UitgeverFeedId` int(11) DEFAULT NULL COMMENT 'Feed-specifieke ID uitgever',
        `Enanderen` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Als er '', e.a.'' getoond moet worden',
        `Titel` varchar(255) NOT NULL DEFAULT '' COMMENT 'Titel van product',
        `Ondertitel` varchar(255) DEFAULT NULL COMMENT 'Ondertitel van product',
        `Naamserie` varchar(255) DEFAULT NULL COMMENT 'Naam van de serie. BV ''Voor Dummies''',
        `Titelserie` varchar(255) DEFAULT NULL COMMENT 'Titel in de serie. BV ''Deel 8''',
        `Reeksnummer` int(11) DEFAULT NULL COMMENT 'Reeksnummer in de serie, BV 8',
        `ReeksnummerTxt` char(10) DEFAULT NULL,
        `Editie` varchar(100) DEFAULT NULL COMMENT '1e druk, etc.',
        `Editieversie` varchar(100) DEFAULT NULL,
        `Bindwijze` varchar(50) NOT NULL DEFAULT 'paperback' COMMENT 'Een door ons goedgekeurde bindwijze',
        `onixbindwijze` char(2) NOT NULL DEFAULT '',
        `InclusiefProdukt` varchar(50) NOT NULL,
        `Aantalblz` smallint(6) DEFAULT NULL,
        `Abstract` text,
        `Samenvatting` text,
        `Inhoudsopgave` text,
        `Aanmaakdatum` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        `Laatstgewijzigd` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        `Taalvermelding` varchar(2) DEFAULT NULL,
        `Hoogte` double DEFAULT NULL,
        `Breedte` double DEFAULT NULL,
        `Dikte` double DEFAULT NULL,
        `Gewicht` double DEFAULT NULL,
        `Verschijningsdatum` date DEFAULT '0000-00-00',
        `Herdrukdatum` date DEFAULT NULL,
        `VerwachteVerschijningsdatum` date DEFAULT '0000-00-00',
        `Uitverkochtdatum` date DEFAULT '0000-00-00',
        `CBstatus` varchar(50) DEFAULT NULL,
        `Boekstatus` smallint(6) NOT NULL DEFAULT '1' COMMENT 'Mainpress boekstatus',
        `PublishingStatus` tinyint(2) DEFAULT '0' COMMENT 'Zie ONIX',
        `ProductAvailability` char(2) DEFAULT NULL COMMENT 'Zie ONIX',
        `PublishingStatusTxt` char(25) DEFAULT NULL,
        `ProductAvailabilityTxt` varchar(50) DEFAULT NULL,
        `Vervangt` char(13) DEFAULT NULL,
        `Vervangen_door` char(13) DEFAULT NULL,
        `Levertijd` tinyint(2) DEFAULT NULL,
        `Fysiek_boek` char(13) NOT NULL COMMENT 'Indien ebook, ean van fysiek boek',
        `ebook` char(13) NOT NULL,
        `eanebook` char(13) NOT NULL,
        `InkoopKorting` tinyint(4) NOT NULL COMMENT 'De korting die wij krijgen. 0 indien niet bekend.',
        `CoverURL` varchar(255) DEFAULT NULL,
        `BackcoverURL` varchar(255) DEFAULT NULL,
        `PDFURL` varchar(255) DEFAULT NULL,
        `BestandLaatsteWijziging` varchar(255) DEFAULT NULL COMMENT 'Bestandsnaam van bestand dat laatste wijziging bevat',
        `InAssortiment` tinyint(1) DEFAULT NULL COMMENT '1 Als het getoond word op de website(s)',
        `BestandsGrootte` varchar(50) DEFAULT NULL,
        `TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`FeedCode`,`Ean`),
        UNIQUE KEY `i1` (`Ean`,`InAssortiment`),
        KEY `i2` (`NaamUitgever`),
        KEY `i15` (`Naamserie`),
        KEY `i16` (`Titelserie`),
        KEY `Laatstgewijzigd` (`Laatstgewijzigd`),
        KEY `Publicatiestatus_code` (`ProductAvailability`,`PublishingStatus`,`Taalvermelding`,`Bindwijze`),
        KEY `Uitgever_cbid` (`UitgeverFeedId`),
        KEY `CBstatus_code` (`PublishingStatus`,`ProductAvailability`),
        KEY `Producten_ibfk_2` (`FeedCode`,`UitgeverFeedId`),
        KEY `InAssortiment` (`InAssortiment`),
        KEY `Bindwijze` (`Bindwijze`),
        CONSTRAINT `Boeken_ibfk_1` FOREIGN KEY (`FeedCode`) REFERENCES `Feeds` (`FeedCode`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
       
      CREATE TABLE `bic` (
        `_Value` char(10) NOT NULL,
        `_Description` varchar(255) DEFAULT NULL,
        `rubriek` char(50) DEFAULT NULL,
        `rubriekman` varchar(50) DEFAULT NULL,
        `rubriekcom` varchar(50) DEFAULT NULL,
        `rubriekjur` varchar(50) DEFAULT NULL,
        `rubriekalg` varchar(50) DEFAULT NULL,
        `website` varchar(50) DEFAULT NULL,
        `NUR` int(11) DEFAULT NULL,
        `extra_check` varchar(3) NOT NULL,
        `plts` varchar(45) DEFAULT NULL,
        `done` varchar(1) DEFAULT NULL,
        `level_1` varchar(255) DEFAULT NULL,
        `level_2` varchar(255) DEFAULT NULL,
        `level_3` varchar(255) DEFAULT NULL,
        `level_4` varchar(255) DEFAULT NULL,
        `level_5` varchar(255) DEFAULT NULL,
        `level_6` varchar(255) DEFAULT NULL,
        `old_value` varchar(64) DEFAULT NULL,
        `in_feed` varchar(2) DEFAULT NULL,
        `ranking` int(11) DEFAULT NULL,
        `JuridicaCode` char(10) DEFAULT NULL,
        PRIMARY KEY (`_Value`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8
      The last two tables are tiny... < 100 records each.
      

      my.cnf

      #
      # The MySQL database server configuration file.
      #
      # You can copy this to one of:
      # - "/etc/mysql/my.cnf" to set global options,
      # - "~/.my.cnf" to set user-specific options.
      # 
      # One can use all long options that the program supports.
      # Run program with --help to get a list of available options and with
      # --print-defaults to see which it would actually understand and use.
      #
      # For explanations see
      # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
       
      # This will be passed to all mysql clients
      # It has been reported that passwords should be enclosed with ticks/quotes
      # escpecially if they contain "#" chars...
      # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
      [client]
      port            = 3306
      socket          = /var/run/mysqld/mysqld.sock
       
      # Here is entries for some specific programs
      # The following values assume you have at least 32M ram
       
      # This was formally known as [safe_mysqld]. Both versions are currently parsed.
      [mysqld_safe]
      socket          = /var/run/mysqld/mysqld.sock
      nice            = 0
       
      [mysqld]
      replicate-wild-ignore-table = %.Voorafgaand%_Klanten
      replicate-wild-ignore-table = tmp.%
      transaction-isolation =  READ-COMMITTED
       
      user            = mysql
      pid-file        = /var/run/mysqld/mysqld.pid
      socket          = /var/run/mysqld/mysqld.sock
      port            = 3306
      basedir         = /usr
      datadir         = /var/lib/mysql
      tmpdir          = /tmp
      lc-messages-dir = /usr/share/mysql
      skip-external-locking
       
      log-error = /data/bigmomma/mysql/log/error.log
      innodb_stats_on_metadata = 0
      lock_wait_timeout = 20
      innodb_lock_wait_timeout = 20
       
      #
      # Instead of skip-networking the default is now to listen only on
      # localhost which is more compatible and is not less secure.
      bind-address            = 0.0.0.0
       
      # GTIDs
      master_info_repository = table
      relay_log_info_repository = table
      master-verify-checksum = on
      slave-sql-verify-checksum = on
       
       
      #
      # * Fine Tuning
      #
      key_buffer              = 16M
      #max_allowed_packet     = 16M
      thread_stack            = 512K
      thread_cache_size       = 8
      myisam-recover         = BACKUP
       
      # SAFETY #
      max_allowed_packet             = 64M
      max_connect_errors             = 1000000
      sysdate_is_now                 = 1
       
      max_connections=500
      event_scheduler=ON
      table_cache     = 3000
       
      tmp_table_size                 = 64M
      max_heap_table_size            = 64M
      query_cache_type               = 1
      query_cache_size               = 8M
      max_connections                = 500
      thread_cache_size              = 50
      open_files_limit               = 65535
      table_definition_cache         = 4096
      table_open_cache               = 4096
       
      max_sp_recursion_depth         = 2
       
      back_log=1500
       
       
      # UTF-8
      character_set_server = utf8
      collation_server = utf8_general_ci
       
       
      #
      # * Logging and Replication
      #
      #log_slow_queries       = /var/log/mysql/mysql-slow.log
      long_query_time         = 1
      log-queries-not-using-indexes
      log-output              = TABLE
      log_warnings            = 2
       
      sync_binlog             = 0
      binlog_checksum         = crc32
      binlog_commit_wait_usec = 50000
      binlog_commit_wait_count= 20
      server-id               = 765
      log_bin                 = /data/bigmomma/mysql/log/mysql-bin.log
      binlog_format           = MIXED
      expire_logs_days        = 7
      max_binlog_size         = 1G
      log-slave-updates
      slave_compressed_protocol = 1
      relay-log               = /data/bigmomma/mysql/log/mysql-relay.log
       
       
      # INNODB #
      innodb_log_files_in_group      = 2
      innodb_flush_log_at_trx_commit = 0
      innodb_buffer_pool_size        = 50G
      innodb_file_per_table
      innodb_log_file_size=1G
      innodb_flush_method=O_DIRECT
      innodb_file_format=barracuda
      innodb_io_capacity = 20000
      innodb_buffer_pool_instances=10
      innodb_log_buffer_size=32M
       
      group_concat_max_len = 8096
       
      [mysqldump]
      quick
      quote-names
      max_allowed_packet      = 16M
      gtid                    = ON
      ignore-table            = telefoongids.white_subscriber
      ignore-table            = telefoongids.pink_subscriber
       
      [mysql]
      #no-auto-rehash # faster start of mysql but no tab completition
       
      [isamchk]
      key_buffer              = 16M
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              CrewOne Sander Pilon
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.