|
To upgrade system-versioned tables from an old version where current records are indicated by 2038-01-dd HH:MM:07.999999 to the new version with 2106-02-dd HH:MM:15.999999, mariadb-upgrade runs ALTER TABLE .. FORCE. Apparently the idea is to rebuild the table.
It doesn't however work for InnoDB, where ALTER TABLE .. FORCE is done "inplace".
|
On 11.3 as an old version
|
MariaDB [test]> show create table tvers \G
|
*************************** 1. row ***************************
|
Table: tvers
|
Create Table: CREATE TABLE `tvers` (
|
`a` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> select a, row_start, row_end from tvers for system_time all;
|
+------+----------------------------+----------------------------+
|
| a | row_start | row_end |
|
+------+----------------------------+----------------------------+
|
| 1 | 2024-01-25 15:38:52.585833 | 2038-01-19 05:14:07.999999 |
|
| 20 | 2024-01-25 15:38:57.737880 | 2038-01-19 05:14:07.999999 |
|
| 3 | 2024-01-25 15:38:52.585833 | 2024-01-25 15:39:02.449904 |
|
| 2 | 2024-01-25 15:38:52.585833 | 2024-01-25 15:38:57.737880 |
|
+------+----------------------------+----------------------------+
|
4 rows in set (0.002 sec)
|
|
Upgrading to bb-11.4-timestamp 53df87b27d9e0c0e7e5f2e2e7e5aabd86aa20bee
|
$ client/mariadb-upgrade -uroot --protocol=tcp
|
...
|
test.tvers Needs upgrade with ALTER TABLE FORCE
|
...
|
and indeed, it was performed:
|
general log
|
12 Query CHECK TABLE `tvers` FOR UPGRADE
|
12 Init DB tmp
|
12 Query SHOW /*!50002 FULL*/ TABLES
|
12 Query ALTER TABLE `test`.`tvers` FORCE
|
However, the timestamps are still the same
MariaDB [test]> select a, row_start, row_end from tvers for system_time all;
|
+------+----------------------------+----------------------------+
|
| a | row_start | row_end |
|
+------+----------------------------+----------------------------+
|
| 1 | 2024-01-25 15:38:52.585833 | 2038-01-19 05:14:07.999999 |
|
| 20 | 2024-01-25 15:38:57.737880 | 2038-01-19 05:14:07.999999 |
|
| 3 | 2024-01-25 15:38:52.585833 | 2024-01-25 15:39:02.449904 |
|
| 2 | 2024-01-25 15:38:52.585833 | 2024-01-25 15:38:57.737880 |
|
+------+----------------------------+----------------------------+
|
4 rows in set (0.002 sec)
|
because if we run ALTER TABLE .. FORCE, nothing happens:
MariaDB [test]> ALTER TABLE tvers FORCE;
|
Query OK, 0 rows affected (0.060 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select a, row_start, row_end from tvers for system_time all;
|
+------+----------------------------+----------------------------+
|
| a | row_start | row_end |
|
+------+----------------------------+----------------------------+
|
| 1 | 2024-01-25 15:38:52.585833 | 2038-01-19 05:14:07.999999 |
|
| 20 | 2024-01-25 15:38:57.737880 | 2038-01-19 05:14:07.999999 |
|
| 3 | 2024-01-25 15:38:52.585833 | 2024-01-25 15:39:02.449904 |
|
| 2 | 2024-01-25 15:38:52.585833 | 2024-01-25 15:38:57.737880 |
|
+------+----------------------------+----------------------------+
|
4 rows in set (0.002 sec)
|
while the explicit ALGORITHM=COPY does the trick:
MariaDB [test]> ALTER TABLE tvers FORCE, ALGORITHM=COPY;
|
Query OK, 4 rows affected (0.050 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select a, row_start, row_end from tvers for system_time all;
|
+------+----------------------------+----------------------------+
|
| a | row_start | row_end |
|
+------+----------------------------+----------------------------+
|
| 1 | 2024-01-25 15:38:52.585833 | 2106-02-07 08:28:15.999999 |
|
| 20 | 2024-01-25 15:38:57.737880 | 2106-02-07 08:28:15.999999 |
|
| 3 | 2024-01-25 15:38:52.585833 | 2024-01-25 15:39:02.449904 |
|
| 2 | 2024-01-25 15:38:52.585833 | 2024-01-25 15:38:57.737880 |
|
+------+----------------------------+----------------------------+
|
4 rows in set (0.016 sec)
|
|