1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
DROP TABLE IF EXISTS t1, t2, t3;
CREATE TABLE t1 (a INT, b CHAR(8) PRIMARY KEY) ENGINE=rocksdb;
INSERT INTO t1 (a,b) VALUES (100,'a'),(2,'foo');
CREATE TABLE t2 (a INT PRIMARY KEY, b CHAR(8)) ENGINE=rocksdb;
INSERT INTO t2 (a,b) VALUES (1,'bar');
set global rocksdb_force_flush_memtable_now = true;
CREATE TABLE t3 (a INT, b CHAR(8), pk INT PRIMARY KEY) ENGINE=rocksdb CHARACTER SET utf8;
SHOW TABLE STATUS WHERE name IN ( 't1', 't2', 't3' );
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
t1 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL # # NULL latin1_swedish_ci NULL 0 N
t2 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL # # NULL latin1_swedish_ci NULL 0 N
t3 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL # # NULL utf8_general_ci NULL 0 N
SHOW TABLE STATUS WHERE name LIKE 't2';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
t2 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL # # NULL latin1_swedish_ci NULL 0 N
DROP TABLE t1, t2, t3;
CREATE DATABASE `db_new..............................................end`;
USE `db_new..............................................end`;
CREATE TABLE `t1_new..............................................end`(a int) engine=rocksdb;
INSERT INTO `t1_new..............................................end` VALUES (1);
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.table_statistics WHERE TABLE_NAME = 't1_new..............................................end';
TABLE_SCHEMA db_new..............................................end
TABLE_NAME t1_new..............................................end
DROP DATABASE `db_new..............................................end`;
#
# MDEV-17171: Bug: RocksDB Tables do not have "Creation Date"
#
use test;
create table t1 (a int) engine=rocksdb;
select create_time is not null, update_time, check_time
from information_schema.tables where table_schema=database() and table_name='t1';
create_time is not null update_time check_time
1 NULL NULL
insert into t1 values (1);
select create_time is not null, update_time is not null, check_time
from information_schema.tables where table_schema=database() and table_name='t1';
create_time is not null update_time is not null check_time
1 1 NULL
flush tables;
select create_time is not null, update_time is not null, check_time
from information_schema.tables where table_schema=database() and table_name='t1';
create_time is not null update_time is not null check_time
1 1 NULL
select create_time, update_time into @create_tm, @update_tm
from information_schema.tables
where table_schema=database() and table_name='t1';
select sleep(3);
sleep(3)
0
insert into t1 values (2);
select
create_time=@create_tm /* should not change */ ,
timestampdiff(second, @update_tm, update_time) > 2,
check_time
from information_schema.tables
where table_schema=database() and table_name='t1';
create_time=@create_tm 1
timestampdiff(second, @update_tm, update_time) > 2 1
check_time NULL
#
# Check how create_time survives ALTER TABLE.
# First, an ALTER TABLE that re-creates the table:
alter table t1 add b int;
select
create_time<>@create_tm /* should change */,
create_time IS NOT NULL,
update_time IS NULL
from information_schema.tables
where table_schema=database() and table_name='t1';
create_time<>@create_tm 1
create_time IS NOT NULL 1
update_time IS NULL 1
insert into t1 values (5,5);
select create_time, update_time into @create_tm, @update_tm
from information_schema.tables
where table_schema=database() and table_name='t1';
# Then, an in-place ALTER TABLE:
alter table t1 add key (a);
select
create_time=@create_tm /* should not change */,
update_time
from information_schema.tables
where table_schema=database() and table_name='t1';
create_time=@create_tm 1
update_time NULL
#
# Check what is left after server restart
#
# Save t1's creation time
create table t2 as
select create_time
from information_schema.tables
where table_schema=database() and table_name='t1';
select
create_time=(select create_time from t2) /* should change */,
update_time
from information_schema.tables
where table_schema=database() and table_name='t1';
create_time=(select create_time from t2) 1
update_time NULL
drop table t1, t2;
#
# Check how it works for partitioned tables
#
create table t1 (pk int primary key) partition by hash(pk) partitions 2;
insert into t1 values (1);
select create_time IS NOT NULL , update_time IS NOT NULL
from information_schema.tables
where table_schema=database() and table_name='t1';
create_time IS NOT NULL 1
update_time IS NOT NULL 1
drop table t1;
|
