[rt-users] Login fails after reinstall of RT 3.4.4 -- SOLVED

[Scott Courtney]

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

-- 
------------------------------------------------------------------------------
Scott Courtney         | "I don't mind Microsoft making money. I mind them
scott at 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