[MDEV-18393] Galera node kills after DELETE statement with foreign keys Created: 2019-01-28  Updated: 2020-10-28  Resolved: 2020-10-28

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.1.37
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Pim Rupert Assignee: Seppo Jaakola
Resolution: Cannot Reproduce Votes: 4
Labels: None
Environment:

Linux 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
MariaDB-server-10.1.37-1.el7.centos.x86_64
galera-25.3.24-1.rhel7.el7.centos.x86_64

Attachments: File user.test    
Issue Links:
Relates
relates to MDEV-15611 Due to the failure of foreign key det... Closed

 Description   

Galera node kills itself after two DELETE statements in tables with a FK. This looks like a repetition of bug MDEV-15611.

Statements run on 'master1':

DELETE FROM `invoice_data` WHERE `project_id`=<identifier>;
 
DELETE FROM `invoice` WHERE `project_id`=<identifier>;

Resulting in shutdown on 'master2':

2019-01-28  9:37:45 140253035670272 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table redacteddbname.invoice; Cannot delete or update a parent row: a foreign key constraint fails (`redacteddbname`.`invoice_data`, CONSTRAINT `invoice_data_ibfk_6` FOREIGN KEY (`invoice_id`) REFERENCES `invoice` (`id`) ON UPDATE CASCADE), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 1137, Internal MariaDB error code: 1451
 
2019-01-28  9:37:45 140253035670272 [Warning] WSREP: RBR event 2 Delete_rows_v1 apply warning: 152, 534700372
 
2019-01-28  9:37:45 140253035670272 [Warning] WSREP: Failed to apply app buffer: seqno: 534700372, status: 1
 
	 at galera/src/trx_handle.cpp:apply():353
 
Retrying 2th time
 
2019-01-28  9:37:45 140253035670272 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table redacteddbname.invoice; Cannot delete or update a parent row: a foreign key constraint fails (`redacteddbname`.`invoice_data`, CONSTRAINT `invoice_data_ibfk_6` FOREIGN KEY (`invoice_id`) REFERENCES `invoice` (`id`) ON UPDATE CASCADE), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 1137, Internal MariaDB error code: 1451
 
2019-01-28  9:37:45 140253035670272 [Warning] WSREP: RBR event 2 Delete_rows_v1 apply warning: 152, 534700372
 
2019-01-28  9:37:45 140253035670272 [Warning] WSREP: Failed to apply app buffer: seqno: 534700372, status: 1
 
	 at galera/src/trx_handle.cpp:apply():353
 
Retrying 3th time
 
2019-01-28  9:37:45 140253035670272 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table redacteddbname.invoice; Cannot delete or update a parent row: a foreign key constraint fails (`redacteddbname`.`invoice_data`, CONSTRAINT `invoice_data_ibfk_6` FOREIGN KEY (`invoice_id`) REFERENCES `invoice` (`id`) ON UPDATE CASCADE), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 1137, Internal MariaDB error code: 1451
 
2019-01-28  9:37:45 140253035670272 [Warning] WSREP: RBR event 2 Delete_rows_v1 apply warning: 152, 534700372
 
2019-01-28  9:37:45 140253035670272 [Warning] WSREP: Failed to apply app buffer: seqno: 534700372, status: 1
 
	 at galera/src/trx_handle.cpp:apply():353
 
Retrying 4th time
 
2019-01-28  9:37:45 140253035670272 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table redacteddbname.invoice; Cannot delete or update a parent row: a foreign key constraint fails (`redacteddbname`.`invoice_data`, CONSTRAINT `invoice_data_ibfk_6` FOREIGN KEY (`invoice_id`) REFERENCES `invoice` (`id`) ON UPDATE CASCADE), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 1137, Internal MariaDB error code: 1451
 
2019-01-28  9:37:45 140253035670272 [Warning] WSREP: RBR event 2 Delete_rows_v1 apply warning: 152, 534700372
 
2019-01-28  9:37:45 140253035670272 [ERROR] WSREP: Failed to apply trx: source: 3eef3e4c-184f-11e9-b37b-a3373db3c1d5 version: 4 local: 0 state: APPLYING flags: 1 conn_id: 8595856 trx_id: 7949399192 seqnos (l: 5819527, g: 534700372, s: 534700371, d: 534700359, ts: 5129740410281513)
 
2019-01-28  9:37:45 140253035670272 [ERROR] WSREP: Failed to apply trx 534700372 4 times
 
2019-01-28  9:37:45 140253035670272 [ERROR] WSREP: Node consistency compromised, aborting...
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: Closing send monitor...
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: Closed send monitor.
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: gcomm: terminating thread
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: gcomm: joining thread
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: gcomm: closing backend
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: view(view_id(NON_PRIM,3eef3e4c,275) memb {
 
	52331508,0
 
} joined {
 
} left {
 
} partitioned {
 
	3eef3e4c,0
 
	a64bdc05,0
 
})
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: view((empty))
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: gcomm: closed
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: Flow-control interval: [16, 16]
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: Trying to continue unpaused monitor
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: Received NON-PRIMARY.
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: Shifting SYNCED -> OPEN (TO: 534700420)
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: Received self-leave message.
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: Flow-control interval: [0, 0]
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: Trying to continue unpaused monitor
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: Received SELF-LEAVE. Closing connection.
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 534700420)
 
2019-01-28  9:37:45 140248671577856 [Note] WSREP: RECV thread exiting 0: Success
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: recv_thread() joined.
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: Closing replication queue.
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: Closing slave action queue.
 
2019-01-28  9:37:45 140253035670272 [Note] WSREP: /usr/sbin/mysqld: Terminated...



 Comments   
Comment by Jan Lindström (Inactive) [ 2019-02-08 ]

Hi, could you please provide show create table output for invoice, invoice_data and any other related table. Additionally, please provide node configuration.

Comment by Pim Rupert [ 2019-02-12 ]

@jplindst hereby the `SHOW CREATE TABLE` output and mysqld configuration:

CREATE TABLE `invoice` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `project_id` int(11) unsigned DEFAULT NULL,
 
  `ref` varchar(128) NOT NULL,
 
  `name` varchar(128) NOT NULL,
 
  `code` varchar(128) NOT NULL,
 
  `descr` varchar(255) NOT NULL,
 
  `attn` varchar(128) NOT NULL,
 
  `address` varchar(128) NOT NULL,
 
  `zipcode` varchar(128) NOT NULL,
 
  `city` varchar(128) NOT NULL,
 
  `country` varchar(128) NOT NULL,
 
  `shortname` varchar(128) NOT NULL,
 
  `contact` varchar(128) NOT NULL,
 
  `blablacontact` varchar(128) NOT NULL,
 
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
 
  `taxperc` tinyint(2) unsigned NOT NULL DEFAULT '21',
 
  `tax` decimal(10,2) NOT NULL DEFAULT '0.00',
 
  `total` decimal(10,2) NOT NULL DEFAULT '0.00',
 
  `date_create` date NOT NULL,
 
  `date_due` date NOT NULL,
 
  `email_sent` tinyint(1) unsigned NOT NULL DEFAULT '0',
 
  `created` datetime NOT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `project_id` (`project_id`),
 
  CONSTRAINT `invoice_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
 
) ENGINE=InnoDB AUTO_INCREMENT=12352 DEFAULT CHARSET=utf8
 
 
 
CREATE TABLE `invoice_data` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `invoice_id` int(11) unsigned DEFAULT NULL,
 
  `project_id` int(11) unsigned DEFAULT NULL,
 
  `user_id` int(11) unsigned DEFAULT NULL,
 
  `type` varchar(32) NOT NULL,
 
  `type_title` varchar(128) DEFAULT NULL,
 
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
 
  `offset` int(11) unsigned DEFAULT NULL,
 
  `descr` varchar(255) NOT NULL,
 
  `code` varchar(255) NOT NULL,
 
  `period_length` tinyint(2) unsigned DEFAULT NULL,
 
  `period_start` date DEFAULT NULL,
 
  `period_end` date DEFAULT NULL,
 
  `user_name` varchar(255) NOT NULL,
 
  `user_groups` varchar(255) NOT NULL,
 
  `user_created` datetime DEFAULT NULL,
 
  `created` datetime NOT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `invoice_id` (`invoice_id`),
 
  KEY `project_id` (`project_id`),
 
  KEY `user_id` (`user_id`),
 
  KEY `type` (`type`),
 
  CONSTRAINT `invoice_data_ibfk_3` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 
  CONSTRAINT `invoice_data_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 
  CONSTRAINT `invoice_data_ibfk_5` FOREIGN KEY (`type`) REFERENCES `invoice_type` (`type`) ON UPDATE CASCADE,
 
  CONSTRAINT `invoice_data_ibfk_6` FOREIGN KEY (`invoice_id`) REFERENCES `invoice` (`id`) ON UPDATE CASCADE
 
) ENGINE=InnoDB AUTO_INCREMENT=2931094 DEFAULT CHARSET=utf8

Configuration:

[mysqld]
 
bind-address=0.0.0.0
 
log-warnings=2
 
local-infile=0
 
symbolic-links=0
 
character-set-server=utf8
 
# Explictly set paths to prevent issues with third-party tools such as xtrabackup
 
datadir=/var/lib/mysql
 
socket=/var/lib/mysql/mysql.sock
 
# Explicitly set path for logs and pidfile, to ensure compatiblity in all our
 
# different setups with MariaDB.
 
log-error=/var/log/mariadb/mariadb.log
 
pid-file=/var/run/mariadb/mariadb.pid
 
 
 
# Binary logging
 
expire_logs_days=2
 
 
 
# Caches and buffers
 
thread_cache_size=256
 
query_cache_size=0
 
query_cache_type=0
 
join_buffer_size=128k
 
max_heap_table_size=32m
 
tmp_table_size=32m
 
sort_buffer_size=2M
 
 
 
# MyISAM
 
key_buffer_size=128m
 
myisam_recover_options=DEFAULT
 
read_rnd_buffer_size=256K
 
 
 
# Files
 
open_files_limit=409600
 
table_open_cache=81920
 
table_definition_cache=81920
 
 
 
# InnoDB tuning
 
innodb_flush_method=O_DIRECT
 
innodb_flush_log_at_trx_commit=0
 
innodb_file_per_table=True
 
innodb_open_files=81920
 
innodb_buffer_pool_size=64G
 
innodb_buffer_pool_instances=16
 
innodb_log_file_size=512M
 
innodb_thread_concurrency=0
 
 
 
# Connection limits and time-outs
 
max_connections=1500
 
skip_name_resolve=True
 
max_connect_errors=1000000
 
wait_timeout=28800
 
max_allowed_packet=20M
 
 
 
# Slow query logging
 
slow_query_log=True
 
slow_query_log_file=/var/log/mariadb/slow_query.log
 
long_query_time=5
 
log_queries_not_using_indexes=False
 
log_slow_rate_limit=1
 
log_slow_verbosity=query_plan
 
 
 
## Mandatory settings for a MariaDB Galera node
 
# Galera only supports replicating InnoDB tables
 
default_storage_engine=InnoDB
 
# Interleaved locking mode for generating auto-increment values
 
innodb_autoinc_lock_mode=2
 
# Store data first to doublewrite buffer before writing to disk
 
innodb_doublewrite=1
 
# If async replication (master/slave) is also used, these settings are required on each Galera node to function as an async replication master:
 
binlog-format=ROW
 
log_slave_updates=1
 
wsrep_gtid_domain_id=1
 
wsrep_gtid_mode=ON
 
## WSREP provider configuration
 
wsrep_on=ON
 
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
 
# Address and name for whole cluster
 
wsrep_cluster_address=gcomm://w.w.w.w:4567,x.x.x.x:4567,y.y.y.y:4571
 
wsrep_cluster_name=redacted_cluster_name
 
wsrep_provider_options="gcache.size=4G; gcache.page_size=256M"
 
# How many threads to use for applying slave writsets.
 
wsrep_slave_threads=24
 
 
 
# Unique for each node
 
# Address and name for this node
 
wsrep_node_address=w.w.w.w
 
wsrep_node_name=redacted-master1
 
 
 
# State transfers
 
# Needs to be set explicitly, otherwise mariabackup or xtrabackup SST will fail
 
datadir=/var/lib/mysql
 
# State transfer method
 
wsrep_sst_method=mariabackup
 
wsrep_sst_auth=galera_sst:redacted-pw
 
 
 
# Auto-increment on multi-master clusters
 
# Set the increment the same on all nodes:
 
auto_increment_increment=3
 
# Set the offset differently for each node:
 
auto_increment_offset=1
 
# Disable the automatic control of auto-increment offset:
 
wsrep_auto_increment_control=0
 
 
 
# Additional cluster logging
 
# Log replication conflicts:
 
wsrep_log_conflicts=1
 
 
 
# If enabled all XtraDB/InnoDB transaction deadlock information is written to the error log.
 
# https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_print_all_deadlocks
 
innodb_print_all_deadlocks=ON
 
 
 
# Enable online InnoDB defragmentation
 
innodb_defragment=1
 
 
 
# Used to identify master and slave servers in replication. The server_id must be unique for each server in the replicating group.
 
# https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#server_id
 
server_id=1
 
 
 
# Ensure that binary logging is enabled
 
log-bin=/path/to/mysqld-bin
 
 
 
# Enable GTID.
 
# https://mariadb.com/kb/en/mariadb/gtid/#gtid_domain_id
 
# https://mariadb.com/kb/en/mariadb/gtid/#use-with-multi-source-replication-and-other-multi-master-setups
 
gtid_domain_id=1
 
 
 
# The GTID strict mode is an optional setting that can be used to help the DBA enforce a strict discipline about keeping binlogs identical across multiple servers replicating using global transaction ID.
 
# https://mariadb.com/kb/en/mariadb/gtid/#gtid_strict_mode
 
gtid_strict_mode=ON

Comment by Jan Lindström (Inactive) [ 2019-02-13 ]

Thank you, can I also have show create table for projects, users and invoice_type. Is those delete-clauses always done only a one server i.e. master1 or do you run them on other nodes in the cluster as well ? Note that foreign key invoice_data_ibfk_6 does not have on delete action.

Comment by Pim Rupert [ 2019-02-14 ]

Is those delete-clauses always done only a one server i.e. master1 or do you run them on other nodes in the cluster as well ?

Yes, DELETE (and all other write) statements are only run on master1.

can I also have show create table for projects, users and invoice_type

Hereby:

CREATE TABLE `projects` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `locale` enum('en-US','nl-NL','nl-NL_formal','de-DE','de-DE_informal') NOT NULL DEFAULT 'nl-NL',
 
  `name` varchar(255) NOT NULL,
 
  `server_name` varchar(255) NOT NULL,
 
  `server_aliases` text,
 
  `default_begeleider_id` int(11) unsigned DEFAULT NULL,
 
  `source_project_id` int(11) unsigned DEFAULT NULL,
 
  `color` varchar(7) NOT NULL DEFAULT '#078b00',
 
  `created` datetime DEFAULT NULL,
 
  `created_by` int(11) DEFAULT NULL,
 
  `created_byfullname` varchar(255) DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `server_name` (`server_name`),
 
  KEY `default_begeleider_id` (`default_begeleider_id`),
 
  KEY `source_project_id` (`source_project_id`),
 
  KEY `locale` (`locale`),
 
  CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`default_begeleider_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 
  CONSTRAINT `projects_ibfk_2` FOREIGN KEY (`source_project_id`) REFERENCES `projects` (`id`) ON UPDATE CASCADE
 
) ENGINE=InnoDB AUTO_INCREMENT=3515 DEFAULT CHARSET=utf8
 
 
 
CREATE TABLE `users` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `type` enum('redopt1','redopt2','redopt3','redopt4','redopt5','redopt6','redopt7','watcher','manager','subadmin','contact','external') NOT NULL DEFAULT 'redopt1',
 
  `usertype_id` int(11) unsigned DEFAULT NULL,
 
  `usertype_name` varchar(255) DEFAULT NULL,
 
  `project_id` int(11) unsigned DEFAULT NULL,
 
  `namo180_id` int(11) unsigned DEFAULT NULL,
 
  `namo180_roles` tinyint(1) unsigned DEFAULT NULL,
 
  `invited_by_id` int(11) unsigned DEFAULT NULL,
 
  `username` varchar(255) DEFAULT NULL,
 
  `email` varchar(255) NOT NULL DEFAULT '',
 
  `email2` varchar(255) NOT NULL DEFAULT '',
 
  `password` varchar(60) DEFAULT NULL,
 
  `logincode` varchar(12) DEFAULT NULL,
 
  `logincode_date` datetime DEFAULT NULL,
 
  `password_sent` tinyint(3) unsigned NOT NULL DEFAULT '0',
 
  `password_sent_date` datetime DEFAULT NULL,
 
  `rednamoconnect_code` varchar(255) DEFAULT NULL,
 
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
 
  `demo` tinyint(1) unsigned NOT NULL DEFAULT '0',
 
  `lastlogin` datetime DEFAULT NULL,
 
  `logins` int(11) unsigned NOT NULL DEFAULT '0',
 
  `login_nonce` varchar(36) DEFAULT NULL,
 
  `firstname` varchar(255) NOT NULL DEFAULT '',
 
  `letters` varchar(255) NOT NULL DEFAULT '',
 
  `linkname` varchar(30) NOT NULL DEFAULT '',
 
  `lastname` varchar(255) NOT NULL DEFAULT '',
 
  `fullname` varchar(255) NOT NULL DEFAULT '',
 
  `fullname_rev` varchar(255) NOT NULL DEFAULT '',
 
  `gender` enum('M','F','U') NOT NULL DEFAULT 'U',
 
  `birthdate` date DEFAULT NULL,
 
  `birthplace` varchar(255) NOT NULL,
 
  `bsn` varchar(64) NOT NULL,
 
  `telday` varchar(30) NOT NULL DEFAULT '',
 
  `telnight` varchar(30) NOT NULL DEFAULT '',
 
  `telmobile` varchar(30) NOT NULL DEFAULT '',
 
  `skype` varchar(50) NOT NULL,
 
  `address` varchar(255) NOT NULL DEFAULT '',
 
  `zipcode` varchar(10) NOT NULL DEFAULT '',
 
  `city` varchar(255) NOT NULL DEFAULT '',
 
  `code` varchar(255) NOT NULL DEFAULT '',
 
  `code_invoice` varchar(64) NOT NULL DEFAULT '',
 
  `code_project` varchar(64) NOT NULL DEFAULT '',
 
  `code_debtor` varchar(64) NOT NULL DEFAULT '',
 
  `userorganisation_id` int(11) unsigned DEFAULT NULL,
 
  `userorganisationcontact_id` int(11) unsigned DEFAULT NULL,
 
  `function` varchar(255) NOT NULL DEFAULT '',
 
  `socialmedia_www1` varchar(255) NOT NULL DEFAULT '',
 
  `socialmedia_www2` varchar(255) NOT NULL DEFAULT '',
 
  `socialmedia_www3` varchar(255) NOT NULL DEFAULT '',
 
  `socialmedia_www4` varchar(255) NOT NULL DEFAULT '',
 
  `socialmedia_weblog` varchar(255) NOT NULL DEFAULT '',
 
  `socialmedia_twitter` varchar(255) NOT NULL DEFAULT '',
 
  `socialmedia_linkedin` varchar(255) NOT NULL DEFAULT '',
 
  `socialmedia_facebook` varchar(255) NOT NULL DEFAULT '',
 
  `socialmedia_googleplus` varchar(255) NOT NULL DEFAULT '',
 
  `socialmedia_youtube` varchar(255) NOT NULL DEFAULT '',
 
  `area` text NOT NULL,
 
  `extra` text NOT NULL,
 
  `custom` text NOT NULL,
 
  `voluntary` tinyint(1) unsigned NOT NULL DEFAULT '0',
 
  `options` varchar(255) NOT NULL,
 
  `photo_file_id` int(11) unsigned DEFAULT NULL,
 
  `idcard_file_id` int(11) unsigned DEFAULT NULL,
 
  `idcard_seen` tinyint(1) unsigned NOT NULL DEFAULT '0',
 
  `idcard_seenby` varchar(255) DEFAULT NULL,
 
  `idcard_seendate` date DEFAULT NULL,
 
  `agreement_file_id` int(11) unsigned DEFAULT NULL,
 
  `bemiddelbaar` tinyint(1) unsigned NOT NULL DEFAULT '0',
 
  `agreed` tinyint(1) unsigned NOT NULL DEFAULT '0',
 
  `ambition` text,
 
  `popactive` tinyint(1) unsigned NOT NULL DEFAULT '0',
 
  `vacaturezoeker` tinyint(1) unsigned NOT NULL DEFAULT '0',
 
  `assignment_dashboard` tinyint(1) unsigned NOT NULL DEFAULT '0',
 
  `vacaturezoeker_added` datetime DEFAULT NULL,
 
  `signup_hash` varchar(32) DEFAULT NULL,
 
  `portfolio_hash` varchar(32) DEFAULT NULL,
 
  `portfolio_status_modified` datetime DEFAULT NULL,
 
  `archivereason` varchar(255) DEFAULT NULL,
 
  `kandidaatprofiel` text,
 
  `rijbewijs` set('B','BE','A','C','CE','D','DE','AM','landbouw','heftruck','trekker','trekkercert') NOT NULL,
 
  `eigenvervoer` enum('no','short','yes') DEFAULT NULL,
 
  `opleidingsniveau` varchar(255) NOT NULL,
 
  `competenties` text NOT NULL,
 
  `interested_professions` text NOT NULL,
 
  `timesheet_hours` int(11) unsigned DEFAULT NULL,
 
  `startdate` date DEFAULT NULL,
 
  `created` datetime NOT NULL,
 
  `finished` datetime DEFAULT NULL,
 
  `finished_submit` text,
 
  `outofoffice_date` date DEFAULT NULL,
 
  `outofoffice_descr` text,
 
  `assignment_settings` text,
 
  `modified` datetime DEFAULT NULL,
 
  `lastaction` datetime DEFAULT NULL,
 
  `lastnotified` datetime DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `username` (`username`),
 
  UNIQUE KEY `logincode` (`logincode`),
 
  KEY `type` (`type`),
 
  KEY `project_id` (`project_id`),
 
  KEY `code_invoice` (`code_invoice`),
 
  KEY `code_project` (`code_project`),
 
  KEY `code_debtor` (`code_debtor`),
 
  KEY `email` (`email`),
 
  KEY `userorganisation_id` (`userorganisation_id`),
 
  KEY `userorganisationcontact_id` (`userorganisationcontact_id`),
 
  KEY `fullname_rev` (`fullname_rev`),
 
  KEY `code` (`code`),
 
  KEY `email2` (`email2`),
 
  KEY `lastnotified` (`lastnotified`),
 
  KEY `FK_users_files` (`idcard_file_id`),
 
  KEY `FK_users_files2` (`agreement_file_id`),
 
  KEY `FK_users_files3` (`photo_file_id`),
 
  KEY `rednamoconnect_code` (`rednamoconnect_code`),
 
  KEY `birthdate` (`birthdate`),
 
  KEY `firstname` (`firstname`),
 
  KEY `usertype_id` (`usertype_id`),
 
  KEY `invited_by_id` (`invited_by_id`),
 
  CONSTRAINT `FK_users_files` FOREIGN KEY (`idcard_file_id`) REFERENCES `files` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 
  CONSTRAINT `FK_users_files2` FOREIGN KEY (`agreement_file_id`) REFERENCES `files` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 
  CONSTRAINT `FK_users_files3` FOREIGN KEY (`photo_file_id`) REFERENCES `files` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 
  CONSTRAINT `FK_users_projects` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON UPDATE CASCADE,
 
  CONSTRAINT `FK_users_userorganisation` FOREIGN KEY (`userorganisation_id`) REFERENCES `userorganisation` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 
  CONSTRAINT `FK_users_userorganisationcontact` FOREIGN KEY (`userorganisationcontact_id`) REFERENCES `userorganisationcontact` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`usertype_id`) REFERENCES `usertype` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 
  CONSTRAINT `users_ibfk_2` FOREIGN KEY (`invited_by_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 
) ENGINE=InnoDB AUTO_INCREMENT=1429700 DEFAULT CHARSET=utf8 PACK_KEYS=0

Thank you!

Comment by Jan Lindström (Inactive) [ 2019-02-26 ]

Hi and thank you. I failed to repeat this problem. As I expected your database contains complex foreign key tree. Questions:

  • When you issue delete on invoice_data and invoice do you use same project_id ?
  • Can you repeat this if master2 has wsrep_sync_wait=15 setting ?
  • Can you share full exported database where this repeats or repeatable test case ?
Comment by Pim Rupert [ 2019-02-26 ]

Hi Jan,

> When you issue delete on invoice_data and invoice do you use same project_id ?

Yes.

> Can you repeat this if master2 has wsrep_sync_wait=15 setting ?

But doesn't that halt / slow down all cluster writes? We are hesitant in making this change on a production cluster before fully understanding the impact.

> Can you share full exported database where this repeats or repeatable test case ?

We have noticed that this issue is very hard to reproduce. To me it looks like a race condition bug with Galera replication and foreign key checks. We were also thinking about trying to set wsrep_slave_thread to 1 in stead of 16 as a possible work-around. What do you think about that?

Comment by Marco Menzel [ 2019-03-04 ]

10.2.22 still crashing with same problem, only if wsrep_slave_thread > 1

Comment by Pim Rupert [ 2019-03-05 ]

Hi Marco,

Do you have an easily reproducible case? We find it incredibly hard to reproduce the problem on our 10.1 cluster.

> only if wsrep_slave_thread > 1

You did not have the issue when setting wsrep_slave_threads to 1?

Comment by Marco Menzel [ 2019-03-13 ]

Sorry, no reproducible case available. With wsrep_slave_threads = 1 the issue does'nt occur so far. There are some foreign key constructs without 'ON DELETE'.

Comment by Jan Lindström (Inactive) [ 2019-03-15 ]

Tried to repeat with different kind of deletes and data (see attached test case) but failed.

Comment by Brendan P [ 2019-05-27 ]

This one isn't easily reproducible at all and occurs completely at random, but is more often to occur when you stress your many wsrep applier threads, such as with a large table alter using an online ddl tool like pt-online-schema-change to alter your table. Something is not sanity checking the execution ordering of the queries with ON QUERYTYPE CASCADE FK statements.

I've had several cases where a simple table rename gets replicated before all the queries that do triggers to that table even get applied, causing the slave to abort on a FK apply error to a table that no longer exists...

Generated at Thu Feb 08 08:43:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.