MySQL timing out

[Jim]

Hi,

When I try to pull out all my data out, the DB appears to be timing out
before I get all the data.

There is not a where clause in the select.

Is there a way I can speed up the query?

it is pretty simple

select
a,b,c,d from table
order by a desc

OR

can I increase the timeout for the DB?

Thank you,

Jim

[Ray in HK]

how do you get your data ? what tool are you using ?

"Jim" <jf*****@gmail.com> ¦b¶l¥ó
news:11**********************@g49g2000cwa.googlegr oups.com ¤¤¼¶¼g...

Hi,

When I try to pull out all my data out, the DB appears to be timing out
before I get all the data.

There is not a where clause in the select.

Is there a way I can speed up the query?

it is pretty simple

select
a,b,c,d from table
order by a desc

OR

can I increase the timeout for the DB?

Thank you,

Jim

[Jim]

I am using a Perl script to pull out the data.

use Win32::ODBC;

Thank you,

Jim

[Jim]

Here is the code, it may not be a timeout problem. I am getting about
4200 records out of 80000

sub update_rate_fields
{
$row=1;
# "DB DSN" points to MySQL server and "test_aces_data" DB
$DSN = "DB";

# Make new ODBC object and connect to the server
if (!($rates = new Win32::ODBC($DSN)))
{
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}

# Make new ODBC object and connect to the server
if (!($insert_rates = new Win32::ODBC($DSN)))
{
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}

# Query to load all data
$reviews_query =
"select
id,
total_num_of_errors,
amount_to_be_inspected,
amount_actually_inspected,
meeting_time,
total_prep_time_hours,
inspected_item_type
from tcgaz_inspections
order by id desc";
# Execute on data
if ($rates -> Sql($reviews_query))
{
# We have an error if we got here
print "SQL failed.\n";
print "Error: " . $rates->Error() . "\n";

# Close it out
$rates->Close();

# Error out of the program
exit;
}
my $now = localtime time;
print "\n$now\n";
undef %id;
$row=1;
while ($rates -> FetchRow())
{
# Clear out for this row
undef %data;

# Get the hash of data
%data = $rates -> DataHash();
$temp_id=$data{"id"};
chomp $temp_id;
# Get our values
$id{$temp_id} = $data{"id"};
$total_num_of_errors{$temp_id} = $data{"total_num_of_errors"};
$meeting_time{$temp_id} = $data{"meeting_time"};
$amount_to_be_inspected{$temp_id} = $data{"amount_to_be_inspected"};
$total_prep_time_hours{$temp_id} = $data{"total_prep_time_hours"};
$amount_actually_inspected{$temp_id} =
$data{"amount_actually_inspected"};
$amount_to_be_inspected{$temp_id} = $data{"amount_to_be_inspected"};
($phase{$temp_id}, $notused{$temp_id}) = split (/:/
,$data{"inspected_item_type"});
$row++;

}
print "\nRetrieved $row records\n";
my $now = localtime time;
print "$now\n";
}

[Ray in HK]

you should try to debug your program then.
adding more print statement to locate where does the error come from.

"Jim" <jf*****@gmail.com> ¦b¶l¥ó
news:11*********************@g47g2000cwa.googlegro ups.com ¤¤¼¶¼g...

Here is the code, it may not be a timeout problem. I am getting about
4200 records out of 80000

sub update_rate_fields
{
$row=1;
# "DB DSN" points to MySQL server and "test_aces_data" DB
$DSN = "DB";

# Make new ODBC object and connect to the server
if (!($rates = new Win32::ODBC($DSN)))
{
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}

# Make new ODBC object and connect to the server
if (!($insert_rates = new Win32::ODBC($DSN)))
{
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}

# Query to load all data
$reviews_query =
"select
id,
total_num_of_errors,
amount_to_be_inspected,
amount_actually_inspected,
meeting_time,
total_prep_time_hours,
inspected_item_type
from tcgaz_inspections
order by id desc";
# Execute on data
if ($rates -> Sql($reviews_query))
{
# We have an error if we got here
print "SQL failed.\n";
print "Error: " . $rates->Error() . "\n";

# Close it out
$rates->Close();

# Error out of the program
exit;
}
my $now = localtime time;
print "\n$now\n";
undef %id;
$row=1;
while ($rates -> FetchRow())
{
# Clear out for this row
undef %data;

# Get the hash of data
%data = $rates -> DataHash();
$temp_id=$data{"id"};
chomp $temp_id;
# Get our values
$id{$temp_id} = $data{"id"};
$total_num_of_errors{$temp_id} = $data{"total_num_of_errors"};
$meeting_time{$temp_id} = $data{"meeting_time"};
$amount_to_be_inspected{$temp_id} = $data{"amount_to_be_inspected"};
$total_prep_time_hours{$temp_id} = $data{"total_prep_time_hours"};
$amount_actually_inspected{$temp_id} =
$data{"amount_actually_inspected"};
$amount_to_be_inspected{$temp_id} = $data{"amount_to_be_inspected"};
($phase{$temp_id}, $notused{$temp_id}) = split (/:/
,$data{"inspected_item_type"});
$row++;

}
print "\nRetrieved $row records\n";
my $now = localtime time;
print "$now\n";
}

[Jim]

I found the problem - it was an ODBC bug I believe. I had one of the
fields defined as decimal(5,2) and the value was 362.00. This reached
the limit of that field and caused the Query to stop everytime it hit
that record. Once I change the definition of that field to
decimal(8,2) the problem went away.

I should have been able to work around the problem by having the script
not stop when it hit the error. I commented out the exit and close
lines in the above code but that did not work. Am I missing something?

Thanks for your encouragement Ray.

Jim