You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by jrgchip <ch...@datamology.com> on 2009/01/28 06:10:01 UTC
Record not found in some SQL - Bug?
I have a "Product" table with a "Num" column that contains a record that is
only accessible by some SQL and not others. I have tested this by JDBC
access from my Java app as well was from IJ directly.
ij> select "Num", length("Num") as "Len" from app."Product" where "Num" like
'HG1549%';
Num |Len
----------------------------
HG15490 |7
HG15493 |7
HG15497 |7 <== Found as expected
HG15499 |7
4 rows selected
ij> select "Num" from app."Product" where "Num" = 'HG15490';
Num
----------------
HG15490 <== Found as expected
1 row selected
ij> select "Num" from app."Product" where "Num" = 'HG15493';
Num
----------------
HG15493 <== Found as expected
1 row selected
ij> select "Num" from app."Product" where "Num" = 'HG15499';
Num
----------------
HG15499 <== Found as expected
1 row selected
ij> select "Num" from app."Product" where "Num" = 'HG15497';
Num
----------------
0 rows selected <== Not found!!!
What could possibly hide the 'HG15497' record from the last SELECT?
And it's not just a matter of equality versus inequality...as the following
SQL does return the record:
SELECT I."STYLE" FROM TEMP."ZJVINV2" AS I INNER JOIN APP."Product" AS P
ON I."STYLE" = P."Num" WHERE I."STYLE" = 'HG15497';
I am using Java DB v10.3.2.1.
--
View this message in context: http://www.nabble.com/Record-not-found-in-some-SQL---Bug--tp21700110p21700110.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Record not found in some SQL - Bug?
Posted by Kristian Waagan <Kr...@Sun.COM>.
jrgchip wrote:
>
> oysteing wrote:
>> Have you tried to run the Derby consistency checker?
>>
>
> Yes...it reports all 44 tables as OK per SYSCS_CHECK_TABLE.
>
>
>
> Kristian Waagan-4 wrote:
>> Be aware that 10.3.1.4 and 10.3.2.1 had a corruption bug, mostly (or
>> only) seen on Windows.
>>
>
> Can you point me to information about that corruption bug?
Here's something to start with (see "IMPORTANT NOTICE"):
http://db.apache.org/derby/releases/release-10.3.3.0.cgi
There is more info under the Jira issue.
>
>
>
> Derby-3 wrote:
>> If you reload the data, the problem goes away. This would imply that it's
>> a data issue.
>>
>
>
> The data is from an ASCII file (with no special chars) and is loaded with
> SYSCS_IMPORT_TABLE. But I conclude the load, itself, is not the problem
> because the data is retrievable after initial load. It is only after the DB
> is used for a while that something becomes unreadable...in this case the
> HG15497 record noted in my post.
>
>
>
> Derby-3 wrote:
>> Since your database is in a 'broken' state, what happens if you create a
>> copy of the table and select the rows that match your like 'xxxx%' clause
>> and insert them in to your temp duplicate table.
>>
>
> I am able to copy the table and select the "bad" row from the copy per the
> following sequence...
Sounds more and more like a Derby bug to me.
Are there any indexes/constraints on the newly created table?
--
Kristian
>
> ij> CREATE TABLE app."ProductCopy" AS SELECT * FROM app."Product" WITH NO
> DATA;
> 0 rows inserted/updated/deleted
> ij> insert into app."ProductCopy" select * from app."Product";
> 34536 rows inserted/updated/deleted
> ij> select "Num" from app."Product" where "Num" = 'HG15497';
> Num
> ----------------
>
> 0 rows selected
> ij> select "Num" from app."ProductCopy" where "Num" = 'HG15497';
> Num
> ----------------
> HG15497
>
> 1 row selected
>
RE: Record not found in some SQL - Bug?
Posted by de...@segel.com.
Outch!
So if you drop and recreate the index, the problem should go away.
If that's true, then it's a bug and the only work around is going to be a
drop and recreate of the index. (Not good.)
This should also be a repeatable problem too.
The issue isn't in the table but the index.
Or am I missing something?
-Mike
> -----Original Message-----
> From: jrgchip [mailto:chip@datamology.com]
> Sent: Friday, January 30, 2009 9:10 AM
> To: derby-user@db.apache.org
> Subject: Re: Record not found in some SQL - Bug?
>
>
>
> jrgchip wrote:
> >
> > The failure still occurs.
> >
>
> Per investigation in DERBY-4032, it appears that there is indeed a bug in
> Derby. The problem here is that there are two records for 'HG15497' in
> the
> unique index on the Product table (one active and one marked as deleted)
> and
> that the B-tree scan logic does not account for that possibility. So,
> depending on the access path chosen and the logic used to satisfy the
> query,
> the record might be found or not found.
>
> --
> View this message in context: http://www.nabble.com/Record-not-found-in-
> some-SQL---Bug--tp21700110p21748874.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Record not found in some SQL - Bug?
Posted by jrgchip <ch...@datamology.com>.
jrgchip wrote:
>
> The failure still occurs.
>
Per investigation in DERBY-4032, it appears that there is indeed a bug in
Derby. The problem here is that there are two records for 'HG15497' in the
unique index on the Product table (one active and one marked as deleted) and
that the B-tree scan logic does not account for that possibility. So,
depending on the access path chosen and the logic used to satisfy the query,
the record might be found or not found.
--
View this message in context: http://www.nabble.com/Record-not-found-in-some-SQL---Bug--tp21700110p21748874.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Record not found in some SQL - Bug?
Posted by jrgchip <ch...@datamology.com>.
Rick Hillegas-2 wrote:
>
> Upgrading to 10.3.3.0 will remove this theory from consideration.
>
I tested with v10.3.3.0 and v10.4.2.0...to no avail. The failure still
occurs.
--
View this message in context: http://www.nabble.com/Record-not-found-in-some-SQL---Bug--tp21700110p21727785.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Record not found in some SQL - Bug?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Rick Hillegas <Ri...@Sun.COM> writes:
> jrgchip wrote:
>> Can you point me to information about that corruption bug?
>>
> Please see the release notes for 10.3.3.0 (
> http://db.apache.org/derby/releases/release-10.3.3.0.cgi ) Detailed
> information can be found on the associated bug report:
> http://issues.apache.org/jira/browse/DERBY-3347 The symptoms in that
> bug report do not match your bug report but maybe the bug manifests
> itself in other ways. Upgrading to 10.3.3.0 will remove this theory
> from consideration.
Just to clarify, upgrading to 10.3.3.0 will not fix the corruption on an
existing database. Only recovery from an uncorrupted backup will do
that. But if it's data corruption, I don't think it's the same as
DERBY-3347 because
1) DERBY-3347 only caused corruption on parts of the page, so it's
very likely that we'd see a checksum error when reading the page from
disk
2) even if the checksum didn't detect the corruption, I think it's
very unlikely that the exact same row should be affected both in the
base table and in the index (as one experiment in DERBY-4032
indicates) if this is DERBY-3347
--
Knut Anders
Re: Record not found in some SQL - Bug?
Posted by Rick Hillegas <Ri...@Sun.COM>.
jrgchip wrote:
> oysteing wrote:
>
>> Have you tried to run the Derby consistency checker?
>>
>>
>
> Yes...it reports all 44 tables as OK per SYSCS_CHECK_TABLE.
>
>
>
> Kristian Waagan-4 wrote:
>
>> Be aware that 10.3.1.4 and 10.3.2.1 had a corruption bug, mostly (or
>> only) seen on Windows.
>>
>>
>
> Can you point me to information about that corruption bug?
>
Please see the release notes for 10.3.3.0 (
http://db.apache.org/derby/releases/release-10.3.3.0.cgi ) Detailed
information can be found on the associated bug report:
http://issues.apache.org/jira/browse/DERBY-3347 The symptoms in that bug
report do not match your bug report but maybe the bug manifests itself
in other ways. Upgrading to 10.3.3.0 will remove this theory from
consideration.
Hope this helps,
-Rick
>
>
> Derby-3 wrote:
>
>> If you reload the data, the problem goes away. This would imply that it's
>> a data issue.
>>
>>
>
>
> The data is from an ASCII file (with no special chars) and is loaded with
> SYSCS_IMPORT_TABLE. But I conclude the load, itself, is not the problem
> because the data is retrievable after initial load. It is only after the DB
> is used for a while that something becomes unreadable...in this case the
> HG15497 record noted in my post.
>
>
>
> Derby-3 wrote:
>
>> Since your database is in a 'broken' state, what happens if you create a
>> copy of the table and select the rows that match your like 'xxxx%' clause
>> and insert them in to your temp duplicate table.
>>
>>
>
> I am able to copy the table and select the "bad" row from the copy per the
> following sequence...
>
> ij> CREATE TABLE app."ProductCopy" AS SELECT * FROM app."Product" WITH NO
> DATA;
> 0 rows inserted/updated/deleted
> ij> insert into app."ProductCopy" select * from app."Product";
> 34536 rows inserted/updated/deleted
> ij> select "Num" from app."Product" where "Num" = 'HG15497';
> Num
> ----------------
>
> 0 rows selected
> ij> select "Num" from app."ProductCopy" where "Num" = 'HG15497';
> Num
> ----------------
> HG15497
>
> 1 row selected
>
>
Re: Record not found in some SQL - Bug?
Posted by jrgchip <ch...@datamology.com>.
oysteing wrote:
>
> Have you tried to run the Derby consistency checker?
>
Yes...it reports all 44 tables as OK per SYSCS_CHECK_TABLE.
Kristian Waagan-4 wrote:
>
> Be aware that 10.3.1.4 and 10.3.2.1 had a corruption bug, mostly (or
> only) seen on Windows.
>
Can you point me to information about that corruption bug?
Derby-3 wrote:
>
> If you reload the data, the problem goes away. This would imply that it's
> a data issue.
>
The data is from an ASCII file (with no special chars) and is loaded with
SYSCS_IMPORT_TABLE. But I conclude the load, itself, is not the problem
because the data is retrievable after initial load. It is only after the DB
is used for a while that something becomes unreadable...in this case the
HG15497 record noted in my post.
Derby-3 wrote:
>
> Since your database is in a 'broken' state, what happens if you create a
> copy of the table and select the rows that match your like 'xxxx%' clause
> and insert them in to your temp duplicate table.
>
I am able to copy the table and select the "bad" row from the copy per the
following sequence...
ij> CREATE TABLE app."ProductCopy" AS SELECT * FROM app."Product" WITH NO
DATA;
0 rows inserted/updated/deleted
ij> insert into app."ProductCopy" select * from app."Product";
34536 rows inserted/updated/deleted
ij> select "Num" from app."Product" where "Num" = 'HG15497';
Num
----------------
0 rows selected
ij> select "Num" from app."ProductCopy" where "Num" = 'HG15497';
Num
----------------
HG15497
1 row selected
--
View this message in context: http://www.nabble.com/Record-not-found-in-some-SQL---Bug--tp21700110p21714590.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Record not found in some SQL - Bug?
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Have you tried to run the Derby consistency checker? (See
http://wiki.apache.org/db-derby/DatabaseConsistencyCheck).
Maybe that could give some clue about what is wrong.
--
Øystein
jrgchip wrote:
> I have reported the problem as
> https://issues.apache.org/jira/browse/DERBY-4032.
>
> The problem is reproducible given the current state of the DB. However, if
> I reload the data, the problem goes away. So I conclude that use of the DB
> has altered its state in some way as to cause this problem to arise.
>
> I do not know if it is a single row that has the problem. The Java app
> failed when it processed this record (because the record was "not found")
> and my investigation into the failure led me to discovery of this anomaly.
> There may be others. I have had two very similar failures over the last 3
> months which I did not investigate as deeply...instead rebuilding the DB
> from scratch. So it appears the problem does arise occasionally...at least
> in 10.3.2.1.
>
> It's clear the SQL returns incorrect results. I am hoping someone else has
> seen similar behavior and can point me to a solution.
>
>
> jrgchip wrote:
>> I have a "Product" table with a "Num" column that contains a record that
>> is only accessible by some SQL and not others.
>>
>
Re: Record not found in some SQL - Bug?
Posted by Kristian Waagan <Kr...@Sun.COM>.
derby@segel.com wrote:
>
>> -----Original Message-----
>> From: jrgchip [mailto:chip@datamology.com]
>> Sent: Wednesday, January 28, 2009 10:10 AM
>> To: derby-user@db.apache.org
>> Subject: Re: Record not found in some SQL - Bug?
[ snip ]
>
> How did you load the data?
>
> What character set(s) are you using? Latin-1 or UTF-8?
>
> Just out of curiosity, if you are using UTF-8 and you have a 2 byte set of
> characters, when you use the length() function, do you see the number of
> bytes or the number of characters. By this I mean suppose you have 'cat' but
> each character is 2 bytes wide. Does length() return 3 or does it return 6?
FYI,
Derby should be returning the number of characters.
The on-disk format of character values in Derby is the modified UTF-8
encoding (see java.io.DataInput).
This information doesn't make neither the bug nor bad-import theory
invalid, of course :)
Be aware that 10.3.1.4 and 10.3.2.1 had a corruption bug, mostly (or
only) seen on Windows. It may or may not be related to the problem
described. In any case, you should move to Derby 10.4 or 10.3.3.0.
--
Kristian
>
> My guess is that you have some corruption in your load file. But that's only
> a SWAG and you haven't presented enough info on this.
>
> Since your database is in a 'broken' state, what happens if you create a
> copy of the table and select the rows that match your like 'xxxx%' clause
> and insert them in to your temp duplicate table.
>
> When you query the bogus row, do you get it or do you still get an error?
>
> I'm pretty hesitant to call this case a bug...
>
> In using other databases, I've had similar 'ghosts' but they all turned out
> to be from corrupted input files.
>
> HTH
>
> -Mike
>
>
>
RE: Record not found in some SQL - Bug?
Posted by de...@segel.com.
> -----Original Message-----
> From: jrgchip [mailto:chip@datamology.com]
> Sent: Wednesday, January 28, 2009 10:10 AM
> To: derby-user@db.apache.org
> Subject: Re: Record not found in some SQL - Bug?
>
>
> I have reported the problem as
> https://issues.apache.org/jira/browse/DERBY-4032.
>
> The problem is reproducible given the current state of the DB. However,
> if
> I reload the data, the problem goes away. So I conclude that use of the
> DB
> has altered its state in some way as to cause this problem to arise.
>
> I do not know if it is a single row that has the problem. The Java app
> failed when it processed this record (because the record was "not found")
> and my investigation into the failure led me to discovery of this anomaly.
> There may be others. I have had two very similar failures over the last 3
> months which I did not investigate as deeply...instead rebuilding the DB
> from scratch. So it appears the problem does arise occasionally...at
> least
> in 10.3.2.1.
>
> It's clear the SQL returns incorrect results. I am hoping someone else
> has
> seen similar behavior and can point me to a solution.
>
Ok,
Something doesn't sound right.
If you reload the data, the problem goes away.
This would imply that it's a data issue.
How are you loading the data in to the database?
If the data is in a flat file, can you use od to see what's in the data
file? Is there some funky character at the end of the troubled row?
If you reload the data, can you still reproduce the problem?
If you can reproduce the problem does it always happen with the same
row/record?
If you cut and paste the record to a different location in your load file
(assuming that you're loading from a file) does the same row get screwed up
or does another row get screwed up?
If you are on unix, can you make a copy of the flat file and then using vi,
truncate the data at the end of the row? (Just in case there's an invisible
bad character hiding.)
How did you load the data?
What character set(s) are you using? Latin-1 or UTF-8?
Just out of curiosity, if you are using UTF-8 and you have a 2 byte set of
characters, when you use the length() function, do you see the number of
bytes or the number of characters. By this I mean suppose you have 'cat' but
each character is 2 bytes wide. Does length() return 3 or does it return 6?
My guess is that you have some corruption in your load file. But that's only
a SWAG and you haven't presented enough info on this.
Since your database is in a 'broken' state, what happens if you create a
copy of the table and select the rows that match your like 'xxxx%' clause
and insert them in to your temp duplicate table.
When you query the bogus row, do you get it or do you still get an error?
I'm pretty hesitant to call this case a bug...
In using other databases, I've had similar 'ghosts' but they all turned out
to be from corrupted input files.
HTH
-Mike
Re: Record not found in some SQL - Bug?
Posted by jrgchip <ch...@datamology.com>.
I have reported the problem as
https://issues.apache.org/jira/browse/DERBY-4032.
The problem is reproducible given the current state of the DB. However, if
I reload the data, the problem goes away. So I conclude that use of the DB
has altered its state in some way as to cause this problem to arise.
I do not know if it is a single row that has the problem. The Java app
failed when it processed this record (because the record was "not found")
and my investigation into the failure led me to discovery of this anomaly.
There may be others. I have had two very similar failures over the last 3
months which I did not investigate as deeply...instead rebuilding the DB
from scratch. So it appears the problem does arise occasionally...at least
in 10.3.2.1.
It's clear the SQL returns incorrect results. I am hoping someone else has
seen similar behavior and can point me to a solution.
jrgchip wrote:
>
> I have a "Product" table with a "Num" column that contains a record that
> is only accessible by some SQL and not others.
>
--
View this message in context: http://www.nabble.com/Record-not-found-in-some-SQL---Bug--tp21700110p21708823.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Record not found in some SQL - Bug?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
jrgchip <ch...@datamology.com> writes:
> I have a "Product" table with a "Num" column that contains a record that is
> only accessible by some SQL and not others. I have tested this by JDBC
> access from my Java app as well was from IJ directly.
>
> ij> select "Num", length("Num") as "Len" from app."Product" where "Num" like
> 'HG1549%';
> Num |Len
> ----------------------------
> HG15490 |7
> HG15493 |7
> HG15497 |7 <== Found as expected
> HG15499 |7
> 4 rows selected
>
> ij> select "Num" from app."Product" where "Num" = 'HG15490';
> Num
> ----------------
> HG15490 <== Found as expected
> 1 row selected
>
> ij> select "Num" from app."Product" where "Num" = 'HG15493';
> Num
> ----------------
> HG15493 <== Found as expected
> 1 row selected
>
> ij> select "Num" from app."Product" where "Num" = 'HG15499';
> Num
> ----------------
> HG15499 <== Found as expected
> 1 row selected
>
> ij> select "Num" from app."Product" where "Num" = 'HG15497';
> Num
> ----------------
> 0 rows selected <== Not found!!!
>
> What could possibly hide the 'HG15497' record from the last SELECT?
I don't know, but it definitely looks like a bug. Please file a bug
report at https://issues.apache.org/jira/browse/DERBY so that we can
track the problem. In the bug report, please include the CREATE TABLE
and CREATE INDEX statements used to create the Product table and its
indexes. Or even better, if possible, a script to reproduce the problem.
--
Knut Anders
RE: Record not found in some SQL - Bug?
Posted by de...@segel.com.
Is this reproducible on all tries?
The point of the question is to understand why you have only one row that is
not working.
What happens if you reload the data and run the test again?
Do you get the same results?
Are you loading from a file? Is there a corrupt character?
The length() function. Is that the length of the string in bytes or the
number of characters? (Hint: Think Unicode)
Before calling this a bug, lets think about where you can have issues and
then work through those issues before saying it's a bug.
Just some thoughts...
-Mike
> -----Original Message-----
> From: jrgchip [mailto:chip@datamology.com]
> Sent: Tuesday, January 27, 2009 11:10 PM
> To: derby-user@db.apache.org
> Subject: Record not found in some SQL - Bug?
>
>
> I have a "Product" table with a "Num" column that contains a record that
> is
> only accessible by some SQL and not others. I have tested this by JDBC
> access from my Java app as well was from IJ directly.
>
> ij> select "Num", length("Num") as "Len" from app."Product" where "Num"
> like
> 'HG1549%';
> Num |Len
> ----------------------------
> HG15490 |7
> HG15493 |7
> HG15497 |7 <== Found as expected
> HG15499 |7
> 4 rows selected
>
> ij> select "Num" from app."Product" where "Num" = 'HG15490';
> Num
> ----------------
> HG15490 <== Found as expected
> 1 row selected
>
> ij> select "Num" from app."Product" where "Num" = 'HG15493';
> Num
> ----------------
> HG15493 <== Found as expected
> 1 row selected
>
> ij> select "Num" from app."Product" where "Num" = 'HG15499';
> Num
> ----------------
> HG15499 <== Found as expected
> 1 row selected
>
> ij> select "Num" from app."Product" where "Num" = 'HG15497';
> Num
> ----------------
> 0 rows selected <== Not found!!!
>
> What could possibly hide the 'HG15497' record from the last SELECT?
>
> And it's not just a matter of equality versus inequality...as the
> following
> SQL does return the record:
> SELECT I."STYLE" FROM TEMP."ZJVINV2" AS I INNER JOIN APP."Product" AS
> P
> ON I."STYLE" = P."Num" WHERE I."STYLE" = 'HG15497';
>
> I am using Java DB v10.3.2.1.
>
> --
> View this message in context: http://www.nabble.com/Record-not-found-in-
> some-SQL---Bug--tp21700110p21700110.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.