Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.1.12
-
None
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
- relates to
-
MDEV-19817 Server crashes in Multiupdate_prelocking_strategy::handle_end upon UPDATE with view and foreign key
- Open
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
(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.