phpmyadmin doesn't update all rows at once after choosing multiple rows to edit

[miklcct]

Describe the bug

PhpMyAdmin doesn't update all rows in a single SQL query after choosing multiple rows to edit.

To Reproduce

1. Browse a table.
2. Choose multiple rows to edit.
3. Edit them all.
4. Save.

Expected behavior

All rows are updated at once in a single SQL query, such that the constraints are not violated.

Actual behavior

It tries to update only one row in the SQL query, which fails because the row conflicts with the original other rows which have been edited in the same page as well.

For example, if I have a unique key on columns A and B, and I choose the rows with the same A value to edit, and shuffle the B values among the rows, the page fail to save.

Server configuration

• Operating system: Debian 12
• Web server: Apache 2.4
• Database version: MariaDB 10.11
• PHP version: 8.2.7
• phpMyAdmin version: 5.1.2

Client configuration

• Browser: Chrome
• Operating system: Ubuntu 23.10

Additional context

Add any other context about the bug here.

[MoonE]

MySQL simply can't make an update like that.

Here is a little test sql, which will result in

#​1062 - Duplicate entry 'b' for key 't_name_unique'

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(254) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

INSERT INTO `t` (`id`, `name`) VALUES (1, 'a'), (2, 'b');

UPDATE t SET `name` = CASE `id` WHEN 1 THEN 'b' WHEN 2 THEN 'a' END WHERE `id` IN (1, 2);

See also:
https://stackoverflow.com/questions/11207574/how-to-swap-values-of-two-rows-in-mysql-without-violating-unique-constraint

I believe PostgreSQL can do this as it checks unique constraints only at the end of a transaction, but sadly MySQL can't.