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
