RE: "DBD::mysql::st execute failed: Unknown MySQL error"? - Gordon Luk - com.mysql.lists.perl

2 messages in com.mysql.lists.perlRE: "DBD::mysql::st execute failed: U...

From	Sent On	Attachments
Gordon Luk	24 Mar 2005 10:27
Gordon Luk	04 Apr 2005 09:26

Subject:	RE: "DBD::mysql::st execute failed: Unknown MySQL error"?
From:	Gordon Luk (gord...@dfafunds.com)
Date:	03/24/2005 10:27:28 AM
List:	com.mysql.lists.perl

I've been looking for more clues, because this is still happening. I enabled the server query log, but didn't see anything there. I was able to fix the problem temporarily again by running check, analyze, repair, and then optimize on the tables throwing errors. I will reply again if I find that this error is fixed permanently, but I'm hoping there was some table-level corruption in a table only used by that perl module.

If I run into more problems, I'll definitely try the mysql-general list. Thanks for the suggestion. As this is the Redhat-supplied version, I'd prefer to live with this version if possible. :) I already know that a source-compiled 4.1.9 on a single processor 2.4 kernel box works without these hitches.

-Gordon

-----Original Message----- From: Rudy Lippan [mailto:rlip...@remotelinux.com] Sent: Thursday, March 17, 2005 5:15 PM To: Gordon Luk Cc: pe...@lists.mysql.com Subject: RE: "DBD::mysql::st execute failed: Unknown MySQL error"?

On Thu, 17 Mar 2005, Gordon Luk wrote:

Date: Thu, 17 Mar 2005 14:12:07 -0800 From: Gordon Luk <gord...@dfafunds.com> To: pe...@lists.mysql.com Subject: RE: "DBD::mysql::st execute failed: Unknown MySQL error"?

This error has cropped up again. On an SQL query that was working fine

for a while, and suddenly stopped. All strings in the entire module are explicitly cast to _latin1, and it's still not helping. The query is still failing but only within this module. My previous fix of casting the strings doesn't solve the problem anymore. Help, anyone?

You might want to try logging the queries server-side to see if DBD::mysql is sending something weird, but my first impression is that shis sounds like a problem server-side, so I'd try asking on the mysql-general list were there are more mysql-hackers or playing around with different versions of the mysql server.

-----Original Message----- From: Gordon Luk [mailto:gord...@dfafunds.com] Sent: Wednesday, March 16, 2005 4:29 PM To: pe...@lists.mysql.com Subject: RE: "DBD::mysql::st execute failed: Unknown MySQL error"?

Regarding this error, I found that I could fix the problems by explicitly casting strings into _latin1 within my SQL queries inside the perl module. Needless to say, this is really annoying, because I have to go through a ton of SQL building statements and explicitly cast all quoted strings. Also, I don't know if this fix is addressing the root cause or if it's just temporary. We'll see.

I have already set the default client collation and stuff in my.cnf with the following lines:

default-character-set = latin1 default-collation = latin1_swedish_ci

So I don't see why I'm needing to do this ONLY in this particular perl module. Is there any way through DBI or DBD::mysql to explicitly set the collation at the beginning of the connection, so that I don't need to edit all my SQL?

Thanks in advance,

Gordon

************************************* Gordon Luk Web Programmer

Dimensional Fund Advisors 1299 Ocean Avenue, 1st Floor Santa Monica, CA 90401

-----Original Message----- From: Gordon Luk [mailto:gord...@dfafunds.com] Sent: Friday, March 11, 2005 9:20 AM To: pe...@lists.mysql.com Subject: "DBD::mysql::st execute failed: Unknown MySQL error"?

Hello,

I'm getting this error message from a custom written perl module that uses DBI to make connections and run queries on a local MySQL database. I've noticed that this happened once before, and I "solved" the problem earlier by restarting the MySQL server. Unfortunately, it has happened again after running the server for several days, and I do not know why.

The symptom: All MySQL queries fail during DBD::MySQL::st->execute with an "Unknown MySQL Error 2000" when called from within a custom perl module in mod_perl under Apache. This happens suddenly after running Apache and MySQL for some time. Restarting Apache or recompiling / reinstalling the custom perl module does not help. Last time I had to restart MySQL to get the thing working.

I searched high and low for more about this "Unknown MySQL Error," but I have found no solid information about it. Since I'm running a relatively new hardware/OS platform, I was wondering if someone on this list could help me debug it.

Some more background information: This is a RHEL 4.0ES box running the following software: * custom built Apache/mod_perl: # mm-1.3.1 # apache_1.3.33 to /opt/apache with modules below: # mod_ssl-2.8.22-1.3.33 (statically compiled) # mod_perl-1.29 (statically compiled) # php-4.3.10 (DSO) * HTML::Mason * the following mysql stock RH RPM's: mysqlclient10-3.23.58-4.RHEL4.1 mysql-devel-4.1.7-4.RHEL4.1 mysql-4.1.7-4.RHEL4.1 mysql-server-4.1.7-4.RHEL4.1 libdbi-dbd-mysql-0.6.5-10.RHEL4.1 * kernel 2.6.9-5.0.3.Elsmp * Apache::DBI is NOT installed The hardware is a dual processor Intel 3.2Ghz with 2GB of RAM.

Okay, so on to the gory details...

Here's the DBI trace log (log level 7(!)): -> prepare for DBD::mysql::db (DBI::db=HASH(0x91cd464)~0x91c5a34 'SELECT referencenumber_type FROM tblreturns_descriptions_singleportorbench WHERE port_or_bench_num = 34 ') thr#89b3950 New DBI::st (for DBD::mysql::st, parent=DBI::db=HASH(0x91c5a34), id=)

dbih_setup_handle(DBI::st=HASH(0x91da64c)=>DBI::st=HASH(0x91e0788),

DBD::mysql::st, 91df4cc, Null!) dbih_make_com(DBI::db=HASH(0x91c5a34), 91cd628, DBD::mysql::st, 208, 0) thr#89b3950 dbih_setup_attrib(DBI::st=HASH(0x91e0788), Err, DBI::db=HASH(0x91c5a34)) SCALAR(0x91cd470) (already defined) dbih_setup_attrib(DBI::st=HASH(0x91e0788), State, DBI::db=HASH(0x91c5a34)) SCALAR(0x91cd3bc) (already defined) dbih_setup_attrib(DBI::st=HASH(0x91e0788), Errstr, DBI::db=HASH(0x91c5a34)) SCALAR(0x91c59e0) (already defined) dbih_setup_attrib(DBI::st=HASH(0x91e0788), TraceLevel, DBI::db=HASH(0x91c5a34)) 0 (already defined) dbih_setup_attrib(DBI::st=HASH(0x91e0788), FetchHashKeyName, DBI::db=HASH(0x91c5a34)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0x91e0788), HandleError, DBI::db=HASH(0x91c5a34)) undef (not defined) Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x91da64c) at /usr/lib/perl5/site_perl/5.8.5/DFA/ReturnsCalc.pm line 120 via /usr/lib/perl5/site_perl/5.8.5/DFA/ReturnsCalc.pm line 300 -> execute for DBD::mysql::st (DBI::st=HASH(0x91da64c)~0x91e0788) thr#89b3950 -> dbd_st_execute for 0921631c Unknown MySQL error error 2000 recorded: Unknown MySQL error <- dbd_st_execute -1 rows !! ERROR: 2000 'Unknown MySQL error' <- execute= -1 at /usr/lib/perl5/site_perl/5.8.5/DFA/ReturnsCalc.pm line 121 <> DESTROY ignored for outer handle DBI::st=HASH(0x91da64c) (inner DBI::st=HASH(0x91e0788)) -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x91e0788)~INNER) thr#89b3950 error: 2000 'Unknown MySQL error' <- DESTROY= undef at /usr/lib/perl5/5.8.5/Carp.pm line 201 DESTROY (dbih_clearcom) (sth 0x91da64c 0x0, com 0x91cbf30, imp DBD::mysql::st): FLAGS 0x191: COMSET Warn RaiseError PrintError PARENT DBI::db=HASH(0x91c5a34) KIDS 0 (0 Active) IMP_DATA undef NUM_OF_FIELDS 0 NUM_OF_PARAMS 0 dbih_clearcom 0x91da64c (com 0x91cbf30, type 3) done.

<> DESTROY ignored for outer handle DBI::st=HASH(0x91d48c0) (inner DBI::st=HASH(0x91e749c)) -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x91e749c)~INNER) thr#89b3950 <- DESTROY= undef at /usr/lib/perl5/5.8.5/Carp.pm line 201 DESTROY (dbih_clearcom) (sth 0x91d48c0 0x0, com 0x91ec6d8, imp DBD::mysql::st): FLAGS 0x191: COMSET Warn RaiseError PrintError PARENT DBI::db=HASH(0x91bb63c) KIDS 0 (0 Active) IMP_DATA undef NUM_OF_FIELDS 2 NUM_OF_PARAMS 1 dbih_clearcom 0x91d48c0 (com 0x91ec6d8, type 3) done.

Here's the output from mysql's `show status` command.

mysql> show status; +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | Aborted_clients | 200 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 300598977 | | Bytes_sent | 838958417 | | Com_admin_commands | 61 | | Com_alter_db | 0 | | Com_alter_table | 8 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 427 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql | 0 | | Com_delete | 26819 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 241809 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 3277 | | Com_set_option | 303 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 10 | | Com_show_charsets | 152 | | Com_show_collations | 152 | | Com_show_column_types | 0 | | Com_show_create_db | 10 | | Com_show_create_table | 3 | | Com_show_databases | 85 | | Com_show_errors | 0 | | Com_show_fields | 85 | | Com_show_grants | 10 | | Com_show_innodb_status | 0 | | Com_show_keys | 47 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 3 | | Com_show_storage_engines | 0 | | Com_show_tables | 800 | | Com_show_variables | 304 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 534934 | | Com_update_multi | 0 | | Connections | 55327 | | Created_tmp_disk_tables | 86 | | Created_tmp_files | 115 | | Created_tmp_tables | 940 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 3 | | Handler_commit | 0 | | Handler_delete | 26818 | | Handler_discover | 0 | | Handler_read_first | 165 | | Handler_read_key | 1028483 | | Handler_read_next | 150349966 | | Handler_read_prev | 100 | | Handler_read_rnd | 94010 | | Handler_read_rnd_next | 142796851 | | Handler_rollback | 0 | | Handler_update | 849953 | | Handler_write | 1693354 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 321123 | | Key_blocks_used | 32464 | | Key_read_requests | 16497974 | | Key_reads | 60879 | | Key_write_requests | 2093183 | | Key_writes | 619632 | | Max_used_connections | 13 | | Not_flushed_delayed_rows | 0 | | Open_files | 211 | | Open_streams | 0 | | Open_tables | 108 | | Opened_tables | 134 | | Qcache_free_blocks | 6 | | Qcache_free_memory | 29166176 | | Qcache_hits | 83459 | | Qcache_inserts | 2831 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 443 | | Qcache_queries_in_cache | 2328 | | Qcache_total_blocks | 4730 | | Questions | 1228878 | | Rpl_status | NULL | | Select_full_join | 18 | | Select_full_range_join | 0 | | Select_range | 10 | | Select_range_check | 10 | | Select_scan | 1463 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 9 | | Sort_merge_passes | 264 | | Sort_range | 39 | | Sort_rows | 14200306 | | Sort_scan | 1005 | | Table_locks_immediate | 813877 | | Table_locks_waited | 0 | | Threads_cached | 7 | | Threads_connected | 1 | | Threads_created | 23 | | Threads_running | 1 | | Uptime | 752319 | +--------------------------+-----------+ 156 rows in set (0.00 sec)

mysql>

Here's the associated line from the Apache error log:

DBD::mysql::st execute failed: Unknown MySQL error at /usr/lib/perl5/site_perl/5.8.5/DFA/ReturnsCalc.pm line 121.

I was able to verify that multiple SQL queries from that perl module are broken by surrounding execute's with eval {} blocks and croaking with SQL statements. The same queries that are failing in the module are executing without incident through phpMyAdmin.

Any ideas of where to debug (or hopefully, how to fix this?). This will eventually become a production configuration, so I do not want to just be content with periodically restarting the server.

Since other perl modules and other queries are working fine on the rest of the HTML::Mason templates that have been authored, I am really stumped on this one. Nothing appears in the MySQL log in /var/log.

Thanks in advance,

Gordon Luk

************************************* Gordon Luk Web Programmer

Dimensional Fund Advisors 1299 Ocean Avenue, 1st Floor Santa Monica, CA 90401