Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
Description
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) |
Attachments
Issue Links
- is caused by
-
MDEV-32188 make TIMESTAMP use whole 32-bit unsigned range
-
- In Testing
-