Affected rows behavior-PolarDB(PolarDB)-阿里云帮助中心

DML operations in PolarDB-X run in a distributed environment, which causes the affected row count to differ from MySQL in certain scenarios. This page explains how useAffectedRows and CLIENT_FOUND_ROWS control what is counted, and documents each known inconsistency with its fix status.

How affected rows are counted

The useAffectedRows parameter in your JDBC connection URL controls the CLIENT_FOUND_ROWS flag, which determines whether DML operations return matched rows or modified rows.

`useAffectedRows`	`CLIENT_FOUND_ROWS`	Affected rows returned
`false` (default)	ON	Matched rows (rows that matched the `WHERE` clause)
`true`	OFF	Modified rows (rows whose values actually changed)

CLIENT_FOUND_ROWS affects only UPDATE and upsert operations. See the MySQL documentation for details.

Default behavior by client:

MySQL Connector/J: useAffectedRows=false by default — returns matched rows. To get modified rows instead, set useAffectedRows=true in your JDBC connection URL.
MySQL command-line client: CLIENT_FOUND_ROWS=OFF by default — returns modified rows.

PolarDB-X started returning affected rows for DML operations in version 5.4.17-16921956, when useAffectedRows=true is set. Test your affected row assumptions and add them to your unit tests — default behavior may change in future versions.

Known inconsistencies

The table below summarizes known differences from MySQL. Behavior marked Permanent is by design in PolarDB-X's distributed architecture and will not be fixed. Behavior marked Fixed was a bug; upgrade to the specified version for MySQL-compatible results.

Operation	Scenario	Status
`REPLACE INTO`	Table with a unique key but no primary key	Permanent
`REPLACE INTO`	Local unique key that is not a shard key	Permanent
`INSERT ... ON DUPLICATE KEY UPDATE`	`DML_SKIP_TRIVIAL_UPDATE=false` hint	Permanent (use with caution)
`INSERT ... ON DUPLICATE KEY UPDATE`	A→B→C→A value cycle in batch insert	Fixed in 5.4.17-16971811
`UPDATE`	Relocate operation	Fixed in 5.4.17-16971811
`UPDATE`	JSON column comparison	Permanent

REPLACE INTO: table with a unique key but no primary key

PolarDB-X returns `2 rows affected`. MySQL returns `1 row affected`.

When a table has no explicit primary key, PolarDB-X creates an implicit primary key (_drds_implicit_id_) and pushes REPLACE INTO down to individual shards with an auto-increment sequence. Because the implicit primary key values differ between executions even when the inserted data is identical, PolarDB-X treats each operation as a delete followed by an insert — resulting in 2 affected rows.

This is by design and is not a bug.

PolarDB-X 2.0

CREATE TABLE `replace_test_tb_no_pk_with_uk` (
    `id` bigint(11) NOT NULL DEFAULT '1',
    `c1` bigint(20) DEFAULT NULL,
    `c2` bigint(20) DEFAULT NULL,
    `c3` bigint(20) DEFAULT NULL,
    `c4` bigint(20) DEFAULT NULL,
    `c5` varchar(255) DEFAULT NULL,
    `c6` datetime DEFAULT NULL,
    `c7` text,
    `c8` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    `_drds_implicit_id_` bigint(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`_drds_implicit_id_`),
    UNIQUE LOCAL KEY `u_id` (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 100022 DEFAULT CHARSET = utf8
PARTITION BY KEY(`id`)
PARTITIONS 7
/* tablegroup = `tg602` */

delete from replace_test_tb_no_pk_with_uk;
Query OK, 2 rows affected (0.09 sec)

replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
Query OK, 1 row affected (0.05 sec)

replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
Query OK, 2 rows affected (0.03 sec)

mysql> replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
Query OK, 2 rows affected (0.11 sec)

MySQL

CREATE TABLE `replace_test_tb_no_pk_with_uk` (
  `id` bigint(11) NOT NULL DEFAULT '1',
  `c1` bigint(20) DEFAULT NULL,
  `c2` bigint(20) DEFAULT NULL,
  `c3` bigint(20) DEFAULT NULL,
  `c4` bigint(20) DEFAULT NULL,
  `c5` varchar(255) DEFAULT NULL,
  `c6` datetime DEFAULT NULL,
  `c7` text,
  `c8` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `u_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

delete from replace_test_tb_no_pk_with_uk;
Query OK, 2 rows affected (0.00 sec)

replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
Query OK, 1 row affected (0.00 sec)

replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
Query OK, 1 row affected (0.00 sec)

replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
Query OK, 1 row affected (0.00 sec)

REPLACE INTO: local unique key that is not a shard key

PolarDB-X returns `4 rows affected` for certain operations where MySQL returns `5 rows affected`.

When a unique key is a local index rather than a global index, uniqueness is enforced only within each shard. Cross-shard duplicate detection does not apply, which affects the delete-plus-insert count that REPLACE INTO reports.

This is by design and is not a bug.

PolarDB-X 2.0

CREATE TABLE `replace_test_tbl` (
    `id` int(11) NOT NULL,
    `a` int(11) DEFAULT '1',
    `b` int(11) DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `b` (`b`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 3

replace into replace_test_tbl (id,a,b) values (0,1,1),(1,2,2),(2,3,3),(100,100,100),(101,103,103);
Query OK, 5 rows affected (0.03 sec)

replace into replace_test_tbl (id) values (1);
Query OK, 2 rows affected (0.01 sec)

replace into replace_test_tbl (id,a,b) values (3,0+2,0+2);
Query OK, 1 row affected (0.01 sec)

select * from replace_test_tbl;
+-----+------+------+
| id  | a    | b    |
+-----+------+------+
|   1 |    1 |    0 |
| 101 |  103 |  103 |
| 100 |  100 |  100 |
|   0 |    1 |    1 |
|   2 |    3 |    3 |
|   3 |    2 |    2 |
+-----+------+------+
6 rows in set (0.03 sec)

replace into replace_test_tbl (id,a,b) values (1,2,2),(2,3,3);
Query OK, 4 rows affected (0.02 sec)

mysql> select * from replace_test_tbl;
+-----+------+------+
| id  | a    | b    |
+-----+------+------+
|   0 |    1 |    1 |
|   2 |    3 |    3 |
|   3 |    2 |    2 |
|   1 |    2 |    2 |
| 101 |  103 |  103 |
| 100 |  100 |  100 |
+-----+------+------+
6 rows in set (0.04 sec)

MySQL

CREATE TABLE `replace_test_tbl` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT '1',
  `b` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

replace into replace_test_tbl (id,a,b) values (0,1,1),(1,2,2),(2,3,3),(100,100,100),(101,103,103);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

replace into replace_test_tbl (id) values (1);
Query OK, 2 rows affected (0.01 sec)

replace into replace_test_tbl (id,a,b) values (3,2,2);
Query OK, 1 row affected (0.00 sec)

select * from replace_test_tbl;
+-----+------+------+
| id  | a    | b    |
+-----+------+------+
|   0 |    1 |    1 |
|   1 |    1 |    0 |
|   2 |    3 |    3 |
|   3 |    2 |    2 |
| 100 |  100 |  100 |
| 101 |  103 |  103 |
+-----+------+------+
6 rows in set (0.00 sec)

replace into replace_test_tbl (id,a,b) values (1,2,2),(2,3,3);
Query OK, 5 rows affected (0.00 sec)
Records: 2  Duplicates: 3  Warnings: 0

select * from replace_test_tbl;
+-----+------+------+
| id  | a    | b    |
+-----+------+------+
|   0 |    1 |    1 |
|   1 |    2 |    2 |
|   2 |    3 |    3 |
| 100 |  100 |  100 |
| 101 |  103 |  103 |
+-----+------+------+
5 rows in set (0.00 sec)

INSERT ... ON DUPLICATE KEY UPDATE: DML_SKIP_TRIVIAL_UPDATE=false

When this hint is active, affected rows may be overcounted.

/*+TDDL: cmd_extra(DML_SKIP_TRIVIAL_UPDATE=FALSE)*/

With DML_SKIP_TRIVIAL_UPDATE=false, PolarDB-X skips the before/after value comparison and always treats the update as a change, even when values are identical. This inflates the affected row count.

Only apply this hint after submitting a support ticket and getting approval from the PolarDB-X engineering team. Evaluate the impact on your application's affected row logic before enabling it.

INSERT ... ON DUPLICATE KEY UPDATE: A→B→C→A value cycle — fixed in 5.4.17-16971811

Before the fix, PolarDB-X returned `0 rows affected` for subsequent runs. MySQL returns `6 rows affected`. After upgrading to 5.4.17-16971811, PolarDB-X matches MySQL.

When a batch insert cycles values back to their original state (A→B→C→A), earlier versions of PolarDB-X incorrectly detected no change and returned 0 affected rows. This was a bug, not a design difference.

PolarDB-X 2.0

CREATE PARTITION TABLE `tb` (
    `id` int(11) NOT NULL,
    `a` int(11) DEFAULT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE GLOBAL INDEX /* ua_$b525 */ `ua` (`a`)
        PARTITION BY KEY(`a`)
        PARTITIONS 3,
    UNIQUE LOCAL KEY `_local_ua` (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 3

insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
Query OK, 8 rows affected (0.44 sec)

# Incorrect results (before fix)
insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
Query OK, 0 rows affected (0.15 sec)

# Incorrect results (before fix)
insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
Query OK, 0 rows affected (0.16 sec)

# After fix (5.4.17-16971811)
insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
Query OK, 6 rows affected (0.23 sec)

MySQL

CREATE TABLE `tb` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ua` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
Query OK, 8 rows affected (0.00 sec)
Records: 5  Duplicates: 3  Warnings: 0

insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
Query OK, 6 rows affected (0.00 sec)
Records: 5  Duplicates: 3  Warnings: 0

insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
Query OK, 6 rows affected (0.00 sec)
Records: 5  Duplicates: 3  Warnings: 0

UPDATE: relocate operation — fixed in 5.4.17-16971811

Before the fix, PolarDB-X returned `1 row affected` for a no-change update. MySQL returns `0 rows affected`. After upgrading to 5.4.17-16971811, PolarDB-X matches MySQL.

A relocate operation moves a row between shards when the primary key is updated. Earlier versions incorrectly counted this internal movement as a modification even when the final values were unchanged.

PolarDB-X 2.0

CREATE TABLE `update_relocate_tb` (
    `id` int(11) NOT NULL,
    `a` varchar(100) DEFAULT NULL,
    `b` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 3

insert into update_relocate_tb (id,a)values (1, 'fdas');
Query OK, 1 row affected (0.11 sec)

update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 1 row affected (0.08 sec)

# Incorrect results (before fix)
update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 1 rows affected (0.11 sec)

# After fix (5.4.17-16971811)
update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 0 rows affected (0.11 sec)

MySQL

CREATE TABLE `update_relocate_tb` (
  `id` int(11) NOT NULL,
  `a` varchar(100) DEFAULT NULL,
  `b` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into update_relocate_tb (id,a)values (1, 'fdas');
Query OK, 1 row affected (0.00 sec)

update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

UPDATE: JSON column comparison

PolarDB-X may overcount affected rows when updating JSON columns.

PolarDB-X does not compare JSON column values before and after an update. It uses string comparison instead, which can report a row as changed even when the JSON content is semantically identical.