Hello,
Thank you very much for your response.
I came across those change notes in trying to discover the reason
for this problem, but didn't think it was a solution as I am using
MySQL 4.0.20a-nt-log and MySQL 4.1.12a-nt to test this on two machines.
In both cases. DBD::mysql was installed with those versions of MySQL
libraries.
Something that is unclear to me from the change notes is which
timeout value is being referred to. I have upped the timeout values
that were 30 seconds (net_read_timeout) and 60 seconds
(net_write_timeout) to 600 seconds. The other timeout variable
possibilities were already set much higher than 30 seconds. Is there
another timeout value that I am missing?
Regards.
Rob
Paul DuBois wrote:
It might be that DBD::mysql on your system is built with a MySQL client
library older than 4.0.16. Your message made me remember that there is
this item in the MySQL 4.0.16 change notes:
If you are using this release on Windows, you should upgrade at
least your clients (any program that uses
<filename>libmysql.lib</filename>) to 4.0.16 or above. This is
because the 4.0.15 release had a bug in the Windows client library
that causes Windows clients using the library to die with a
<literal>Lost connection to MySQL server during query</literal>
error for queries that take more than 30 seconds. This problem is
specific to Windows; clients on other platforms are unaffected.
This sounds similar to your report.
On 8/2/05 17:36, "Rob Craig" <[EMAIL PROTECTED]> wrote:
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.10.5/67 - Release Date: 8/9/2005
