Behavior of affected rows - PolarDB - Alibaba Cloud Documentation Center

This topic describes the behavior of affected rows in PolarDB-X.

Usage notes

Starting from version 5.4.17-16921956, PolarDB-X has returned affected rows in Data Manipulation Language (DML) operations if the useAffectedRows option is set to true.
DML operations in PolarDB-X involve complex logic. The behavior of the affected rows in PolarDB-X is different from that of the affected rows in MySQL.
The useAffectedRows option in JDBC URLs affects the marker bits of the CLIENT_FOUND_ROWS command line. If the useAffectedRows option is set to false, the CLIENT_FOUND_ROWS command line is set to true by default. By default, the CLIENT_FOUND_ROWS command line is set to false.
If the CLIENT_FOUND_ROWS command line is set to true, DML operations return the number of matched rows. If the command line is set to false, DML operations return the number of updated rows.
MySQL documentation describe the impacts of the command line on DML operations. The command line affects only the UPDATE and UPSERT operations.
Before you make business decisions based on affected rows, we recommend that you perform tests and integrate the tests into unit test cases. In the later versions of PolarDB-X, the default behavior of affected rows may change.
When you use the MySQL connector JDBC operation to access PolarDB-X, the useAffectedRows option is set to false by default. This corresponds to setting the CLIENT_FOUND_ROWS' command line to ON. To obtain the number of modified rows, you can set the option in URLs to true.
When you use MySQL command-line client programs, the CLIENT_FOUND_ROWS command line is set to OFF by default. In this case, DML operations return the number of modified rows.

Known inconsistent behavior

replace into

Data that is identical to existing data is inserted into a table that has only a unique key but not a primary key.

Note

The preceding behavior is different from that in MySQL. However, this behavior is expected.

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)

This behavior occurs for the following reason: If a table does not have a primary key, an implicit primary key is created. To optimize performance, the REPLACE INTO command is pushed down, and an auto-increment sequence is automatically added to the implicit primary key. As a result, the implicit primary keys are inconsistent even if the inserted data is consistent. In this case, two rows are affected, indicating that one row is deleted and one row is inserted.

A local unique key is not a shard key, and the unique limits apply only to the current shard.

Note

The preceding behavior is different from that in MySQL. However, this behavior is expected.

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

The DML_SKIP_TRIVIAL_UPDATE hint is set to false.
Note
The preceding behavior is different from that in MySQL. However, this behavior is expected.
```
/*+TDDL: cmd_extra(DML_SKIP_TRIVIAL_UPDATE=FALSE)*/
```
If the DML_SKIP_TRIVIAL_UPDATE hint is included in an SQL UPDATE statement, the system does not check whether the values before and after an update operation are consistent. The system considers the values as inconsistent and performs an update operation to resolve equality check errors. This causes the inaccurate number of affected rows. We recommend that you set the DML_SKIP_TRIVIAL_UPDATE hint to false only after you submit a ticket and obtain approval from R&D personnel. You must also evaluate the impacts of the preceding settings in advance.

The original data A->B->C->A is considered not updated in batch insert.

Note

Compatibility issues are fixed in version 5.4.17-16971811.

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
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
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 issue fixes
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

The number of affected rows is incorrect when a relocate operation is performed.

Note

Compatibility issues are fixed in version 5.4.17-16971811.

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
update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 1 rows affected (0.11 sec)

# After issue fixes
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

JSON

The preceding behavior is different from that in MySQL. PolarDB-X does not compare data in JSON columns. Updating JSON data is determined only by string comparison, which may cause the inaccurate number of affected rows.