[#MDEV-9674] Server crash on large transaction combined with multi-update inside stored procedure

[MDEV-9674] Server crash on large transaction combined with multi-update inside stored procedure Created: 2016-03-02 Updated: 2020-10-20 Resolved: 2020-10-20
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Manipulation - Update, Views
Affects Version/s:	10.1.12
Fix Version/s:	N/A

Type:

Bug

Priority:

Major

Reporter:

Sander Pilon

Assignee:

Unassigned

Resolution:

Incomplete

Votes:

Labels:

None

Attachments:

error1.log

tables-mdev9674.txt

Issue Links:

Relates
relates to	MDEV-19817	Server crashes in Multiupdate_prelock...	Confirmed

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

Comments

Comment by Elena Stepanova [ 2016-03-02 ]

CrewOne,
Is it reproducible if you run the same query outside a large transaction?
Did it happen more than once in a large transaction?
Is it a production server? Would you be able to upload a data dump of the involved tables? If you can do it, please use ftp.askmonty.org/private, this way only MariaDB developers will have access to it.
If you can't provide the dump, please paste or attach the output of

SHOW CREATE TABLE <table_name> ;

SHOW INDEX IN <table_name> ;

SHOW TRIGGERS LIKE '<table_name>'

for all tables involved in the query. Please also attach your cnf file(s).

Thanks.

Comment by Sander Pilon [ 2016-03-02 ]

I'm able to reproduce this, given the same server and same table. The amount of time it takes to crash differs though.

If I do the replace into a new empty table - works fine.
If I do the replace in another server with similar tables - works fine.

Can it be that the file itself is corrupt? (That a re-import of the table could fix this?)

Comment by Elena Stepanova [ 2016-03-02 ]

CrewOne,
It's definitely worth trying. You don't have to drop the existing table just yet, just create a new one in a different schema on the same server.
Are there any errors or warnings in the error log prior to the crash?
Did you try to run CHECK TABLE on the table in question?

Comment by Sander Pilon [ 2016-03-02 ]

I have a (totally unfounded) feeling that this crash is also caused by the interaction of other queries that access the Feeds table. It is quite a "busy" table. I have, and not for the lack of trying, yet to reproduce this outside of the original crash scenario.

Trying this outside of the live environment basically means the table is not accessed during that transaction.

Anyway - quite a pain, because it prevents me from importing 5M records into a table in a live scenario.

I will see and test how long "check table" will run, and maybe run it this evening on the live data.

Comment by Sander Pilon [ 2016-03-02 ]

It seems to be size-related. (Or time...)

If I limit the replace query to 1000000 (1M) - everything works fine.
If I limit it to 2M - the server crashes.

This is on the live table. On copies of that table on the same or antother server, it completes without problems.

(Edit) I just ran the query with 1.5M - query itself completes just fine. But the servers crashed when I did 'ROLLBACK'.

Comment by Elena Stepanova [ 2016-03-02 ]

CrewOne, what about structures of the table that you are updating, and the last joined table (however small)?

Comment by Sander Pilon [ 2016-03-02 ]

Just added the 2 remaining tables.

Something curious happened also.

I exported the data using SELECT INTO OUTFILE.
I did a truncate table.
Then I did a LOAD DATA INFILE.

Which completed 100%, but I assume just before the commit came through - crash. It ran for 25 minutes and using innotop I saw that more or less 100% of the data was complete and then it crashed. Recovery took forever too.

Comment by Sander Pilon [ 2016-03-03 ]

The server is in a habit of crashing multiple times a day now, every time the same backtrace - but not always when we do a large insert.

Is there anything we can do to figure out which query is actually crashing?

Comment by Sander Pilon [ 2016-03-03 ]

Actually, we got a new one today...

Thread pointer: 0x0x7f52dee95008

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 = 0x7f5344765df0 thread_stack 0x80000

*** buffer overflow detected ***: /usr/sbin/mysqld terminated

======= Backtrace: =========

/lib/x86_64-linux-gnu/libc.so.6(+0x7338f)[0x7f614884e38f]

/lib/x86_64-linux-gnu/libc.so.6(__fortify_fail+0x5c)[0x7f61488e5c9c]

/lib/x86_64-linux-gnu/libc.so.6(+0x109b60)[0x7f61488e4b60]

/lib/x86_64-linux-gnu/libc.so.6(+0x10abe7)[0x7f61488e5be7]

/usr/sbin/mysqld(my_addr_resolve+0x48)[0x7f614b14aaf8]

/usr/sbin/mysqld(my_print_stacktrace+0x1c2)[0x7f614b1371f2]

/usr/sbin/mysqld(handle_fatal_signal+0x38d)[0x7f614ac641bd]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f61491ba340]

/usr/sbin/mysqld(+0x75241d)[0x7f614ae0641d]

/usr/sbin/mysqld(+0x75f69d)[0x7f614ae1369d]

/usr/sbin/mysqld(+0x374476)[0x7f614aa28476]

/usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x818)[0x7f614ab369e8]

/usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x1b)[0x7f614ab391bb]

/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x8f)[0x7f614ab3930f]

/usr/sbin/mysqld(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lexPP12multi_update+0x122)[0x7f614ab80fc2]

/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6bc6)[0x7f614aae9236]

/usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x15)[0x7f614ad707c5]

/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x83)[0x7f614ad77373]

/usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x204)[0x7f614ad77924]

/usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x786)[0x7f614ad737f6]

/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x617)[0x7f614ad74ea7]

/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6889)[0x7f614aae8ef9]

/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x26d)[0x7f614aaebfed]

/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x2460)[0x7f614aaef330]

/usr/sbin/mysqld(_Z10do_commandP3THD+0x169)[0x7f614aaefae9]

/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x18a)[0x7f614abb30fa]

/usr/sbin/mysqld(handle_one_connection+0x40)[0x7f614abb32d0]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f61491b2182]

/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f61488d547d]

======= Memory map: ========

7f52a2000000-7f52a6000000 rw-p 00000000 00:00 0

7f52a637f000-7f52a6380000 ---p 00000000 00:00 0

7f52a6380000-7f52b3c00000 rw-p 00000000 00:00 0                          [stack:22546]

7f52b3c79000-7f52b3c7a000 ---p 00000000 00:00 0

7f52b3c7a000-7f52b3cfa000 rw-p 00000000 00:00 0                          [stack:22510]

7f52b3cfa000-7f52b3cfb000 ---p 00000000 00:00 0

7f52b3cfb000-7f52b3d7b000 rw-p 00000000 00:00 0                          [stack:22509]

7f52b3d7b000-7f52b3d7c000 ---p 00000000 00:00 0

7f52b3d7c000-7f52b3dfc000 rw-p 00000000 00:00 0                          [stack:28883]

7f52b3dfc000-7f52b3dfd000 ---p 00000000 00:00 0

7f52b3dfd000-7f52b3e7d000 rw-p 00000000 00:00 0

7f52b3e7d000-7f52b3e7e000 ---p 00000000 00:00 0

7f52b3e7e000-7f52b3efe000 rw-p 00000000 00:00 0                          [stack:28881]

7f52b3efe000-7f52b3eff000 ---p 00000000 00:00 0

7f52b3eff000-7f52b3f7f000 rw-p 00000000 00:00 0                          [stack:28880]

7f52b3f7f000-7f52b3f80000 ---p 00000000 00:00 0

7f52b3f80000-7f52be800000 rw-p 00000000 00:00 0

7f52be879000-7f52be87a000 ---p 00000000 00:00 0

7f52be87a000-7f52be8fa000 rw-p 00000000 00:00 0                          [stack:15678]

.... this continues for a while ...

Comment by Elena Stepanova [ 2016-03-03 ]

CrewOne,

I don't see how this REPLACE statement can possibly cause this stack traces.
All stack traces are the same – the crash happen inside of a stored procedure, which, in turn, executes a multi-table update. This REPLACE is nothing of the sort.
Could you maybe enable the general log for one session, till the next crash? Even if the guilty query is not printed in the error log, it will be easy to find in the general log. When server crashes, it says something like

Query (0x7f0d5ec72260): is an invalid pointer

Connection ID (thread ID): 3267

The last query printed into the general log from the given connection ID (3267 in the example above) will be the one we are looking for.

Comment by Sander Pilon [ 2016-03-04 ]

I hope so.
Here it is!

841140 Connect  site@solrhost as anonymous on

                841140 Query    SET NAMES 'utf8'

                841140 Query    SET @context  = 'SITE( /checkout/bedankt )'

                841140 Query    SET @initials = 'WEB'

                841140 Query    SELECT MAX( IFNULL( Bestellingid, 0 ) ) FROM Managementboek_Klanten.BestellingAll JOIN Managementboek_Klanten.BestellingHerkomst USING ( BestellingId )

                841140 Query    SELECT SUM( Aantal ) FROM Managementboek_Klanten.Bestellingen

                841140 Query    CALL Managementboek_Klanten.permissie_toegestane_klanten( '2570661', 'serie-abo.view' )

                841140 Query    SELECT

                841140 Query    SELECT COUNT(A.ID)

                841140 Query    CALL Managementboek_Klanten.permissie_toegestane_klanten( '2570661', 'periodieken.view' )

                841140 Query    SELECT

                841140 Query    SELECT COUNT(A.ID)

                841140 Query    SELECT O.label as naam, O.BestellijstPrefix as lijsten

                841140 Query    SELECT * FROM Managementboek_Klanten.BestellingAll WHERE BestellingId = '12919596'

                841140 Query    SELECT * FROM Managementboek_Klanten.Bestellingen LEFT JOIN Managementboek_Klanten.BestellingenExtra USING ( Id ) WHERE BestellingId = '12919596' ORDER BY (Ean='BONG'),Auteurnaam, Omschrijving, DTOrderregel

                841140 Query    SELECT COUNT(*) FROM Managementboek_Klanten.BestellingAll WHERE BestellingId = '12919596' AND IdealOpen = 1 AND IdealTimeout > NOW() /* ideal_open_transaction */

                841140 Query    SELECT SHA( GROUP_CONCAT( CONCAT( BestellingId, '-', Id, '-', Ean, '-', Aantal, '-', OmzetIncBtwIncKorting, '-', A.`FactuurPostcode`, '-', A.`FactuurHuisnummer`, '-', A.`VerzendPostcode`, '-', A.`VerzendHuisnummer`, '-', A.`FactuurAchternaam`, '-', A.`VerzendAchternaam`, '-', A.`FactuurBedrijfsnaam`, '-', A.`VerzendBedrijfsnaam`, '-', A.`VerzendHuisnummerToevoeging`, '-', A.`FactuurHuisnummerToevoeging`, '-', A.`FactuurLand` ) ) ) FROM Managementboek_Klanten.Bestellingen JOIN Managementboek_Klanten.`BestellingAdressen` A USING ( BestellingId ) WHERE BestellingId = '12919596' /* SHA */

                841140 Query    SELECT Id, Ean, Aantal

                841140 Query    CALL Voorraadbeheer.vbmih_tekst( '9789080850705', '1', '' )

                841140 Query    UPDATE Managementboek_Klanten.BestellingenBelofte

                841140 Query    UPDATE Managementboek_Klanten.BestellingTracking SET DTAfgerond=NOW() WHERE BestellingId='12919596'

                841140 Query    SELECT * FROM Managementboek_Klanten.Klanten_met_adres WHERE Klantid = '2570661'

                841140 Query    SELECT SHA( GROUP_CONCAT( CONCAT( BestellingId, '-', Id, '-', Ean, '-', Aantal, '-', OmzetIncBtwIncKorting, '-', A.`FactuurPostcode`, '-', A.`FactuurHuisnummer`, '-', A.`VerzendPostcode`, '-', A.`VerzendHuisnummer`, '-', A.`FactuurAchternaam`, '-', A.`VerzendAchternaam`, '-', A.`FactuurBedrijfsnaam`, '-', A.`VerzendBedrijfsnaam`, '-', A.`VerzendHuisnummerToevoeging`, '-', A.`FactuurHuisnummerToevoeging`, '-', A.`FactuurLand` ) ) ) FROM Managementboek_Klanten.Bestellingen JOIN Managementboek_Klanten.`BestellingAdressen` A USING ( BestellingId ) WHERE BestellingId = '12919596' /* SHA */

                841140 Query    DELETE FROM Managementboek_Klanten.Bestellingen WHERE BestellingId = '12919596' AND Ean = 'porto' AND Prijs = 0

                841140 Query    SELECT * FROM Managementboek_Klanten.Klanten_met_adres WHERE Klantid = '2570661'

                841140 Query    UPDATE Managementboek_Klanten.BestellingAll

                841140 Query    UPDATE Bestellingen JOIN BestellingenBelofte USING ( Id ) SET VoorraadBijBevestiging = Voorraadbeheer.`beschikbareVoorraad`( Ean ) WHERE Bestellingid = OLD.BestellingId

                841140 Query    SELECT BeschikbareVoorraad INTO RV FROM Voorraad WHERE Ean =  NAME_CONST('inEan',_latin1'9789080850705' COLLATE 'latin1_swedish_ci')

                841140 Query    SELECT BeschikbareVoorraad INTO RV FROM Voorraad WHERE Ean =  NAME_CONST('inEan',_latin1'porto' COLLATE 'latin1_swedish_ci')

                841140 Query    SELECT IFNULL( NULLIF(K.FactuurEmailadres,''), K.Emailadres ) INTO _email FROM Klanten K WHERE Klantid = NEW.Klantid

                841140 Query    UPDATE BestellingAdressen SET FactuurEmailadres =  NAME_CONST('_email',_utf8'hverbeeten@krikkeenvanderhulst.nl' COLLATE 'utf8_general_ci') WHERE BestellingId = NEW.BestellingId

                841140 Query    SELECT Kanaal INTO _kanaal FROM BestellingHerkomst WHERE BestellingId = NEW.BestellingId

                841140 Query    SELECT AchterafFactureren INTO _achteraf FROM KlantVinken WHERE Klantid = NEW.Klantid

                841140 Query    SELECT FactuurKlantid INTO _factuurklantid FROM Klanten WHERE Klantid = NEW.Klantid

                841140 Query    UPDATE OrderkopStatusPlat SET bevestigd=1 WHERE BestellingId = NEW.BestellingId

                841140 Query    SET  @skip_orderkopstatus_update = 1

                841140 Query    INSERT INTO BestellingStatus VALUES (  2, OLD.BestellingId, 'gelukt', @current_initials, NOW() )

                841140 Query    SET  @skip_orderkopstatus_update = 0

                841140 Query    SELECT niet_inpakken INTO _ni FROM OrderkopStatusPlat WHERE BestellingId = NEW.BestellingId

                841140 Query    CALL update_kosten_mp( NEW.BestellingId )

                841140 Query    CALL update_bestelling_herkomst( NEW.BestellingId )

                841140 Query    UPDATE Klanten SET AutorisatieNodig = 1 WHERE Klantid = NEW.Klantid

                841140 Query    CALL log_klanten_wijziging( NEW.Klantid, 'Emailadres', OLD.Emailadres, NEW.Emailadres, @context, @initials )

                841140 Query    CALL log_klanten_wijziging( NEW.Klantid, 'Exactid', OLD.Exactid, NEW.Exactid, @context, @initials )

                841140 Query    CALL log_klanten_wijziging( NEW.Klantid, 'Mailcode', OLD.Mailcode, NEW.Mailcode, @context, @initials )

                841140 Query    CALL log_klanten_wijziging( NEW.Klantid, 'AdresStatus', OLD.AdresStatus, NEW.AdresStatus, @context, @initials )

                841140 Query    CALL log_klanten_wijziging( NEW.Klantid, 'Bankrekening', OLD.Bankrekening, NEW.Bankrekening, @context, @initials )

                841140 Query    CALL log_klanten_wijziging( NEW.Klantid, 'BankrekeningIncasso', OLD.BankrekeningIncasso, NEW.BankrekeningIncasso, @context, @initials )

                841140 Query    SELECT COUNT(*) INTO SUB FROM Klantmailinglist WHERE Klantid = NEW.Klantid AND Mailinglistid = 1

                841140 Query    UPDATE Managementboek_Nieuwsbrieven.`Nieuwsbrief_Vinkjes` SET SoftOptIn = 1 WHERE Klantid = NEW.Klantid

                841140 Query    INSERT IGNORE INTO KlantAanbevolenBasis SELECT NEW.Klantid, Ean, NOW(), 'besteld' FROM Bestellingen WHERE BestellingId = NEW.Bestellingid

                841140 Query    UPDATE Bestellingen BN JOIN BestellingenExtra XX USING ( Id ) JOIN Manpo.Boeken BK USING ( Ean ) JOIN BestellingAll B USING ( BestellingId )

                841140 Query    REPLACE INTO Managementboek_Extra.`laatstverkocht`

                841140 Query    CALL orderregel_status_op_orderid( 2, NEW.BestellingId, 'gelukt' )

                841140 Query    INSERT INTO OrderregelStatus SET StatusId =  NAME_CONST('iStatus',2), Id =  NAME_CONST('iOrderRegel',50723420), DT = NOW(), Gebruiker = IFNULL( @initials, '' ), Resultaat =  NAME_CONST('IResultaat',_utf8'gelukt' COLLATE 'utf8_general_ci')

                841140 Query    SET  @skip_orderregelstatus_update = 1

                841140 Query    UPDATE OrderregelStatusPlat SET `bevestigd` = 1 WHERE Id = NEW.Id

                841140 Query    SET  @skip_orderregelstatus_update = 0

                841140 Query    SELECT    BS.Ean, BS.Aantal, BS.Bestellingid, BS.BackorderOrigineel, BA.OrigineelBestellingid, BA.Klantid, BS.Omschrijving, BS.Aantal + BS.AantalBackorder

                841140 Query    SELECT IFNULL( Bindwijze.Download, 0 ) INTO Download

                841140 Query    UPDATE OrderregelStatusPlat SET `bevestigd`                        = 1 WHERE Id =  NAME_CONST('iOrderRegel',50723420)

                841140 Query    UPDATE OrderregelStatusPlat SET `bevestigd` = 1 WHERE Id = NEW.Id

                841140 Query    SET  @skip_orderregelstatus_update = 0

                841140 Query    SELECT    BS.Ean, BS.Aantal, BS.Bestellingid, BS.BackorderOrigineel, BA.OrigineelBestellingid, BA.Klantid, BS.Omschrijving, BS.Aantal + BS.AantalBackorder

                841140 Query    SELECT IFNULL( Bindwijze.Download, 0 ) INTO Download

                841140 Query    UPDATE OrderregelStatusPlat SET `bevestigd`                        = 1 WHERE Id =  NAME_CONST('iOrderRegel',50723423)

                841140 Query    REPLACE INTO `BestellingStatus` SET

                841140 Query    SET  @skip_orderkopstatus_update = 1

                841140 Query    UPDATE OrderkopStatusPlat SET bevestigd = 1                        WHERE BestellingId = NEW.BestellingId

                841140 Query    SET  @skip_orderkopstatus_update = 0

                841140 Query    CALL Managementboek_Klanten.update_ebooks_verzonden( '12919596' )

                841140 Query    CALL Managementboek_Klanten.update_ebooks_verzonden( '12919596' )

.... here it ends ...

the crash

160304  9:21:39 [ERROR] mysqld got signal 11 ;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed,

something is definitely wrong and this may fail.

Server version: 10.1.12-MariaDB-1~trusty

key_buffer_size=16777216

read_buffer_size=131072

max_used_connections=288

max_threads=502

thread_count=50

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: 0x0x7f097f5dd008

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 = 0x7f08d94b8df0 thread_stack 0x80000

/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7f17d2fe505e]

/usr/sbin/mysqld(handle_fatal_signal+0x38d)[0x7f17d2b121bd]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f17d1068340]

/usr/sbin/mysqld(+0x75241d)[0x7f17d2cb441d]

/usr/sbin/mysqld(+0x75f69d)[0x7f17d2cc169d]

/usr/sbin/mysqld(+0x374476)[0x7f17d28d6476]

/usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x818)[0x7f17d29e49e8]

/usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x1b)[0x7f17d29e71bb]

/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x8f)[0x7f17d29e730f]

/usr/sbin/mysqld(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lexPP12multi_update+0x122)[0x7f17d2a2efc2]

/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6bc6)[0x7f17d2997236]

/usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x15)[0x7f17d2c1e7c5]

/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x83)[0x7f17d2c25373]

/usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x204)[0x7f17d2c25924]

/usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x786)[0x7f17d2c217f6]

/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x617)[0x7f17d2c22ea7]

/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6889)[0x7f17d2996ef9]

/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x26d)[0x7f17d2999fed]

/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x2460)[0x7f17d299d330]

/usr/sbin/mysqld(_Z10do_commandP3THD+0x169)[0x7f17d299dae9]

/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x18a)[0x7f17d2a610fa]

/usr/sbin/mysqld(handle_one_connection+0x40)[0x7f17d2a612d0]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f17d1060182]

/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f17d078347d]

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (0x7f092c426260): is an invalid pointer

Connection ID (thread ID): 841140

Status: NOT_KILLED

And the stored procedure in the last call:

CREATE DEFINER=`sander`@`%` PROCEDURE `update_ebooks_verzonden`( bid INT )

    MODIFIES SQL DATA

BEGIN

	UPDATE Managementboek_Klanten.`Bestelling`

	JOIN Managementboek_Klanten.`Bestellingen` USING ( BestellingId )

	JOIN Managementboek_Klanten.`BestellingenStatus` USING ( id )

	JOIN Managementboek_Boeken.Boeken USING ( Ean )

	JOIN Managementboek_Boeken.`Bindwijze` USING ( Bindwijze )

	SET `BestellingenStatus`.`VerzondenOp` = Bestelling.`DT`

	WHERE BestellingId = bid

	AND ( Bindwijze LIKE "%epub%" OR Bindwijze LIKE "%ebook%" OR Bindwijze LIKE "%download%" );

    END$$

DELIMITER ;

Comment by Sander Pilon [ 2016-03-04 ]

We removed that particular stored procedure and since then we tried to crash it, no such luck. That is good news, but we keep trying. (Edit: We ran a number of succesful imports and the first import we enabled this stored procedure again - it crashed with exactly the same trace and general_log)

The connection between that stored procedure and the large (5M rows) insert on another table (see original issue above) however eludes me. They seem totally unrelated, yet the insert almost always results in a crash. Also, the actual commit - which takes 60s - seems to lock about everything.

Comment by Sander Pilon [ 2016-03-04 ]

Added the tables involved in the multi-update. (In an attachment file)

Also, Elena Stepanova, thanks for the support. You're our hero!

Comment by Elena Stepanova [ 2016-03-04 ]

So, the Managementboek_Boeken.Boeken.table used in the procedure is not the same as the one you are replacing into? I see they have different structure.

I also don't see an obvious relation between the REPLACE sandbox and the procedure sandbox.
Did you check triggers on the Boeken table you replace into, and Managementboek_Klanten.BestellingAll table that the procedure updates? Like, for example, your Boeken table having a trigger synchronizing it with Managementboek_Boeken.Boeken, or something like that?

Comment by Sander Pilon [ 2016-03-04 ]

No they are indeed different. And the table we are inserting into has no triggers, and nothing inserts into the Feeds.Boeken table using triggers.

Comment by Elena Stepanova [ 2016-03-07 ]

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.

Comment by Sander Pilon [ 2016-03-07 ]

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.

Comment by Elena Stepanova [ 2016-03-07 ]

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.

Comment by Sander Pilon [ 2016-03-07 ]

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);

Comment by Elena Stepanova [ 2020-10-20 ]

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.

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

[MDEV-9674] Server crash on large transaction combined with multi-update inside stored procedure Created: 2016-03-02 Updated: 2020-10-20 Resolved: 2020-10-20