Dear List, Sorry for reposting the issue, once again
One of our MySQL db's containing BDB tables is exhibiting a strange behavior. A query consisting of an ORDER BY clause is returning wrong result set. The same query when used without an ORDER BY clause returns the correct result set. Environment: Sun Solaris 2.7, MySQL 3.23.51, Table Type = BerkleyDB Given below is the query for which we are seeing this issue: select orders.order_uid, orders.status, (to_days(now()) - to_days(orders.verification_date)) as age from orders where orders.status = 'verified' order by orders.verification_date; The above query returns: +-----------+--------+------+ | order_uid | status | age | +-----------+--------+------+ | 130 | new | NULL | | 130 | new | NULL | | 130 | new | NULL | | 130 | new | NULL | | 130 | new | NULL | +-----------+--------+------+ What SHOULD have been returned is: +-----------+----------+------+ | order_uid | status | age | +-----------+----------+------+ | 57 | verified | 4 | | 76 | verified | 4 | | 79 | verified | 4 | | 233 | verified | 3 | | 234 | verified | 0 | +-----------+----------+------+ Currently i get around this issue by analyzing the BDB tables, but this seems to be a temporary solution. The issue is intermittent, keeps surfacing again after a certain no of days. Did not find any related bugs with the MySQL version we use (3.23.51). From what i can understand so far, the index statistics related to the BDB tables are not getting updated. What can be done to correct this issue ? Are there any other alternative's to this issue ? Would converting the BerkleyDB (BDB) tables to InnoDB tables solve the issue ? Here are a few other details about the BDB table that is exhibiting this problem: kcaset02:{}mysql sbiz Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21716 to server version: 3.23.51-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> desc orders; +---------------------+--------------------------------------------------------------------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+--------------------------------------------------------------------------------+------+-----+---------------------+----------------+ | order_uid | bigint(20) | | PRI | NULL | auto_increment | | creation_date | datetime | | | 0000-00-00 00:00:00 | | | modified_date | timestamp(14) | YES | | NULL | | | status | enum('inprogress','new','submitted','verified','fulfilled','error','canceled') | YES | | inprogress | | | verification_num | char(10) | YES | | NULL | | | verification_method | enum('third_party','customer_online','customer_fax','none') | YES | | none | | | batch_number | bigint(20) | YES | | NULL | | | verification_date | datetime | YES | | NULL | | | salesmaker_fid | bigint(20) | YES | | NULL | | | legalese_fid | bigint(20) | | MUL | 0 | | | loa_sig | char(60) | | | | | | sa_sig | char(60) | | | | | | sa_signed_date | datetime | YES | | NULL | | +---------------------+--------------------------------------------------------------------------------+------+-----+---------------------+----------------+ 13 rows in set (0.00 sec) mysql> show table status like 'ord%'; +--------+------------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+---------+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--------+------------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+---------+ | orders | BerkeleyDB | Fixed | 236 | 0 | 0 | NULL | 0 | 0 | 237 | NULL | NULL | NULL | | | +--------+------------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+---------+ 1 row in set (0.01 sec) mysql> show index from orders; +--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+---------+ | orders | 0 | PRIMARY | 1 | order_uid | A | 236 | NULL | NULL | | | orders | 1 | idx01_orders | 1 | legalese_fid | A | 1 | NULL | NULL | | +--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+---------+ 2 rows in set (0.00 sec) mysql> show variables like 'have%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_bdb | YES | | have_gemini | NO | | have_innodb | DISABLED | | have_isam | YES | | have_raid | NO | | have_openssl | NO | +---------------+----------+ 6 rows in set (0.00 sec) I would appreciate if one of you can shed some light on this issue. Thanks psr --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php