mirror of
https://github.com/MariaDB/server.git
synced 2025-04-22 15:15:41 +02:00
152 lines
5.6 KiB
Text
152 lines
5.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 flush_redo_log,localhost,root,,;
|
|
SET GLOBAL innodb_flush_log_at_trx_commit=1;
|
|
BEGIN;
|
|
DELETE FROM articles LIMIT 1;
|
|
ROLLBACK;
|
|
disconnect flush_redo_log;
|
|
connection default;
|
|
# restart
|
|
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;
|
|
BEGIN;
|
|
DELETE FROM articles LIMIT 1;
|
|
ROLLBACK;
|
|
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);
|
|
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;
|
|
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;
|