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.813KB => Deadlock! M-05-055.jpg 6KB => Works
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?
Between the Mysql servers was a 10MBit/s LAN. As a result, MySQL database replication appeared to time out on larger files, causing the transaction to rollback, which in turn resulted in a tables lock.
With a 100MBit/s LAN, the error does not occur after several tests. Only this helped. An increase of slave_net_timeout did not help either.
Why in an asynchronous replication a timeout can lead to a table lock on the master does not make sense in my view.
UPDATE: With the 100MBit/s LAN, the error now occurs for files larger than about 20MB. Since we do not want to store any files larger than 16 MB in the database anyway, we simply set the maximum SQL packet size to 16 MB. This causes the error code 1301 "... larger than max_allowed_packet ..." to be output directly to the INSERT of a larger file, thereby preventing table locks.
UPDATE 2:
and setting group_replication_transaction_size_limit in /etc/mysql/mysql.conf.d/mysqld.cnf partially helps.
FINAL SULUTION: It seems that MySQL replication generally has a problem when larger files are stored in the tables. We have therefore changed our application so that all files are saved in the file system and the MySQL table only stores the file key and other file information. We have created our own solution for replicating files over several machines.