1# Testcase for the following bugs
2# Bug#16691130 - ASSERT WHEN INNODB_LOG_GROUP_HOME_DIR DOES NOT EXIST
3# Bug#16418661 - CHANGING NAME IN FOR INNODB_DATA_FILE_PATH SHOULD NOT SUCCEED WITH LOG FILES
4# Start mysqld without the possibility to create innodb_undo_tablespaces
5# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
6SELECT * FROM INFORMATION_SCHEMA.ENGINES
7WHERE engine = 'innodb'
8AND support IN ('YES', 'DEFAULT', 'ENABLED');
9ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
10FOUND 1 /\[ERROR\] InnoDB: Could not create undo tablespace '.*undo002'/ in mysqld.1.err
11# Remove  undo001,undo002,ibdata1,ibdata2,ib_logfile1,ib_logfile2,ib_logfile101
12# Start mysqld with non existent innodb_log_group_home_dir
13# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend --innodb_log_group_home_dir=/path/to/non-existent/
14SELECT * FROM INFORMATION_SCHEMA.ENGINES
15WHERE engine = 'innodb'
16AND support IN ('YES', 'DEFAULT', 'ENABLED');
17ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
18FOUND 1 /File .path.to.non-existent.*ib_logfile101: 'create' returned OS error \d+/ in mysqld.1.err
19# Successfully let InnoDB create tablespaces
20# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
21SELECT COUNT(*) `1` FROM INFORMATION_SCHEMA.ENGINES
22WHERE engine='innodb'
23AND support IN ('YES', 'DEFAULT', 'ENABLED');
241
251
26# Backup tmp/logfile/*
27# 1. With ibdata2, Without ibdata1
28# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
29SELECT * FROM INFORMATION_SCHEMA.ENGINES
30WHERE engine = 'innodb'
31AND support IN ('YES', 'DEFAULT', 'ENABLED');
32ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
33FOUND 1 /The innodb_system data file 'ibdata1' was not found but one of the other data files 'ibdata2' exists/ in mysqld.1.err
34bak_ib_logfile0
35bak_ib_logfile1
36bak_ib_logfile2
37bak_ibdata1
38bak_ibdata2
39bak_undo001
40bak_undo002
41bak_undo003
42ib_buffer_pool
43ib_logfile0
44ib_logfile1
45ib_logfile2
46ibdata2
47undo001
48undo002
49undo003
50# 2. With ibdata1, without ibdata2
51# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
52SELECT * FROM INFORMATION_SCHEMA.ENGINES
53WHERE engine = 'innodb'
54AND support IN ('YES', 'DEFAULT', 'ENABLED');
55ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
56FOUND 1 /InnoDB: Tablespace size stored in header is \d+ pages, but the sum of data file sizes is \d+ pages/ in mysqld.1.err
57FOUND 1 /InnoDB: Cannot start InnoDB. The tail of the system tablespace is missing/ in mysqld.1.err
58bak_ib_logfile0
59bak_ib_logfile1
60bak_ib_logfile2
61bak_ibdata1
62bak_ibdata2
63bak_undo001
64bak_undo002
65bak_undo003
66ib_buffer_pool
67ib_logfile0
68ib_logfile1
69ib_logfile2
70ibdata1
71ibdata2
72undo001
73undo002
74undo003
75# 3. Without ibdata1 & ibdata2
76bak_ib_logfile0
77bak_ib_logfile1
78bak_ib_logfile2
79bak_ibdata1
80bak_ibdata2
81bak_undo001
82bak_undo002
83bak_undo003
84ib_buffer_pool
85ib_logfile0
86ib_logfile1
87ib_logfile2
88undo001
89undo002
90undo003
91# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
92SELECT * FROM INFORMATION_SCHEMA.ENGINES
93WHERE engine = 'innodb'
94AND support IN ('YES', 'DEFAULT', 'ENABLED');
95ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
96FOUND 1 /InnoDB: undo tablespace .*undo001.* exists\. Creating system tablespace with existing undo tablespaces is not supported\. Please delete all undo tablespaces before creating new system tablespace\./ in mysqld.1.err
97bak_ib_logfile0
98bak_ib_logfile1
99bak_ib_logfile2
100bak_ibdata1
101bak_ibdata2
102bak_undo001
103bak_undo002
104bak_undo003
105ib_buffer_pool
106ib_logfile0
107ib_logfile1
108ib_logfile2
109undo001
110undo002
111undo003
112# 4. Without ibdata*, ib_logfile* and with undo00*
113bak_ib_logfile0
114bak_ib_logfile1
115bak_ib_logfile2
116bak_ibdata1
117bak_ibdata2
118bak_undo001
119bak_undo002
120bak_undo003
121ib_buffer_pool
122undo001
123undo002
124undo003
125# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
126SELECT * FROM INFORMATION_SCHEMA.ENGINES
127WHERE engine = 'innodb'
128AND support IN ('YES', 'DEFAULT', 'ENABLED');
129ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
130bak_ib_logfile0
131bak_ib_logfile1
132bak_ib_logfile2
133bak_ibdata1
134bak_ibdata2
135bak_undo001
136bak_undo002
137bak_undo003
138ib_buffer_pool
139undo001
140undo002
141undo003
142# 5. Without ibdata*,ib_logfile* files & Without undo002
143bak_ib_logfile0
144bak_ib_logfile1
145bak_ib_logfile2
146bak_ibdata1
147bak_ibdata2
148bak_undo001
149bak_undo002
150bak_undo003
151ib_buffer_pool
152undo001
153undo003
154# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
155SELECT * FROM INFORMATION_SCHEMA.ENGINES
156WHERE engine = 'innodb'
157AND support IN ('YES', 'DEFAULT', 'ENABLED');
158ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
159bak_ib_logfile0
160bak_ib_logfile1
161bak_ib_logfile2
162bak_ibdata1
163bak_ibdata2
164bak_undo001
165bak_undo002
166bak_undo003
167ib_buffer_pool
168undo001
169undo003
170# 6. Without ibdata*,ib_logfile* files & Without undo001, undo002
171bak_ib_logfile0
172bak_ib_logfile1
173bak_ib_logfile2
174bak_ibdata1
175bak_ibdata2
176bak_undo001
177bak_undo002
178bak_undo003
179ib_buffer_pool
180undo003
181# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
182SELECT * FROM INFORMATION_SCHEMA.ENGINES
183WHERE engine = 'innodb'
184AND support IN ('YES', 'DEFAULT', 'ENABLED');
185ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
186FOUND 1 /undo tablespace .*undo003.* exists\. Creating system tablespace with existing undo tablespaces is not supported\. Please delete all undo tablespaces before creating new system tablespace\./ in mysqld.1.err
187bak_ib_logfile0
188bak_ib_logfile1
189bak_ib_logfile2
190bak_ibdata1
191bak_ibdata2
192bak_undo001
193bak_undo002
194bak_undo003
195ib_buffer_pool
196undo003
197# 7. With ibdata files & Without undo002
198bak_ib_logfile0
199bak_ib_logfile1
200bak_ib_logfile2
201bak_ibdata1
202bak_ibdata2
203bak_undo001
204bak_undo002
205bak_undo003
206ib_buffer_pool
207ib_logfile0
208ib_logfile1
209ib_logfile2
210ibdata1
211ibdata2
212undo001
213undo003
214# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
215SELECT * FROM INFORMATION_SCHEMA.ENGINES
216WHERE engine = 'innodb'
217AND support IN ('YES', 'DEFAULT', 'ENABLED');
218ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
219FOUND 1 /InnoDB: Unable to open undo tablespace.*undo002/ in mysqld.1.err
220bak_ib_logfile0
221bak_ib_logfile1
222bak_ib_logfile2
223bak_ibdata1
224bak_ibdata2
225bak_undo001
226bak_undo002
227bak_undo003
228ib_buffer_pool
229ib_logfile0
230ib_logfile1
231ib_logfile2
232ibdata1
233ibdata2
234undo001
235undo003
236# 8. With ibdata files & Without undo001, undo002
237bak_ib_logfile0
238bak_ib_logfile1
239bak_ib_logfile2
240bak_ibdata1
241bak_ibdata2
242bak_undo001
243bak_undo002
244bak_undo003
245ib_buffer_pool
246ib_logfile0
247ib_logfile1
248ib_logfile2
249ibdata1
250ibdata2
251undo003
252# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
253SELECT * FROM INFORMATION_SCHEMA.ENGINES
254WHERE engine = 'innodb'
255AND support IN ('YES', 'DEFAULT', 'ENABLED');
256ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
257FOUND 1 /InnoDB: Unable to open undo tablespace.*undo001/ in mysqld.1.err
258bak_ib_logfile0
259bak_ib_logfile1
260bak_ib_logfile2
261bak_ibdata1
262bak_ibdata2
263bak_undo001
264bak_undo002
265bak_undo003
266ib_buffer_pool
267ib_logfile0
268ib_logfile1
269ib_logfile2
270ibdata1
271ibdata2
272undo003
273# 9. Without ibdata*, without undo*, Without ib_logfile1 and with ib_logfile2
274bak_ib_logfile0
275bak_ib_logfile1
276bak_ib_logfile2
277bak_ibdata1
278bak_ibdata2
279bak_undo001
280bak_undo002
281bak_undo003
282ib_buffer_pool
283ib_logfile0
284ib_logfile2
285# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
286SELECT * FROM INFORMATION_SCHEMA.ENGINES
287WHERE engine = 'innodb'
288AND support IN ('YES', 'DEFAULT', 'ENABLED');
289ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
290InnoDB	YES	Supports transactions, row-level locking, foreign keys and encryption for tables	YES	YES	YES
291NOT FOUND /redo log file .*ib_logfile0.* exists\. Creating system tablespace with existing redo log files is not recommended\. Please delete all redo log files before creating new system tablespace\./ in mysqld.1.err
292bak_ib_logfile0
293bak_ib_logfile1
294bak_ib_logfile2
295bak_ibdata1
296bak_ibdata2
297bak_undo001
298bak_undo002
299bak_undo003
300ib_buffer_pool
301ib_logfile0
302ib_logfile1
303ib_logfile2
304ibdata1
305ibdata2
306undo001
307undo002
308undo003
309# 10. With ibdata*, without ib_logfile0
310# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
311SELECT * FROM INFORMATION_SCHEMA.ENGINES
312WHERE engine = 'innodb'
313AND support IN ('YES', 'DEFAULT', 'ENABLED');
314ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
315InnoDB	YES	Supports transactions, row-level locking, foreign keys and encryption for tables	YES	YES	YES
316bak_ib_logfile0
317bak_ib_logfile1
318bak_ib_logfile2
319bak_ibdata1
320bak_ibdata2
321bak_undo001
322bak_undo002
323bak_undo003
324ib_buffer_pool
325ib_logfile0
326ib_logfile1
327ib_logfile2
328ibdata1
329ibdata2
330undo001
331undo002
332undo003
333# 11. With ibdata*, without ib_logfile1
334bak_ib_logfile0
335bak_ib_logfile1
336bak_ib_logfile2
337bak_ibdata1
338bak_ibdata2
339bak_undo001
340bak_undo002
341bak_undo003
342ib_buffer_pool
343ib_logfile0
344ib_logfile2
345ibdata1
346ibdata2
347undo001
348undo002
349undo003
350# restart: --innodb-log-files-in-group=3 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_file --innodb-undo-logs=20 --innodb-undo-tablespaces=3 --innodb-data-file-path=ibdata1:16M;ibdata2:10M:autoextend
351SELECT COUNT(*) `1` FROM INFORMATION_SCHEMA.ENGINES
352WHERE engine='innodb'
353AND support IN ('YES', 'DEFAULT', 'ENABLED');
3541
3551
356FOUND 1 /Resizing redo log from 1\*\d+ to 3\*\d+ bytes; LSN=\d+/ in mysqld.1.err
357# restart
358# Cleanup
359bak_ib_logfile0
360bak_ib_logfile1
361bak_ib_logfile2
362bak_ibdata1
363bak_ibdata2
364bak_undo001
365bak_undo002
366bak_undo003
367ib_buffer_pool
368ib_logfile0
369ib_logfile1
370ib_logfile2
371ibdata1
372ibdata2
373undo001
374undo002
375undo003
376