[EMAIL PROTECTED] wrote:
>
> Sridhar,
> Wednesday, September 25, 2002, 12:23:29 AM, you wrote:
>
> SP> One of our MySQL db's containing BDB tables is exhibiting a strange
> SP> behavior.
> SP> A query consisting of an ORDER BY clause is returning wrong result set.
> SP> The
> SP> same query when used without an ORDER BY clause returns the correct
> SP> result
> SP> set.
>
> SP> Environment: Sun Solaris 2.7, MySQL 3.23.51, Table Type = BerkleyDB
>
> SP> Given below is the query for which we are seeing this issue:
> SP> select orders.order_uid, orders.status,
> SP> (to_days(now()) - to_days(orders.verification_date)) as age
> SP> from orders
> SP> where orders.status = 'verified'
> SP> order by orders.verification_date;
>
> SP> The above query returns:
> SP> +-----------+--------+------+
> SP> | order_uid | status | age |
> SP> +-----------+--------+------+
> SP> | 130 | new | NULL |
> SP> | 130 | new | NULL |
> SP> | 130 | new | NULL |
> SP> | 130 | new | NULL |
> SP> | 130 | new | NULL |
> SP> +-----------+--------+------+
>
> SP> What SHOULD have been returned is:
> SP> +-----------+----------+------+
> SP> | order_uid | status | age |
> SP> +-----------+----------+------+
> SP> | 57 | verified | 4 |
> SP> | 76 | verified | 4 |
> SP> | 79 | verified | 4 |
> SP> | 233 | verified | 3 |
> SP> | 234 | verified | 0 |
> SP> +-----------+----------+------+
>
> SP> Currently i get around this issue by analyzing the BDB tables, but this
> SP> seems to be a temporary solution. The issue is intermittent, keeps
> SP> surfacing
> SP> again after a certain no of days. Did not find any related bugs with the
> SP> MySQL version we use (3.23.51). From what i can understand so far, the
> SP> index
> SP> statistics related to the BDB tables are not getting updated.
>
> SP> What can be done to correct this issue ?
> SP> Are there any other alternative's to this issue ?
> SP> Would converting the BerkleyDB (BDB) tables to InnoDB tables solve the
> SP> issue ?
>
> I tested your query on the test BDB table and it worked well, here is
> ther result that I got:
> +-----------+----------+------+
> | order_uid | status | age |
> +-----------+----------+------+
> | 5 | verified | 501 |
> | 4 | verified | 267 |
> +-----------+----------+------+
> 2 rows in set (0.00 sec)
>
> Could you provide a repeatable test case?
>
> BTW if you send bug report, send output of SHOW CREATE TABLE or output
> of mysqldump, not the output of DESC table_name.
>
> I tested it on 3.23.52.
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Victoria Reznichenko
> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
> <___/ www.mysql.com
>
> ---------------------------------------------------------------------
> 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
Victoria,
As said earlier, i cannot reproduce the problem because i analyzed the
BDB tables yesterday.
If i don't analyze the tables for a week, then the above said problem
surfaces again. The
application is still in test phase and will be put into production the
following Monday. I
strongly believe this issue is some how related to BDB index link list
statistics that might
be getting corrupted. I do not know if this is due to a bug, if so i
cannot reproduce it, until
i hit the problem again. Speaking of bug, would i abe able to submit
this as a bug ?
I am seriously pondering if i had a mistake in choosing MySQL for our
transactional application.
Here's the 'create table':
CREATE TABLE `orders` (
`order_uid` bigint(20) NOT NULL auto_increment,
`creation_date` datetime NOT NULL default '0000-00-00 00:00:00',
`modified_date` timestamp(14) NOT NULL,
`status`
enum('inprogress','new','submitted','verified','fulfilled','error','canceled')
default 'inprogress',
`verification_num` char(10) default NULL,
`verification_method`
enum('third_party','customer_online','customer_fax','none') default
'none',
`batch_number` bigint(20) default NULL,
`verification_date` datetime default NULL,
`salesmaker_fid` bigint(20) default NULL,
`legalese_fid` bigint(20) NOT NULL default '0',
`loa_sig` char(60) NOT NULL default '',
`sa_sig` char(60) NOT NULL default '',
`sa_signed_date` datetime default NULL,
PRIMARY KEY (`order_uid`),
KEY `idx01_orders` (`legalese_fid`)
) TYPE=BerkeleyDB
Thanks
Sri
---------------------------------------------------------------------
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
