Debian Codegraph @

mysql-test/suite/innodb_fts/r/crash_recovery.result

raw
1 FLUSH TABLES;
2 CREATE TABLE articles (
3 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
4 title VARCHAR(200),
5 body TEXT,
6 FULLTEXT (title,body)
7 ) ENGINE=InnoDB;
8 DROP INDEX title ON articles;
9 INSERT INTO articles (title,body) VALUES
10 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
11 ('How To Use MySQL Well','After you went through a ...'),
12 ('Optimizing MySQL','In this tutorial we will show ...'),
13 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
14 ('MySQL vs. YourSQL','In the following database comparison ...'),
15 ('MySQL Security','When configured properly, MySQL ...');
16 BEGIN;
17 INSERT INTO articles (title,body) VALUES
18 ('MySQL Tutorial','DBMS stands for DataBase ...');
19 # Make durable the AUTO_INCREMENT in the above incomplete transaction.
20 connect ddl1, localhost, root,,;
21 CREATE TABLE t1(a TEXT,b TEXT,FULLTEXT INDEX(a)) ENGINE=InnoDB;
22 ALTER TABLE t1 ADD FULLTEXT INDEX(b);
23 connection default;
24 connect ddl2, localhost, root,,;
25 CREATE TABLE t2(a TEXT,b TEXT,FULLTEXT INDEX(a)) ENGINE=InnoDB;
26 ALTER TABLE t2 DROP INDEX a, ADD FULLTEXT INDEX(b), FORCE;
27 connection default;
28 connect ddl3, localhost, root,,;
29 CREATE TABLE t3(a TEXT,b TEXT,FULLTEXT INDEX(a)) ENGINE=InnoDB;
30 ALTER TABLE t3 DROP INDEX a, ADD FULLTEXT INDEX(b), ALGORITHM=COPY;
31 connection default;
32 # restart
33 disconnect ddl1;
34 disconnect ddl2;
35 disconnect ddl3;
36 SET GLOBAL innodb_max_purge_lag_wait=0;
37 CHECK TABLE t1,t2,t3;
38 Table Op Msg_type Msg_text
39 test.t1 check status OK
40 test.t2 check status OK
41 test.t3 check status OK
42 DROP TABLE t1,t2,t3;
43 INSERT INTO articles (title,body) VALUES
44 ('MySQL Tutorial','DBMS stands for DataBase ...');
45 CREATE FULLTEXT INDEX idx ON articles (title,body);
46 SELECT * FROM articles
47 WHERE MATCH (title,body)
48 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
49 id title body
50 1 MySQL Tutorial DBMS stands for DataBase ...
51 5 MySQL vs. YourSQL In the following database comparison ...
52 8 MySQL Tutorial DBMS stands for DataBase ...
53 INSERT INTO articles (title,body) VALUES
54 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
55 ('How To Use MySQL Well','After you went through a ...'),
56 ('Optimizing MySQL','In this tutorial we will show ...'),
57 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
58 ('MySQL vs. YourSQL','In the following database comparison ...'),
59 ('MySQL Security','When configured properly, MySQL ...');
60 connect dml, localhost, root,,;
61 BEGIN;
62 INSERT INTO articles (title,body) VALUES
63 ('MySQL Tutorial','DBMS stands for DataBase ...');
64 connection default;
65 # Make durable the AUTO_INCREMENT in the above incomplete transaction.
66 connect flush_redo_log,localhost,root,,;
67 SET GLOBAL innodb_flush_log_at_trx_commit=1;
68 SET SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
69 BEGIN;
70 UPDATE articles SET id=0 WHERE id=1;
71 UPDATE articles SET id=1 WHERE id=0;
72 COMMIT;
73 disconnect flush_redo_log;
74 connection default;
75 # restart
76 disconnect dml;
77 INSERT INTO articles (title,body) VALUES
78 ('MySQL Tutorial','DBMS stands for DataBase ...');
79 SELECT * FROM articles
80 WHERE MATCH (title,body)
81 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
82 id title body
83 1 MySQL Tutorial DBMS stands for DataBase ...
84 5 MySQL vs. YourSQL In the following database comparison ...
85 8 MySQL Tutorial DBMS stands for DataBase ...
86 9 MySQL Tutorial DBMS stands for DataBase ...
87 13 MySQL vs. YourSQL In the following database comparison ...
88 16 MySQL Tutorial DBMS stands for DataBase ...
89 DROP TABLE articles;
90 CREATE TABLE articles (
91 id int PRIMARY KEY,
92 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
93 title VARCHAR(200),
94 body TEXT
95 ) ENGINE=InnoDB;
96 CREATE FULLTEXT INDEX idx1 on articles (title, body);
97 INSERT INTO articles VALUES
98 (1, 10, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
99 (2, 1, 'How To Use MySQL Well','After you went through a ...'),
100 (3, 2, 'Optimizing MySQL','In this tutorial we will show ...'),
101 (4, 11, '1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
102 (5, 6, 'MySQL vs. YourSQL','In the following database comparison ...'),
103 (7, 4, 'MySQL Security','When configured properly, MySQL ...');
104 connect dml, localhost, root,,;
105 BEGIN;
106 INSERT INTO articles VALUES
107 (100, 200, 'MySQL Tutorial','DBMS stands for DataBase ...');
108 connect dml2, localhost, root,,;
109 #
110 # MDEV-19073 FTS row mismatch after crash recovery
111 #
112 CREATE TABLE mdev19073(id SERIAL, title VARCHAR(200), body TEXT,
113 FULLTEXT(title,body)) ENGINE=InnoDB;
114 INSERT INTO mdev19073 (title, body) VALUES
115 ('MySQL Tutorial', 'DBMS stands for Database...');
116 CREATE FULLTEXT INDEX idx ON mdev19073(title, body);
117 Warnings:
118 Note 1831 Duplicate index `idx`. This is deprecated and will be disallowed in a future release
119 CREATE TABLE mdev19073_2 LIKE mdev19073;
120 INSERT INTO mdev19073_2 (title, body) VALUES
121 ('MySQL Tutorial', 'DBMS stands for Database...');
122 INSERT INTO mdev19073 (title, body) VALUES
123 ('MariaDB Tutorial', 'DB means Database ...');
124 INSERT INTO mdev19073_2 (title, body) VALUES
125 ('MariaDB Tutorial', 'DB means Database ...');
126 SELECT * FROM mdev19073 WHERE MATCH (title, body)
127 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
128 id title body
129 1 MySQL Tutorial DBMS stands for Database...
130 2 MariaDB Tutorial DB means Database ...
131 SELECT * FROM mdev19073_2 WHERE MATCH (title, body)
132 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
133 id title body
134 1 MySQL Tutorial DBMS stands for Database...
135 2 MariaDB Tutorial DB means Database ...
136 connection default;
137 # restart
138 disconnect dml;
139 disconnect dml2;
140 INSERT INTO articles VALUES (8, 12, 'MySQL Tutorial','DBMS stands for DataBase ...');
141 SELECT * FROM articles WHERE MATCH (title, body)
142 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
143 id FTS_DOC_ID title body
144 3 2 Optimizing MySQL In this tutorial we will show ...
145 1 10 MySQL Tutorial DBMS stands for DataBase ...
146 8 12 MySQL Tutorial DBMS stands for DataBase ...
147 DROP TABLE articles;
148 SELECT * FROM mdev19073 WHERE MATCH (title, body)
149 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
150 id title body
151 1 MySQL Tutorial DBMS stands for Database...
152 2 MariaDB Tutorial DB means Database ...
153 SELECT * FROM mdev19073_2 WHERE MATCH (title, body)
154 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
155 id title body
156 1 MySQL Tutorial DBMS stands for Database...
157 2 MariaDB Tutorial DB means Database ...
158 DROP TABLE mdev19073, mdev19073_2;
159 #
160 # MDEV-28706 Redundant InnoDB table fails during alter
161 #
162 SET @@global.innodb_file_per_table = 0;
163 Warnings:
164 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
165 CREATE TABLE t1 (
166 col_int INTEGER, col_text TEXT,
167 col_text_1 TEXT
168 ) ENGINE = InnoDB ROW_FORMAT = Redundant ;
169 ALTER TABLE t1 ADD FULLTEXT KEY `ftidx` ( col_text ) ;
170 INSERT INTO t1 VALUES ( 1255, "mariadb", "InnoDB");
171 # restart
172 ALTER TABLE t1 ADD FULLTEXT(col_text_1);
173 DROP TABLE t1;
174 SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE 'test/%';
175 TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE