Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15969

System versioning and FEDERATED don't work well together: DML and discovery fail

Details

    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
      

      Attachments

        Issue Links

          Activity

            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.
            serg Sergei Golubchik added a comment - 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.
            midenok Aleksey Midenkov added a comment - - edited

            Analysis.

            UPDATE and DELETE don't work too.

            midenok Aleksey Midenkov added a comment - - edited Analysis . UPDATE and DELETE don't work too.

            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?

            serg Sergei Golubchik added a comment - 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?
            midenok Aleksey Midenkov added a comment - - edited

            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.

            midenok Aleksey Midenkov added a comment - - edited 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.
            serg Sergei Golubchik added a comment - - edited

            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.

            serg Sergei Golubchik added a comment - - edited 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.

            People

              midenok Aleksey Midenkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.