RE: "DBD::mysql::st execute failed: Unknown MySQL error"?
Rudy Lippan <[email protected]>
| Newsgroups | gmane.comp.db.mysql.perl |
|---|---|
| Message-ID | <[email protected]> |
On Thu, 17 Mar 2005, Gordon Luk wrote: > Date: Thu, 17 Mar 2005 14:12:07 -0800 > From: Gordon Luk <[email protected]> > To: [email protected] > 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:[email protected]] > Sent: Wednesday, March 16, 2005 4:29 PM > To: [email protected] > 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 > > 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] > > > > > > > -- MySQL Perl Mailing List For list archives: http://lists.mysql.com/perl To unsubscribe: http://lists.mysql.com/[email protected]