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?