mirror of
https://github.com/MariaDB/server.git
synced 2025-10-30 18:36:12 +01:00
161 lines
4.4 KiB
Text
161 lines
4.4 KiB
Text
drop table if exists t1;
|
|
#
|
|
# Test simple create of s3 table
|
|
#
|
|
create or replace table t1 (a int, b int, c varchar(1000), key (a), key(c)) engine=aria;
|
|
Warnings:
|
|
Note 1071 Specified key was too long; max key length is 2300 bytes
|
|
insert into t1 select seq, seq+10, repeat(char(65+ mod(seq, 20)),mod(seq,1000)) from seq_1_to_10000;
|
|
alter table t1 engine=s3;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` varchar(1000) DEFAULT NULL,
|
|
KEY `a` (`a`),
|
|
KEY `c` (`c`(575))
|
|
) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
|
|
select * from information_schema.tables where table_schema="database" and table_name="t1";;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY
|
|
def # t1 BASE TABLE S3 10 Page 10000 567 5677056 # 385024 0 NULL # # # utf8mb4_uca1400_ai_ci NULL page_checksum=1 2305843009213685760 #
|
|
show table status like "t1";
|
|
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 S3 10 Page 10000 567 5677056 # 385024 0 NULL # # # utf8mb4_uca1400_ai_ci NULL page_checksum=1 # N
|
|
select a,b from t1 limit 10;
|
|
a b
|
|
1 11
|
|
2 12
|
|
3 13
|
|
4 14
|
|
5 15
|
|
6 16
|
|
7 17
|
|
8 18
|
|
9 19
|
|
10 20
|
|
select count(*) from t1;
|
|
count(*)
|
|
10000
|
|
select a,b from t1 where a between 10 and 20;
|
|
a b
|
|
10 20
|
|
11 21
|
|
12 22
|
|
13 23
|
|
14 24
|
|
15 25
|
|
16 26
|
|
17 27
|
|
18 28
|
|
19 29
|
|
20 30
|
|
explain select * from t1 where a between 10 and 20;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 5 NULL # Using index condition
|
|
insert into t1 values (1,1);
|
|
ERROR HY000: Table 't1' is read only
|
|
update t1 set b=100 where a=1;
|
|
ERROR HY000: Table 't1' is read only
|
|
delete from t1 where a>10;
|
|
ERROR HY000: Table 't1' is read only
|
|
#
|
|
# Analyze, repair, optimize and check table
|
|
#
|
|
set @@use_stat_tables='never';
|
|
truncate mysql.table_stats;
|
|
check table t1 fast;
|
|
Table Op Msg_type Msg_text
|
|
database.t1 check status Table is already up to date
|
|
check table t1 quick;
|
|
Table Op Msg_type Msg_text
|
|
database.t1 check status OK
|
|
check table t1 extended;
|
|
Table Op Msg_type Msg_text
|
|
database.t1 check status OK
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
database.t1 analyze status Table 'database.t1' is read only
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
database.t1 analyze status Table 'database.t1' is read only
|
|
database.t1 analyze status Engine-independent statistics collected
|
|
database.t1 analyze status OK
|
|
repair table t1;
|
|
Table Op Msg_type Msg_text
|
|
database.t1 repair Error Table 't1' is read only
|
|
database.t1 repair status Operation failed
|
|
optimize table t1;
|
|
Table Op Msg_type Msg_text
|
|
database.t1 optimize Error Table 't1' is read only
|
|
database.t1 optimize status Operation failed
|
|
select * from mysql.table_stats;
|
|
db_name table_name cardinality
|
|
database t1 10000
|
|
#
|
|
# Converting table back to Aria
|
|
#
|
|
alter table t1 engine=aria;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` varchar(1000) DEFAULT NULL,
|
|
KEY `a` (`a`),
|
|
KEY `c` (`c`(575))
|
|
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
|
|
select a,b from t1 limit 10;
|
|
a b
|
|
1 11
|
|
2 12
|
|
3 13
|
|
4 14
|
|
5 15
|
|
6 16
|
|
7 17
|
|
8 18
|
|
9 19
|
|
10 20
|
|
select count(*) from t1;
|
|
count(*)
|
|
10000
|
|
delete from t1 where a=1;
|
|
drop table t1;
|
|
#
|
|
# status
|
|
#
|
|
show variables like "s3%";
|
|
Variable_name Value
|
|
s3_access_key X
|
|
s3_block_size X
|
|
s3_bucket X
|
|
s3_debug X
|
|
s3_host_name X
|
|
s3_no_content_type X
|
|
s3_pagecache_age_threshold X
|
|
s3_pagecache_buffer_size X
|
|
s3_pagecache_division_limit X
|
|
s3_pagecache_file_hash_size X
|
|
s3_port X
|
|
s3_protocol_version X
|
|
s3_provider X
|
|
s3_region X
|
|
s3_replicate_alter_as_create_select X
|
|
s3_secret_key X
|
|
s3_slave_ignore_updates X
|
|
s3_ssl_no_verify X
|
|
s3_use_http X
|
|
show variables like "s3_slave%";
|
|
Variable_name Value
|
|
s3_slave_ignore_updates OFF
|
|
show variables like "s3_replicate%";
|
|
Variable_name Value
|
|
s3_replicate_alter_as_create_select ON
|
|
show status like "s3%";
|
|
Variable_name Value
|
|
S3_pagecache_blocks_not_flushed X
|
|
S3_pagecache_blocks_unused X
|
|
S3_pagecache_blocks_used X
|
|
S3_pagecache_read_requests X
|
|
S3_pagecache_reads X
|