[TYPO3-english] database problems
Jigal van Hemert
jigal at xs4all.nl
Tue Aug 9 23:34:29 CEST 2011
On 9-8-2011 19:29, Katja Lampela wrote:
> ok more info; in one of these only-myISAM-sites (that I want to change
> back to InnoDB) the phpmyadmin shows in Structure tab the cache tables,
> but in their column (combined columns Records, Type, Collation, Size and
> Overhead) there is only this text: "in use".
Maybe the table is locked by one of your conversions?
MyISAM and InnoDB are completely different things. The file structures
they live in are so different. Something simple like changing the engine
basically means creating a new table, copying all the records to the new
table and removing the old table.
If it's a really, really big table the best solution is to make a dump
of the table, drop the table, create the new table and import the data.
If it's a cache table, do a truncate before converting it, or drop it
first and create it new.
Converting from e.g. InnoDB to another engine introduces another
challenge: if you didn't configure InnoDB to use a file per table all
the tables will live in a single tablespace (maybe in several files for
file system restrictions). Dropping a table will not reduce the size of
that tablespace; once the tablespace grows it will not get smaller.
Only solution would be to recreate the entire database.
Another fun fact: if you kill MySQL in the middle of an InnoDB operation
it can take ages before it gets up an running again. InnoDB is a
transactional engine, so if an operation is interupted InnoDB has to
roll back the operation. If that operation in itself took a long, long
time the roll back will take a long, long time also.
There is a lot of information in the MySQL documentation about how to do
these operations properly and how to recover from problems, but it can
take a lot of effort.
Kind regards / met vriendelijke groet,
Jigal van Hemert.
More information about the TYPO3-english