MySQL: how to prevent deadlock on concurrent inserts

Percona Server 5.6.39-83.1-log

Table structure:

CREATE TABLE `myTable` (     `column_1` VARCHAR(50) NOT NULL DEFAULT '',     `column_2` VARCHAR(50) NOT NULL DEFAULT '',     `column_3` VARCHAR(50) NOT NULL DEFAULT '',     `column_4` VARCHAR(50) NOT NULL DEFAULT '',     `column_5` VARCHAR(50) NOT NULL DEFAULT '',     PRIMARY KEY (`column_1`, `column_2`, `column_3`) USING BTREE,     INDEX `column_3` (`column_3`) USING BTREE,     INDEX `column_4_5` (`column_4`, `column_5`) USING BTREE ) COLLATE='utf8_general_ci' ENGINE=InnoDB; 

There are two or more concurrent insert leads to deadlock:

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-03-23 18:12:45 7f1c08eb8700 *** (1) TRANSACTION: TRANSACTION 55665288601, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s) MySQL thread id 10275055, OS thread handle 0x7f1c08c6c700, query id 56392293629 localhost user update INSERT IGNORE INTO myDB.myTable (`column_1`, `column_2`, `column_3`, `column_4`, `column_5`) VALUES ('a1', 'a2', 'a3', 'a4', 'a5'), ('b1', 'b2', 'b3', 'b4', 'b5'), ('c1', 'c2', 'c3', 'c4', 'c5'), *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 337207190 page no 7370 n bits 200 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288601 lock mode S locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 55665288598, ACTIVE 0 sec inserting, thread declared inside InnoDB 4965 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 35 MySQL thread id 10275057, OS thread handle 0x7f1c08eb8700, query id 56392293627 localhost user update INSERT IGNORE INTO myDB.myTable (`column_1`, `column_2`, `column_3`, `column_4`, `column_5`) VALUES ('a1', 'a2', 'a3', 'a4', 'a5'), ('b1', 'b2', 'b3', 'b4', 'b5'), ('c1', 'c2', 'c3', 'c4', 'c5') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 337207190 page no 7370 n bits 200 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288598 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 337207190 page no 83133 n bits 144 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288598 lock_mode X insert intention waiting *** WE ROLL BACK TRANSACTION (1) 

Why is this happening?
Despite the fact that I do not use auto-increment in the primary key and use IGNORE in query.
How should I avoid this problem?

Data Consistency Between 2 Mysql Databases Running Different Versions

I need to migrate an old web application using old php and mysql versions. I am intending to start by migrating the code that interacts with the database to a new server with the latest versions and allow the old server to use an API in the new server. Ideally I would like to have the data consistent between the database in the new server and the one in the old server. It seems that replication only works only when the master is only one version behind the slave. In this case the difference is a way more than one version. I also want changes to the database on the new server to reflect on the database on the old server. Are there any tools/techniques to help with this situation other than ensuring the data consistency programmatically with cron jobs …. If something is not clear please let me know and all suggestions are welcome.

MySQL 5.7 – Calling procedure with dynamic name

I’m trying to write a MySQL (5.7) procedure that dynamically calls another procedure based on the one of its inputs.

The code I came up with so far is this (procedure_exists works for checking whether or not the function is available):

CREATE PROCEDURE process_event (IN event_type VARCHAR(32), IN aggregate_id CHAR(36), IN payload JSON) BEGIN     DECLARE handler_name VARCHAR(38) DEFAULT CONCAT('handle', event_type);      IF procedure_exists(handler_name) THEN         SET @handler_sql = CONCAT('CALL ', handler_name, '(?,?)');          PREPARE prepared_statement FROM @handler_sql;          SET @aggregate_id = aggregate_id;         SET @payload = payload;          EXECUTE prepared_statement USING @aggregate_id, @payload;     END IF; END; 

This doesn’t work however, because I keep getting

[0A000][1336] Dynamic SQL is not allowed in stored function or trigger

From what I understand, disallowing that by default makes perfect sense, but from what I’ve gathered there seems to be no way of opting in to theoretically less secure behavior, by allowing certain types of statements or disabling the check altogether.

Do you have any suggestions for getting this to work?

In short the question boils down to this:

Can I somehow have a procedure to which I pass event_type (VARCHAR(32)), aggregate_id (CHAR(36)) and payload (JSON) which will then automatically call handle{event_type}(aggregate_id, payload) if it exists?

Mysql CSV import when using LOAD DATA LOCAL INFILE only imports half of the rows

I am doing an import with below code and it works but only imports every second row (1,3,5,7…) and only imports half the total csv contents at the end.

LOAD DATA LOCAL INFILE "'.$  file.'" INTO TABLE '.$  table.' FIELDS TERMINATED by \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' (column1,column2,etc) 

My csv is like this “text”,1234,”text” etc

Can anyone pls help with this.

MySQL Split-Brain Scenario

So basically a Fault-Tolerant InnoDB Cluster has 3 nodes. In my case I have the same setup as recommended, then suddenly the Master Node(Primary) crashed then when the Master Node(Primary) trying to join in the cluster again, this what we called “Split Brain” happened I have now 2 Node with R/W and 1 R/O. Surprisingly my app can write to this 2 R/W node knowing that I have MySQL ROUTER that the app can connect to. Can MySQL Router caused this error? or a bug?

How to correctly interpret index usages from EXPLAIN of mysql?

enter image description here

I’ve exported the results of an EXPLAIN run on a query. What I find confusing is that there’s the key column listing out one of the indexes from the list of possible_keys(not shown in picture) however only the top row makes mention of Using index explicitely in the Extra column.

  1. What does this mean in the 2nd row, is it not using the index listed in the key column?
  2. How should I interpret what the contents of the key column is about and how it is used?
    enter code here

    a. Should I interpret this as that index is used in the where stage of this query?

MySQL table export – where clause that ignores empty rows Vs getting whole table, which would be faster?

I’m using MySQLWorkbench to export a large table from a database into a CSV. This is what the data in the table is like:

enter image description here

All the rows in the table have values for date, code1, string1, code2 and string2. But, the majority of a, b and c are empty. The command I’m using for getting this data right now is:

select * from table where code1 = '0001'; 

Given that a, b and c are empty for most rows, I could also do:

select * from table where code1 = '0001' and a <> ''; 

Which of the two do you think would export this table more quickly?

Edit: I don’t care about the rows where a, b and c are empty.

How to trigger Mysql parallel query function to start?

Please help,how to trigger Mysql parallel query function to start? MySQL version is 8.0.19.

mysql> show variables like 'innodb_parallel_read_threads'; +------------------------------+-------+ | Variable_name                | Value | +------------------------------+-------+ | innodb_parallel_read_threads | 4     | +------------------------------+-------+ 1 row in set (0.00 sec) 

Judging from the execution plan, although the cost value is high, it does not trigger parallel queries.

mysql> explain analyze SELECT count(a.name) FROM b,a WHERE b.id = a.id AND a.id < 10000\G *************************** 1. row *************************** EXPLAIN: -> Aggregate: count(a.`name`)  (actual time=79199.970..79199.971 rows=1 loops=1)     -> Inner hash join (b.id = a.id)  (cost=29804029261222.55 rows=29803521726988) (actual time=52129.791..79198.823 rows=9999 loops=1)         -> Table scan on b  (cost=31.81 rows=18758239) (actual time=0.385..19630.712 rows=20000000 loops=1)         -> Hash             -> Filter: (a.id < 10000)  (cost=4909601.51 rows=15888229) (actual time=2.266..52117.583 rows=9999 loops=1)                 -> Table scan on a  (cost=4909601.51 rows=47669458) (actual time=2.262..48985.413 rows=50000000 loops=1)  1 row in set (1 min 19.25 sec)