[rt-users] Problems with script
Mathew Snyder
theillien at yahoo.com
Sat Mar 31 05:48:33 EDT 2007
Disregard this. It ended up being a corrupted database. Reinstating it from
our production server fixed the problem.
Mathew
Mathew Snyder wrote:
> I'm getting all kinds of errors:
>
> DBD::mysql::st execute failed: Incorrect information in file:
> './rt3_devel/Users.frm' at
> /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505.
> RT::Handle=HASH(0x99e5a80) couldn't execute the query 'SELECT * FROM Users
> WHERE Name = ?' at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm
> line 518
> DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0x99e5a80)',
> 'SELECT * FROM Users WHERE Name = ?', 'RT_System') called at
> /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1208
>
> DBIx::SearchBuilder::Record::_LoadFromSQL('RT::CurrentUser=HASH(0x99be9ec)',
> 'SELECT * FROM Users WHERE Name = ?', 'RT_System') called at
> /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1127
>
> DBIx::SearchBuilder::Record::LoadByCols('RT::CurrentUser=HASH(0x99be9ec)',
> 'Name', 'RT_System') called at
> /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record/Cachable.pm line 119
>
> DBIx::SearchBuilder::Record::Cachable::LoadByCols('RT::CurrentUser=HASH(0x99be9ec)',
> 'Name', 'RT_System') called at /usr/local/rt-3.6.3/lib/RT/Record.pm line 395
> RT::Record::LoadByCols('RT::CurrentUser=HASH(0x99be9ec)', 'Name',
> 'RT_System') called at
> /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1072
>
> DBIx::SearchBuilder::Record::LoadByCol('RT::CurrentUser=HASH(0x99be9ec)',
> 'Name', 'RT_System') called at /usr/local/rt-3.6.3/lib/RT/CurrentUser.pm line 253
> RT::CurrentUser::LoadByName('RT::CurrentUser=HASH(0x99be9ec)',
> 'RT_System') called at /usr/local/rt-3.6.3/lib/RT.pm line 181
> RT::Init() called at ./user_timesheet.pl line 13
> DBD::mysql::st execute failed: Incorrect information in file:
> './rt3_devel/Users.frm' at
> /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505.
> RT::Handle=HASH(0x99e5a80) couldn't execute the query 'SELECT * FROM Users
> WHERE Name = ?' at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm
> line 518
> DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0x99e5a80)',
> 'SELECT * FROM Users WHERE Name = ?', 'Nobody') called at
> /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1208
>
> DBIx::SearchBuilder::Record::_LoadFromSQL('RT::CurrentUser=HASH(0x99ecf20)',
> 'SELECT * FROM Users WHERE Name = ?', 'Nobody') called at
> /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1127
>
> DBIx::SearchBuilder::Record::LoadByCols('RT::CurrentUser=HASH(0x99ecf20)',
> 'Name', 'Nobody') called at
> /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record/Cachable.pm line 119
>
> DBIx::SearchBuilder::Record::Cachable::LoadByCols('RT::CurrentUser=HASH(0x99ecf20)',
> 'Name', 'Nobody') called at /usr/local/rt-3.6.3/lib/RT/Record.pm line 395
> RT::Record::LoadByCols('RT::CurrentUser=HASH(0x99ecf20)', 'Name',
> 'Nobody') called at
> /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1072
>
> DBIx::SearchBuilder::Record::LoadByCol('RT::CurrentUser=HASH(0x99ecf20)',
> 'Name', 'Nobody') called at /usr/local/rt-3.6.3/lib/RT/CurrentUser.pm line 253
> RT::CurrentUser::LoadByName('RT::CurrentUser=HASH(0x99ecf20)', 'Nobody')
> called at /usr/local/rt-3.6.3/lib/RT.pm line 185
> RT::Init() called at ./user_timesheet.pl line 13
>
>
> What might the problem be? Script:
> #!/usr/bin/perl
>
> use warnings;
> use strict;
> use lib '/usr/local/rt-3.6.3/lib';
> use lib '/usr/local/rt-3.6.3/local/lib';
> use RT;
> use RT::Tickets;
> use RT::Users;
> use MIME::Lite;
>
> RT::LoadConfig();
> RT::Init();
>
> # Declare our global variables
> my (@days, @months, @years, @date);
> my $time = time();
> my %environment;
> my %timeworked;
>
> for (1 .. 7) {
> $time -= 24*60*60;
> @date = (localtime($time))[3 .. 5];
> push @days, (sprintf '%02d', $date[0]);
> push @months,(sprintf '%02d',$date[1] + 1);
> push @years, $date[2] + 1900;
> }
>
> my $startDate = join "-", $months[$#months], $days[$#days], $years[$#years];
> my $endDate = join "-", $months[0], $days[0], $years[0];
> my $searchStart = join "-", $years[$#years], $months[$#months], $days[$#days];
> my $searchEnd = join "-", $years[0], $months[0], $days[0];
> print "Search Start -> $searchStart\n";
> print "Search End -> $searchEnd\n";
>
> my $emailTo = "msnyder\@servervault.com"; #"rbates\@servervault.com,
> jcurran\@servervault.com";
> my $emailFrom = "root";
> #my $emailBcc = "msnyder\@servervault.com";
> my $emailSubj = "RT User Timesheet for The Week Ending $endDate";
> my $emailMsg = "Attached is a file containing billable time committed by
> each user for the week ending $endDate. You'll find it to be a bit rudimentary
> but it contains the data I've been asked to extract. There are two problems
> left to solve at this point. One is to convert each user's ID number to his or
> her name. The other is to get the query to work with any date and cover
> anything that is open and has been worked on in the last week as well as
> anything resoved in the last week. I have a help request in for both of these.";
>
> my $tix = new RT::Tickets(RT::SystemUser);
> $tix->FromSQL('Queue = "CustomerCare" AND (LastUpdated > "' . $searchStart . '"
> AND LastUpdated < "' . $searchEnd . '")');
>
> while (my $ticket = $tix->Next) {
> my $env = $ticket->FirstCustomFieldValue('Environment');
> unless ($env) {
> warn "warning " . $ticket->id. " has no environment";
> next
> }
>
> my $transactions = $ticket->Transactions;
> while (my $transaction = $transactions->Next) {
> my $creator = $transactions->CreatorObj;
> next unless ($transaction->TimeTaken);
> next unless $creator->Privileged;
> $timeworked{$creator->Name} += $transaction->TimeWorked;
> }
>
> foreach my $user (keys %timeworked) {
> my @endTime;
> my $temp = $timeworked{$user} / 60;
> my @temp = split /\./, $temp;
> $endTime[0] = $temp[0];
> $endTime[1] = $timeworked{$user} % 60;
> my $wholeTime = sprintf '%d:%02d', @endTime[0,1];
> $environment{$env}{$user} = $wholeTime;
> }
> }
>
> # The nitty gritty of the script
> open TIMESHEET, ">/work_reports/weekly/timesheet_weekof_$endDate.txt";
>
> print TIMESHEET "Timesheet for $startDate to $endDate\n\n\n";
> #printf TIMESHEET "\nUser Timesheet for $startDate to $endDate\n";
>
> foreach my $env (sort keys %environment) {
> #Print the header for our data
> print TIMESHEET "$env", "\n";
> printf TIMESHEET "%10s%8s\n", "User", "hh:mm";
> print TIMESHEET ("-" x 30);
> print TIMESHEET "\n";
> foreach my $name (sort keys %{ $environment{$env} }) {
> printf TIMESHEET "%10s%8s\n", "$name", "$environment{$env}{$name}";
> }
> print TIMESHEET "\n";
> }
>
> close TIMESHEET;
>
> # Prepare and send the email which with the report to all necessary parties.
> my $fullEmail = new MIME::Lite(From => $emailFrom,
> To => $emailTo,
> # Bcc => $emailBcc,
> Subject => $emailSubj,
> Type => "multipart/mixed");
>
> $fullEmail->attach(Type => "TEXT",
> Data => $emailMsg);
>
> $fullEmail->attach(Type => "text/plain",
> Path =>
> "/work_reports/weekly/timesheet_weekof_$endDate.txt",
> Disposition => "attachment");
>
> $fullEmail->send("sendmail", "/usr/sbin/sendmail -t");
>
> exit;
>
>
> Mathew
More information about the rt-users
mailing list