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

[Ruslan Zakirov]

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

On 11/8/05, Scott Courtney <scott at 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.