Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8, 12.1, 11.8.3
-
None
Description
Summary
SET timestamp affects ROW_START values but does not affect partition placement in PARTITION BY SYSTEM_TIME tables, leading to data inconsistency and potential issues with dump/restore operations.
Environment
- MariaDB Version: 11.8+ (WITH SYSTEM VERSIONING PARTITION BY)
- Storage Engine: InnoDB
- Feature: System Versioning Time Partitioning with AUTO PARTITIONS
Issue Description
Current Behavior
When using SET timestamp before inserting data into a table with PARTITION BY SYSTEM_TIME, the ROW_START value reflects the set timestamp, but the actual partition placement is determined by the real system time, creating an inconsistency.
Expected Behavior
SET timestamp should consistently affect both ROW_START values and partition placement decisions to maintain data consistency.
Reproduction Steps
1. Create Test Table
CREATE TABLE `test_versioning` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`data_at` datetime NOT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB
|
WITH SYSTEM VERSIONING |
PARTITION BY SYSTEM_TIME |
INTERVAL 1 YEAR STARTS TIMESTAMP'2023-01-01 00:00:00' |
AUTO PARTITIONS 3;
|
2. Insert Data with SET timestamp
-- Insert with manipulated timestamp
|
SET timestamp = UNIX_TIMESTAMP('2023-06-15 10:30:00'); |
INSERT INTO test_versioning (data_at) VALUES ('2023-06-15 10:30:00'); |
SET timestamp = DEFAULT; |
|
|
-- Insert with current timestamp (for comparison)
|
INSERT INTO test_versioning (data_at) VALUES ('2025-09-15 08:33:36'); |
3. Observe the Inconsistency
-- Check ROW_START values
|
SELECT id, data_at, ROW_START FROM test_versioning FOR SYSTEM_TIME ALL; |
|
|
-- Expected result:
|
-- +----+---------------------+----------------------------+
|
-- | id | data_at | ROW_START |
|
-- +----+---------------------+----------------------------+
|
-- | 1 | 2023-06-15 10:30:00 | 2023-06-15 10:30:00.000000 | <- SET timestamp affected
|
-- | 2 | 2025-09-15 08:33:36 | 2025-09-15 08:33:36.123456 | <- Normal timestamp
|
-- +----+---------------------+----------------------------+
|
|
|
-- Check partition placement
|
SELECT PARTITION_NAME, TABLE_ROWS |
FROM information_schema.PARTITIONS |
WHERE TABLE_NAME = 'test_versioning' AND TABLE_ROWS > 0; |
|
|
-- Actual result:
|
-- +----------------+------------+
|
-- | PARTITION_NAME | TABLE_ROWS |
|
-- +----------------+------------+
|
-- | pn | 2 | <- Both records in current partition!
|
-- +----------------+------------+
|
|
|
-- Expected result should be:
|
-- +----------------+------------+
|
-- | PARTITION_NAME | TABLE_ROWS |
|
-- +----------------+------------+
|
-- | p0 | 1 | <- 2023 data in p0
|
-- | pn | 1 | <- 2025 data in pn
|
-- +----------------+------------+ |
Impact Analysis
1. Data Consistency Issues
- ROW_START indicates 2023 timestamp but data is stored in 2025 partition
- Time-based queries may scan unexpected partitions
- Partition pruning effectiveness is reduced
2. Dump/Restore Problems
When performing mariadb-dump and mariadb restore:
-- During restore, INSERT statements will be executed at restore time
|
-- causing all historical data to be placed in current partitions
|
-- regardless of original ROW_START values |
Example scenario:
- Original table has data distributed across p0-p2 partitions (2023-2025)
- Dump the table: mariadb-dump --single-transaction test test_versioning > backup.sql
- Restore to new server: mariadb test < backup.sql
- Result: All data ends up in current partition, losing original partition distribution
3. ETL and Data Migration Issues
- ETL processes that handle historical data cannot be placed in appropriate partitions
- Idempotent operations become inconsistent
- Time-based archiving strategies become unreliable
Attachments
Issue Links
- relates to
-
-
- Confirmed
-
-
-
- Confirmed
-