: Couldn't load from the users database

Help.
I have a major problem. My RT2 on RH 8.0 with MySQL was
working great. Now it doesn’t send any emails and I get
this error when I take ownership of a ticket.
[err]: Couldn’t load from the users database.

I manually deleted a number of unwanted usernames from
within MySQL that has their password set to NO-PASSWORD
(because I noticed there were a lot of SPAM addresses).
I must have removed a few important Users, Nobody and root.

I believe I can fix this problem if I can recreate the
base Users table for RT2… Most importantly 'Nobody’
and ‘root’. I found $RT/etc/insertdata and recreated
the two users based off that script but still no go.

I can promise the Users tables the only RT2 table I
issued a delete command on.

Please reply to john@trdlnk.com AND the RT list with all
emails.

John Giles
john@trdlnk.com


[err]: Couldn’t load from the users database.

I manually deleted a number of unwanted usernames from
within MySQL that has their password set to NO-PASSWORD

First rule of RT:

Do not manually add/modify/delete rows/columns in the RT database
unless you really, really know what you are doing.

( And have taken out kneecap insurance. )

(because I noticed there were a lot of SPAM addresses).
I must have removed a few important Users, Nobody and root.

Aaiieeee.

I believe I can fix this problem if I can recreate the
base Users table for RT2… Most importantly 'Nobody’
and ‘root’. I found $RT/etc/insertdata and recreated
the two users based off that script but still no go.

More importantly, did you recreate them with the same ‘id’ number? RT
depends on being able to load certain users (root for instance, and
Nobody), and those users being able to do particular things as defined by
the RT Access Control System (which is based on the User’s ‘id’ number).

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

Regards,

                         Bruce Campbell                            RIPE
               Systems/Network Engineer                             NCC
             www.ripe.net - PGP562C8B1B             Operations/Security

First rule of RT:

Do not manually add/modify/delete rows/columns in the RT database
unless you really, really know what you are doing.

( And have taken out kneecap insurance. )

Noted :slight_smile:

More importantly, did you recreate them with the same ‘id’ number? RT
depends on being able to load certain users (root for instance, and
Nobody), and those users being able to do particular things as defined by
the RT Access Control System (which is based on the User’s ‘id’ number).

I think the ‘id’ to be 1, 2 or 3 but I’m not 100% sure. When I added
users, RT started at ‘id’ 4 and went up.

Could someone tell me entries for Users…id => 1, 2 and 3.

If I populate the Users table with the information of the Nobody and root
accounts given in the insertdata script AND have the correct ‘id’ then
things should be in the clear.
I want to make sure the Nobody and root accounts are the same.

Are there other users that RT requires ??

John Giles
john@trdlnk.com


[err]: Couldn’t load from the users database.

I resolved this by creating a temp rt2 database from source.
The only entry in the Users table was:

| id | Name | Password | Privileged |
| 1 | RT_System | NO-PASSWORD | 2 |

I modified my ‘broken’ Users table to the above settings
and everything started working again.

John Giles
john@trdlnk.com

I have RT2 on RedHat 8.0 with MySQL.

I’ve been setting my SPAM mail to the ‘DEAD’ status. Now that
I’ve setup my SPAM filter I’d like to clean up all of the
unwanted tickets.

How can I safely remove all the ‘DEAD’ tickets.

I don’t want to set the tickets to ‘RESOLVED’ because there was
no work performed on the tickets.

Is this advisable ?? Has anyone done any similar clean-up ??

John Giles
-john@trdlnk.com

See below. This script was based on Steve Poirier’s ‘cleanup’ script, but
has been modified to kill what I am calling ‘Crazy’ tickets – ones that
for whatever reason have gotten >200 transactions associated with them
(this would never happen naturally in our environment, YMMV).

Also kills ‘dead’ tickets as well as optimizes all the tables. I run this
twice a day.

Dave

#!/usr/bin/perl

Steve Poirier - steve@inet-technologies.com

to use with RT2 and MySQL

use strict;
use DBI;

definition of variables

my $db=“rt2”;
my $host=“localhost”;
my $user=“rt_user”;
my $password="";

my $ticketnbr = 0;

connect to MySQL database

my $dbh = DBI->connect (“DBI:mysql:database=$db:host=$host”,
$user,
$password)
or die “Can’t connect to database: $DBI::errstr\n”;

prepare the query for crazy tickets

my $sth = $dbh->prepare( “
select Ticket, count(id) ct FROM Transactions GROUP BY Ticket HAVING ct>200”);
$sth->execute( );

proceed with deletion

while ( my @row = $sth->fetchrow_array( ) ) {
my $ticketid = $row[0];
print "Ticket $ticketid is crazy. Deleting: ";
my $str = $dbh->prepare( “
SELECT id FROM Transactions where Ticket=$row[0]”);
$str->execute( );

delete Attachments for this ticket

print "Attachments. ";
while ( my @row2 = $str->fetchrow_array( ) )  {
	my $transid = $row2[0];
	$dbh->do( "
	DELETE FROM Attachments WHERE TransactionId=$transid");
}

delete Ticket in Transactions table

print "Transactions. ";
	$dbh->do( "
	DELETE FROM Transactions WHERE Ticket=$ticketid;");

delete Ticket in ticket in Watchers table

print "Watchers. ";
	$dbh->do( "
	DELETE FROM Watchers WHERE Value=$ticketid;");

delete Ticket in ObjectKeywords table

print "ObjectKeywords. ";
	$dbh->do( "
	DELETE FROM ObjectKeywords WHERE ObjectId=$ticketid;");

delete Ticket from the Tickets table

print "Ticket. ";
	$dbh->do( "
	DELETE FROM Tickets WHERE id=$ticketid;");

$ticketnbr++;	

}
print “Deleted $ticketnbr crazy tickets.\n”;

reset this.

$ticketnbr = 0;

prepare the query for dead tickets

my $sth = $dbh->prepare( “
SELECT id FROM Tickets where Status=‘dead’;”);
$sth->execute( );

proceed with deletion

while ( my @row = $sth->fetchrow_array( ) ) {
my $ticketid = $row[0];
print "Ticket $ticketid is dead. Deleting: ";
my $str = $dbh->prepare( “
SELECT id FROM Transactions where Ticket=$row[0]”);
$str->execute( );

delete Attachments for this ticket

print "Attachments. ";
while ( my @row2 = $str->fetchrow_array( ) )  {
	my $transid = $row2[0];
	$dbh->do( "
	DELETE FROM Attachments WHERE TransactionId=$transid");
}

delete Ticket in Transactions table

print "Transactions. ";
	$dbh->do( "
	DELETE FROM Transactions WHERE Ticket=$ticketid;");

delete Ticket in ticket in Watchers table

print "Watchers. ";
	$dbh->do( "
	DELETE FROM Watchers WHERE Value=$ticketid;");

delete Ticket in ObjectKeywords table

print "ObjectKeywords. ";
	$dbh->do( "
	DELETE FROM ObjectKeywords WHERE ObjectId=$ticketid;");

delete Ticket from the Tickets table

print "Ticket.\n";
	$dbh->do( "
	DELETE FROM Tickets WHERE id=$ticketid;");

$ticketnbr++;	

}
print “Deleted $ticketnbr dead tickets.\n”;

reset this.

$ticketnbr = 0;

prepare the query for orphaned attachments

my $sth = $dbh->prepare( “SELECT at.TransactionId, tr.id
FROM Attachments at LEFT JOIN Transactions tr ON at.TransactionId=tr.id
WHERE tr.id IS NULL”);

$sth->execute( );

proceed with deletion

while ( my @row = $sth->fetchrow_array( ) ) {
my $atid = $row[0];

print "Attachment $atid is orphaned.  Deleting.\n";
$dbh->do( "DELETE FROM Attachments WHERE TransactionId=$atid");
$ticketnbr++;	

}

print “Deleted $ticketnbr orphaned attachments.\n”;

$dbh->do(“OPTIMIZE TABLE Tickets”);
print “Optimized Tickets.\n”;
$dbh->do(“OPTIMIZE TABLE Transactions”);
print “Optimized Transactions.\n”;
$dbh->do(“OPTIMIZE TABLE Attachments”);
print “Optimized Attachments.\n”;
$dbh->do(“OPTIMIZE TABLE Watchers”);
print “Optimized Watchers.\n”;
$dbh->do(“OPTIMIZE TABLE ObjectKeywords”);
print “Optimized ObjectKeywords.\nDone!\n\n”;

exit;On Tue, 14 Jan 2003, John Giles wrote:

I have RT2 on RedHat 8.0 with MySQL.

I’ve been setting my SPAM mail to the ‘DEAD’ status. Now that
I’ve setup my SPAM filter I’d like to clean up all of the
unwanted tickets.

How can I safely remove all the ‘DEAD’ tickets.

I don’t want to set the tickets to ‘RESOLVED’ because there was
no work performed on the tickets.

Is this advisable ?? Has anyone done any similar clean-up ??

John Giles
-john@trdlnk.com


rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

Hi Dave,

I think I have found one case where the script can corrupt the database. I
used it before trying to upgrade a database from RT2 to RT3 and it made the
import fail.

I had ticket A in my database marked as depending on ticket B. This appears
in table “links”

Ticket B is now marked as "solved"
Ticket A has been deleted. I guess it was marked as “DEAD”

The inconsistency is in the “links” database. The link between tickets A and
B is not removed. It probably shouldn’t anyway. It now points to a non
existing ticket A.

In this situation, it would probably be better not to delete ticket A.

In a functionning RT2 database, this inconsistency does not seem to have any
negative effects. Only one ticket was involved in my system.

Blaise

Hi Dave,

I think I have found one case where the script can corrupt the database. I
used it before trying to upgrade a database from RT2 to RT3 and it made the
import fail.

For the record, I consider this to be a bug in my code for RT3 as well
as the dead ticket deletion script :wink: RT 2.1.62 should fix it.
»|« http://www.bestpractical.com/rt – Trouble Ticketing. Free.

“JV” == Jesse Vincent jesse@bestpractical.com writes:

JV> On Wed, Jan 22, 2003 at 11:01:13PM +0100, THAUVIN Blaise (Dir. Informatique FRP) wrote:

Hi Dave,

I think I have found one case where the script can corrupt the database. I
used it before trying to upgrade a database from RT2 to RT3 and it made the
import fail.

JV> For the record, I consider this to be a bug in my code for RT3 as well
JV> as the dead ticket deletion script :wink: RT 2.1.62 should fix it.

Referential integrity checks in the DB are good. Please tell the
MySQL folks. :wink:

I delete my dead tickets thusly: “delete from tickets where
status=‘dead’” and let the RI checks delete the associated records in
other tables.

Help.
I have a major problem. My RT2 on RH 8.0 with MySQL was
working great. Now it doesn’t send any emails and I get
this error when I take ownership of a ticket.
[err]: Couldn’t load from the users database.

I manually deleted a number of unwanted usernames from
within MySQL that has their password set to NO-PASSWORD
(because I noticed there were a lot of SPAM addresses).
I must have removed a few important Users, Nobody and root.

I believe I can fix this problem if I can recreate the
base Users table for RT2… Most importantly 'Nobody’
and ‘root’. I found $RT/etc/insertdata and recreated
the two users based off that script but still no go.

I can promise the Users tables the only RT2 table I
issued a delete command on.

Please reply to john@trdlnk.com AND the RT list with all
emails.

John Giles
john@trdlnk.com