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

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;

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

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.

#

# 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