[TYPO3-english] database problems

[Jigal van Hemert]

Hi,

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.