After adding new column on a SYSTEM VERSIONED Table, the column positions for row_start and rows_end are not updated.
Here the use case:
CREATE TABLE `andreversion` (
|
`id` bigint(20) unsigned NOT NULL,
|
`spend` decimal(10,2) NOT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING;
|
Query OK, 0 rows affected (0.008 sec)
|
|
|
|
MariaDB [andre]> insert into andreversion values (77,'90.5');
|
Query OK, 1 row affected (0.000 sec)
|
|
BINLOG:
|
### INSERT INTO `andre`.`andreversion`
|
### SET
|
### @1=77 /* LONGINT meta=0 nullable=0 is_null=0 */
|
### @2=90.50 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */
|
### @3=1649141510.442272 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
|
### @4=2147483647.999999 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
|
|
|
|
MariaDB [andre]> set session system_versioning_alter_history=KEEP;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [andre]> alter table andreversion add column newcol int ;
|
Query OK, 0 rows affected (0.002 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [andre]> insert into andreversion values (2,'20.5',8);
|
Query OK, 1 row affected (0.000 sec)
|
|
Binlog:
|
|
### INSERT INTO `andre`.`andreversion`
|
### SET
|
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
|
### @2=20.50 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */
|
### @3=1649141654.094756 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
|
### @4=2147483647.999999 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
|
### @5=8 /* INT meta=0 nullable=1 is_null=0 */
|
|
|
When we are going to create a Replica server from logical dump
CREATE TABLE `andreversion` (
|
`id` bigint(20) unsigned NOT NULL,
|
`spend` decimal(10,2) NOT NULL,
|
`newcol` int(11) DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING
|
|
performing new INSERT we are facing this error:
cannot be converted from type 'timestamp' to type 'int(11)'
|
Also checking the FRM file we can have the evidence that the order columns is different.
MASTER FRM: �id�spend�row_start�row_end�newcol
|
|
SLAVE FRM: �id�spend�newcol�row_start�row_end
|
- is caused by
-
MDEV-16355
Add option for mysqldump to read data as of specific timestamp from system-versioned tables
-
-
Closed
Aleksey Midenkov
made changes -
| Field |
Original Value |
New Value |
|
Assignee
|
|
Aleksey Midenkov
[ midenok
]
|
Aleksey Midenkov
made changes -
|
Description
|
Hello,
After adding new column on a SYSTEM VERSIONED Table, the column positions for row_start and rows_end are not updated.
Here the use case:
{code:java}
CREATE TABLE `andreversion` (
`id` bigint(20) unsigned NOT NULL,
`spend` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING;
Query OK, 0 rows affected (0.008 sec)
MariaDB [andre]> insert into andreversion values (77,'90.5');
Query OK, 1 row affected (0.000 sec)
BINLOG:
### INSERT INTO `andre`.`andreversion`
### SET
### @1=77 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=90.50 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */
### @3=1649141510.442272 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
### @4=2147483647.999999 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
MariaDB [andre]> set session system_versioning_alter_history=KEEP;
Query OK, 0 rows affected (0.000 sec)
MariaDB [andre]> alter table andreversion add column newcol int ;
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [andre]> insert into andreversion values (2,'20.5',8);
Query OK, 1 row affected (0.000 sec)
Binlog:
### INSERT INTO `andre`.`andreversion`
### SET
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=20.50 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */
### @3=1649141654.094756 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
### @4=2147483647.999999 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
### @5=8 /* INT meta=0 nullable=1 is_null=0 */
{code}
When we are going to create a Replica server from logical dump
{code:java}
CREATE TABLE `andreversion` (
`id` bigint(20) unsigned NOT NULL,
`spend` decimal(10,2) NOT NULL,
`newcol` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING
{code}
performing new INSERT we are facing this error:
{code:java}
cannot be converted from type 'timestamp' to type 'int(11)'
{code}
Also checking the FRM file we can have the evidence that the order columns is different.
{code:java}
MASTER FRM: �id�spend�row_start�row_end�newcol
SLAVE FRM: �id�spend�newcol�row_start�row_end
{code}
|
After adding new column on a SYSTEM VERSIONED Table, the column positions for row_start and rows_end are not updated.
Here the use case:
{code:java}
CREATE TABLE `andreversion` (
`id` bigint(20) unsigned NOT NULL,
`spend` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING;
Query OK, 0 rows affected (0.008 sec)
MariaDB [andre]> insert into andreversion values (77,'90.5');
Query OK, 1 row affected (0.000 sec)
BINLOG:
### INSERT INTO `andre`.`andreversion`
### SET
### @1=77 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=90.50 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */
### @3=1649141510.442272 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
### @4=2147483647.999999 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
MariaDB [andre]> set session system_versioning_alter_history=KEEP;
Query OK, 0 rows affected (0.000 sec)
MariaDB [andre]> alter table andreversion add column newcol int ;
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [andre]> insert into andreversion values (2,'20.5',8);
Query OK, 1 row affected (0.000 sec)
Binlog:
### INSERT INTO `andre`.`andreversion`
### SET
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=20.50 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */
### @3=1649141654.094756 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
### @4=2147483647.999999 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
### @5=8 /* INT meta=0 nullable=1 is_null=0 */
{code}
When we are going to create a Replica server from logical dump
{code:java}
CREATE TABLE `andreversion` (
`id` bigint(20) unsigned NOT NULL,
`spend` decimal(10,2) NOT NULL,
`newcol` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING
{code}
performing new INSERT we are facing this error:
{code:java}
cannot be converted from type 'timestamp' to type 'int(11)'
{code}
Also checking the FRM file we can have the evidence that the order columns is different.
{code:java}
MASTER FRM: �id�spend�row_start�row_end�newcol
SLAVE FRM: �id�spend�newcol�row_start�row_end
{code}
|
Aleksey Midenkov
made changes -
|
Component/s
|
|
Replication
[ 10100
]
|
|
Component/s
|
|
Scripts & Clients
[ 11002
]
|
Aleksey Midenkov
made changes -
|
Status
|
Open
[ 1
]
|
In Progress
[ 3
]
|
Aleksey Midenkov
made changes -
|
Summary
|
SYSTEM VERSIONED Table - Wrong position for row_start,row_end column on Binlog after adding new column
|
Wrong position for row_start, row_end after adding column to implicit versioned table
|
Aleksey Midenkov
made changes -
|
Assignee
|
Aleksey Midenkov
[ midenok
]
|
Nikita Malyavin
[ nikitamalyavin
]
|
|
Status
|
In Progress
[ 3
]
|
In Review
[ 10002
]
|
Nikita Malyavin
made changes -
|
Assignee
|
Nikita Malyavin
[ nikitamalyavin
]
|
Aleksey Midenkov
[ midenok
]
|
|
Status
|
In Review
[ 10002
]
|
Stalled
[ 10000
]
|
Aleksey Midenkov
made changes -
|
Status
|
Stalled
[ 10000
]
|
In Progress
[ 3
]
|
Aleksey Midenkov
made changes -
|
Affects Version/s
|
|
10.3
[ 22126
]
|
|
Affects Version/s
|
|
10.4
[ 22408
]
|
Aleksey Midenkov
made changes -
|
Fix Version/s
|
|
10.3.35
[ 27512
]
|
|
Fix Version/s
|
|
10.4.25
[ 27510
]
|
|
Fix Version/s
|
|
10.5.16
[ 27508
]
|
|
Fix Version/s
|
|
10.6.8
[ 27506
]
|
|
Fix Version/s
|
|
10.7.4
[ 27504
]
|
|
Fix Version/s
|
|
10.8.3
[ 27502
]
|
|
Fix Version/s
|
10.5
[ 23123
]
|
|
|
Resolution
|
|
Fixed
[ 1
]
|
|
Status
|
In Progress
[ 3
]
|
Closed
[ 6
]
|
{"report":{"fcp":1037.6000000238419,"ttfb":405.60000002384186,"pageVisibility":"visible","entityId":109639,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"21c71df8-d88f-4eb6-ab50-662b35869188","navigationType":0,"readyForUser":1123.5,"redirectCount":0,"resourceLoadedEnd":769.3000000715256,"resourceLoadedStart":411.5,"resourceTiming":[{"duration":68,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":411.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":411.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":479.5,"responseStart":0,"secureConnectionStart":0},{"duration":68.19999992847443,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":411.7000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":411.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":479.89999997615814,"responseStart":0,"secureConnectionStart":0},{"duration":235.60000002384186,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":411.89999997615814,"connectEnd":411.89999997615814,"connectStart":411.89999997615814,"domainLookupEnd":411.89999997615814,"domainLookupStart":411.89999997615814,"fetchStart":411.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":482.5,"responseEnd":647.5,"responseStart":513.3000000715256,"secureConnectionStart":411.89999997615814},{"duration":345.89999997615814,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":412.10000002384186,"connectEnd":412.10000002384186,"connectStart":412.10000002384186,"domainLookupEnd":412.10000002384186,"domainLookupStart":412.10000002384186,"fetchStart":412.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":483.89999997615814,"responseEnd":758,"responseStart":514.6000000238419,"secureConnectionStart":412.10000002384186},{"duration":106.29999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":412.3000000715256,"connectEnd":412.3000000715256,"connectStart":412.3000000715256,"domainLookupEnd":412.3000000715256,"domainLookupStart":412.3000000715256,"fetchStart":412.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":484,"responseEnd":518.6000000238419,"responseStart":515.8999999761581,"secureConnectionStart":412.3000000715256},{"duration":106.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":412.39999997615814,"connectEnd":412.39999997615814,"connectStart":412.39999997615814,"domainLookupEnd":412.39999997615814,"domainLookupStart":412.39999997615814,"fetchStart":412.39999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":484.8000000715256,"responseEnd":518.8000000715256,"responseStart":517.3000000715256,"secureConnectionStart":412.39999997615814},{"duration":109.60000002384186,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":412.5,"connectEnd":412.5,"connectStart":412.5,"domainLookupEnd":412.5,"domainLookupStart":412.5,"fetchStart":412.5,"redirectEnd":0,"redirectStart":0,"requestStart":485.89999997615814,"responseEnd":522.1000000238419,"responseStart":519.2000000476837,"secureConnectionStart":412.5},{"duration":71.79999995231628,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":412.8000000715256,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":412.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":484.60000002384186,"responseStart":0,"secureConnectionStart":0},{"duration":109.39999997615814,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":413,"connectEnd":413,"connectStart":413,"domainLookupEnd":413,"domainLookupStart":413,"fetchStart":413,"redirectEnd":0,"redirectStart":0,"requestStart":487.39999997615814,"responseEnd":522.3999999761581,"responseStart":519.7000000476837,"secureConnectionStart":413},{"duration":72.70000004768372,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":413.10000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":413.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":485.8000000715256,"responseStart":0,"secureConnectionStart":0},{"duration":110.29999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":413.3000000715256,"connectEnd":413.3000000715256,"connectStart":413.3000000715256,"domainLookupEnd":413.3000000715256,"domainLookupStart":413.3000000715256,"fetchStart":413.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":488,"responseEnd":523.6000000238419,"responseStart":520.5,"secureConnectionStart":413.3000000715256},{"duration":348.60000002384186,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":419,"connectEnd":419,"connectStart":419,"domainLookupEnd":419,"domainLookupStart":419,"fetchStart":419,"redirectEnd":0,"redirectStart":0,"requestStart":517.2000000476837,"responseEnd":767.6000000238419,"responseStart":759.1000000238419,"secureConnectionStart":419},{"duration":350.3000000715256,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":419,"connectEnd":419,"connectStart":419,"domainLookupEnd":419,"domainLookupStart":419,"fetchStart":419,"redirectEnd":0,"redirectStart":0,"requestStart":545.8000000715256,"responseEnd":769.3000000715256,"responseStart":763.2000000476837,"secureConnectionStart":419},{"duration":161.10000002384186,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":790.7000000476837,"connectEnd":790.7000000476837,"connectStart":790.7000000476837,"domainLookupEnd":790.7000000476837,"domainLookupStart":790.7000000476837,"fetchStart":790.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":917.3999999761581,"responseEnd":951.8000000715256,"responseStart":949.8999999761581,"secureConnectionStart":790.7000000476837}],"fetchStart":0,"domainLookupStart":127,"domainLookupEnd":173,"connectStart":173,"connectEnd":193,"secureConnectionStart":182,"requestStart":193,"responseStart":405,"responseEnd":414,"domLoading":408,"domInteractive":1199,"domContentLoadedEventStart":1199,"domContentLoadedEventEnd":1256,"domComplete":1864,"loadEventStart":1864,"loadEventEnd":1864,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1168.3999999761581},{"name":"bigPipe.sidebar-id.end","time":1169.1000000238419},{"name":"bigPipe.activity-panel-pipe-id.start","time":1169.3000000715256},{"name":"bigPipe.activity-panel-pipe-id.end","time":1173.7000000476837},{"name":"activityTabFullyLoaded","time":1273.2000000476837}],"measures":[],"correlationId":"c56b26f31a9b94","effectiveType":"4g","downlink":9.6,"rtt":0,"serverDuration":140,"dbReadsTimeInMs":25,"dbConnsTimeInMs":35,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Major
Please review bb-10.3-midenok