pt-archiver unexpected handling of timestamp default current_timestamp column
General
Escalation
General
Escalation
Description
Environment
percona-toolkit 3.5.0-5.buster
10.4.27-MariaDB-1:10.4.27+maria~deb10-log
percona-toolkit 3.3.1-1.stretch
10.3.37-MariaDB-1:10.3.37+maria~deb10-log
Activity
Show:
Aaditya Dubey January 25, 2023 at 12:15 PMEdited
Hi @Daniel Gavrila,
Thank you for the report.
Verified as described:
$ ./pt-archiver --version
pt-archiver 3.5.1
$ ./pt-archiver --source h=localhost,u=xxxx,p=xxxx,D=test,t=tt --socket=/tmp/mysql_sandbox8028.sock --dest D=test,t=tt2 --where "1=1" --limit 10 --sleep 1 --commit-each --primary-key-only --progress 10000 --no-delete --no-check-charset
TIME ELAPSED COUNT
2023-01-25T17:42:59 0 0
2023-01-25T17:42:59 0 6
mysql [localhost:8028] {msandbox} (test) > select * from tt;
+----+---------------------+
| id | created |
+----+---------------------+
| 5 | 2023-01-25 17:36:18 |
| 6 | 2023-01-25 17:36:19 |
| 7 | 2023-01-25 17:36:19 |
| 8 | 2023-01-25 17:36:20 |
| 9 | 2023-01-25 17:36:21 |
| 10 | 2023-01-25 17:36:21 |
| 11 | 2023-01-25 17:36:21 |
+----+---------------------+
7 rows in set (0.00 sec)
mysql [localhost:8028] {msandbox} (test) > select * from tt2;
+----+---------------------+
| id | created |
+----+---------------------+
| 5 | 2023-01-25 17:42:59 |
| 6 | 2023-01-25 17:42:59 |
| 7 | 2023-01-25 17:42:59 |
| 8 | 2023-01-25 17:42:59 |
| 9 | 2023-01-25 17:42:59 |
| 10 | 2023-01-25 17:42:59 |
+----+---------------------+
6 rows in set (0.00 sec)sending the concern to engineering for further review and updates.
Details
Assignee
UnassignedUnassignedReporter
Daniel GavrilaDaniel GavrilaPriority
MediumAffects versions
Needs QA
Yes
Details
Details
Assignee
Unassigned
UnassignedReporter
Daniel Gavrila
Daniel GavrilaPriority
MediumAffects versions
Needs QA
Yes
Smart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Created January 3, 2023 at 8:32 AM
Updated February 29, 2024 at 8:42 PM
I have the following table definition:
MariaDB [audit]> show create table tt; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tt | CREATE TABLE `tt` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `created` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec)Created a second table tt2 like tt:
MariaDB [audit]> create table tt2 like tt;MariaDB [audit]> show create table tt2; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tt2 | CREATE TABLE `tt2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `created` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec)When running pt-archiver to sync tt->tt2:
pt-archiver -u root -p*** --source h=127.0.0.1,D=audit,t=tt --dest h=127.0.0.1,D=audit,t=tt2 --where "1=1" --limit 10 --sleep 1 --commit-each --primary-key-only --progress 10000 --no-deleteThe `created` values from table `tt` do not replicate to table `tt2`, instead these get overwritten with new default current_timestamp values hence losing the original value.