mirror of
https://github.com/MariaDB/server.git
synced 2025-02-07 06:12:18 +01:00
062f8eb37d
the information about index algorithm was stored in two places inconsistently split between both. BTREE index could have key->algorithm == HA_KEY_ALG_BTREE, if the user explicitly specified USING BTREE or HA_KEY_ALG_UNDEF, if not. RTREE index had key->algorithm == HA_KEY_ALG_RTREE and always had key->flags & HA_SPATIAL FULLTEXT index had key->algorithm == HA_KEY_ALG_FULLTEXT and always had key->flags & HA_FULLTEXT HASH index had key->algorithm == HA_KEY_ALG_HASH or HA_KEY_ALG_UNDEF long unique index always had key->algorithm == HA_KEY_ALG_LONG_HASH In this commit: All indexes except BTREE and HASH always have key->algorithm set, HA_SPATIAL and HA_FULLTEXT flags are not used anymore (except for storage to keep frms backward compatible). As a side effect ALTER TABLE now detects FULLTEXT index renames correctly
175 lines
6.6 KiB
Text
175 lines
6.6 KiB
Text
FLUSH TABLES;
|
|
CREATE TABLE articles (
|
|
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
body TEXT,
|
|
FULLTEXT (title,body)
|
|
) ENGINE=InnoDB;
|
|
DROP INDEX title ON articles;
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL Tutorial','DBMS stands for DataBase ...') ,
|
|
('How To Use MySQL Well','After you went through a ...'),
|
|
('Optimizing MySQL','In this tutorial we will show ...'),
|
|
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
|
('MySQL vs. YourSQL','In the following database comparison ...'),
|
|
('MySQL Security','When configured properly, MySQL ...');
|
|
BEGIN;
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL Tutorial','DBMS stands for DataBase ...');
|
|
# Make durable the AUTO_INCREMENT in the above incomplete transaction.
|
|
connect ddl1, localhost, root,,;
|
|
CREATE TABLE t1(a TEXT,b TEXT,FULLTEXT INDEX(a)) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD FULLTEXT INDEX(b);
|
|
connection default;
|
|
connect ddl2, localhost, root,,;
|
|
CREATE TABLE t2(a TEXT,b TEXT,FULLTEXT INDEX(a)) ENGINE=InnoDB;
|
|
ALTER TABLE t2 DROP INDEX a, ADD FULLTEXT INDEX(b), FORCE;
|
|
connection default;
|
|
connect ddl3, localhost, root,,;
|
|
CREATE TABLE t3(a TEXT,b TEXT,FULLTEXT INDEX(a)) ENGINE=InnoDB;
|
|
ALTER TABLE t3 DROP INDEX a, ADD FULLTEXT INDEX(b), ALGORITHM=COPY;
|
|
connection default;
|
|
# restart
|
|
disconnect ddl1;
|
|
disconnect ddl2;
|
|
disconnect ddl3;
|
|
SET GLOBAL innodb_max_purge_lag_wait=0;
|
|
CHECK TABLE t1,t2,t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
test.t2 check status OK
|
|
test.t3 check status OK
|
|
DROP TABLE t1,t2,t3;
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL Tutorial','DBMS stands for DataBase ...');
|
|
CREATE FULLTEXT INDEX idx ON articles (title,body);
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
1 MySQL Tutorial DBMS stands for DataBase ...
|
|
5 MySQL vs. YourSQL In the following database comparison ...
|
|
8 MySQL Tutorial DBMS stands for DataBase ...
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL Tutorial','DBMS stands for DataBase ...') ,
|
|
('How To Use MySQL Well','After you went through a ...'),
|
|
('Optimizing MySQL','In this tutorial we will show ...'),
|
|
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
|
('MySQL vs. YourSQL','In the following database comparison ...'),
|
|
('MySQL Security','When configured properly, MySQL ...');
|
|
connect dml, localhost, root,,;
|
|
BEGIN;
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL Tutorial','DBMS stands for DataBase ...');
|
|
connection default;
|
|
# Make durable the AUTO_INCREMENT in the above incomplete transaction.
|
|
connect flush_redo_log,localhost,root,,;
|
|
SET GLOBAL innodb_flush_log_at_trx_commit=1;
|
|
SET SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
|
|
BEGIN;
|
|
UPDATE articles SET id=0 WHERE id=1;
|
|
UPDATE articles SET id=1 WHERE id=0;
|
|
COMMIT;
|
|
disconnect flush_redo_log;
|
|
connection default;
|
|
# restart
|
|
disconnect dml;
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL Tutorial','DBMS stands for DataBase ...');
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
1 MySQL Tutorial DBMS stands for DataBase ...
|
|
5 MySQL vs. YourSQL In the following database comparison ...
|
|
8 MySQL Tutorial DBMS stands for DataBase ...
|
|
9 MySQL Tutorial DBMS stands for DataBase ...
|
|
13 MySQL vs. YourSQL In the following database comparison ...
|
|
16 MySQL Tutorial DBMS stands for DataBase ...
|
|
DROP TABLE articles;
|
|
CREATE TABLE articles (
|
|
id int PRIMARY KEY,
|
|
FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
|
|
title VARCHAR(200),
|
|
body TEXT
|
|
) ENGINE=InnoDB;
|
|
CREATE FULLTEXT INDEX idx1 on articles (title, body);
|
|
INSERT INTO articles VALUES
|
|
(1, 10, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
|
|
(2, 1, 'How To Use MySQL Well','After you went through a ...'),
|
|
(3, 2, 'Optimizing MySQL','In this tutorial we will show ...'),
|
|
(4, 11, '1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
|
(5, 6, 'MySQL vs. YourSQL','In the following database comparison ...'),
|
|
(7, 4, 'MySQL Security','When configured properly, MySQL ...');
|
|
connect dml, localhost, root,,;
|
|
BEGIN;
|
|
INSERT INTO articles VALUES
|
|
(100, 200, 'MySQL Tutorial','DBMS stands for DataBase ...');
|
|
connect dml2, localhost, root,,;
|
|
#
|
|
# MDEV-19073 FTS row mismatch after crash recovery
|
|
#
|
|
CREATE TABLE mdev19073(id SERIAL, title VARCHAR(200), body TEXT,
|
|
FULLTEXT(title,body)) ENGINE=InnoDB;
|
|
INSERT INTO mdev19073 (title, body) VALUES
|
|
('MySQL Tutorial', 'DBMS stands for Database...');
|
|
CREATE FULLTEXT INDEX idx ON mdev19073(title, body);
|
|
Warnings:
|
|
Note 1831 Duplicate index `idx`. This is deprecated and will be disallowed in a future release
|
|
CREATE TABLE mdev19073_2 LIKE mdev19073;
|
|
INSERT INTO mdev19073_2 (title, body) VALUES
|
|
('MySQL Tutorial', 'DBMS stands for Database...');
|
|
INSERT INTO mdev19073 (title, body) VALUES
|
|
('MariaDB Tutorial', 'DB means Database ...');
|
|
INSERT INTO mdev19073_2 (title, body) VALUES
|
|
('MariaDB Tutorial', 'DB means Database ...');
|
|
SELECT * FROM mdev19073 WHERE MATCH (title, body)
|
|
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
1 MySQL Tutorial DBMS stands for Database...
|
|
2 MariaDB Tutorial DB means Database ...
|
|
SELECT * FROM mdev19073_2 WHERE MATCH (title, body)
|
|
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
1 MySQL Tutorial DBMS stands for Database...
|
|
2 MariaDB Tutorial DB means Database ...
|
|
connection default;
|
|
# restart
|
|
disconnect dml;
|
|
disconnect dml2;
|
|
INSERT INTO articles VALUES (8, 12, 'MySQL Tutorial','DBMS stands for DataBase ...');
|
|
SELECT * FROM articles WHERE MATCH (title, body)
|
|
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
|
|
id FTS_DOC_ID title body
|
|
3 2 Optimizing MySQL In this tutorial we will show ...
|
|
1 10 MySQL Tutorial DBMS stands for DataBase ...
|
|
8 12 MySQL Tutorial DBMS stands for DataBase ...
|
|
DROP TABLE articles;
|
|
SELECT * FROM mdev19073 WHERE MATCH (title, body)
|
|
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
1 MySQL Tutorial DBMS stands for Database...
|
|
2 MariaDB Tutorial DB means Database ...
|
|
SELECT * FROM mdev19073_2 WHERE MATCH (title, body)
|
|
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
1 MySQL Tutorial DBMS stands for Database...
|
|
2 MariaDB Tutorial DB means Database ...
|
|
DROP TABLE mdev19073, mdev19073_2;
|
|
#
|
|
# MDEV-28706 Redundant InnoDB table fails during alter
|
|
#
|
|
SET @@global.innodb_file_per_table = 0;
|
|
Warnings:
|
|
Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
|
|
CREATE TABLE t1 (
|
|
col_int INTEGER, col_text TEXT,
|
|
col_text_1 TEXT
|
|
) ENGINE = InnoDB ROW_FORMAT = Redundant ;
|
|
ALTER TABLE t1 ADD FULLTEXT KEY `ftidx` ( col_text ) ;
|
|
INSERT INTO t1 VALUES ( 1255, "mariadb", "InnoDB");
|
|
# restart
|
|
ALTER TABLE t1 ADD FULLTEXT(col_text_1);
|
|
DROP TABLE t1;
|
|
SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE 'test/%';
|
|
TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE
|