mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
3bd23b76c5
For InnoDB tables, parse the CREATE TABLE statement to defer index and constraint creation until after data loading. For other storage engines, the DISABLE/ENABLE KEYS commands achieve similar optimization. This behavior is controlled by a new option, innodb-optimize-keys (default: ON), compatible with mydumper. Additionally, this commit separates the table creation phase from data loading. Running DDL statements (such as DROP IF EXISTS) in a single thread avoids the "table not locked" issue from MDEV-34741. As a bonus, view creation no longer requires a separate step.
227 lines
7.6 KiB
Text
227 lines
7.6 KiB
Text
--source include/not_embedded.inc
|
|
--source include/have_innodb.inc
|
|
|
|
# Basic test case for --table (restore single table)
|
|
create table t1(i int);
|
|
insert t1 values(100);
|
|
create view v1 as select 1;
|
|
|
|
--mkdir $MYSQLTEST_VARDIR/tmp/dump
|
|
--exec $MYSQL_DUMP --dir=$MYSQLTEST_VARDIR/tmp/dump test
|
|
drop table t1;
|
|
--exec $MYSQL_IMPORT --table=test.t1 --dir=$MYSQLTEST_VARDIR/tmp/dump
|
|
select * from t1;
|
|
--rmdir $MYSQLTEST_VARDIR/tmp/dump
|
|
|
|
# Test cases for --dir
|
|
# test --all-databases
|
|
--mkdir $MYSQLTEST_VARDIR/tmp/dump
|
|
--exec $MYSQL_DUMP --dir=$MYSQLTEST_VARDIR/tmp/dump --all-databases
|
|
--echo # Content of dump directory
|
|
--list_files $MYSQLTEST_VARDIR/tmp/dump
|
|
--echo # Content of 'test' dump subdirectory
|
|
--list_files $MYSQLTEST_VARDIR/tmp/dump/test
|
|
--echo # Content of 'mysql' dump subdirectory
|
|
--list_files $MYSQLTEST_VARDIR/tmp/dump/mysql
|
|
--echo # Content of 'mtr' dump subdirectory
|
|
--list_files $MYSQLTEST_VARDIR/tmp/dump/mtr
|
|
|
|
# Test --dir
|
|
--replace_result $MYSQLTEST_VARDIR vardir
|
|
# Ignore mtr.test_suppressions (may have suppressions or not), mysql.proc is smaller without perfschema/sys schema
|
|
--exec $MYSQL_IMPORT --local --verbose --dir $MYSQLTEST_VARDIR/tmp/dump --ignore-table=mtr.test_suppressions --ignore-table=mysql.proc
|
|
|
|
drop table t1;
|
|
drop view v1;
|
|
--rmdir $MYSQLTEST_VARDIR/tmp/dump
|
|
|
|
# Test foreign keys
|
|
create database db2;
|
|
use db2;
|
|
CREATE TABLE parent (
|
|
id INT NOT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=INNODB;
|
|
CREATE TABLE child (
|
|
id INT,
|
|
parent_id INT,
|
|
c CHAR(4),
|
|
INDEX par_ind (parent_id),
|
|
UNIQUE INDEX(c),
|
|
FOREIGN KEY (parent_id)
|
|
REFERENCES parent(id)
|
|
ON DELETE CASCADE,
|
|
CHECK (c >= 'a')
|
|
) ENGINE=INNODB;
|
|
insert into parent values(1),(2);
|
|
insert into child values (1,1,'a'),(1,2,'b'),(2,1,'c'),(2,2,'d');
|
|
|
|
# Example from https://github.com/mydumper/mydumper/issues/395 (can't repeat now)
|
|
CREATE TABLE offices (
|
|
id int NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE users (
|
|
id int NOT NULL AUTO_INCREMENT,
|
|
office_id int DEFAULT NULL,
|
|
slogan text GENERATED ALWAYS AS (concat('Hello world #',office_id)) STORED,
|
|
PRIMARY KEY (id),
|
|
KEY office_id (office_id),
|
|
CONSTRAINT users_ibfk_1 FOREIGN KEY (office_id) REFERENCES offices (id)
|
|
) ENGINE=InnoDB;
|
|
|
|
insert into offices values();
|
|
insert into offices values();
|
|
insert into offices values();
|
|
insert into offices values();
|
|
|
|
insert into users (office_id) values (1);
|
|
insert into users (office_id) values (2);
|
|
insert into users (office_id) values (3);
|
|
|
|
--mkdir $MYSQLTEST_VARDIR/tmp/dump
|
|
--exec $MYSQL_DUMP --dir=$MYSQLTEST_VARDIR/tmp/dump --all-databases
|
|
|
|
drop database db2;
|
|
--replace_result $MYSQLTEST_VARDIR vardir
|
|
--exec $MYSQL_IMPORT --local --silent --dir $MYSQLTEST_VARDIR/tmp/dump --database=db2 --parallel=2
|
|
use db2;
|
|
select * from parent;
|
|
select * from child;
|
|
show create table parent;
|
|
show create table child;
|
|
drop database db2;
|
|
|
|
--echo # Repeat import with --verbose to see "Adding secondary keys" in the output
|
|
--replace_result $MYSQLTEST_VARDIR vardir
|
|
--exec $MYSQL_IMPORT --verbose --dir $MYSQLTEST_VARDIR/tmp/dump --database=db2
|
|
|
|
--echo # Repeat import with --verbose and --innodb-optimize-indexes=0, to "not" see "Adding secondary indexes"
|
|
--replace_result $MYSQLTEST_VARDIR vardir
|
|
--exec $MYSQL_IMPORT --verbose --dir $MYSQLTEST_VARDIR/tmp/dump --database=db2 --innodb-optimize-keys=0
|
|
--rmdir $MYSQLTEST_VARDIR/tmp/dump
|
|
|
|
drop database db2;
|
|
create database db2;
|
|
use db2;
|
|
|
|
# Test with vector/fulltext/spatial indexes
|
|
create table vec (id int auto_increment primary key, v vector(5) not null,
|
|
vector index (v)) ENGINE=InnoDB;
|
|
insert vec(v) values (x'e360d63ebe554f3fcdbc523f4522193f5236083d'),
|
|
(x'f511303f72224a3fdd05fe3eb22a133ffae86a3f'),
|
|
(x'f09baa3ea172763f123def3e0c7fe53e288bf33e'),
|
|
(x'b97a523f2a193e3eb4f62e3f2d23583e9dd60d3f'),
|
|
(x'f7c5df3e984b2b3e65e59d3d7376db3eac63773e'),
|
|
(x'de01453ffa486d3f10aa4d3fdd66813c71cb163f'),
|
|
(x'76edfc3e4b57243f10f8423fb158713f020bda3e'),
|
|
(x'56926c3fdf098d3e2c8c5e3d1ad4953daa9d0b3e'),
|
|
(x'7b713f3e5258323f80d1113d673b2b3f66e3583f'),
|
|
(x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
|
|
create table ft(v text, fulltext(v)) ENGINE=InnoDB;
|
|
insert into ft(v) values ('Once upon a time'),
|
|
('There was a wicked witch'), ('Who ate everybody up');
|
|
create table locations (id int auto_increment primary key, geom geometry NOT NULL) ENGINE=InnoDB;
|
|
create spatial index idx_geom on locations (geom);
|
|
insert into locations (geom) values (ST_GeomFromText('POINT(40.785091 -73.968285)'));
|
|
--mkdir $MYSQLTEST_VARDIR/tmp/dump
|
|
--exec $MYSQL_DUMP --dir=$MYSQLTEST_VARDIR/tmp/dump db2
|
|
--echo # use --verbose to see "Adding secondary indexes" in the output
|
|
--replace_result $MYSQLTEST_VARDIR vardir
|
|
--exec $MYSQL_IMPORT --verbose --dir $MYSQLTEST_VARDIR/tmp/dump --database=db2
|
|
|
|
# smoke-test restored tables
|
|
show index from vec;
|
|
show index from locations;
|
|
show index from ft;
|
|
|
|
--replace_regex /(\.\d{5})\d+/\1/
|
|
select id,vec_distance_euclidean(v, x'B047263c9f87233fcfd27e3eae493e3f0329f43e') d from vec order by d limit 3;
|
|
select * from ft where match(v) against('wicked');
|
|
|
|
--rmdir $MYSQLTEST_VARDIR/tmp/dump
|
|
|
|
drop database db2;
|
|
create database db2;
|
|
use db2;
|
|
|
|
# Test with triggers (using https://mariadb.com/kb/en/trigger-overview/ example)
|
|
CREATE TABLE animals (id mediumint(9)
|
|
NOT NULL AUTO_INCREMENT,
|
|
name char(30) NOT NULL,
|
|
PRIMARY KEY (`id`));
|
|
|
|
CREATE TABLE animal_count (animals int);
|
|
INSERT INTO animal_count (animals) VALUES(0);
|
|
|
|
CREATE TRIGGER increment_animal
|
|
AFTER INSERT ON animals
|
|
FOR EACH ROW
|
|
UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
|
|
|
|
INSERT INTO animals (name) VALUES('aardvark');
|
|
INSERT INTO animals (name) VALUES('baboon');
|
|
|
|
|
|
--echo # Content of tables before backup
|
|
select * from animals;
|
|
select * from animal_count;
|
|
|
|
--mkdir $MYSQLTEST_VARDIR/tmp/dump
|
|
--exec $MYSQL_DUMP --dir=$MYSQLTEST_VARDIR/tmp/dump db2
|
|
use test;
|
|
drop database db2;
|
|
--replace_result $MYSQLTEST_VARDIR vardir
|
|
--exec $MYSQL_IMPORT --local --verbose --dir $MYSQLTEST_VARDIR/tmp/dump
|
|
use db2;
|
|
--echo # Content of tables after import
|
|
select * from animals;
|
|
select * from animal_count;
|
|
drop table animals;
|
|
drop table animal_count;
|
|
|
|
# Test VIEW
|
|
create table t1 as select 1 as val;
|
|
create view a1 as select * from t1;
|
|
--rmdir $MYSQLTEST_VARDIR/tmp/dump
|
|
--mkdir $MYSQLTEST_VARDIR/tmp/dump
|
|
--exec $MYSQL_DUMP --dir=$MYSQLTEST_VARDIR/tmp/dump db2
|
|
use test;
|
|
drop database db2;
|
|
--replace_result $MYSQLTEST_VARDIR vardir
|
|
--exec $MYSQL_IMPORT --local --verbose --dir $MYSQLTEST_VARDIR/tmp/dump
|
|
use db2;
|
|
select * from t1;
|
|
select * from a1;
|
|
drop database db2;
|
|
--rmdir $MYSQLTEST_VARDIR/tmp/dump
|
|
use test;
|
|
|
|
# Test --ignore-database
|
|
# Do full backup, drop one db, restore with --ignore-database=db
|
|
# Check that database does not exist anymore
|
|
create database db;
|
|
use db;
|
|
create table t1 as select 1 as val;
|
|
--mkdir $MYSQLTEST_VARDIR/tmp/dump
|
|
--exec $MYSQL_DUMP --dir=$MYSQLTEST_VARDIR/tmp/dump --all-databases
|
|
use test;
|
|
drop database db;
|
|
--replace_result $MYSQLTEST_VARDIR vardir
|
|
--exec $MYSQL_IMPORT --local --silent --dir $MYSQLTEST_VARDIR/tmp/dump --ignore-database=db
|
|
--error ER_BAD_DB_ERROR
|
|
use db;
|
|
use test;
|
|
|
|
--echo # Test non-existing --dir
|
|
--replace_result mariadb-import.exe mariadb-import $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
--error 1
|
|
--exec $MYSQL_IMPORT --dir $MYSQLTEST_VARDIR/tmp/non_existing 2>&1
|
|
|
|
--echo # Test too many threads, builtin limit 256
|
|
--replace_result mariadb-import.exe mariadb-import $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
--error 1
|
|
--exec $MYSQL_IMPORT --dir $MYSQLTEST_VARDIR/tmp/dump --parallel=300 2>&1
|
|
|
|
--rmdir $MYSQLTEST_VARDIR/tmp/dump
|