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

Limitations which the row end as a part of PK imposes due to CURRENT_TIMESTAMP behavior and otherwise

Details

    Description

      Note: Possibly it's the matter of documentation, but before we proceed with documenting it, it needs to be confirmed as intended behavior.

      CURRENT_TIMESTAMP freezes its value upon the beginning of the statement/function/trigger execution. Given that the row end column is added automatically to the PK, it means that certain DML statements that work with non-versioned tables will become impossible with versioning. Here is basic example for a single statement, it can of course become more elaborate and less degenerate within triggers or functions.

      MariaDB [test]> create table t1 (pk int primary key, i int) with system versioning;
      		 
      Query OK, 0 rows affected (0.15 sec)
      		 
       
      		 
      MariaDB [test]> replace into t1 values (1,10),(1,100),(1,1000);
      		 
      ERROR 1062 (23000): Duplicate entry '1-2038-01-19 05:14:07.999999' for key 'PRIMARY'

      That is, everything that removes a row which is represented by the non-"row end" part of PK more than once within a statement/function/trigger will fail.

      Although, maybe in the case above the reason is actually different, not related to CURRENT_TIMESTAMP behavior as such, since it claims that it can't insert 2038-01-19 05:14:07.999999, which is just wrong. But even if it goes as expected, it will still attempt to insert (1,<current timestamp>) twice and fail.

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14829 Assertion `0' failed in Protocol::end_statement upon concurrent UPDATE

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Here is another kind of limitation which enforced addition of row end to the PK causes, also quite unexpected and without any obvious workaround:

            CREATE OR REPLACE TABLE t1 (
            		 
              `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
            		 
              `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
            		 
              `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
            		 
              `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
            		 
              `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
            		 
              `Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
            		 
              `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
            		 
              PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
            		 
            ) ENGINE=MyISAM;

            Adding system versioning to the table causes the error:

            MariaDB [test]> ALTER TABLE t1 ADD SYSTEM VERSIONING;
            		 
            ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

            Same happens if versioning is added explicitly, through creating a table and a period, because even in this case row end is added to the PK, which is even stranger.

            elenst Elena Stepanova added a comment - Here is another kind of limitation which enforced addition of row end to the PK causes, also quite unexpected and without any obvious workaround: CREATE OR REPLACE TABLE t1 ( `Host` char (60) COLLATE utf8_bin NOT NULL DEFAULT '' , `Db` char (64) COLLATE utf8_bin NOT NULL DEFAULT '' , ` User ` char (80) COLLATE utf8_bin NOT NULL DEFAULT '' , `Table_name` char (64) COLLATE utf8_bin NOT NULL DEFAULT '' , `Column_name` char (64) COLLATE utf8_bin NOT NULL DEFAULT '' , ` Timestamp ` timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), `Column_priv` set ( 'Select' , 'Insert' , 'Update' , 'References' ) CHARACTER SET utf8 NOT NULL DEFAULT '' , PRIMARY KEY (`Host`,`Db`,` User `,`Table_name`,`Column_name`) ) ENGINE=MyISAM; Adding system versioning to the table causes the error: MariaDB [test]> ALTER TABLE t1 ADD SYSTEM VERSIONING; ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes Same happens if versioning is added explicitly, through creating a table and a period, because even in this case row end is added to the PK, which is even stranger.
            serg Sergei Golubchik added a comment -

            The first case (with REPLACE) is a bug, should be fixed. The second, with "key was too long" — that's a limitation, nothing we can do...

            serg Sergei Golubchik added a comment - The first case (with REPLACE) is a bug, should be fixed. The second, with "key was too long" — that's a limitation, nothing we can do...
            kevg Eugene Kosov (Inactive) added a comment -

            In `tt-1.3.5` it works like this:

            create table t1 (pk int primary key, i int) with system versioning;
            		 
            replace into t1 values (1,10),(1,100),(1,1000);
            		 
            select *, row_start, row_end from t1 for system_time all;
            		 
            pk	i	row_start	row_end
            		 
            1	1000	2018-02-14 14:22:04.358922	2038-01-19 06:14:07.999999

            Relevant commit is https://github.com/tempesta-tech/mariadb/commit/d95c74d8280f93b73995935a5dcf2359ad375daa

            kevg Eugene Kosov (Inactive) added a comment - In `tt-1.3.5` it works like this: create table t1 (pk int primary key , i int ) with system versioning; replace into t1 values (1,10),(1,100),(1,1000); select *, row_start, row_end from t1 for system_time all ; pk i row_start row_end 1 1000 2018-02-14 14:22:04.358922 2038-01-19 06:14:07.999999 Relevant commit is https://github.com/tempesta-tech/mariadb/commit/d95c74d8280f93b73995935a5dcf2359ad375daa

            People

              kevg Eugene Kosov (Inactive)
              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.