MySQL Forums
Forum List  »  InnoDB clusters

Deadlock BLOB INSERT MySQL 8.0x InnoDB Cluster
Posted by: Volker Friedetzki
Date: October 28, 2019 04:19AM

When inserting a larger file into a MySQL 8.0 database with configured InnoDB cluster replication, the query runs into a table deadlock. For a smaller 6 KB file, the INSERT works. Problem occurs when running through the MySQL router as well as when running the INSERT via a direct connection to the "R / W" host. Appropriate transaction limits are unlikely to be reached. See MySQL settings below.

Table:
CREATE TABLE `onlineorder_attachments` (
`AttachmentGUID` varchar(36) NOT NULL,
`Filename` varchar(80) DEFAULT NULL,
`File` mediumblob,
PRIMARY KEY (`AttachmentGUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Testfiles:
M-02-012.jpg (4.813 KB) https://drive.google.com/open?id=19VIvwF9lM3mRzZqlDT-fl0kit0XG7_9k
M-05-055.jpg (6 KB) https://drive.google.com/open?id=1gFAr-vHfQ9G_6iHjrtrxnhsP95DJtRNJ

Command:
INSERT INTO `onlineorder_attachments`
(`AttachmentGUID`,`Filename`,`File`)
VALUES
('00a2b54f-b0cf-4f3a-9bed-02dba853b505', 'M-02-012.jpg', LOAD_FILE('/var/lib/mysql-files/tmp/M-02-012.jpg'));

MySQL settings output:
group_replication_communication_max_message_size 10485760
group_replication_components_stop_timeout 31536000
group_replication_compression_threshold 1000000
group_replication_group_seeds 10.29.169.13:33561
group_replication_local_address 10.29.169.12:33561
group_replication_member_expel_timeout 0
group_replication_message_cache_size 1073741824
group_replication_transaction_size_limit 150000000
slave_max_allowed_packet 1073741824
slave_net_timeout 60

InnoDB Cluster Setup status:
{
"clusterName": "AppCluster",
"defaultReplicaSet": {
"GRProtocolVersion": "8.0.16",
"groupName": "3afe628e-bdd1-11e9-8bbe-ac1f6bd3521c",
"name": "default",
"primary": "10.29.169.12:3356",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"10.29.169.12:3356": {
"address": "10.29.169.12:3356",
"fenceSysVars": [],
"memberId": "a715990f-bdc2-11e9-8ec6-ac1f6bd3521c",
"memberRole": "PRIMARY",
"memberState": "ONLINE",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.18"
},
"10.29.169.13:3356": {
"address": "10.29.169.13:3356",
"fenceSysVars": [
"read_only",
"super_read_only"
],
"memberId": "74c57dda-bdbb-11e9-94f8-ac1f6bd350ce",
"memberRole": "SECONDARY",
"memberState": "ONLINE",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.18"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "10.29.169.12:3356"
}


MySQL Shell output on error:
mysql> show open tables where in_use>0;
+----------+-------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------------------------+--------+-------------+
| appws30 | onlineorder_attachments | 1 | 0 |


mysql> show processlist;
+------+-----------------------------+---------------------------------+----------+---------+------+--------------------------------------------------------+----------------------- -------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------------------+---------------------------------+----------+---------+------+--------------------------------------------------------+----------------------- -------------------------------------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 472 | Waiting on empty queue | NULL |
| 9 | system user | | NULL | Connect | 472 | waiting for handler commit | Group replication appl ier module |
| 14 | system user | | NULL | Query | 472 | Slave has read all relay log; waiting for more updates | NULL |
| 344 | remoteuser | 10.29.169.12:56834 | NULL | Sleep | 351 | | NULL |
| 350 | remoteuser | 10.29.169.12:56842 | NULL | Sleep | 388 | | NULL
|
| 497 | remoteuser | 10.29.169.12:56996 | NULL | Sleep | 351 | | NULL |
| 615 | root | localhost | appws30 | Query | 255 | waiting for handler commit | INSERT INTO `onlineord er_attachments` (`AttachmentGUID`,`Filename`,`File`) VALUES ('44a2b54f-b0cf-4 |
| |
+------+-----------------------------+---------------------------------+----------+---------+------+--------------------------------------------------------+----------------------- -------------------------------------------------------------------------------+


Testcases:

For normal MySQL 8.0 installation without InnoDB Cluster configuration, the INSERT works.

For MySQL 8.0 with InnoDB Cluster configuration but only one host in the cluster, the INSERT also works.

Only MySQL 8.0 with InnoDB Cluster Configuration and more hosts INSERT runs in deadlock.



Have we forgotten a setting or is this a bug?

Options: ReplyQuote


Subject
Views
Written By
Posted
Deadlock BLOB INSERT MySQL 8.0x InnoDB Cluster
833
October 28, 2019 04:19AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.