[MDEV-15969] System versioning and FEDERATED don't work well together: DML and discovery fail Created: 2018-04-22  Updated: 2022-10-19  Resolved: 2018-05-17

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Federated, Versioned Tables
Affects Version/s: 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-16157 federated corrupts timestamps Closed
Relates
relates to MDEV-29829 System versioning does not work with ... Open

 Description   

As analysis for MDEV-14789 says, there were two ways to handle it – either disable the combination, or fix it. What happened at the end is that SELECT works, but neither table discovery nor data modification do. So, all in all, probably it makes sense to disable it.

Discovery:

create or replace table t3 (a int) with system versioning;
 
create or replace table t3f engine=Federated CONNECTION='mysql://root@127.0.0.1:3306/test/t3' ;

10.3 c39f8a80c9fe7

 
MariaDB [test]> create or replace table t3f engine=Federated CONNECTION='mysql://root@127.0.0.1:3306/test/t3' ;
 
ERROR 1939 (HY000): Engine FEDERATED failed to discover table `test`.`t3f` with 'CREATE TABLE `t3` (
 
  `a` int(11) DEFAULT NULL
 
) WITH SYSTEM VERSIONING CONNECTION='mysql://root@127.0.0.1:3306/test/t3''

DML:

create or replace table t3f (a int) engine=Federated CONNECTION='mysql://root@127.0.0.1:3306/test/t3' with system versioning;
 
insert into t3f (a) values (1);


MariaDB [test]> insert into t3f (a) values (1);
 
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1054: Unknown column 'row_start' in 'field list'' from FEDERATED



 Comments   
Comment by Sergei Golubchik [ 2018-04-22 ]

There are two issues here:

  • the server doesn't allow to discover WITH SYSTEM VERSIONING clause. So FederatedX should strip it away before doing discovery, but it apparently doesn't. That's why DDL fails.
  • row_start is invisible. Apparently FederatedX doesn't handle it correctly, it shouldn't add it to the the insert list, but it does.
Comment by Aleksey Midenkov [ 2018-04-27 ]

Analysis.

UPDATE and DELETE don't work too.

Comment by Sergei Golubchik [ 2018-04-27 ]

Forget invisible fields for now, it's a separate issue. Let's say a table is created with all fields specified explicitly:

create table t1 (
 
  a int,
 
  rs timestamp(6) as row start,
 
  re timestamp(6) as row end,
 
  period for system_time (rs,re)
 
) with system versioning;

What works and what doesn't work with this table?

Comment by Aleksey Midenkov [ 2018-04-27 ]

The issue is not with invisible fields (that's not a big deal). UPDATE and DELETE don't work correctly because versioning is applied twice: on client and server side (see my last comment). If we are talking about support for federated tables everything in DDL and DML must be tested as well (I guess nothing will work for free).

SELECT doesn't work because SYSTEM_TIME clause as well as on_expr conditions are not passed from client to server.

Comment by Sergei Golubchik [ 2018-04-30 ]

I don't quite understand. Why would be versioning applied twice? TABLE_SHARE::init_from_sql_statement_string does not allow WITH SYSTEM VERSIONING, so first federated should not put it into CREATE TABLE otherwise discovery won't even work. And when federated won't do that, there won't be any double-versioning.

Generated at Thu Feb 08 08:25:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.