471,108 Members | 1,300 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

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

Jul 23 '05 #1
5 1612
how do you get your data ? what tool are you using ?

"Jim" <jf*****@gmail.com> 在郵件
news:11**********************@g49g2000cwa.googlegr oups.com 中撰寫...
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

Jul 23 '05 #2
Jim
I am using a Perl script to pull out the data.

use Win32::ODBC;

Thank you,

Jim

Jul 23 '05 #3
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";
}

Jul 23 '05 #4
you should try to debug your program then.
adding more print statement to locate where does the error come from.

"Jim" <jf*****@gmail.com> 在郵件
news:11*********************@g47g2000cwa.googlegro ups.com 中撰寫...
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";
}

Jul 23 '05 #5
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

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Shabam | last post: by
reply views Thread by Philip Stoev | last post: by
reply views Thread by Shabam | last post: by
2 posts views Thread by bwana.mpoa | last post: by
2 posts views Thread by Steven D'Aprano | last post: by
2 posts views Thread by daniel | last post: by
1 post views Thread by Charles Crume | last post: by
1 post views Thread by cmgmyr | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.