RE: BDB table : different results on adjacent queries
Hi Dana -Original Message- From: Dana Powers [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 19, 2001 7:19 PM To: [EMAIL PROTECTED] Cc: mysql mailing list (E-Mail) Subject: Re: BDB table : different results on adjacent queries What is the query you are running + what version of mysql are you using? dpk It was a simple 'select * from table where a=value and b=value'. Version: 3.23.41, compiled with BDB and INNODB support, running on Linux (SuSE 7.0). The table we used had the following structure: a varchar(36) not null, pri b varchar(50) null c varchar(4) null d varchar(50) null e decimal (10,0) null f varchar(36) null, mul g varchar(36) null, mul h varchar(50) not null i varchar(50) null there are 3 non_unique keys defined: f+b f+d g column f is filled with all the same values (262 records) the last test was performed with the command line tool mysql. query: select count(*) from table where f='the only value in that column'; count(*) = 262 - ok. (the value in column f is a GUID which uses the full length of the field.) I repeated the query several times, with always the same positive results. Then I opened another connection (as a different user), using the same database, applied my query, and ... OOPS ... count(*) = 0 ! I turned back to the other client and again performed the same query (using the commandline buffer). Now the result of the count was 0 also there !!! Next I tried another application: mysql_navigator (1.2.4), the same query, count was 262, ok. next try: added and b = '4004' to the where clause so that the count should be 1 as there is only one record with '4004' in column 'b'. result: count was 0 ! another try on the previous select : count is 2 instead of 262 ! so what ??? replaced 'count(*)' by '*': one of the resulting records is the one with '4004' in column 'b', the other one's 'b' value is 400403. There is another record in the table beginning with 4004 in column 'b' : value '40040307', but this one wasn't selected. I hope you have an idea about this very weird behaviour. Thanx in advance Andy - 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: BDB table : different results on adjacent queries
Try running ANALYZE TABLE x; and redoing the query. If this fixes your query, there is some index corruption going on. Are there any other queries going through the system during these tests, or is it only your 2 threads? There were some BDB index bugs fixed in the last few mysql releases, but 3.23.41 should be fine. Im using .42 and having no more problems. dpk - Original Message - From: [EMAIL PROTECTED] To: 'Dana Powers' [EMAIL PROTECTED] Cc: mysql mailing list (E-Mail) [EMAIL PROTECTED] Sent: Thursday, September 20, 2001 7:23 AM Subject: RE: BDB table : different results on adjacent queries Hi Dana -Original Message- From: Dana Powers [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 19, 2001 7:19 PM To: [EMAIL PROTECTED] Cc: mysql mailing list (E-Mail) Subject: Re: BDB table : different results on adjacent queries What is the query you are running + what version of mysql are you using? dpk It was a simple 'select * from table where a=value and b=value'. Version: 3.23.41, compiled with BDB and INNODB support, running on Linux (SuSE 7.0). The table we used had the following structure: a varchar(36) not null, pri b varchar(50) null c varchar(4) null d varchar(50) null e decimal (10,0) null f varchar(36) null, mul g varchar(36) null, mul h varchar(50) not null i varchar(50) null there are 3 non_unique keys defined: f+b f+d g column f is filled with all the same values (262 records) the last test was performed with the command line tool mysql. query: select count(*) from table where f='the only value in that column'; count(*) = 262 - ok. (the value in column f is a GUID which uses the full length of the field.) I repeated the query several times, with always the same positive results. Then I opened another connection (as a different user), using the same database, applied my query, and ... OOPS ... count(*) = 0 ! I turned back to the other client and again performed the same query (using the commandline buffer). Now the result of the count was 0 also there !!! Next I tried another application: mysql_navigator (1.2.4), the same query, count was 262, ok. next try: added and b = '4004' to the where clause so that the count should be 1 as there is only one record with '4004' in column 'b'. result: count was 0 ! another try on the previous select : count is 2 instead of 262 ! so what ??? replaced 'count(*)' by '*': one of the resulting records is the one with '4004' in column 'b', the other one's 'b' value is 400403. There is another record in the table beginning with 4004 in column 'b' : value '40040307', but this one wasn't selected. I hope you have an idea about this very weird behaviour. Thanx in advance Andy - 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
BDB table : different results on adjacent queries
hi all we encountered the following: - altered table type to 'BDB' - did 2 identical queries - got different result sets ! after restart of MySQL the very next query gave the correct results, the next did NOT. any ideas? Thanx in advance Andreas Schoelver -- Andreas Schoelver (AS) mailto: [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] - 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: BDB table : different results on adjacent queries
What is the query you are running + what version of mysql are you using? dpk - Original Message - From: [EMAIL PROTECTED] To: mysql mailing list (E-Mail) [EMAIL PROTECTED] Sent: Wednesday, September 19, 2001 2:12 AM Subject: BDB table : different results on adjacent queries hi all we encountered the following: - altered table type to 'BDB' - did 2 identical queries - got different result sets ! after restart of MySQL the very next query gave the correct results, the next did NOT. any ideas? Thanx in advance Andreas Schoelver -- Andreas Schoelver (AS) mailto: [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] - 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 - 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
