1
drop table if exists t1;
2
set @@session.max_heap_table_size=16*1024*1024;
3
create temporary table t1 (a int not null, b varchar(400), c int, primary key (a), key (c)) engine=MEMORY comment="testing heaps" block_size=128;
4
ERROR 42000: Incorrect usage/placement of 'block_size'
5
create temporary table t1 (a int not null, b int, c varchar(400), primary key (a), key (b)) engine=MEMORY comment="testing heaps" block_size=4;
6
ERROR 42000: Incorrect usage/placement of 'block_size'
7
create temporary table t1 (a int not null, b int, c varchar(400), d varchar(400), primary key (a), key (b)) engine=MEMORY comment="testing heaps" block_size=24;
8
show table status like "t1";
9
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
10
# test t1 TEMPORARY MEMORY # # # # #
11
insert into t1 values (1,1,'012',NULL), (2,2,'0123456789',NULL), (3,3,'012345678901234567890123456789',NULL), (4,4,NULL,'0123456789012345678901234567890123456789012345678901234567890123456789');
16
3 3 012345678901234567890123456789 NULL
17
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
18
delete from t1 where a = 3;
23
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
24
insert into t1 values (5,5,NULL,'0123'), (6,6,NULL,'0123');
31
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
32
update t1 set c = '012345678901234567890123456789' where a = 2;
36
2 2 012345678901234567890123456789 NULL
39
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
40
update t1 set c = '0123456789' where a = 2;
47
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
48
insert into t1 values (7,7,'0123',NULL), (8,8,'0123',NULL);
55
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
58
show table status like "t1";
59
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
60
# test t1 TEMPORARY MEMORY # # # # #
61
alter table t1 block_size = 0;
62
show table status like "t1";
63
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
64
# test t1 TEMPORARY MEMORY # # # # #
65
alter table t1 row_format = dynamic;
66
show table status like "t1";
67
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
68
# test t1 TEMPORARY MEMORY # # # # #
69
alter table t1 block_size = 128;
70
show table status like "t1";
71
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
72
# test t1 TEMPORARY MEMORY # # # # #
79
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
85
select count(*) from t1;
88
insert into t1 values (100000,100000,NULL,'0123'), (100000,100000,NULL,'0123');
89
ERROR 23000: Duplicate entry '100000' for key 'PRIMARY'
90
show table status like "t1";
91
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
92
# test t1 TEMPORARY MEMORY # # # # #
93
select count(*) from t1;
96
set @@session.max_heap_table_size=default;