0 results after corrupt sessions table, myisamchk

RT Users
1

On Friday afternoon, for no apparent reason, while I was the only user
on my RT 3.8.0 system, after clicking a link, I was presented with the
logon prompt.

Puzzled, I entered my username and password and logged back in.
Everything seemed normal, and the ticket I was trying to edit was there.
I continued my edits, and clicked to update the ticket.

Login prompt again.

I tried using a different browser (trusty Firefox instead of Opera,
which I’ve been experimenting with lately). Same thing. Login prompt
after every click.

Some research in the RT Mailing List finally led me to uncomment the
following line in my RT_SiteConfig.pm file:

Set($WebSessionClass, ‘Apache::Session::File’);

Doing this, and restarting Apache fixed the problem. Or so it seemed…

Last night, while reviewing the server’s backups, I realized that the
nightly MySQL dump from the 22nd was much smaller than previous nights’
backups. Curious, I re-ran the backup script. It immediately failed,
saying that rt3.sessions was corrupt.

“Session? That sounds familiar…” More Googling. I found several
references to the same sort of problems. One fix I found was to run
myisamchk on the data file in question which, in this case, was
/var/lib/mysql/rt3/sessions.MYI. I did this, and it identified errors. I
proceeded to run myisamchck -r -q sessions in the data directory. It
threw a lot of data on the screen, and appeared to be repairing lots of
stuff.

So this explains the sudden need to add this line to my RT_SiteConfig.pm
where it was never needed previously - database corruption. One mystery
solved.

I figured if the sessions table was corrupt, maybe running a quick
repair on the rest of the tables would be a good idea. I wrote up a
quick bash script to loop through all of the table files in the
directory and myisamchck -r -q all of them.

Here’s the puzzling part where my memory gets a little fuzzy. I am
fairly certain that RT was working fine after this, and I went to bed.
This morning, I attempted to log in, did so, but then saw no tickets!
Clicking my saved bookmarks to take me directly to my preferred view of
a queue showed 0 tickets. Similarly, requesting all “new” and "open"
tickets in my “support” queue from the command line returned 0 results.

However, if I fire up mysql, I can select data from the tables just fine.

I pulled a copy of last night’s mysqldump, and attempted to open it in
Notepad++, which I was using to take notes (since I couldn’t very well
take notes into RT, could I?). Bad idea. Notepad++ crashed, taking all
of my notes (essentially, everything you’ve read up to this point) down
with it.

So it seems I have a couple of options: attempt to roll back to the
latest known good MySQL dump, which may be 3-4 days old at this point,
or figure out what happened to the files (indexes?) and repair them.

Any suggestions greatly appreciated.

Thanks,

Peter

2

Additional info:

After a short while, I observed the rt.log file was growing at an
astounding rate, and quickly grew to fill up the entire available space.
Every second or faster, it was filling up with the lines below. Inspired
by the first line, I ran another myisamchck against the Attributes.MYI file.

Now the RT homepage is loading, and I can again see all of my queues,
but it’s still showing as having no tickets in any of them. There are
1.2GB of files in /var/lib/mysql/rt3, so the data’s got to be there, but
I still need suggestions on how to get RT to realize this.

I’ve also observed that if I try to select all tickets that are open and
in the ‘support’ queue, I get no results. But if I list only tickets
whose status is ‘open’, I see the expected results. However, if I click
on any of them, I cannot open it.

Any advice greatly appreciated.

Thanks,

Peter

[Mon Aug 25 02:14:04 2008] [warning]: DBD::mysql::st execute failed:
Incorrect key file for table ‘./rt3/Attributes.MYI’; try to repair it at
/usr/local/share/perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505
, line 4.
(/usr/local/share/perl/5.8.8/DBIx/SearchBuilder/Handle.pm:505)
[Mon Aug 25 02:14:05 2008] [warning]: RT::Handle=HASH(0x9fd6e84)
couldn’t execute the query 'SELECT main.* FROM Attributes main WHERE
(main.ObjectType = ‘RT::User’) AND (main.ObjectId = 35) ’ at /usr/local/
share/perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 518

DBIx::SearchBuilder::Handle::SimpleQuery(‘RT::Handle=HASH(0x9fd6e84)’,
‘SELECT main.* FROM Attributes main WHERE (main.ObjectType = …’)
called at /usr/local/share/perl/5.8.8/DBIx/SearchBuilder.pm
line 238
DBIx::SearchBuilder::_DoSearch(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/rt3.8/bin/…/lib/RT/Attributes_Overlay.pm line 74
RT::Attributes::_DoSearch(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/share/perl/5.8.8/DBIx/SearchBuilder.pm line 498
DBIx::SearchBuilder::Next(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/rt3.8/bin/…/lib/RT/Attributes_Overlay.pm line 82

RT::Attributes::_BuildAccessTable(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/rt3.8/bin/…/lib/RT/Attributes_Overlay.pm line 75
RT::Attributes::DoSearch(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/rt3.8/bin/…/lib/RT/Attributes_Overlay.pm line 90
RT::Attributes::AttrHash(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/rt3.8/bin/…/lib/RT/Attributes_Overlay.pm line 121
RT::Attributes::Named(‘RT::Attributes=HASH(0xa67447c)’,
‘AuthToken’) called at /usr/local/rt3.8/bin/…/lib/RT/Record.pm line 239
RT::Record::FirstAttribute(‘RT::User=HASH(0xa50fe00)’,
‘AuthToken’) called at /usr/local/rt3.8/bin/…/lib/RT/User_Overlay.pm
line 1011
RT::User::AuthToken(‘RT::User=HASH(0xa50fe00)’) called at
/usr/local/rt3.8/bin/…/lib/RT/User_Overlay.pm line 1049
RT::User::GenerateAuthString(‘RT::User=HASH(0xa50fe00)’, ‘Queue
= ‘support’ AND (Status = ‘open’ OR Status = ‘new’)’) called at
/usr/local/rt3.8/share/html/Search/Elements/ResultViews line 74
HTML::Mason::Commands::ANON(‘QueryString’,
’?Format=%0A%20%20%20’%3CB%3E%3CA%20HREF%3D%22__WebPath%2FT…’,
‘Query’, ‘Queue = ‘support’ AND (Status = ‘open’ OR Status =
‘new’)’, ‘Format’
, '\x{a} ‘…’, ‘Rows’, 50,
‘OrderBy’, …) called at
/usr/local/share/perl/5.8.8/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa6743ec)’,
‘QueryString’,
’?Format=%0A%20%20%20’%3CB%3E%3CA%20HREF%3D%22__WebPath__%2FT…’,
‘Query’, ‘Queue = ‘support’ AND (
Status = ‘open’ OR Status = ‘new’)’, ‘Format’, ‘\x{a} ‘…’, ‘Rows’, 50, …)
called at /usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 12
84
eval {…} called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 1274
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘QueryString’,
’?Format=%0A%20%20%20’%3CB%3E%3CA%20HREF%3D%22__WebPath__%2FT…’,
‘Query’, ‘Queue = ‘support’ AND (Status = ‘open’ OR Status = ‘new
’)’, ‘Format’, '\x{a} ‘…’, ‘Rows’, …)
called at /usr/local/rt3.8/share/html/Search/Results.html line 83
HTML::Mason::Commands::ANON(‘pass’, ‘passwordwouldgohere’,
‘Rows’, 50, ‘Format’, ‘’, ‘Page’, 1, ‘Order’, …) called at
/usr/local/share/perl/5.8.8/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa518be8)’,
‘pass’, ‘mypasswordwouldbehere’, ‘Rows’, 50, ‘Format’, ‘’, ‘Page’, 1,
…) called at /usr/local/share/perl/5.8.8/HTML/Mason/Request.
pm line 1284
eval {…} called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 1274
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘pass’,
‘mypasswordwouldbehere’, ‘Rows’, 50, ‘Format’, ‘’, …) called at
/usr/local/rt3.8/share/html/autohandler line 308
HTML::Mason::Commands::ANON(‘pass’, ‘passwordwouldgohere’,
‘Rows’, 50, ‘Format’, ‘’, ‘Page’, 1, ‘Order’, …) called at
/usr/local/share/perl/5.8.8/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa3d9058)’,
‘pass’, ‘mypasswordwouldbehere’, ‘Rows’, 50, ‘Format’, ‘’, ‘Page’, 1,
…) called at /usr/local/share/perl/5.8.8/HTML/Mason/Request.
pm line 1279
eval {…} called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 1274
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘pass’,
‘mypasswordwouldbehere’, ‘Rows’, 50, ‘Format’, ‘’, …) called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 473
eval {…} called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 473
eval {…} called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 425

HTML::Mason::Request::exec(‘RT::Interface::Web::Request=HASH(0xa46e218)’)
called at /usr/local/share/perl/5.8.8/HTML/Mason/ApacheHandler.pm line 168

HTML::Mason::Request::ApacheHandler::exec(‘RT::Interface::Web::Request=HASH(0xa46e218)’)
called at /usr/local/share/perl/5.8.8/HTML/Mason/ApacheHandler.pm line 825

HTML::Mason::ApacheHandler::handle_request(‘HTML::Mason::ApacheHandler=HASH(0x9979c58)’,
‘Apache=SCALAR(0xa024c60)’) called at /usr/local/rt3.8/bin/webmux.pl
line 148
eval {…} called at /usr/local/rt3.8/bin/webmux.pl line 148
RT::Mason::handler(‘Apache=SCALAR(0xa024c60)’) called at
/dev/null line 0
eval {…} called at /dev/null line 0
(/usr/share/perl/5.8/Carp.pm:105)

3

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.On Mon, Aug 25, 2008 at 6:40 AM, Peter Nikolaidis rt-list@paradigmcc.com wrote:

Additional info:

After a short while, I observed the rt.log file was growing at an
astounding rate, and quickly grew to fill up the entire available space.
Every second or faster, it was filling up with the lines below. Inspired
by the first line, I ran another myisamchck against the Attributes.MYI file.

Now the RT homepage is loading, and I can again see all of my queues,
but it’s still showing as having no tickets in any of them. There are
1.2GB of files in /var/lib/mysql/rt3, so the data’s got to be there, but
I still need suggestions on how to get RT to realize this.

I’ve also observed that if I try to select all tickets that are open and
in the ‘support’ queue, I get no results. But if I list only tickets
whose status is ‘open’, I see the expected results. However, if I click
on any of them, I cannot open it.

Any advice greatly appreciated.

Thanks,

Peter

[Mon Aug 25 02:14:04 2008] [warning]: DBD::mysql::st execute failed:
Incorrect key file for table ‘./rt3/Attributes.MYI’; try to repair it at
/usr/local/share/perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505
, line 4.
(/usr/local/share/perl/5.8.8/DBIx/SearchBuilder/Handle.pm:505)
[Mon Aug 25 02:14:05 2008] [warning]: RT::Handle=HASH(0x9fd6e84)
couldn’t execute the query 'SELECT main.* FROM Attributes main WHERE
(main.ObjectType = ‘RT::User’) AND (main.ObjectId = 35) ’ at /usr/local/
share/perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 518

DBIx::SearchBuilder::Handle::SimpleQuery(‘RT::Handle=HASH(0x9fd6e84)’,
‘SELECT main.* FROM Attributes main WHERE (main.ObjectType = …’)
called at /usr/local/share/perl/5.8.8/DBIx/SearchBuilder.pm
line 238
DBIx::SearchBuilder::_DoSearch(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/rt3.8/bin/…/lib/RT/Attributes_Overlay.pm line 74
RT::Attributes::_DoSearch(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/share/perl/5.8.8/DBIx/SearchBuilder.pm line 498
DBIx::SearchBuilder::Next(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/rt3.8/bin/…/lib/RT/Attributes_Overlay.pm line 82

RT::Attributes::_BuildAccessTable(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/rt3.8/bin/…/lib/RT/Attributes_Overlay.pm line 75
RT::Attributes::DoSearch(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/rt3.8/bin/…/lib/RT/Attributes_Overlay.pm line 90
RT::Attributes::AttrHash(‘RT::Attributes=HASH(0xa67447c)’)
called at /usr/local/rt3.8/bin/…/lib/RT/Attributes_Overlay.pm line 121
RT::Attributes::Named(‘RT::Attributes=HASH(0xa67447c)’,
‘AuthToken’) called at /usr/local/rt3.8/bin/…/lib/RT/Record.pm line 239
RT::Record::FirstAttribute(‘RT::User=HASH(0xa50fe00)’,
‘AuthToken’) called at /usr/local/rt3.8/bin/…/lib/RT/User_Overlay.pm
line 1011
RT::User::AuthToken(‘RT::User=HASH(0xa50fe00)’) called at
/usr/local/rt3.8/bin/…/lib/RT/User_Overlay.pm line 1049
RT::User::GenerateAuthString(‘RT::User=HASH(0xa50fe00)’, ‘Queue
= 'support' AND (Status = 'open' OR Status = 'new')’) called at
/usr/local/rt3.8/share/html/Search/Elements/ResultViews line 74
HTML::Mason::Commands::ANON(‘QueryString’,
'?Format=%0A%20%20%20'%3CB%3E%3CA%20HREF%3D%22__WebPath%2FT…',
‘Query’, ‘Queue = 'support' AND (Status = 'open' OR Status =
'new')’, ‘Format’
, ‘\x{a} '…’, ‘Rows’, 50,
‘OrderBy’, …) called at
/usr/local/share/perl/5.8.8/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa6743ec)’,
‘QueryString’,
‘?Format=%0A%20%20%20'%3CB%3E%3CA%20HREF%3D%22__WebPath__%2FT…’,
‘Query’, ‘Queue = 'support' AND (
Status = 'open' OR Status = 'new')’, ‘Format’, ‘\x{a} '…’, ‘Rows’, 50, …)
called at /usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 12
84
eval {…} called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 1274
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘QueryString’,
‘?Format=%0A%20%20%20'%3CB%3E%3CA%20HREF%3D%22__WebPath__%2FT…’,
‘Query’, 'Queue = 'support' AND (Status = 'open' OR Status = 'new
‘)’, ‘Format’, ‘\x{a} '…’, ‘Rows’, …)
called at /usr/local/rt3.8/share/html/Search/Results.html line 83
HTML::Mason::Commands::ANON(‘pass’, ‘passwordwouldgohere’,
‘Rows’, 50, ‘Format’, ‘’, ‘Page’, 1, ‘Order’, …) called at
/usr/local/share/perl/5.8.8/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa518be8)’,
‘pass’, ‘mypasswordwouldbehere’, ‘Rows’, 50, ‘Format’, ‘’, ‘Page’, 1,
…) called at /usr/local/share/perl/5.8.8/HTML/Mason/Request.
pm line 1284
eval {…} called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 1274
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘pass’,
‘mypasswordwouldbehere’, ‘Rows’, 50, ‘Format’, ‘’, …) called at
/usr/local/rt3.8/share/html/autohandler line 308
HTML::Mason::Commands::ANON(‘pass’, ‘passwordwouldgohere’,
‘Rows’, 50, ‘Format’, ‘’, ‘Page’, 1, ‘Order’, …) called at
/usr/local/share/perl/5.8.8/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa3d9058)’,
‘pass’, ‘mypasswordwouldbehere’, ‘Rows’, 50, ‘Format’, ‘’, ‘Page’, 1,
…) called at /usr/local/share/perl/5.8.8/HTML/Mason/Request.
pm line 1279
eval {…} called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 1274
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘pass’,
‘mypasswordwouldbehere’, ‘Rows’, 50, ‘Format’, ‘’, …) called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 473
eval {…} called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 473
eval {…} called at
/usr/local/share/perl/5.8.8/HTML/Mason/Request.pm line 425

HTML::Mason::Request::exec(‘RT::Interface::Web::Request=HASH(0xa46e218)’)
called at /usr/local/share/perl/5.8.8/HTML/Mason/ApacheHandler.pm line 168

HTML::Mason::Request::ApacheHandler::exec(‘RT::Interface::Web::Request=HASH(0xa46e218)’)
called at /usr/local/share/perl/5.8.8/HTML/Mason/ApacheHandler.pm line 825

HTML::Mason::ApacheHandler::handle_request(‘HTML::Mason::ApacheHandler=HASH(0x9979c58)’,
‘Apache=SCALAR(0xa024c60)’) called at /usr/local/rt3.8/bin/webmux.pl
line 148
eval {…} called at /usr/local/rt3.8/bin/webmux.pl line 148
RT::Mason::handler(‘Apache=SCALAR(0xa024c60)’) called at
/dev/null line 0
eval {…} called at /dev/null line 0
(/usr/share/perl/5.8/Carp.pm:105)

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.
4

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.

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

Thanks,

Peter

5

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.

6

Problem solved.

I wrote a loop to go through and convert all of my tables to InnoDB. It
choked on the Tickets table, so I ran a “myisamchk -r Tickets” on it,
which repaired 7 indexes. I was then able to successfully convert it to
InnoDB along with the rest of the tables.

Oh Happy Day! It’s working! Everything seems to be working perfectly again!

I need to run some more tests, but I think this was just a case of
MyISAM indexes being corrupted. I don’t know why, but I guess we’ve been
dodging bullets for years because AFAIK, we’ve run MyISAM since we first
installed RT 3.4 way back when. Now we know better.

Thanks again to Ruslan and Tim for their help.

Best regards,

Peter