Hi,
I have a perl script that uses DBI but it times out on queries that
take over 30 seconds. The same query works fine if I issue it directly
from the mysql client. I have included the program, the trace output
from the program, the server variables and the table definition. There
are approximately 8.5 million rows in this table. The computer is
Windows 2000 and perl version is 5.8.3. The message printed when it
times out is: execute failed: Lost connection to MySQL server during
query(2013).
Any help would be much appreciated.
Thanks.
Rob
perl script:
#!c:/perl/bin/perl.exe
use DBI;
connect....
DBI->trace(4);
$sql = "select count(DISTINCT seq) FROM Peptide;";
$sth = $dbh->prepare($sql) or warn "prepare failed:
$DBI::errstr($DBI::err)\n";
$rows = $sth->execute() or warn "execute failed: $DBI::errstr($DBI::err)\n";
@result=$sth->fetchrow_array();
$count = @result[0];
print "Count: $count\n";
$sth->finish();
disconnect....
trace output level 4:
DBI 1.42-ithread default trace level set to Ox4/0 (in pid 2804)
Note: perl is running without the recommended perl -w option
-> prepare for DBD::mysql::db (DBI::db=HASH(0x1bce098)~0x1bf9354
'select count(DISTINCT seq) FROM Peptide;') thr#15d4314
New DBI::st (for DBD::mysql::st, parent=DBI::db=HASH(0x1bf9354), id=)
dbih_setup_handle(DBI::st=HASH(0x1bf94bc)=>DBI::st=HASH(0x15d52e0),
DBD::mysql::st, 1bf94c8, Null!)
dbih_make_com(DBI::db=HASH(0x1bf9354), 1bf9c54, DBD::mysql::st, 208,
0) thr#15d4314
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0x1bf94bc) at timeouttest.pl line 11
-> execute for DBD::mysql::st (DBI::st=HASH(0x1bf94bc)~0x15d52e0)
thr#15d4314
-> dbd_st_execute for 01bbc538
Lost connection to MySQL server during query error 2013 recorded: Lost
connection to MySQL server during query
<- dbd_st_execute -2 rows
!! ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- execute= undef at timeouttest.pl line 12
-> $DBI::errstr (&) FETCH from lasth=HASH
>> DBD::mysql::st::errstr
<- $DBI::errstr= 'Lost connection to MySQL server during query'
-> $DBI::err (*) FETCH from lasth=HASH
<- $DBI::err= 2013
execute failed: Lost connection to MySQL server during query(2013)
!! ERROR: 2013 CLEARED by call to fetchrow_array method
-> fetchrow_array for DBD::mysql::st
(DBI::st=HASH(0x1bf94bc)~0x15d52e0) thr#15d4314
-> dbd_st_fetch for 01bbc538, chopblanks 0
fetch() without execute() error 19 recorded: fetch() without execute()
!! ERROR: 19 'fetch() without execute()' (err#0)
<- fetchrow_array= ( ) [0 items] row-1 at timeouttest.pl line 13
Count:
!! ERROR: 19 CLEARED by call to finish method
-> finish for DBD::mysql::st (DBI::st=HASH(0x1bf94bc)~0x15d52e0)
thr#15d4314
<- finish= 1 at timeouttest.pl line 17
-> disconnect for DBD::mysql::db (DBI::db=HASH(0x1bce098)~0x1bf9354)
thr#15d4314
&imp_dbh->mysql: 1bf9cac
<- disconnect= 1 at timeouttest.pl line 29 via timeouttest.pl line 19
Disconnected
-- DBI::END
-> disconnect_all for DBD::mysql::dr
(DBI::dr=HASH(0x1b52830)~0x1bce0bc) thr#15d4314
<- disconnect_all= (not implemented) at DBI.pm line 657 via
timeouttest.pl line 0
! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x1bf9354)~INNER)
thr#15d4314
! <- DESTROY= undef during global destruction
dbih_clearcom 0x1bce098 (com 0x1bf9c54, type 2) done.
! -> DESTROY for DBD::mysql::dr (DBI::dr=HASH(0x1bce0bc)~INNER)
thr#15d4314
! <- DESTROY= (not implemented) during global destruction
dbih_clearcom 0x1b52830 (com 0x1bf8f14, type 1) done.
! <> DESTROY for DBI::dr=HASH(0x1b52830) ignored (inner handle gone)
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x15d52e0)~INNER)
thr#15d4314
! <- DESTROY= undef during global destruction
dbih_clearcom 0x1bf94bc (com 0x1bfda04, type 3) done.
! <> DESTROY for DBI::st=HASH(0x1bf94bc) ignored (inner handle gone)
! <> DESTROY for DBI::db=HASH(0x1bce098) ignored (inner handle gone)
table definition:
+--------+---------------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+--------+---------------------------+------+-----+------------+----------------+
| pepid | int(10) unsigned zerofill | | PRI | NULL |
auto_increment |
| proid | int(10) unsigned zerofill | | MUL | 0000000000
| |
| seq | tinytext | | MUL |
| |
| mh | double | | MUL | 0
| |
| expect | double | | | 0
| |
| start | int(11) | | | 0
| |
| end | int(11) | | | 0
| |
| charge | tinyint(1) unsigned | YES | | NULL
| |
| delta | float | | | 0
| |
| dida | int(11) | | | 0
| |
| didb | int(11) | | | 0
| |
| didc | int(11) | | | 0
| |
+--------+---------------------------+------+-----+------------+----------------+
12 rows in set (0.34 sec)
variables:
mysql> show variables;
+---------------------------------+---------------------------
| Variable_name | Value
+---------------------------------+---------------------------
| back_log | 50
| basedir | C:\mysql\
| binlog_cache_size | 32768
| bulk_insert_buffer_size | 8388608
| character_set | latin1
| character_sets | latin1 big5 czech euc_kr g
| concurrent_insert | ON
| connect_timeout | 5
| convert_character_set |
| datadir | e:\gpmdb\data\
| default_week_format | 0
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 1800
| ft_boolean_syntax | + -><()~*:""&|
| ft_min_word_len | 3
| ft_max_word_len | 254
| ft_max_word_len_for_sort | 20
| ft_stopword_file | (built-in)
| have_bdb | NO
| have_crypt | NO
| have_innodb | YES
| have_isam | YES
| have_raid | NO
| have_symlink | YES
| have_openssl | NO
| have_query_cache | YES
| init_file |
| innodb_additional_mem_pool_size | 1048576
| innodb_buffer_pool_size | 8388608
| innodb_data_file_path | ibdata1:10M:autoextend
| innodb_data_home_dir |
| innodb_file_io_threads | 4
| innodb_force_recovery | 0
| innodb_thread_concurrency | 8
| innodb_flush_log_at_trx_commit | 1
| innodb_fast_shutdown | ON
| innodb_flush_method |
| innodb_lock_wait_timeout | 50
| innodb_log_arch_dir | .\
| innodb_log_archive | OFF
| innodb_log_buffer_size | 1048576
| innodb_log_file_size | 5242880
| innodb_log_files_in_group | 2
| innodb_log_group_home_dir | .\
| innodb_mirrored_log_groups | 1
| innodb_max_dirty_pages_pct | 90
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 402653184
| language | C:\mysql\share\english\
| large_files_support | ON
| license | GPL
| local_infile | ON
| log | OFF
| log_update | OFF
| log_bin | ON
| log_slave_updates | OFF
| log_slow_queries | OFF
| log_warnings | ON
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_file_system | OFF
| lower_case_table_names | 1
| max_allowed_packet | 33553408
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connections | 100
| max_connect_errors | 10
| max_delayed_threads | 20
| max_insert_delayed_threads | 20
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_relay_log_size | 0
| max_seeks_for_key | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_max_extra_sort_file_size | 268435456
| myisam_max_sort_file_size | 2147483647
| myisam_repair_threads | 1
| myisam_recover_options | OFF
| myisam_sort_buffer_size | 67108864
| named_pipe | OFF
| net_buffer_length | 1048576
| net_read_timeout | 600
| net_retry_count | 10
| net_write_timeout | 600
| new | OFF
| open_files_limit | 0
| pid_file | e:\gpmdb\data\hs3.pid
| log_error | .\hs3.err
| port | 3306
| protocol_version | 10
| query_alloc_block_size | 8192
| query_cache_limit | 1048576
| query_cache_size | 33554432
| query_cache_type | ON
| query_prealloc_size | 8192
| range_alloc_block_size | 2048
| read_buffer_size | 67104768
| read_only | OFF
| read_rnd_buffer_size | 262144
| rpl_recovery_rank | 0
| server_id | 1
| slave_net_timeout | 3600
| skip_external_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| sort_buffer_size | 67108856
| sql_mode | 0
| table_cache | 512
| table_type | MYISAM
| thread_cache_size | 8
| thread_stack | 196608
| tx_isolation | REPEATABLE-READ
| timezone | Central Daylight Time
| tmp_table_size | 33554432
| tmpdir | C:\WINNT\TEMP\
| transaction_alloc_block_size | 8192
| transaction_prealloc_size | 4096
| version | 4.0.20a-nt-log
| version_comment | Source distribution
| version_compile_os | NT
| wait_timeout | 28800
+---------------------------------+---------------------------
130 rows in set (0.00 sec)
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.7/60 - Release Date: 7/28/2005
