0

I have a table like the following:

CREATE TABLE `demo_upsert` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `LastListID` BIGINT(20) NULL DEFAULT NULL,
    `Statistics` BIGINT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (`ID`) USING BTREE,
    UNIQUE INDEX `Url` (`Url`) USING HASH
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

When I try a bulk upsert operation:

INSERT INTO demo_upsert (Url, LastListID) VALUES (?, 9)
ON DUPLICATE KEY UPDATE LastListID=9

Out of ~15K rows for insert, I'm getting ~200 rows actually affected. The latter number depends on the previous number of rows in demo_upsert table.

The MariaDB versions tried are 10.4.12 and 10.4.13 - same problem.

UPDATE: I've implemented a reproducer program and submitted a bug https://jira.mariadb.org/browse/CONC-472

  • Out of ~15K rows for insert, I'm getting ~200 rows actually affected. Affected == which's values were really changed, i.e. only those duplicates which's old LastListID value was NOT 9. – Akina May 30 '20 at 13:24
  • @Akina, according to the documentation, MariaDB/MySQL must report each inserted row as 1 and each updated row as 2. So if rows are updated rather than inserted, the number must be even larger. – Serge Rogatch May 30 '20 at 17:57
  • I cannot say anything about MariaDB - I have no it on my comp now. But MySQL reports 4 different numbers during INSERT .. ODKU, like: Query OK, 5 rows affected (0.10 sec) Records: 4 Duplicates: 2 Warnings: 0. What number you tell about? – Akina May 30 '20 at 18:47
  • @Akina, I mean the number reported by mysql_affected_rows() from Connector/C. – Serge Rogatch May 31 '20 at 3:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.