Login fails after reinstall of RT 3.4.4

Scott_Courtney · November 7, 2005, 4:31am

Good evening, all

Preface: I’m probably doing something stupid. I’ve been an RT user for several
years, but only recently started administering. OTOH, I have been all over the
list archives, “RT Essentials”, and Google, looking for a solution to this, and
I’m stumped.

I had a perfectly good, working installation of RT 3.4.4 under Perl 5.8.5-12 on
RHEL 4. I did a routine security update (via “up2date”) of Perl itself, from
5.8.5-12 to 5.8.5-16, and this blasted my RT installation.

I’ve spent the last two days figuring out what went wrong, have reinstalled
RT, reinvented the wheel of getting Mason to find its documents (I’ll bet you
all are sick of seeing that question in the list). Ironically, when I solved
that one before, I carefully documented what I had done…in RT. Ouch. Next
time I’ll print that ticket out. {GRIN}

Anyway, after about 10 hours of work, I’m back to the point of having RT up
and running as far as the login screen, but I can’t login. That’s where I’m
stuck.

I have verified that cookies are enabled in my browser for this domain, and
that RT is able to access the database for both read and update. I have
two known-good userids, and I’ve checked that the MD5 password in the Users
table matches the MD5 of what I thought was the password. I have verified
that the Mason directory and the session data directory are being written
to by RT, which creates files there. And I’ve verified that sessions are
being created in the corresponding database table.

There are no errors in the Apache logs. I’ve tried stopping Apache, flushing
the sessions table and Mason cache files, then restarting Apache.

A search of the archives of this list found that this problem could be caused
by a server’s timezone not matching the timezone in the RT_SiteConfig.pm file.
It turns out I had that error, and I’ve since fixed it by copying my old
site config file back to the $PREFIX/etc/ directory. After doing this, I
repeated the stop-flush_everything-restart sequence (see above). I’ve also
tried two different browsers, and restarting the browser, and forcing the
browser to delete all cookies.

The only oddity I see is that the sessions table, while it does have rows
for sessions, has nothing in the a_session colum for any of them. Could
this be a clue? If so, to what? I have verified that the times stored in
the sessions table match the time the server reports.

I would appreciate any help with this. It’s especially frustrating because
I had a wonderfully working installation, and what I thought would be a
trivial security update to Perl broke it so badly. Is there a good way
to turn up logging verbosity on the authentication process?

Versions:
RT 3.4.4
Perl 5.8.5-16 installed from RHEL 4 RPMs
Apache 2.0.55 built from source
mod_perl 2.0.2 built from source
MySQL 5.0.15 installed from official RPMs

Incidentally, the versions of everything except Perl were all working before
the problem arose, so I can rule out compatibility problems with that new
version of MySQL.

Thanks very much for any suggestions.

Kind regards,

Scott

Scott Courtney | “I don’t mind Microsoft making money. I mind them
scott@4th.com | having a bad operating system.” – Linus Torvalds
http://4th.com/ | (“The Rebel Code,” NY Times, 21 February 1999)
| PGP Public Key at http://4th.com/keys/scott.pubkey

Les_Mikesell · November 7, 2005, 5:00am

I would appreciate any help with this. It’s especially frustrating because
I had a wonderfully working installation, and what I thought would be a
trivial security update to Perl broke it so badly. Is there a good way
to turn up logging verbosity on the authentication process?

Versions:
RT 3.4.4
Perl 5.8.5-16 installed from RHEL 4 RPMs
Apache 2.0.55 built from source
mod_perl 2.0.2 built from source
MySQL 5.0.15 installed from official RPMs

Did you rebuild mod_perl to match the version of perl you
have installed now?

Les Mikesell
lesmikesell@gmail.com

Luke_Vanderfluit · November 7, 2005, 6:16am

Hi.

Not sure if this will help you but it might:
Execute this SQL command to reset the root password to ‘password’.
update Users set Password=‘X03MO1qnZdYdgyfeuILPmQ’ where Name=‘root’;

hth.

Kind regards.
Luke

Scott Courtney wrote:

Good evening, all

Preface: I’m probably doing something stupid. I’ve been an RT user for several
years, but only recently started administering. OTOH, I have been all over the
list archives, “RT Essentials”, and Google, looking for a solution to this, and
I’m stumped.

I had a perfectly good, working installation of RT 3.4.4 under Perl 5.8.5-12 on
RHEL 4. I did a routine security update (via “up2date”) of Perl itself, from
5.8.5-12 to 5.8.5-16, and this blasted my RT installation.

I’ve spent the last two days figuring out what went wrong, have reinstalled
RT, reinvented the wheel of getting Mason to find its documents (I’ll bet you
all are sick of seeing that question in the list). Ironically, when I solved
that one before, I carefully documented what I had done…in RT. Ouch. Next
time I’ll print that ticket out. {GRIN}

Anyway, after about 10 hours of work, I’m back to the point of having RT up
and running as far as the login screen, but I can’t login. That’s where I’m
stuck.

I have verified that cookies are enabled in my browser for this domain, and
that RT is able to access the database for both read and update. I have
two known-good userids, and I’ve checked that the MD5 password in the Users
table matches the MD5 of what I thought was the password. I have verified
that the Mason directory and the session data directory are being written
to by RT, which creates files there. And I’ve verified that sessions are
being created in the corresponding database table.

There are no errors in the Apache logs. I’ve tried stopping Apache, flushing
the sessions table and Mason cache files, then restarting Apache.

A search of the archives of this list found that this problem could be caused
by a server’s timezone not matching the timezone in the RT_SiteConfig.pm file.
It turns out I had that error, and I’ve since fixed it by copying my old
site config file back to the $PREFIX/etc/ directory. After doing this, I
repeated the stop-flush_everything-restart sequence (see above). I’ve also
tried two different browsers, and restarting the browser, and forcing the
browser to delete all cookies.

The only oddity I see is that the sessions table, while it does have rows
for sessions, has nothing in the a_session colum for any of them. Could
this be a clue? If so, to what? I have verified that the times stored in
the sessions table match the time the server reports.

I would appreciate any help with this. It’s especially frustrating because
I had a wonderfully working installation, and what I thought would be a
trivial security update to Perl broke it so badly. Is there a good way
to turn up logging verbosity on the authentication process?

Versions:
RT 3.4.4
Perl 5.8.5-16 installed from RHEL 4 RPMs
Apache 2.0.55 built from source
mod_perl 2.0.2 built from source
MySQL 5.0.15 installed from official RPMs

Incidentally, the versions of everything except Perl were all working before
the problem arose, so I can rule out compatibility problems with that new
version of MySQL.

Thanks very much for any suggestions.

Kind regards,

Scott

Luke

Scott_Courtney · November 7, 2005, 3:05pm

Did you rebuild mod_perl to match the version of perl you
have installed now?

Good morning, and thanks for the reply. Yes, I did rebuild mod_perl after
the Perl upgrade.

Scott

Scott Courtney | “I don’t mind Microsoft making money. I mind them
scott@4th.com | having a bad operating system.” – Linus Torvalds
http://4th.com/ | (“The Rebel Code,” NY Times, 21 February 1999)
| PGP Public Key at http://4th.com/keys/scott.pubkey

Scott_Courtney · November 7, 2005, 3:09pm

Hi.

Not sure if this will help you but it might:
Execute this SQL command to reset the root password to ‘password’.
update Users set Password=‘X03MO1qnZdYdgyfeuILPmQ’ where Name=‘root’;

hth.

Kind regards.
Luke

I’m puzzled…the passwords in my Users table are MD5 hashed, while the above
is not. I didn’t manually put the MD5 strings into the table; RT did that when
creating the accounts. Is it possible that RT uses some underlying
authentication library that used to use MD5 and now uses something else, and
that’s the source of my problem? Maybe that authentication library got updated
along with Perl, silently?

Or is the encryption/hash something that can be specified in the site config?

Kind regards, and thanks for the reply,

Scott

Scott Courtney | “I don’t mind Microsoft making money. I mind them
scott@4th.com | having a bad operating system.” – Linus Torvalds
http://4th.com/ | (“The Rebel Code,” NY Times, 21 February 1999)
| PGP Public Key at http://4th.com/keys/scott.pubkey

Scott_Courtney · November 8, 2005, 2:20am

Not sure if this will help you but it might:
Execute this SQL command to reset the root password to ‘password’.
update Users set Password=‘X03MO1qnZdYdgyfeuILPmQ’ where Name=‘root’;

I tried this, even though my other passwords are MD5, figuring I had
nothing to lose. Unfortunately, it didn’t help, but thanks for the
suggestion nonetheless.

Kind regards,

Scott

Scott Courtney | “I don’t mind Microsoft making money. I mind them
scott@4th.com | having a bad operating system.” – Linus Torvalds
http://4th.com/ | (“The Rebel Code,” NY Times, 21 February 1999)
| PGP Public Key at http://4th.com/keys/scott.pubkey

Scott_Courtney · November 8, 2005, 3:17am

Anyway, after about 10 hours of work, I’m back to the point of having RT up
and running as far as the login screen, but I can’t login. That’s where I’m
stuck.

WOW! This was a sneaky problem, but it’s now working. The problem turned out
to be a subtle MySQL database issue. Specifically, there was some sort of a
compare failure in the Users.name field, causing no userids to match. Now, I
have no idea why this worked before the Perl upgrade, except that DBI got
upgraded along with Perl, and I’m guessing that’s where the cause lies.

In any case, the solution was to unload all of the RT data, re-create the
database, and reload it in, but switching from InnoDB engine to MyISAM engine.

An example of the table create syntax follows, in case anyone who isn’t
familiar with MySQL needs to replicate this. Note that this shows the “ACL”
table only, and the change must be repeated for each table.

OLD (fails to work with RT):

CREATE TABLE ACL (
id int(11) NOT NULL auto_increment,
PrincipalType varchar(25) NOT NULL default ‘’,
PrincipalId int(11) NOT NULL default ‘0’,
RightName varchar(25) NOT NULL default ‘’,
ObjectType varchar(25) NOT NULL default ‘’,
ObjectId int(11) NOT NULL default ‘0’,
DelegatedBy int(11) NOT NULL default ‘0’,
DelegatedFrom int(11) NOT NULL default ‘0’,
PRIMARY KEY (id),
KEY ACL1 (RightName,ObjectType,ObjectId,PrincipalType,PrincipalId)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

NEW (works correctly with RT):

CREATE TABLE ACL (
id int(11) NOT NULL auto_increment,
PrincipalType varchar(25) NOT NULL default ‘’,
PrincipalId int(11) NOT NULL default ‘0’,
RightName varchar(25) NOT NULL default ‘’,
ObjectType varchar(25) NOT NULL default ‘’,
ObjectId int(11) NOT NULL default ‘0’,
DelegatedBy int(11) NOT NULL default ‘0’,
DelegatedFrom int(11) NOT NULL default ‘0’,
PRIMARY KEY (id),
KEY ACL1 (RightName,ObjectType,ObjectId,PrincipalType,PrincipalId)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I only discovered the problem by accident. As I was trying Luke’s suggestion
for the password, and had previously been referencing records by ID number,
as in “SELECT * FROM Users WHERE id=12;”. In that case, however, I issued the
command “SELECT * FROM Users WHERE name=‘root’;” and was amazed that MySQL
didn’t match the record. Some further testing, such as checking the character
length of the string, showed that the name really was “root”, but for whatever
reason, that didn’t match on comparisons.

I’m planning to check into this further with the MySQL developers, to see if
this could possibly be a bug in the new 5.0 version or if it’s some kind of
collation or character set subtlety (i.e., “working as designed, but designed
weirdly”, so to speak).

If I find out anything useful, I’ll post here on the list; for now, I suggest
that people using MySQL 5.x with RT use the MyISAM engine rather than InnoDB.

Thanks again to those who replied to my post.

Scott

Scott Courtney | “I don’t mind Microsoft making money. I mind them
scott@4th.com | having a bad operating system.” – Linus Torvalds
http://4th.com/ | (“The Rebel Code,” NY Times, 21 February 1999)
| PGP Public Key at http://4th.com/keys/scott.pubkey

Les_Mikesell · November 8, 2005, 6:29am

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I only discovered the problem by accident. As I was trying Luke’s suggestion
for the password, and had previously been referencing records by ID number,
as in “SELECT * FROM Users WHERE id=12;”. In that case, however, I issued the
command “SELECT * FROM Users WHERE name=‘root’;” and was amazed that MySQL
didn’t match the record.

Is your system default characterset latin1? If not, when does the
conversion happen when a non-default characterset is specified
at the table level in mysql?

Les Mikesell
les@futuresource.com

Scott_Courtney · November 8, 2005, 2:50pm

I only discovered the problem by accident. As I was trying Luke’s suggestion
for the password, and had previously been referencing records by ID number,
as in “SELECT * FROM Users WHERE id=12;”. In that case, however, I issued the
command “SELECT * FROM Users WHERE name=‘root’;” and was amazed that MySQL
didn’t match the record.

Is your system default characterset latin1? If not, when does the
conversion happen when a non-default characterset is specified
at the table level in mysql?

I’m pretty sure it’s UTF-8 encoding, and I’m not sure about the character
set. I haven’t yet had time to test with specifying the charset at the table
level. (At this point, I’m just glad to have my users back online. {GRIN})

Scott

Scott Courtney | “I don’t mind Microsoft making money. I mind them
scott@4th.com | having a bad operating system.” – Linus Torvalds
http://4th.com/ | (“The Rebel Code,” NY Times, 21 February 1999)
| PGP Public Key at http://4th.com/keys/scott.pubkey

Les_Mikesell · November 8, 2005, 3:06pm

I only discovered the problem by accident. As I was trying Luke’s suggestion
for the password, and had previously been referencing records by ID number,
as in “SELECT * FROM Users WHERE id=12;”. In that case, however, I issued the
command “SELECT * FROM Users WHERE name=‘root’;” and was amazed that MySQL
didn’t match the record.

Is your system default characterset latin1? If not, when does the
conversion happen when a non-default characterset is specified
at the table level in mysql?

I’m pretty sure it’s UTF-8 encoding, and I’m not sure about the character
set. I haven’t yet had time to test with specifying the charset at the table
level. (At this point, I’m just glad to have my users back online. {GRIN})

The table schema you posted did specify the charset at the
table level, which mysql 4.x and up allows. I’m just not sure
how and where conversions happen if that isn’t the system
default and thought it might be the cause of your problem
although I don’t know why it would be different between
innodb and myisam tables.

Les Mikesell
les@futuresource.com

Scott_Courtney · November 8, 2005, 3:23pm

The table schema you posted did specify the charset at the
table level, which mysql 4.x and up allows.

Yes, I noticed that. I didn’t pick latin1; that was MySQL’s default choice.
I am not sure what the system default is. It’s a brand-new server, and this
issue hasn’t arisen with any other software yet, so I hadn’t paid much
attention to it.

I’m just not sure
how and where conversions happen if that isn’t the system
default and thought it might be the cause of your problem

I agree; in fact, I am almost certain this is the cause.

although I don’t know why it would be different between
innodb and myisam tables.

If I recall, MyISAM doesn’t support certain character sets that are supported
in InnoDB. I’ll have to do some reading up on this in the MySQL docs.

Scott

Scott Courtney | “I don’t mind Microsoft making money. I mind them
scott@4th.com | having a bad operating system.” – Linus Torvalds
http://4th.com/ | (“The Rebel Code,” NY Times, 21 February 1999)
| PGP Public Key at http://4th.com/keys/scott.pubkey

Ruslan_Zakirov · November 10, 2005, 3:45pm

ENGINE TYPE MUST BE InnoDB!!! Other variants are not supported.On 11/8/05, Scott Courtney scott@4th.com wrote:

On Sunday 06 November 2005 23:31, Scott Courtney wrote:

Anyway, after about 10 hours of work, I’m back to the point of having RT up
and running as far as the login screen, but I can’t login. That’s where I’m
stuck.

WOW! This was a sneaky problem, but it’s now working. The problem turned out
to be a subtle MySQL database issue. Specifically, there was some sort of a
compare failure in the Users.name field, causing no userids to match. Now, I
have no idea why this worked before the Perl upgrade, except that DBI got
upgraded along with Perl, and I’m guessing that’s where the cause lies.

In any case, the solution was to unload all of the RT data, re-create the
database, and reload it in, but switching from InnoDB engine to MyISAM engine.

An example of the table create syntax follows, in case anyone who isn’t
familiar with MySQL needs to replicate this. Note that this shows the “ACL”
table only, and the change must be repeated for each table.

OLD (fails to work with RT):

CREATE TABLE ACL (
id int(11) NOT NULL auto_increment,
PrincipalType varchar(25) NOT NULL default ‘’,
PrincipalId int(11) NOT NULL default ‘0’,
RightName varchar(25) NOT NULL default ‘’,
ObjectType varchar(25) NOT NULL default ‘’,
ObjectId int(11) NOT NULL default ‘0’,
DelegatedBy int(11) NOT NULL default ‘0’,
DelegatedFrom int(11) NOT NULL default ‘0’,
PRIMARY KEY (id),
KEY ACL1 (RightName,ObjectType,ObjectId,PrincipalType,PrincipalId)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

NEW (works correctly with RT):

CREATE TABLE ACL (
id int(11) NOT NULL auto_increment,
PrincipalType varchar(25) NOT NULL default ‘’,
PrincipalId int(11) NOT NULL default ‘0’,
RightName varchar(25) NOT NULL default ‘’,
ObjectType varchar(25) NOT NULL default ‘’,
ObjectId int(11) NOT NULL default ‘0’,
DelegatedBy int(11) NOT NULL default ‘0’,
DelegatedFrom int(11) NOT NULL default ‘0’,
PRIMARY KEY (id),
KEY ACL1 (RightName,ObjectType,ObjectId,PrincipalType,PrincipalId)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I only discovered the problem by accident. As I was trying Luke’s suggestion
for the password, and had previously been referencing records by ID number,
as in “SELECT * FROM Users WHERE id=12;”. In that case, however, I issued the
command “SELECT * FROM Users WHERE name=‘root’;” and was amazed that MySQL
didn’t match the record. Some further testing, such as checking the character
length of the string, showed that the name really was “root”, but for whatever
reason, that didn’t match on comparisons.

I’m planning to check into this further with the MySQL developers, to see if
this could possibly be a bug in the new 5.0 version or if it’s some kind of
collation or character set subtlety (i.e., “working as designed, but designed
weirdly”, so to speak).

If I find out anything useful, I’ll post here on the list; for now, I suggest
that people using MySQL 5.x with RT use the MyISAM engine rather than InnoDB.

Thanks again to those who replied to my post.

Scott

Best regards, Ruslan.

Scott_Courtney · November 12, 2005, 9:01pm

ENGINE TYPE MUST BE InnoDB!!! Other variants are not supported.

Thanks for the warning. I wasn’t aware of this. I dumped the database,
re-created using InnoDB (and modified the engine type on the table create
statements in the dump file), and reloaded.

All is well. I found that the root cause of the problem was that the data
I migrated from our old server was UTF8, but our new server (and the new
version of MySQL) use LATIN1 by default. Now that I have everything in
agreement, RT works fine.

Thanks again for all the good advice. Ruslan, you probably saved me from
some nasty problems down the road.

Scott

Scott Courtney | “I don’t mind Microsoft making money. I mind them
scott@4th.com | having a bad operating system.” – Linus Torvalds
http://4th.com/ | (“The Rebel Code,” NY Times, 21 February 1999)
| PGP Public Key at http://4th.com/keys/scott.pubkey