Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.6.15
-
None
-
Debian 11
Description
Don't know if it's a bug or not but it's a miss for sure we refer to the standard behavior.
I got a script who UPDATE some lines to small table, this table is important because i keep there all modifications event if the table is not not big, (not more than 2000 lines, yes I don't have clients who more than 2000 servers MySQL & MariaDB 
)
here my table :
CREATE TABLE `mysql_server` (
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
`id_client` int(11) NOT NULL, |
`id_environment` int(11) NOT NULL, |
`name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, |
`display_name` varchar(100) NOT NULL, |
`ip` char(15) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, |
`hostname` varchar(200) NOT NULL DEFAULT '', |
`login` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, |
`passwd` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, |
`database` varchar(64) NOT NULL, |
`is_password_crypted` int(11) NOT NULL, |
`port` int(11) NOT NULL, |
`ssh_port` int(11) NOT NULL DEFAULT 22, |
`ssh_login` text CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '\'\'', |
`is_sudo` int(11) NOT NULL DEFAULT 0, |
`is_root` int(11) NOT NULL DEFAULT 1, |
`is_monitored` int(11) NOT NULL DEFAULT 1, |
`is_proxy` int(11) NOT NULL DEFAULT 0, |
`is_available` int(11) NOT NULL DEFAULT 0 COMMENT '-1= not asnwered, 0 = HS, 1=OK', |
`is_acknowledged` int(11) NOT NULL DEFAULT 0, |
`error` text NOT NULL DEFAULT '', |
`warning` text NOT NULL DEFAULT '', |
`date_refresh` datetime NOT NULL DEFAULT current_timestamp(),
|
`ssh_available` int(11) NOT NULL DEFAULT 0 COMMENT '-1= not asnwered, 0 = HS, 1=OK', |
`ssh_date_refresh` datetime NOT NULL DEFAULT current_timestamp(),
|
`ssh_error` text NOT NULL DEFAULT '', |
PRIMARY KEY (`id`),
|
UNIQUE KEY `name` (`name`),
|
UNIQUE KEY `ip` (`ip`,`port`),
|
KEY `id_client` (`id_client`),
|
KEY `id_environment` (`id_environment`),
|
KEY `is_monitored` (`is_monitored`,`id_client`),
|
CONSTRAINT `mysql_server_ibfk_1` FOREIGN KEY (`id_client`) REFERENCES `client` (`id`),
|
CONSTRAINT `mysql_server_ibfk_2` FOREIGN KEY (`id_environment`) REFERENCES `environment` (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci WITH SYSTEM VERSIONING |
To be able to be back in the past on some dashboard I added SYSTEM VERSIONING.
when you make an UPDATE on a standard table you got it : (when data are already SET correctly) :
(the table `gg` is the same table than `mysql_server` without SYSTEM VERSIONING)
MariaDB [pmacontrol]> show master status;
|
+--------------------+----------+--------------+------------------+
|
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
|
+--------------------+----------+--------------+------------------+
|
| mariadb-bin.002394 | 5289522 | | | |
+--------------------+----------+--------------+------------------+
|
1 row in set (0,000 sec) |
|
|
MariaDB [pmacontrol]> UPDATE gg SET is_available = 1, error = '',is_acknowledged=0 WHERE id in (5,10,6,9,22,11,14,13,15,12,16,21,18,19,24,20,2,3,4,1,7,8,23); |
Query OK, 0 rows affected (0,000 sec) |
Rows matched: 23 Changed: 0 Warnings: 0 |
|
|
MariaDB [pmacontrol]> show master status;
|
+--------------------+----------+--------------+------------------+
|
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
|
+--------------------+----------+--------------+------------------+
|
| mariadb-bin.002394 | 5289522 | | | |
+--------------------+----------+--------------+------------------+
|
1 row in set (0,000 sec) |
|
As you can see nothing changed, here my question why we don't have the same behavior with SYSTEM VERSIONING ?
MariaDB [pmacontrol]> show master status;
|
+--------------------+----------+--------------+------------------+
|
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
|
+--------------------+----------+--------------+------------------+
|
| mariadb-bin.002394 | 5266436 | | | |
+--------------------+----------+--------------+------------------+
|
1 row in set (0,000 sec) |
|
|
MariaDB [pmacontrol]> UPDATE mysql_server SET is_available = 1, error = '',is_acknowledged=0 WHERE id in (5,10,6,9,22,11,14,13,15,12,16,21,18,19,24,20,2,3,4,1,7,8,23); |
Query OK, 23 rows affected (0,001 sec) |
Rows matched: 23 Changed: 23 Inserted: 23 Warnings: 0 |
|
|
MariaDB [pmacontrol]> show master status;
|
+--------------------+----------+--------------+------------------+
|
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
|
+--------------------+----------+--------------+------------------+
|
| mariadb-bin.002394 | 5289522 | | | |
+--------------------+----------+--------------+------------------+
|
1 row in set (0,000 sec) |
After of course you can tell me I can find a work around with :
MariaDB [pmacontrol]> show master status;
|
+--------------------+----------+--------------+------------------+
|
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
|
+--------------------+----------+--------------+------------------+
|
| mariadb-bin.002394 | 5266436 | | | |
+--------------------+----------+--------------+------------------+
|
1 row in set (0,000 sec) |
|
|
MariaDB [pmacontrol]> UPDATE mysql_server SET is_available = 1, error = '',is_acknowledged=0 WHERE id in (SELECT id from mysql_server where (is_available != 1 or error != '') and is_acknowledged=0 and id in (5,10,6,9,22,11,14,13,15,12,16,21,18,19,24,20,2,3,4,1,7,8,23)); |
Query OK, 0 rows affected (0,001 sec) |
Rows matched: 0 Changed: 0 Warnings: 0 |
|
|
MariaDB [pmacontrol]> show master status;
|
+--------------------+----------+--------------+------------------+
|
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
|
+--------------------+----------+--------------+------------------+
|
| mariadb-bin.002394 | 5266436 | | | |
+--------------------+----------+--------------+------------------+
|
1 row in set (0,000 sec) |
Attachments
Issue Links
- duplicates
-
-
- Closed
-
-
-
- Closed
-
- is caused by
-
-
- Closed
-
Activity
Hi Sergei
I rode : https://jira.mariadb.org/browse/MDEV-23446
what is the goal to generate a new line in history if nothing change ?
Maybe it's could be a good deal to add a new Variable on table to select this behavior or the other one ? (and not be abble to change once it's chosen)
This is intentional. At first, such updates were not creating historical rows and in the course of
MDEV-23446it was changed.Indeed, SQL Standard does not have the language "if the new row was equal to the old row, then it's not really an UPDATE". There's no concept of comparing and old and new rows there, it's an optimization inside MariaDB. We can use this optimization only when it doesn't cause any user visible effects. But, for example, triggers are still run even if the row is updated to itself. And a new historical row has to be created too.