subject:"Select with order by clause on a MySQL BDB table returning incorrect"

re: Re: Select with order by clause on a MySQL BDB table returning incorrect

2002-09-26 Thread Victoria Reznichenko


Sridhar,
Wednesday, September 25, 2002, 6:11:30 PM, you wrote:

 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.

SP Victoria,

SP As said earlier, i cannot reproduce the problem because i analyzed the
SP BDB tables yesterday.
SP If i don't analyze the tables for a week, then the above said problem
SP surfaces again. The 
SP application is still in test phase and will be put into production the
SP following Monday. I
SP strongly believe this issue is some how related to BDB index link list
SP statistics that might
SP be getting corrupted. I do not know if this is due to a bug, if so i
SP cannot reproduce it, until
SP i hit the problem again. Speaking of bug, would i abe able to submit
SP this as a bug ?

We need a repeatable testcase if this is a bug. Could you inform me if
it appears again? In my part I'll try to play with your table. Thanks for
CREATE TABLE!

SP I am seriously pondering if i had a mistake in choosing MySQL for our
SP transactional application.

What about InnoDB tables?


-- 
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

re: Select with order by clause on a MySQL BDB table returning incorrect

2002-09-25 Thread Victoria Reznichenko


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:
SPselect orders.order_uid, orders.status,
SP(to_days(now()) - to_days(orders.verification_date)) as age
SPfrom orders
SPwhere orders.status = 'verified'
SPorder 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

Re: Select with order by clause on a MySQL BDB table returning incorrect

2002-09-25 Thread Sridhar Peddireddy


[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:
 SPselect orders.order_uid, orders.status,
 SP(to_days(now()) - to_days(orders.verification_date)) as age
 SPfrom orders
 SPwhere orders.status = 'verified'
 SPorder 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 '-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:

Select with order by clause on a MySQL BDB table returning incorrect

2002-09-24 Thread Sridhar Peddireddy


Dear List,

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  
|  | | -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%';

re: Re: Select with order by clause on a MySQL BDB table returning incorrect

re: Select with order by clause on a MySQL BDB table returning incorrect

Re: Select with order by clause on a MySQL BDB table returning incorrect

Select with order by clause on a MySQL BDB table returning incorrect

4 matches

Site Navigation

Mail list logo

Footer information