[rt-users] 0 results after corrupt sessions table, myisamchk

[Tim Cutts]

On 25 Aug 2008, at 7:37 am, Peter Nikolaidis wrote:

> Ruslan Zakirov wrote:
>>
>> Most of RT's table MUST use InnoDB engine to work correctly. I'm not
>> sure what's going on but I have feeling that you've disabled innodb
>> and mysql went wild after that.
>>
> Hi Ruslan,
>
> To my knowledge, we've never used InnoDB on this system. We upgraded  
> to
> 3.6.6 months ago, and 3.8.0 about a week ago.

Running MySQL using MyISAM is always a risky proposition, in my view.   
You lose all transactions, and you also lose quite a lot of atomicity  
(any multi-row UPDATE, INSERT or DELETE statement which gets  
interrupted, for example, can be left half-completed).  MyISAM is also  
quite a lot slower in tables which are written to frequently, because  
the only lock it supports is a full table lock.

>
> Can we convert and somehow get access to the data again?

You can certainly convert the table, yes, using:

ALTER TABLE tablename Engine=InnoDB

It can take some time to run, if the table being converted is large.

Chances are, though, that it is already too late.  myisamchk does  
nothing to check the data contents of your table are correct - all it  
does is check that that the indices are consistent with the rows of  
data that it currently has in the table.

If I were you, I'd find a known good backup of the database, modify  
its create table statements so they use the InnoDB engine (so you  
don't have to do the above ALTER TABLE stuff by hand), and then  
restore that backup database.

Regards,

Tim.


-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.