mirror of
https://github.com/MariaDB/server.git
synced 2025-01-27 17:33:44 +01:00
d5e15424d8
The MDEV-29693 conflict resolution is from Monty, as well as is
a bug fix where ANALYZE TABLE wrongly built histograms for
single-column PRIMARY KEY.
Also includes a fix for safe_malloc error reporting.
Other things:
- Copied main.log_slow from 10.4 to avoid mtr issue
Disabled test:
- spider/bugfix.mdev_27239 because we started to get
+Error 1429 Unable to connect to foreign data source: localhost
-Error 1158 Got an error reading communication packets
- main.delayed
- Bug#54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
This part is disabled for now as it fails randomly with different
warnings/errors (no corruption).
178 lines
5.1 KiB
Text
178 lines
5.1 KiB
Text
#
|
|
# Check that ANALYZE TABLE is remembered by MyISAM and Aria
|
|
#
|
|
create table t1 (a int) engine=myisam;
|
|
insert into t1 select seq from seq_0_to_99;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
flush tables;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
update t1 set a=100 where a=1;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
update t1 set a=100 where a=2;
|
|
flush tables;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
# Aria transactional=0
|
|
ALTER TABLE t1 ENGINE=aria transactional=0;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
update t1 set a=100 where a=10;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
flush tables;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
update t1 set a=100 where a=11;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
update t1 set a=100 where a=12;
|
|
flush tables;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
# Aria transactional=1
|
|
ALTER TABLE t1 ENGINE=aria transactional=1;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
update t1 set a=100 where a=20;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
flush tables;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
update t1 set a=100 where a=21;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
update t1 set a=100 where a=22;
|
|
flush tables;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
drop table t1;
|
|
#
|
|
# Test that histograms are read after flush
|
|
#
|
|
create table t1 (a int);
|
|
insert into t1 select seq from seq_1_to_10;
|
|
insert into t1 select A.seq from seq_10_to_20 A, seq_1_to_9 B;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
explain format=json select * from t1 where a between 2 and 5;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 109,
|
|
"filtered": 3.669724703,
|
|
"attached_condition": "t1.a between 2 and 5"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
explain format=json select * from t1 where a between 12 and 15;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 109,
|
|
"filtered": 33.02752304,
|
|
"attached_condition": "t1.a between 12 and 15"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
flush tables;
|
|
set @@optimizer_use_condition_selectivity=3;
|
|
explain format=json select * from t1 where a between 2 and 5;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 109,
|
|
"filtered": 15.78947353,
|
|
"attached_condition": "t1.a between 2 and 5"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
set @@optimizer_use_condition_selectivity=4;
|
|
explain format=json select * from t1 where a between 2 and 5;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 109,
|
|
"filtered": 3.669724703,
|
|
"attached_condition": "t1.a between 2 and 5"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
drop table t1;
|
|
set @@optimizer_use_condition_selectivity=default;
|
|
#
|
|
# End of 10.6 tests
|
|
#
|