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

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

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

            elenst Elena Stepanova added a comment - - edited

            I'm still struggling to reproduce the crash. Could you please let me know if any of these actions are possible for you?

            1)
            Run

            CALL Managementboek_Klanten.update_ebooks_verzonden( '12919596' );
            CALL Managementboek_Klanten.update_ebooks_verzonden( '12919596' );
            

            (or maybe some other argument, but importantly two identical calls one after another) from a fresh MySQL client and see if it causes the crash reliably?

            2)
            Create a procedure identical to Managementboek_Klanten.update_ebooks_verzonden, only instead of UPDATE it would do EXPLAIN UPDATE, and run it, also twice, and if it does not crash, paste the output it produces?

            3)
            Run the instance on a debug binary so that we could get a decent stack trace (I will provide the binary if you are willing to do so);

            4)
            Provide SHOW INDEX IN ... for the tables involved in the multi-table update – I mentioned it before, but we never got to it.

            5)
            Upload the full version of the general log which you grepped in one of your previous comment (if you upload it to our ftp.askmonty.org/private, only MariaDB developers will have access to it).

            I realize that some of these steps are unwanted for a production server, but whatever of the above that you find possible could help us to pinpoint the problem.

            elenst Elena Stepanova added a comment - - edited I'm still struggling to reproduce the crash. Could you please let me know if any of these actions are possible for you? 1) Run CALL Managementboek_Klanten.update_ebooks_verzonden( '12919596' ); CALL Managementboek_Klanten.update_ebooks_verzonden( '12919596' ); (or maybe some other argument, but importantly two identical calls one after another) from a fresh MySQL client and see if it causes the crash reliably? 2) Create a procedure identical to Managementboek_Klanten.update_ebooks_verzonden , only instead of UPDATE it would do EXPLAIN UPDATE , and run it, also twice, and if it does not crash, paste the output it produces? 3) Run the instance on a debug binary so that we could get a decent stack trace (I will provide the binary if you are willing to do so); 4) Provide SHOW INDEX IN ... for the tables involved in the multi-table update – I mentioned it before, but we never got to it. 5) Upload the full version of the general log which you grepped in one of your previous comment (if you upload it to our ftp.askmonty.org/private, only MariaDB developers will have access to it). I realize that some of these steps are unwanted for a production server, but whatever of the above that you find possible could help us to pinpoint the problem.
            CrewOne Sander Pilon added a comment -

            I can imagine. I will try and see what I can do for you tomorrow.

            I can tell you this at the moment: That stored procedure is called 1000+ times every day. We have had a lot of server crashes the last months (starting last year) - but we only noticed a reliable pattern when we started doing those large 5M updates.
            However, 99,9% of the times the stored procedure yields no affected rows and it was only called from one place in the code. So we removed the code and did the UPDATE loop in PHP. We haven't had a crash since. Besides, the stored procedure nicely fits your prediction - a multi update inside a stored procedure.

            Also, I just tried - calling that stored procedure manually does not crash the server. Not when called twice, not when called 100 times.

            I might be able to run a debug version sometime late at night, but I tried it last week and it was too slow to handle production traffic. So I can't do that during business hours. (07:00-24:00)
            Problem is, when I ran the queries on a test server it did not crash. From my limited observations, it seems that for the crash to happen at least three things are nessecary: (1) Actual traffic on the SQL server and (2) that stored procedure and (3) a large transaction.

            CrewOne Sander Pilon added a comment - I can imagine. I will try and see what I can do for you tomorrow. I can tell you this at the moment: That stored procedure is called 1000+ times every day. We have had a lot of server crashes the last months (starting last year) - but we only noticed a reliable pattern when we started doing those large 5M updates. However, 99,9% of the times the stored procedure yields no affected rows and it was only called from one place in the code. So we removed the code and did the UPDATE loop in PHP. We haven't had a crash since. Besides, the stored procedure nicely fits your prediction - a multi update inside a stored procedure. Also, I just tried - calling that stored procedure manually does not crash the server. Not when called twice, not when called 100 times. I might be able to run a debug version sometime late at night, but I tried it last week and it was too slow to handle production traffic. So I can't do that during business hours. (07:00-24:00) Problem is, when I ran the queries on a test server it did not crash. From my limited observations, it seems that for the crash to happen at least three things are nessecary: (1) Actual traffic on the SQL server and (2) that stored procedure and (3) a large transaction.

            CrewOne,

            Yes, it's certainly the procedure that causes the crash, everything fits, including two calls in a row – it so happens due to internals of the server that the 2nd execution of a stored procedure or prepared statement is a notoriously weak spot.

            The crash happens somewhere in optimizer code, which means that it depends on the contents of tables, or rather on the execution plan that the optimizer chooses (which, in turn, depends on the contents of the tables, indexes and such). So, if your test server does not have data identical to the production server, it might also be the reason why you don't observe the problem on the test server.

            I realize that even if you could provide the data dump, it would be too big, so I'm not asking about it, but SHOW INDEX might help me generate similar artificial data which would make optimizer follow the same code path. Getting execution plan is another approach to the same problem. But of course if you are not getting the crash reliably by executing the procedure, getting EXPLAIN is useless. SHOW INDEX could still be helpful though.

            There are known ways to make multi-update crash, but none of them 100% matches the query that you have, so you probably have something new. Unfortunately, the visible part of the stack trace is too generic, the most interesting lines would be the upper ones, so it's hard to say what the problem is, exactly. That's how the debug stack trace would help.

            Finally, regarding a large transaction, that's why I was asking for an unabridged general log – it would be useful to see what else was running at the time of the crash.

            elenst Elena Stepanova added a comment - CrewOne , Yes, it's certainly the procedure that causes the crash, everything fits, including two calls in a row – it so happens due to internals of the server that the 2nd execution of a stored procedure or prepared statement is a notoriously weak spot. The crash happens somewhere in optimizer code, which means that it depends on the contents of tables, or rather on the execution plan that the optimizer chooses (which, in turn, depends on the contents of the tables, indexes and such). So, if your test server does not have data identical to the production server, it might also be the reason why you don't observe the problem on the test server. I realize that even if you could provide the data dump, it would be too big, so I'm not asking about it, but SHOW INDEX might help me generate similar artificial data which would make optimizer follow the same code path. Getting execution plan is another approach to the same problem. But of course if you are not getting the crash reliably by executing the procedure, getting EXPLAIN is useless. SHOW INDEX could still be helpful though. There are known ways to make multi-update crash, but none of them 100% matches the query that you have, so you probably have something new. Unfortunately, the visible part of the stack trace is too generic, the most interesting lines would be the upper ones, so it's hard to say what the problem is, exactly. That's how the debug stack trace would help. Finally, regarding a large transaction, that's why I was asking for an unabridged general log – it would be useful to see what else was running at the time of the crash.
            CrewOne Sander Pilon added a comment -

            The test server has more or less the same data - it is a replicated server.

            MariaDB [(none)]> SHOW INDEX IN Managementboek_Klanten.`Bestelling`;
            Empty set (0.00 sec)
             
            MariaDB [(none)]> SHOW INDEX IN Managementboek_Klanten.`Bestellingen`;
            +--------------+------------+---------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            | Table        | Non_unique | Key_name            | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
            +--------------+------------+---------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            | Bestellingen |          0 | PRIMARY             |            1 | Id                   | A         |     4181117 |     NULL | NULL   |      | BTREE      |         |               |
            | Bestellingen |          1 | Bestellingid        |            1 | Bestellingid         | A         |     4181117 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bestellingen |          1 | Artikelcode         |            1 | Ean                  | A         |      209055 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bestellingen |          1 | NewIndex1           |            1 | Id                   | A         |     4181117 |     NULL | NULL   |      | BTREE      |         |               |
            | Bestellingen |          1 | NewIndex1           |            2 | BackorderOrigineel   | A         |     4181117 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bestellingen |          1 | NewIndex2           |            1 | AantalBackorder      | A         |         202 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bestellingen |          1 | backorder           |            1 | BackorderOrigineel   | A         |      696852 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bestellingen |          1 | NewIndex3           |            1 | StatusId             | A         |          70 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bestellingen |          1 | FactuurId           |            1 | FactuurId            | A         |     2090558 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bestellingen |          1 | IsPromo             |            1 | IsPromo              | A         |          90 |     NULL | NULL   |      | BTREE      |         |               |
            | Bestellingen |          1 | Delete              |            1 | Bestellingid         | A         |     4181117 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bestellingen |          1 | Delete              |            2 | IsPromo              | A         |     4181117 |     NULL | NULL   |      | BTREE      |         |               |
            | Bestellingen |          1 | BesteldVoor         |            1 | RegelAfleverdebiteur | A         |     1393705 |     NULL | NULL   |      | BTREE      |         |               |
            | Bestellingen |          1 | RegelKostenplaatsID |            1 | RegelKostenplaatsID  | A         |        1068 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bestellingen |          1 | DTOrderregel        |            1 | DTOrderregel         | A         |     4181117 |     NULL | NULL   | YES  | BTREE      |         |               |
            +--------------+------------+---------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            15 rows in set (0.01 sec)
             
            MariaDB [(none)]> SHOW INDEX IN Managementboek_Klanten.`BestellingenStatus`;
            +--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            | Table              | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
            +--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            | BestellingenStatus |          0 | PRIMARY  |            1 | Id          | A         |     3838574 |     NULL | NULL   |      | BTREE      |         |               |
            +--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            1 row in set (0.00 sec)
             
            MariaDB [(none)]> SHOW INDEX IN Managementboek_Boeken.Boeken;
            +--------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            | Table  | Non_unique | Key_name       | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
            +--------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            | Boeken |          0 | PRIMARY        |            1 | Ean                | A         |      156867 |     NULL | NULL   |      | BTREE      |         |               |
            | Boeken |          0 | i1             |            1 | Ean                | A         |      156867 |     NULL | NULL   |      | BTREE      |         |               |
            | Boeken |          1 | i2             |            1 | Uitgever           | A         |        5602 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | i3             |            1 | Auteur1            | A         |       78433 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | i4             |            1 | Auteur2            | A         |       31373 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | i5             |            1 | Auteur3            | A         |       12066 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | i6             |            1 | Jaaruitgifte       | A         |          74 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | i7             |            1 | Aanbiedingsprijs   | A         |         886 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | i8             |            1 | Boekstatus         | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
            | Boeken |          1 | i9             |            1 | Rubriek            | A         |          54 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | i10            |            1 | Aanmaakdatum       | A         |      156867 |     NULL | NULL   |      | BTREE      |         |               |
            | Boeken |          1 | i11            |            1 | Laatstgewijzigd    | A         |       15686 |     NULL | NULL   |      | BTREE      |         |               |
            | Boeken |          1 | i12            |            1 | Isgewist           | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
            | Boeken |          1 | i13            |            1 | Extraaandacht      | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
            | Boeken |          1 | i14            |            1 | Random             | A         |          44 |     NULL | NULL   |      | BTREE      |         |               |
            | Boeken |          1 | i15            |            1 | Naamserie          | A         |        2571 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | i16            |            1 | Titelserie         | A         |        7130 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | Titel          |            1 | Titel              | A         |      156867 |     NULL | NULL   |      | BTREE      |         |               |
            | Boeken |          1 | Aanbiedingen   |            1 | Aanbiedingvanaf    | A         |        1634 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | Aanbiedingen   |            2 | Aanbiedingtotenmet | A         |        2091 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | Rubriek        |            1 | Rubriek            | A         |          54 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | Rubriek        |            2 | Boekstatus         | A         |         384 |     NULL | NULL   |      | BTREE      |         |               |
            | Boeken |          1 | Bindwijze      |            1 | Bindwijze          | A         |         104 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Boeken |          1 | Taalvermelding |            1 | Taalvermelding     | A         |          16 |     NULL | NULL   | YES  | BTREE      |         |               |
            +--------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            24 rows in set (0.00 sec)
             
            MariaDB [(none)]> SHOW INDEX IN Managementboek_Boeken.`Bindwijze`;
            +-----------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            | Table     | Non_unique | Key_name     | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
            +-----------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            | Bindwijze |          0 | PRIMARY      |            1 | Bindwijze              | A         |          95 |     NULL | NULL   |      | BTREE      |         |               |
            | Bindwijze |          1 | NewIndex1    |            1 | Productsoort           | A         |          95 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bindwijze |          1 | Productsoort |            1 | Bindwijze_productsoort | A         |          95 |     NULL | NULL   | YES  | BTREE      |         |               |
            | Bindwijze |          1 | Producttype  |            1 | Bindwijze_producttype  | A         |          47 |     NULL | NULL   | YES  | BTREE      |         |               |
            +-----------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            4 rows in set (0.00 sec)
             
            MariaDB [(none)]> SHOW INDEX IN Managementboek_Klanten.`BestellingAll`;
            +---------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            | Table         | Non_unique | Key_name              | Seq_in_index | Column_name           | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
            +---------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            | BestellingAll |          0 | PRIMARY               |            1 | Bestellingid          | A         |     2310982 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | Klantid               |            1 | Klantid               | A         |     2310982 |     NULL | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | Afleverid             |            1 | Afleverid             | A         |         168 |     NULL | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | ReferrerDomain        |            1 | ReferrerDomain        | A         |     1155491 |     NULL | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | AffiliateId           |            1 | AffiliateId           | A         |        3763 |     NULL | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | BatchID               |            1 | BatchID               | A         |       92439 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | BatchID               |            2 | Afgerond              | A         |       92439 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | DT_Index              |            1 | DT                    | A         |     2310982 |     NULL | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | DT_Index              |            2 | Afgerond              | A         |     2310982 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | DT_Index              |            3 | BatchID               | A         |     2310982 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | NewIndex1             |            1 | SessionId             | A         |     2310982 |     NULL | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | NewIndex3             |            1 | OrigineelBestellingId | A         |      462196 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | NewIndex4             |            1 | Herkomst              | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | NewIndex5             |            1 | Afgerond              | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | NewIndex5             |            2 | DT                    | A         |     2310982 |     NULL | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | IP                    |            1 | IP                    | A         |      462196 |       11 | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | AutoClassificatie     |            1 | AutoClassificatie     | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | AutoClassificatie     |            2 | Geexporteerd          | A         |          14 |     NULL | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | AutoClassificatie     |            3 | Bestellingid          | A         |     2310982 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | IdealOpen             |            1 | IdealOpen             | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | OrigineelBestellingId |            1 | OrigineelBestellingId | A         |      462196 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | ProspectId            |            1 | ProspectId            | A         |      770327 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | ProcessStatus         |            1 | Afgerond              | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | ProcessStatus         |            2 | ProcessStatus         | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | ProcessStatus         |            3 | Geexporteerd          | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | ProcessStatus         |            4 | DT                    | A         |     2310982 |     NULL | NULL   | YES  | BTREE      |         |               |
            | BestellingAll |          1 | FactuurID             |            1 | Factuurid             | A         |     2310982 |     NULL | NULL   |      | BTREE      |         |               |
            | BestellingAll |          1 | Uwreferentie          |            1 | Uwreferentie          | A         |      330140 |     NULL | NULL   | YES  | BTREE      |         |               |
            +---------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
            28 rows in set (0.00 sec)
            

            You have to know Bestelling (in the update) is a VIEW for BestellingAll (WHERE Afgerond=1);

            CrewOne Sander Pilon added a comment - The test server has more or less the same data - it is a replicated server. MariaDB [(none)]> SHOW INDEX IN Managementboek_Klanten.`Bestelling`; Empty set (0.00 sec)   MariaDB [(none)]> SHOW INDEX IN Managementboek_Klanten.`Bestellingen`; +--------------+------------+---------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+---------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Bestellingen | 0 | PRIMARY | 1 | Id | A | 4181117 | NULL | NULL | | BTREE | | | | Bestellingen | 1 | Bestellingid | 1 | Bestellingid | A | 4181117 | NULL | NULL | YES | BTREE | | | | Bestellingen | 1 | Artikelcode | 1 | Ean | A | 209055 | NULL | NULL | YES | BTREE | | | | Bestellingen | 1 | NewIndex1 | 1 | Id | A | 4181117 | NULL | NULL | | BTREE | | | | Bestellingen | 1 | NewIndex1 | 2 | BackorderOrigineel | A | 4181117 | NULL | NULL | YES | BTREE | | | | Bestellingen | 1 | NewIndex2 | 1 | AantalBackorder | A | 202 | NULL | NULL | YES | BTREE | | | | Bestellingen | 1 | backorder | 1 | BackorderOrigineel | A | 696852 | NULL | NULL | YES | BTREE | | | | Bestellingen | 1 | NewIndex3 | 1 | StatusId | A | 70 | NULL | NULL | YES | BTREE | | | | Bestellingen | 1 | FactuurId | 1 | FactuurId | A | 2090558 | NULL | NULL | YES | BTREE | | | | Bestellingen | 1 | IsPromo | 1 | IsPromo | A | 90 | NULL | NULL | | BTREE | | | | Bestellingen | 1 | Delete | 1 | Bestellingid | A | 4181117 | NULL | NULL | YES | BTREE | | | | Bestellingen | 1 | Delete | 2 | IsPromo | A | 4181117 | NULL | NULL | | BTREE | | | | Bestellingen | 1 | BesteldVoor | 1 | RegelAfleverdebiteur | A | 1393705 | NULL | NULL | | BTREE | | | | Bestellingen | 1 | RegelKostenplaatsID | 1 | RegelKostenplaatsID | A | 1068 | NULL | NULL | YES | BTREE | | | | Bestellingen | 1 | DTOrderregel | 1 | DTOrderregel | A | 4181117 | NULL | NULL | YES | BTREE | | | +--------------+------------+---------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 15 rows in set (0.01 sec)   MariaDB [(none)]> SHOW INDEX IN Managementboek_Klanten.`BestellingenStatus`; +--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | BestellingenStatus | 0 | PRIMARY | 1 | Id | A | 3838574 | NULL | NULL | | BTREE | | | +--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)   MariaDB [(none)]> SHOW INDEX IN Managementboek_Boeken.Boeken; +--------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Boeken | 0 | PRIMARY | 1 | Ean | A | 156867 | NULL | NULL | | BTREE | | | | Boeken | 0 | i1 | 1 | Ean | A | 156867 | NULL | NULL | | BTREE | | | | Boeken | 1 | i2 | 1 | Uitgever | A | 5602 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | i3 | 1 | Auteur1 | A | 78433 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | i4 | 1 | Auteur2 | A | 31373 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | i5 | 1 | Auteur3 | A | 12066 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | i6 | 1 | Jaaruitgifte | A | 74 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | i7 | 1 | Aanbiedingsprijs | A | 886 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | i8 | 1 | Boekstatus | A | 8 | NULL | NULL | | BTREE | | | | Boeken | 1 | i9 | 1 | Rubriek | A | 54 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | i10 | 1 | Aanmaakdatum | A | 156867 | NULL | NULL | | BTREE | | | | Boeken | 1 | i11 | 1 | Laatstgewijzigd | A | 15686 | NULL | NULL | | BTREE | | | | Boeken | 1 | i12 | 1 | Isgewist | A | 2 | NULL | NULL | | BTREE | | | | Boeken | 1 | i13 | 1 | Extraaandacht | A | 2 | NULL | NULL | | BTREE | | | | Boeken | 1 | i14 | 1 | Random | A | 44 | NULL | NULL | | BTREE | | | | Boeken | 1 | i15 | 1 | Naamserie | A | 2571 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | i16 | 1 | Titelserie | A | 7130 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | Titel | 1 | Titel | A | 156867 | NULL | NULL | | BTREE | | | | Boeken | 1 | Aanbiedingen | 1 | Aanbiedingvanaf | A | 1634 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | Aanbiedingen | 2 | Aanbiedingtotenmet | A | 2091 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | Rubriek | 1 | Rubriek | A | 54 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | Rubriek | 2 | Boekstatus | A | 384 | NULL | NULL | | BTREE | | | | Boeken | 1 | Bindwijze | 1 | Bindwijze | A | 104 | NULL | NULL | YES | BTREE | | | | Boeken | 1 | Taalvermelding | 1 | Taalvermelding | A | 16 | NULL | NULL | YES | BTREE | | | +--------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 24 rows in set (0.00 sec)   MariaDB [(none)]> SHOW INDEX IN Managementboek_Boeken.`Bindwijze`; +-----------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Bindwijze | 0 | PRIMARY | 1 | Bindwijze | A | 95 | NULL | NULL | | BTREE | | | | Bindwijze | 1 | NewIndex1 | 1 | Productsoort | A | 95 | NULL | NULL | YES | BTREE | | | | Bindwijze | 1 | Productsoort | 1 | Bindwijze_productsoort | A | 95 | NULL | NULL | YES | BTREE | | | | Bindwijze | 1 | Producttype | 1 | Bindwijze_producttype | A | 47 | NULL | NULL | YES | BTREE | | | +-----------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)   MariaDB [(none)]> SHOW INDEX IN Managementboek_Klanten.`BestellingAll`; +---------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | BestellingAll | 0 | PRIMARY | 1 | Bestellingid | A | 2310982 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | Klantid | 1 | Klantid | A | 2310982 | NULL | NULL | YES | BTREE | | | | BestellingAll | 1 | Afleverid | 1 | Afleverid | A | 168 | NULL | NULL | YES | BTREE | | | | BestellingAll | 1 | ReferrerDomain | 1 | ReferrerDomain | A | 1155491 | NULL | NULL | YES | BTREE | | | | BestellingAll | 1 | AffiliateId | 1 | AffiliateId | A | 3763 | NULL | NULL | YES | BTREE | | | | BestellingAll | 1 | BatchID | 1 | BatchID | A | 92439 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | BatchID | 2 | Afgerond | A | 92439 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | DT_Index | 1 | DT | A | 2310982 | NULL | NULL | YES | BTREE | | | | BestellingAll | 1 | DT_Index | 2 | Afgerond | A | 2310982 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | DT_Index | 3 | BatchID | A | 2310982 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | NewIndex1 | 1 | SessionId | A | 2310982 | NULL | NULL | YES | BTREE | | | | BestellingAll | 1 | NewIndex3 | 1 | OrigineelBestellingId | A | 462196 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | NewIndex4 | 1 | Herkomst | A | 24 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | NewIndex5 | 1 | Afgerond | A | 2 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | NewIndex5 | 2 | DT | A | 2310982 | NULL | NULL | YES | BTREE | | | | BestellingAll | 1 | IP | 1 | IP | A | 462196 | 11 | NULL | YES | BTREE | | | | BestellingAll | 1 | AutoClassificatie | 1 | AutoClassificatie | A | 6 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | AutoClassificatie | 2 | Geexporteerd | A | 14 | NULL | NULL | YES | BTREE | | | | BestellingAll | 1 | AutoClassificatie | 3 | Bestellingid | A | 2310982 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | IdealOpen | 1 | IdealOpen | A | 2 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | OrigineelBestellingId | 1 | OrigineelBestellingId | A | 462196 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | ProspectId | 1 | ProspectId | A | 770327 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | ProcessStatus | 1 | Afgerond | A | 2 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | ProcessStatus | 2 | ProcessStatus | A | 10 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | ProcessStatus | 3 | Geexporteerd | A | 18 | NULL | NULL | YES | BTREE | | | | BestellingAll | 1 | ProcessStatus | 4 | DT | A | 2310982 | NULL | NULL | YES | BTREE | | | | BestellingAll | 1 | FactuurID | 1 | Factuurid | A | 2310982 | NULL | NULL | | BTREE | | | | BestellingAll | 1 | Uwreferentie | 1 | Uwreferentie | A | 330140 | NULL | NULL | YES | BTREE | | | +---------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 28 rows in set (0.00 sec) You have to know Bestelling (in the update) is a VIEW for BestellingAll (WHERE Afgerond=1);

            We have never been able to reproduce this exact failure, but we have other known bugs, either fixed or still open, which involve views, foreign keys and multi-table updates. Without a fully resolved stack trace it is difficult to determine the exact match, but some of them are bound to be related. An example of a still open one would be MDEV-19817 (it's filed as plain SQL test case without stored procedures, but can be converted into stored procedures as well).

            This group of problems will be further tracked within JIRA items which have ready-to-be-debugged test cases.

            elenst Elena Stepanova added a comment - We have never been able to reproduce this exact failure, but we have other known bugs, either fixed or still open, which involve views, foreign keys and multi-table updates. Without a fully resolved stack trace it is difficult to determine the exact match, but some of them are bound to be related. An example of a still open one would be MDEV-19817 (it's filed as plain SQL test case without stored procedures, but can be converted into stored procedures as well). This group of problems will be further tracked within JIRA items which have ready-to-be-debugged test cases.

            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.