Is it me, or is working with InnoDB tablespaces akin to working with Oracle 6.0?
For example, today somebody filled up a filesystem by unintentially loading a large amount of data. It was development, so this stuff happens. Since it was development, I setup the InnoDB tablespace to start with size 10M and autoextend to whatever free space was available. The one datafile kept autoextending until it completely filled the disk. The developer dropped the table and thought that would free up the space.
I knew that wouldn't be the case, so I had to dump all the data, recreate the tablespace, and reload all my data. Pretty standard DBA stuff so far.
"This time", I thought to myself, " I'm going to create a couple files each 10m and let them autoextend to 10240m so this doesn't happen again".
Speedbump #1: InnoDB doesn't let you autoextend more than one file.
OK, I know my data is about 30G and we'll be doing some development, so I'll create three 10240m files and let the last one autoextend to 10240m. After 20 minutes of combing through the manuals trying to find a "DROP TABLESPACE" or similar command, I finally deduce that you can't drop a tablespace while the server is up.
Speedbump #2: The server (or instance for you Oracle people) has to be down to remove the tablespace.
So I shutdown the server, remove my datafiles per the docs, setup my config file, and restarted the server. The logfile spits out that it's pre-creating the files (as expected) but then throws an error message:
Error: All logfiles must be created at the same time.
Speedbump #3: You have to recreate the logfiles when you recreate the tablespace.
I don't want to recreate 30G of space, so I shutdown my server, remove the logfiles, and restart the server. Except this time I get another error:
InnoDB: Cannot initialize created log files because
InnoDB: data files are corrupt, or new data files were
InnoDB: created when the database was started previous
InnoDB: time but the database was not shutdown
InnoDB: normally after that.
Speedbump #4: You have to recreate your logfiles and datafiles at the same time.
It's not out of the realm of possibility that I'm doing something totally wrong or I don't understand InnoDB very well. Maybe my version (5.0.27) doesn't support these features but they are in a newer version. Or maybe it's just a hassle. I'd be interested in other's practical experiences with managing the InnoDB tablespace.
Powered by Zoundry