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
-