RE: "DBD::mysql::st execute failed: Unknown MySQL error"?

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:[email protected]] 
Sent: Thursday, March 17, 2005 5:15 PM
To: Gordon Luk
Cc: [email protected]
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 <[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]