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