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