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)
>
>
>
