0.2 part III: DML supports `trx_id` instead of timestamps

[midenok]

TODO:

• check if it is correct to allocate from table->heap in row_insert_for_mysql();
• test with AUTOINC;
• multi-update;
• test all 'is_max()' cases;
• is it correct to use UPD_NODE_NO_ORD_CHANGE in UPDATE?
  • Yes, because it's calculated on lower level (row_upd(), requires in_mysql_interface).

[midenok]

Wrong:

create table t1 (XNo INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING;

Correct:

create table t1 (XNo INT UNSIGNED, Sys_start BIGINT UNSIGNED GENERATED ALWAYS AS ROW START, Sys_end BIGINT UNSIGNED GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING;

Wrong:

create table t1 (XNo INT UNSIGNED, Sys_start BIGINT GENERATED ALWAYS AS ROW START, Sys_end BIGINT UNSIGNED GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING;
create table t1 (XNo INT UNSIGNED, Sys_start BIGINT UNSIGNED GENERATED ALWAYS AS ROW START, Sys_end BIGINT GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING;

[midenok]

DICT_TF2_VERSIONED

Create table, add column:

#0  dict_mem_table_add_col (table=0x7fffceca6878, heap=0x7fffcec23400, name=0x7fffcec1cc19 "XNo", mtype=0x6, prtype=0x603, len=0x4) at /home/midenok/src/mariadb/midenok/src/storage/innobase/dict/dict0mem.cc:299
#1  0x0000000000bfebda in create_table_def (trx=0x7fffcec96478, form=0x7ffff7f79aa0, table_name=0x7ffff7f78ef0 "test/t1", temp_path=0x7ffff7f790f0 "", remote_path=0x7ffff7f792f0 "", flags=0x1, flags2=0xd0, mode=FIL_SPACE_ENCRYPTION_DEFAULT, key_id=0x1) at /home/midenok/src/mariadb/midenok/src/storage/innobase/handler/ha_innodb.cc:10814
#2  0x0000000000c00d11 in ha_innobase::create (this=0x7fffcecaf088, name=0x7ffff7f7bc80 "./test/t1", form=0x7ffff7f79aa0, create_info=0x7ffff7f7c270) at /home/midenok/src/mariadb/midenok/src/storage/innobase/handler/ha_innodb.cc:11958
#3  0x00000000008ee8ec in handler::ha_create (this=0x7fffcecaf088, name=0x7ffff7f7bc80 "./test/t1", form=0x7ffff7f79aa0, info_arg=0x7ffff7f7c270) at /home/midenok/src/mariadb/midenok/src/sql/handler.cc:4315
#4  0x00000000008ef825 in ha_create_table (thd=0x7fffcec16070, path=0x7ffff7f7bc80 "./test/t1", db=0x7fffcec5e8f8 "test", table_name=0x7fffcec5e2a8 "t1", create_info=0x7ffff7f7c270, frm=0x7ffff7f7bc70) at /home/midenok/src/mariadb/midenok/src/sql/handler.cc:4686
#5  0x00000000007a01de in rea_create_table (thd=0x7fffcec16070, frm=0x7ffff7f7bc70, path=0x7ffff7f7bc80 "./test/t1", db=0x7fffcec5e8f8 "test", table_name=0x7fffcec5e2a8 "t1", create_info=0x7ffff7f7c270, file=0x7fffcec5f098, no_ha_create_table=0x0) at /home/midenok/src/mariadb/midenok/src/sql/unireg.cc:474
#6  0x0000000000754878 in create_table_impl (thd=0x7fffcec16070, orig_db=0x7fffcec5e8f8 "test", orig_table_name=0x7fffcec5e2a8 "t1", db=0x7fffcec5e8f8 "test", table_name=0x7fffcec5e2a8 "t1", path=0x7ffff7f7bc80 "./test/t1", options=..., create_info=0x7ffff7f7c270, alter_info=0x7ffff7f7c1e0, create_table_mode=0x0, is_trans=0x7ffff7f7bede, key_info=0x7ffff7f7bc60, key_count=0x7ffff7f7bc54, frm=0x7ffff7f7bc70) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:4970
#7  0x0000000000754e26 in mysql_create_table_no_lock (thd=0x7fffcec16070, db=0x7fffcec5e8f8 "test", table_name=0x7fffcec5e2a8 "t1", create_info=0x7ffff7f7c270, alter_info=0x7ffff7f7c1e0, is_trans=0x7ffff7f7bede, create_table_mode=0x0) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:5087
#8  0x00000000007550f8 in mysql_create_table (thd=0x7fffcec16070, create_table=0x7fffcec5e2e0, create_info=0x7ffff7f7c270, alter_info=0x7ffff7f7c1e0) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:5151
#9  0x00000000006927f3 in mysql_execute_command (thd=0x7fffcec16070) at /home/midenok/src/mariadb/midenok/src/sql/sql_parse.cc:3853
#10 0x000000000069f395 in mysql_parse (thd=0x7fffcec16070, rawbuf=0x7fffcec5e088 "create table t1 (XNo INT UNSIGNED, Sys_start BIGINT UNSIGNED GENERATED ALWAYS AS ROW START, Sys_end BIGINT UNSIGNED GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING", length=0xd4, parser_state=0x7ffff7f7cf70, is_com_multi=0x0, is_next_command=0x0) at /home/midenok/src/mariadb/midenok/src/sql/sql_parse.cc:7792

ha_innodb.cc:

  10832                 dict_mem_table_add_col(table, heap,
  10833                         field->field_name,
  10834                         col_type,
  10835                         dtype_form_prtype(
  10836                                 (ulint) field->type()
  10837                                 | nulls_allowed | unsigned_type
  10838                                 | binary_type | long_true_varchar
  10839                                 charset_no),
  10840                         col_len);

DATA_NOT_NULL 0x100
DATA_UNSIGNED 0x200
DATA_BINARY_TYPE 0x400
DATA_LONG_TRUE_VARCHAR 0x1000
charset: from 0x10000

3 bits free: 0xE000

#define DATA_VERS_ROW_START 0x2000  /* System Versioning row start */
#define DATA_VERS_ROW_END   0x4000  /* System Versioning row end */

[midenok]

Load table:

#0  0x0000000000e33fc0 in dict_load_table (name=0x7ffff7f7bfc0 "test/t1", cached=0x1, ignore_err=DICT_ERR_IGNORE_NONE) at /home/midenok/src/mariadb/midenok/src/storage/innobase/dict/dict0load.cc:2448
#1  0x0000000000e18b4d in dict_table_open_on_name (table_name=0x7ffff7f7bfc0 "test/t1", dict_locked=0x0, try_drop=0x1, ignore_err=DICT_ERR_IGNORE_NONE) at /home/midenok/src/mariadb/midenok/src/storage/innobase/dict/dict0dict.cc:1181
#2  0x0000000000bf3876 in ha_innobase::open (this=0x7fffce079088, name=0x7fffce068568 "./test/t1", mode=0x2, test_if_locked=0x12) at /home/midenok/src/mariadb/midenok/src/storage/innobase/handler/ha_innodb.cc:5717
#3  0x00000000008e9c9f in handler::ha_open (this=0x7fffce079088, table_arg=0x7fffce099870, name=0x7fffce068568 "./test/t1", mode=0x2, test_if_locked=0x12) at /home/midenok/src/mariadb/midenok/src/sql/handler.cc:2497
#4  0x000000000078a7f9 in open_table_from_share (thd=0x7fffce0ba070, share=0x7fffce068088, alias=0x7fffce110090 "t1", db_stat=0x27, prgflag=0x2c, ha_open_flags=0x12, outparam=0x7fffce099870, is_create_table=0x0) at /home/midenok/src/mariadb/midenok/src/sql/table.cc:3028
#5  0x0000000000624c5c in open_table (thd=0x7fffce0ba070, table_list=0x7ffff7f7cf70, ot_ctx=0x7ffff7f7cc20) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.cc:2527
#6  0x0000000000627691 in open_and_process_table (thd=0x7fffce0ba070, lex=0x7fffce0bda00, tables=0x7ffff7f7cf70, counter=0x7ffff7f7ccec, flags=0x400, prelocking_strategy=0x7ffff7f7ccf0, has_prelocking_list=0x0, ot_ctx=0x7ffff7f7cc20) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.cc:4055

Load columns:

#0  dict_mem_fill_column_struct (column=0x7f9dca096a78, col_pos=0x0, mtype=0x6, prtype=0x603, col_len=0x4) at /home/midenok/src/mariadb/midenok/src/storage/innobase/dict/dict0mem.cc:531
#1  0x0000000000e377e2 in dict_mem_table_add_col (table=0x7f9dca0a6378, heap=0x7f9dca0f0000, name=0x7f9dca0f0120 "XNo", mtype=0x6, prtype=0x603, len=0x4) at /home/midenok/src/mariadb/midenok/src/storage/innobase/dict/dict0mem.cc:301
#2  0x0000000000e31790 in dict_load_column_low (table=0x7f9dca0a6378, heap=0x7f9dca0f0000, column=0x0, table_id=0x0, col_name=0x7f9df311cfd0, rec=0x7f9ddac1ca64 "") at /home/midenok/src/mariadb/midenok/src/storage/innobase/dict/dict0load.cc:1433
#3  0x0000000000e31ad7 in dict_load_columns (table=0x7f9dca0a6378, heap=0x7f9dca0f0000) at /home/midenok/src/mariadb/midenok/src/storage/innobase/dict/dict0load.cc:1495
#4  0x0000000000e3429b in dict_load_table (name=0x7f9df311dfc0 "test/t1", cached=0x1, ignore_err=DICT_ERR_IGNORE_NONE) at /home/midenok/src/mariadb/midenok/src/storage/innobase/dict/dict0load.cc:2525
#5  0x0000000000e18b4d in dict_table_open_on_name (table_name=0x7f9df311dfc0 "test/t1", dict_locked=0x0, try_drop=0x1, ignore_err=DICT_ERR_IGNORE_NONE) at /home/midenok/src/mariadb/midenok/src/storage/innobase/dict/dict0dict.cc:1181
#6  0x0000000000bf3876 in ha_innobase::open (this=0x7f9dca0af088, name=0x7f9dca096968 "./test/t1", mode=0x2, test_if_locked=0x12) at /home/midenok/src/mariadb/midenok/src/storage/innobase/handler/ha_innodb.cc:5717
#7  0x00000000008e9c9f in handler::ha_open (this=0x7f9dca0af088, table_arg=0x7f9dca0cfc70, name=0x7f9dca096968 "./test/t1", mode=0x2, test_if_locked=0x12) at /home/midenok/src/mariadb/midenok/src/sql/handler.cc:2497
#8  0x000000000078a7f9 in open_table_from_share (thd=0x7f9dca016070, share=0x7f9dca096488, alias=0x7f9dca05e090 "t1", db_stat=0x27, prgflag=0x2c, ha_open_flags=0x12, outparam=0x7f9dca0cfc70, is_create_table=0x0) at /home/midenok/src/mariadb/midenok/src/sql/table.cc:3028
#9  0x0000000000624c5c in open_table (thd=0x7f9dca016070, table_list=0x7f9df311ef70, ot_ctx=0x7f9df311ec20) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.cc:2527
#10 0x0000000000627691 in open_and_process_table (thd=0x7f9dca016070, lex=0x7f9dca019a00, tables=0x7f9df311ef70, counter=0x7f9df311ecec, flags=0x400, prelocking_strategy=0x7f9df311ecf0, has_prelocking_list=0x0, ot_ctx=0x7f9df311ec20) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.cc:4055

[avodaniel]

So will we allow only integers for versioning fields for InnoDB tables? Or will we allow timestamps and they will mean that even InnoDB table should use SQL layer implementation?

[midenok]

HLD says:

So create table statement for system versioned table will look this way:

    create table t1 (
      ...
      sys_trx_start BIGINT UNSIGNED generated always as row start,
      sys_trx_end BIGINT UNSIGNED generated always as row end,
      period for system_time (sys_trx_start, sys_trx_end)
    ) with system versioning;

[midenok]

Change primary key for System Versioning:

#0  prepare_keys_for_sys_ver (thd=0x7fffce816070, create_info=0x7ffff7f7c270, alter_info=0x7ffff7f7c1e0, key_info=0x7ffff7f7bc60, key_count=0x0) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:4427
#1  0x0000000000753dd3 in mysql_create_frm_image (thd=0x7fffce816070, db=0x7fffce85e928 "test", table_name=0x7fffce85e2d8 "t2", create_info=0x7ffff7f7c270, alter_info=0x7ffff7f7c1e0, create_table_mode=0x0, key_info=0x7ffff7f7bc60, key_count=0x7ffff7f7bc54, frm=0x7ffff7f7bc70) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:4721
#2  0x000000000075481f in create_table_impl (thd=0x7fffce816070, orig_db=0x7fffce85e928 "test", orig_table_name=0x7fffce85e2d8 "t2", db=0x7fffce85e928 "test", table_name=0x7fffce85e2d8 "t2", path=0x7ffff7f7bc80 "./test/t2", options=..., create_info=0x7ffff7f7c270, alter_info=0x7ffff7f7c1e0, create_table_mode=0x0, is_trans=0x7ffff7f7bede, key_info=0x7ffff7f7bc60, key_count=0x7ffff7f7bc54, frm=0x7ffff7f7bc70) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:4967
#3  0x0000000000754e26 in mysql_create_table_no_lock (thd=0x7fffce816070, db=0x7fffce85e928 "test", table_name=0x7fffce85e2d8 "t2", create_info=0x7ffff7f7c270, alter_info=0x7ffff7f7c1e0, is_trans=0x7ffff7f7bede, create_table_mode=0x0) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:5087
#4  0x00000000007550f8 in mysql_create_table (thd=0x7fffce816070, create_table=0x7fffce85e310, create_info=0x7ffff7f7c270, alter_info=0x7ffff7f7c1e0) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:5151
#5  0x00000000006927f3 in mysql_execute_command (thd=0x7fffce816070) at /home/midenok/src/mariadb/midenok/src/sql/sql_parse.cc:3853
#6  0x000000000069f395 in mysql_parse (thd=0x7fffce816070, rawbuf=0x7fffce85e088 "create table t2 (XNo INT UNSIGNED primary key, y int, Sys_start timestamp(6) GENERATED ALWAYS AS ROW START, Sys_end timestamp(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING engine myisam", length=0xef, parser_state=0x7ffff7f7cf70, is_com_multi=0x0, is_next_command=0x0) at /home/midenok/src/mariadb/midenok/src/sql/sql_parse.cc:7792

[midenok]

    // FIXME: REMOVE
    {
        dfield_t* d0 = dtuple_get_nth_field(node->row, 0);
        dfield_t* d1 = dtuple_get_nth_field(node->row, 1);
        dfield_t* d2 = dtuple_get_nth_field(node->row, 2);
        dfield_t* d3 = dtuple_get_nth_field(node->row, 3);
        dfield_t* d4 = dtuple_get_nth_field(node->row, 4);
        dfield_t* d5 = dtuple_get_nth_field(node->row, 5);
        int i = 0;
    }

[midenok]

Update field in clustered index on UPDATE

#0  ut_memcpy (dest=0x7fffdffa00ef, sour=0x7fffcc8c7380, n=0x8) at /home/midenok/src/mariadb/midenok/src/storage/innobase/include/ut0mem.ic:39
#1  0x0000000000d3701a in rec_set_nth_field (rec=0x7fffdffa00d8 "", offsets=0x7ffff7f7ac40, n=0x4, data=0x7fffcc8c7380, len=0x8) at /home/midenok/src/mariadb/midenok/src/storage/innobase/include/rem0rec.ic:1410
#2  0x0000000000d390a8 in row_upd_rec_in_place (rec=0x7fffdffa00d8 "", index=0x7fffd1c34af8, offsets=0x7ffff7f7ac40, update=0x7fffcc9428f0, page_zip=0x0) at /home/midenok/src/mariadb/midenok/src/storage/innobase/row/row0upd.cc:743
#3  0x0000000000db893f in btr_cur_update_in_place (flags=0x2, cursor=0x7fffcc80fb78, offsets=0x7ffff7f7ac40, update=0x7fffcc9428f0, cmpl_info=0x1, thr=0x7fffcc942ac0, trx_id=0x8b07, mtr=0x7ffff7f7af60) at /home/midenok/src/mariadb/midenok/src/storage/innobase/btr/btr0cur.cc:2116
#4  0x0000000000db8e7a in btr_cur_optimistic_update (flags=0x2, cursor=0x7fffcc80fb78, offsets=0x7ffff7f7ab58, heap=0x7ffff7f7abf8, update=0x7fffcc9428f0, cmpl_info=0x1, thr=0x7fffcc942ac0, trx_id=0x8b07, mtr=0x7ffff7f7af60) at /home/midenok/src/mariadb/midenok/src/storage/innobase/btr/btr0cur.cc:2238
#5  0x0000000000d3c666 in row_upd_clust_rec (node=0x7fffcc9427e8, index=0x7fffd1c34af8, offsets=0x7ffff7f7ac40, offsets_heap=0x7ffff7f7abf8, thr=0x7fffcc942ac0, mtr=0x7ffff7f7af60) at /home/midenok/src/mariadb/midenok/src/storage/innobase/row/row0upd.cc:2371
#6  0x0000000000d3d2a6 in row_upd_clust_step (node=0x7fffcc9427e8, thr=0x7fffcc942ac0) at /home/midenok/src/mariadb/midenok/src/storage/innobase/row/row0upd.cc:2723
#7  0x0000000000d3d519 in row_upd (node=0x7fffcc9427e8, thr=0x7fffcc942ac0) at /home/midenok/src/mariadb/midenok/src/storage/innobase/row/row0upd.cc:2813
#8  0x0000000000d3d98f in row_upd_step (thr=0x7fffcc942ac0) at /home/midenok/src/mariadb/midenok/src/storage/innobase/row/row0upd.cc:2965
#9  0x0000000000cfe6da in row_update_for_mysql (mysql_rec=0x7fffcc81c188 "\361\027", prebuilt=0x7fffcc942078) at /home/midenok/src/mariadb/midenok/src/storage/innobase/row/row0mysql.cc:1853

[midenok]

Prebuilt node is freed:

#0  mem_heap_free_func (heap=0x7f861f886400, file_name=0x1123738 "/home/midenok/src/mariadb/midenok/src/storage/innobase/que/que0que.cc", line=0x1f5) at /home/midenok/src/mariadb/midenok/src/storage/innobase/include/mem0mem.ic:486
#1  0x0000000000cb774b in que_graph_free_recursive (node=0x7f861f88daf8) at /home/midenok/src/mariadb/midenok/src/storage/innobase/que/que0que.cc:501
#2  0x0000000000cb760c in que_graph_free_recursive (node=0x7f861f88ddd0) at /home/midenok/src/mariadb/midenok/src/storage/innobase/que/que0que.cc:451
#3  0x0000000000cb757a in que_graph_free_recursive (node=0x7f861f88dd08) at /home/midenok/src/mariadb/midenok/src/storage/innobase/que/que0que.cc:430
#4  0x0000000000cfc957 in row_prebuilt_free (prebuilt=0x7f861f88d078, dict_locked=0x0) at /home/midenok/src/mariadb/midenok/src/storage/innobase/row/row0mysql.cc:906
#5  0x0000000000bf4953 in ha_innobase::close (this=0x7f861f8af088) at /home/midenok/src/mariadb/midenok/src/storage/innobase/handler/ha_innodb.cc:6135
#6  0x00000000008ea4a0 in handler::ha_close (this=0x7f861f8af088) at /home/midenok/src/mariadb/midenok/src/sql/handler.cc:2562
#7  0x000000000078ad55 in closefrm (table=0x7f861f8cfc70, free_share=0x1) at /home/midenok/src/mariadb/midenok/src/sql/table.cc:3119
#8  0x0000000000620677 in intern_close_table (table=0x7f861f8cfc70) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.cc:354
#9  0x0000000000866f81 in tc_purge (mark_flushed=0x1) at /home/midenok/src/mariadb/midenok/src/sql/table_cache.cc:233
#10 0x0000000000620a4f in close_cached_tables (thd=0x0, tables=0x0, wait_for_refresh=0x0, timeout=0x1e13380) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.cc:470
#11 0x0000000000867eeb in tdc_start_shutdown () at /home/midenok/src/mariadb/midenok/src/sql/table_cache.cc:599
#12 0x00000000005ba7bb in clean_up (print_message=0x1) at /home/midenok/src/mariadb/midenok/src/sql/mysqld.cc:2188
#13 0x00000000005ba388 in unireg_end () at /home/midenok/src/mariadb/midenok/src/sql/mysqld.cc:2068
#14 0x00000000005ba29a in kill_server (sig_ptr=0x0) at /home/midenok/src/mariadb/midenok/src/sql/mysqld.cc:1995
#15 0x00000000005ba2c4 in kill_server_thread (arg=0x7f864833ae10) at /home/midenok/src/mariadb/midenok/src/sql/mysqld.cc:2018
#16 0x00007f8646b856fa in start_thread (arg=0x7f86482d9700) at pthread_create.c:333
#17 0x00007f8646232b5d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109