RE: "DBD::mysql::st execute failed: Unknown MySQL error"?
"Gordon Luk" <[email protected]>
| Newsgroups | gmane.comp.db.mysql.perl |
|---|---|
| Message-ID | <[email protected]> |
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 email: gordon.luk AT dfafunds.com ************************************* -----Original Message----- From: Gordon Luk [mailto:[email protected]] Sent: Friday, March 11, 2005 9:20 AM To: [email protected] 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 email: gordon.luk AT dfafunds.com ************************************* -- MySQL Perl Mailing List For list archives: http://lists.mysql.com/perl To unsubscribe: http://lists.mysql.com/[email protected] -- MySQL Perl Mailing List For list archives: http://lists.mysql.com/perl To unsubscribe: http://lists.mysql.com/[email protected]