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

Improve this question
4
  • 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, 2020 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, 2020 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, 2020 at 18:47
  • @Akina, I mean the number reported by mysql_affected_rows() from Connector/C.
    – Serge Rogatch
    May 31, 2020 at 3:08

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.