Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 10.3.2.1
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Windows XP
    • Urgency:
      Normal
    • Bug behavior facts:
      Data corruption, Seen in production, Wrong query result

      Description

      Per discussion at http://www.nabble.com/Record-not-found-in-some-SQL---Bug--td21700110.html...

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

      1. check-table.txt
        91 kB
        Knut Anders Hatlen
      2. OrderEntryDB-seg0-part7.zip
        5.43 MB
        Chip Hartney
      3. OrderEntryDB-seg0-part6.zip
        5.90 MB
        Chip Hartney
      4. OrderEntryDB-seg0-part5.zip
        6.24 MB
        Chip Hartney
      5. OrderEntryDB-seg0-part4.zip
        8.97 MB
        Chip Hartney
      6. OrderEntryDB-seg0-part3.zip
        9.41 MB
        Chip Hartney
      7. OrderEntryDB-seg0-part2.zip
        9.10 MB
        Chip Hartney
      8. OrderEntryDB-seg0-part1.zip
        4.75 MB
        Chip Hartney
      9. OrderEntryDB-base.zip
        3 kB
        Chip Hartney

        Activity

        Hide
        Rick Hillegas added a comment -

        This issue has been open for a long time but no reproducible test case has been posted. If someone can post a repro, then we can re-open the bug. Thanks.

        Show
        Rick Hillegas added a comment - This issue has been open for a long time but no reproducible test case has been posted. If someone can post a repro, then we can re-open the bug. Thanks.
        Hide
        Knut Anders Hatlen added a comment -

        Reopening pending further investigation.

        Show
        Knut Anders Hatlen added a comment - Reopening pending further investigation.
        Hide
        Chip Hartney added a comment -

        I understand the difficulty in reproduction, but per my earlier entries, this is reproducible...having occurred a few times for a few of our customers. Not often, but it has happened more than once. Given the infrequency, I suggest Knut's idea of a problem handling page boundaries should be checked...if not done already...maybe by a code review.

        Besides, I believe Knut has already pointed out a problem that needs resolution per his entry on 1/30: "When you scan a range in the index, both of the rows are found, and the one that's not deleted is returned."

        So it appears that the scan logic DOES allow for the existence of duplicate rows in the index, but the B-tree lookup logic does not. That is an inconsistency that should be addressed. It seems to me that you have gone part way...allowing for duplicates in one case, but not the other, and should therefore change the B-tree logic to accommodate the duplicates also.

        If, on the other hand, you decide the scan logic is wrong (and that dups should never be handled), perhaps it should be changed. But I wouldn't be surprised if such logic was added to address an inconsistency discovered in the past...one much like mine. In fact, given that SYSCS_UTIL.SYSCS_CHECK_TABLE reports such problems, it's clear you guys have found in the past that such dups do occur. I'm uncomfortable acknowledging that they do occur, and cause problems when they do, but declaring the cause unresolvable.

        Having said all that, be aware that I am no longer working for the client whom I wrote this app for. Therefore I am unable to provide more information about the app (code samples, etc). So I realize I will be of little help now and can't complain if you leave this as Not Reproducible.

        Show
        Chip Hartney added a comment - I understand the difficulty in reproduction, but per my earlier entries, this is reproducible...having occurred a few times for a few of our customers. Not often, but it has happened more than once. Given the infrequency, I suggest Knut's idea of a problem handling page boundaries should be checked...if not done already...maybe by a code review. Besides, I believe Knut has already pointed out a problem that needs resolution per his entry on 1/30: "When you scan a range in the index, both of the rows are found, and the one that's not deleted is returned." So it appears that the scan logic DOES allow for the existence of duplicate rows in the index, but the B-tree lookup logic does not. That is an inconsistency that should be addressed. It seems to me that you have gone part way...allowing for duplicates in one case, but not the other, and should therefore change the B-tree logic to accommodate the duplicates also. If, on the other hand, you decide the scan logic is wrong (and that dups should never be handled), perhaps it should be changed. But I wouldn't be surprised if such logic was added to address an inconsistency discovered in the past...one much like mine. In fact, given that SYSCS_UTIL.SYSCS_CHECK_TABLE reports such problems, it's clear you guys have found in the past that such dups do occur. I'm uncomfortable acknowledging that they do occur, and cause problems when they do, but declaring the cause unresolvable. Having said all that, be aware that I am no longer working for the client whom I wrote this app for. Therefore I am unable to provide more information about the app (code samples, etc). So I realize I will be of little help now and can't complain if you leave this as Not Reproducible.
        Hide
        Knut Anders Hatlen added a comment -

        Triaged for 10.5.2. Setting resolution to Cannot Reproduce as suggested by Kathey. If someone finds a way to reproduce this, please reopen the issue.

        Show
        Knut Anders Hatlen added a comment - Triaged for 10.5.2. Setting resolution to Cannot Reproduce as suggested by Kathey. If someone finds a way to reproduce this, please reopen the issue.
        Hide
        Knut Anders Hatlen added a comment -

        The index in question is on a VARCHAR(16) column, so it's probably a different issue.

        Show
        Knut Anders Hatlen added a comment - The index in question is on a VARCHAR(16) column, so it's probably a different issue.
        Hide
        Kathey Marsden added a comment -

        DERBY-4181 seems specific to the IBM 1.6 JVM and also to specific to numeric indexes. Is either the case in this issue?

        Show
        Kathey Marsden added a comment - DERBY-4181 seems specific to the IBM 1.6 JVM and also to specific to numeric indexes. Is either the case in this issue?
        Hide
        Knut Anders Hatlen added a comment -

        SYSCS_CHECK_TABLE() does detect this error, by the way. But it has to be run with a debug build.

        I've attached a file the full output. Here's the first line:

        ERROR 38000: The exception 'org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED Bad order of rows found in conglomerate: org.apache.derby.impl.store.access.btree.index.B2IController@1eb717e

        Show
        Knut Anders Hatlen added a comment - SYSCS_CHECK_TABLE() does detect this error, by the way. But it has to be run with a debug build. I've attached a file the full output. Here's the first line: ERROR 38000: The exception 'org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED Bad order of rows found in conglomerate: org.apache.derby.impl.store.access.btree.index.B2IController@1eb717e
        Hide
        Knut Anders Hatlen added a comment -

        Could there be a remote possibility that this is related to DERBY-4181? Both issues have key value overlap between siblings.

        Show
        Knut Anders Hatlen added a comment - Could there be a remote possibility that this is related to DERBY-4181 ? Both issues have key value overlap between siblings.
        Hide
        Kathey Marsden added a comment -

        I wonder if there was any progress on creating a reproduction for the cause of the corruption on this issue. If not, I think we should go ahead an close cannot reproduce and open an improvement request for SYSCS_UTIL.SYSCS_CHECK_TABLE as Rick suggests. The issue can then be reopened if we find a way to reproduce the corrupt index.

        Show
        Kathey Marsden added a comment - I wonder if there was any progress on creating a reproduction for the cause of the corruption on this issue. If not, I think we should go ahead an close cannot reproduce and open an improvement request for SYSCS_UTIL.SYSCS_CHECK_TABLE as Rick suggests. The issue can then be reopened if we find a way to reproduce the corrupt index.
        Hide
        Rick Hillegas added a comment -

        Great detective work, Knut! Perhaps SYSCS_UTIL.SYSCS_CHECK_TABLE can be enhanced to detect these corrupt entries in unique indexes. That would help users know whether they need to workaround the underlying bug by bouncing their unique constraints and indexes.

        Show
        Rick Hillegas added a comment - Great detective work, Knut! Perhaps SYSCS_UTIL.SYSCS_CHECK_TABLE can be enhanced to detect these corrupt entries in unique indexes. That would help users know whether they need to workaround the underlying bug by bouncing their unique constraints and indexes.
        Hide
        Chip Hartney added a comment -

        As I think about it more, I realize that the "corruption" in my prior scenarios may have actually occurred during the reload process, itself. If that is the case, then my patch would be of no use as it is designed to fix the index before commencing with the reload. That raises the criticality of this problem as far as I'm concerned.

        And I use the term "corruption" loosely. As I understand it, one of the following is the case (either of which is a bug):
        o The index is not supposed to have duplicate records in it, but does.
        o The index is allowed to have duplicate records, but the b-tree logic does not properly allow for that.

        Show
        Chip Hartney added a comment - As I think about it more, I realize that the "corruption" in my prior scenarios may have actually occurred during the reload process, itself. If that is the case, then my patch would be of no use as it is designed to fix the index before commencing with the reload. That raises the criticality of this problem as far as I'm concerned. And I use the term "corruption" loosely. As I understand it, one of the following is the case (either of which is a bug): o The index is not supposed to have duplicate records in it, but does. o The index is allowed to have duplicate records, but the b-tree logic does not properly allow for that.
        Hide
        Chip Hartney added a comment -

        This makes sense to me as one of the processes the app undergoes is a reload of the Product data. That process includes a DELETE of most records in the table followed by a series of INSERTs to rebuild the list of products. This is all done within a single UOW. Perhaps, in this process, the HG15497 record is deleted from the table, but only marked as deleted in the index. Then, perhaps, the following INSERT may not have cleaned up the record marked as deleted...perhaps because it is on a page boundary.

        I have seen similar page boundary quirks in other DBMSs (that I was more familiar with) in the past. That would explain why most rows are not so affected.

        I also want to mention that I have seen what I believe is this same behavior twice before...2-3 months ago...on another table using the same app and the same version of Derby. Again, during the reload process, many records are deleted from a table, then a complex query is run that joins the remaining records to a master list to identify those that need further processing. The JOIN returned at least one of the records that had just been deleted! It was repeatable. I did not investigate as deeply because it occurred only in my development environment. I simply recreated the DB and all was well again. I now believe this was the same problem.

        Given that the problem is that there is an undesired row in the index, we can fix the DB by rebuilding the index. I just did so, successfully correcting the problem, as follows...

        ij> select "Num" from "APP"."Product" where "Num" = 'HG15497';
        Num
        ----------------
        0 rows selected

        ij> ALTER TABLE "APP"."Product" DROP CONSTRAINT "Product_AK_Num";
        0 rows inserted/updated/deleted

        ij> select "Num" from "APP"."Product" where "Num" = 'HG15497';
        Num
        ----------------
        HG15497
        1 row selected

        ij> ALTER TABLE "APP"."Product" ADD CONSTRAINT "Product_AK_Num" UNIQUE ("Num");
        0 rows inserted/updated/deleted

        ij> select "Num" from "APP"."Product" where "Num" = 'HG15497';
        Num
        ----------------
        HG15497
        1 row selected

        I will use this as the patch if the problem arises again and will look for a new version of Derby with a resolution to this problem to prevent the problem from arising to start with.

        Show
        Chip Hartney added a comment - This makes sense to me as one of the processes the app undergoes is a reload of the Product data. That process includes a DELETE of most records in the table followed by a series of INSERTs to rebuild the list of products. This is all done within a single UOW. Perhaps, in this process, the HG15497 record is deleted from the table, but only marked as deleted in the index. Then, perhaps, the following INSERT may not have cleaned up the record marked as deleted...perhaps because it is on a page boundary. I have seen similar page boundary quirks in other DBMSs (that I was more familiar with) in the past. That would explain why most rows are not so affected. I also want to mention that I have seen what I believe is this same behavior twice before...2-3 months ago...on another table using the same app and the same version of Derby. Again, during the reload process, many records are deleted from a table, then a complex query is run that joins the remaining records to a master list to identify those that need further processing. The JOIN returned at least one of the records that had just been deleted! It was repeatable. I did not investigate as deeply because it occurred only in my development environment. I simply recreated the DB and all was well again. I now believe this was the same problem. Given that the problem is that there is an undesired row in the index, we can fix the DB by rebuilding the index. I just did so, successfully correcting the problem, as follows... ij> select "Num" from "APP"."Product" where "Num" = 'HG15497'; Num ---------------- 0 rows selected ij> ALTER TABLE "APP"."Product" DROP CONSTRAINT "Product_AK_Num"; 0 rows inserted/updated/deleted ij> select "Num" from "APP"."Product" where "Num" = 'HG15497'; Num ---------------- HG15497 1 row selected ij> ALTER TABLE "APP"."Product" ADD CONSTRAINT "Product_AK_Num" UNIQUE ("Num"); 0 rows inserted/updated/deleted ij> select "Num" from "APP"."Product" where "Num" = 'HG15497'; Num ---------------- HG15497 1 row selected I will use this as the patch if the problem arises again and will look for a new version of Derby with a resolution to this problem to prevent the problem from arising to start with.
        Hide
        Knut Anders Hatlen added a comment -

        It turns out that the optimizer overrides I suggested didn't do what I expected them to do. In particular, constraint=null didn't stop the query from using the constraint. index=null did, and then the row was returned.

        ij> select "Num" from "Product" --DERBY-PROPERTIES index=null
        > where "Num" = 'HG15497';
        Num
        ----------------
        HG15497

        1 row selected
        ij> select "Num" from "Product" --DERBY-PROPERTIES constraint="Product_AK_Num"
        > where "Num" = 'HG15497';
        Num
        ----------------

        0 rows selected

        It looks like that there are two rows with that value in the index. One of them is marked as deleted. When you scan a range in the index, both of the rows are found, and the one that's not deleted is returned. If you fetch the row by exact match, the B-tree scan happens to see the deleted one first. Since it is a unique index, and the row it found first was deleted, it assumes that there is no match and stops searching, and it never sees the row that isn't deleted.

        As I understand the B-tree code, there should never be duplicates in a unique index, even if some of the rows are marked as deleted. So the question is how did the duplicates end up in the unique index.

        It seems like one of the matching rows (the one that's not deleted) is the last row on one index page, and the deleted one is the first one on another page. So my guess is that there is a bug in the code that inserts rows in the index when there's a matching, deleted row on a page boundary.

        Show
        Knut Anders Hatlen added a comment - It turns out that the optimizer overrides I suggested didn't do what I expected them to do. In particular, constraint=null didn't stop the query from using the constraint. index=null did, and then the row was returned. ij> select "Num" from "Product" --DERBY-PROPERTIES index=null > where "Num" = 'HG15497'; Num ---------------- HG15497 1 row selected ij> select "Num" from "Product" --DERBY-PROPERTIES constraint="Product_AK_Num" > where "Num" = 'HG15497'; Num ---------------- 0 rows selected It looks like that there are two rows with that value in the index. One of them is marked as deleted. When you scan a range in the index, both of the rows are found, and the one that's not deleted is returned. If you fetch the row by exact match, the B-tree scan happens to see the deleted one first. Since it is a unique index, and the row it found first was deleted, it assumes that there is no match and stops searching, and it never sees the row that isn't deleted. As I understand the B-tree code, there should never be duplicates in a unique index, even if some of the rows are marked as deleted. So the question is how did the duplicates end up in the unique index. It seems like one of the matching rows (the one that's not deleted) is the last row on one index page, and the deleted one is the first one on another page. So my guess is that there is a bug in the code that inserts rows in the index when there's a matching, deleted row on a page boundary.
        Hide
        Knut Anders Hatlen added a comment -

        Thanks Chip, now I'm able to boot the database and reproduce the problem. I'll see if I get some time to investigate what happens in a debugger soon.

        Show
        Knut Anders Hatlen added a comment - Thanks Chip, now I'm able to boot the database and reproduce the problem. I'll see if I get some time to investigate what happens in a debugger soon.
        Hide
        Chip Hartney added a comment -

        I tested the DB with v10.3.3.0 and v10.4.2.0. The SQL continued to fail in each. This furthers my belief that the DB has come to be in a state which causes the SQL to fail as opposed to a fundamental problem with the version of Derby, itself.

        Show
        Chip Hartney added a comment - I tested the DB with v10.3.3.0 and v10.4.2.0. The SQL continued to fail in each. This furthers my belief that the DB has come to be in a state which causes the SQL to fail as opposed to a fundamental problem with the version of Derby, itself.
        Hide
        Chip Hartney added a comment -

        I'm sorry, I forgot the boot instructions. I use:
        connect 'jdbc:derby:OrderEntryDB;user=oeapp;password=assuredsys;bootPassword=As2s0u0r8eD';

        I've looked at DERBY-3347, too, which one of my users incurred recently and am upgrading our DB for that reason. In fact, I will be testing that version with this DB shortly and will report results. But I, also, do not think it the cause of this particular problem.

        Show
        Chip Hartney added a comment - I'm sorry, I forgot the boot instructions. I use: connect 'jdbc:derby:OrderEntryDB;user=oeapp;password=assuredsys;bootPassword=As2s0u0r8eD'; I've looked at DERBY-3347 , too, which one of my users incurred recently and am upgrading our DB for that reason. In fact, I will be testing that version with this DB shortly and will report results. But I, also, do not think it the cause of this particular problem.
        Hide
        Knut Anders Hatlen added a comment -

        One more thing. The version against which this bug was reported (10.3.2.1) is known to have a data corruption bug (DERBY-3347). I don't think it's very likely that it's the cause of the problem reported here (I would have expected checksum errors, or at least that you wouldn't see the same problem both with the index and without the index), but I thought I'd mention it since it's highly recommended to upgrade to 10.3.3.0 or higher.

        Show
        Knut Anders Hatlen added a comment - One more thing. The version against which this bug was reported (10.3.2.1) is known to have a data corruption bug ( DERBY-3347 ). I don't think it's very likely that it's the cause of the problem reported here (I would have expected checksum errors, or at least that you wouldn't see the same problem both with the index and without the index), but I thought I'd mention it since it's highly recommended to upgrade to 10.3.3.0 or higher.
        Hide
        Knut Anders Hatlen added a comment -

        Thanks for testing the queries. If the two queries had returned different results, it would have meant that the row only in the base table and not in the index (or vice versa), which would have explained why the row sometimes shows up and sometimes doesn't. But that's not the case, apparently.

        I downloaded the database and tried to boot it, but it appears to be encrypted, and I don't have the boot password. Booting the database failed with this error message:

        ERROR XBM06: Startup failed. An encrypted database cannot be accessed without the correct boot password.

        Show
        Knut Anders Hatlen added a comment - Thanks for testing the queries. If the two queries had returned different results, it would have meant that the row only in the base table and not in the index (or vice versa), which would have explained why the row sometimes shows up and sometimes doesn't. But that's not the case, apparently. I downloaded the database and tried to boot it, but it appears to be encrypted, and I don't have the boot password. Booting the database failed with this error message: ERROR XBM06: Startup failed. An encrypted database cannot be accessed without the correct boot password.
        Hide
        Chip Hartney added a comment -

        Part 7 of 7 of seg0 content

        Show
        Chip Hartney added a comment - Part 7 of 7 of seg0 content
        Hide
        Chip Hartney added a comment -

        Part 6 of 7 of seg0 content

        Show
        Chip Hartney added a comment - Part 6 of 7 of seg0 content
        Hide
        Chip Hartney added a comment -

        Part 5 of 7 of seg0 content

        Show
        Chip Hartney added a comment - Part 5 of 7 of seg0 content
        Hide
        Chip Hartney added a comment -

        Part 4 of 7 of seg0 content

        Show
        Chip Hartney added a comment - Part 4 of 7 of seg0 content
        Hide
        Chip Hartney added a comment -

        Part 3 of 7 of seg0 content

        Show
        Chip Hartney added a comment - Part 3 of 7 of seg0 content
        Hide
        Chip Hartney added a comment -

        Part 2 of 7 of seg0 content.

        Show
        Chip Hartney added a comment - Part 2 of 7 of seg0 content.
        Hide
        Chip Hartney added a comment -

        Part 1 of 7 of seg0 content.

        Show
        Chip Hartney added a comment - Part 1 of 7 of seg0 content.
        Hide
        Chip Hartney added a comment -

        Here is the base archive containing all but the seg0 folder of our DB. Unzip it and then create the seg0 folder, then get the following 7 archives and unzip each into that seg0 folder.

        Show
        Chip Hartney added a comment - Here is the base archive containing all but the seg0 folder of our DB. Unzip it and then create the seg0 folder, then get the following 7 archives and unzip each into that seg0 folder.
        Hide
        Chip Hartney added a comment -

        Here are the results of the constrained queries...

        ij> select "Num" from app."Product" --DERBY-PROPERTIES constraint=null
        where "Num" = 'HG15497';
        Num
        ----------------

        0 rows selected
        ij> select "Num" from app."Product" --DERBY-PROPERTIES constraint="Product_AK_Num"
        where "Num" = 'HG15497';
        Num
        ----------------

        0 rows selected
        ij>

        I will split the DB by files and provide the partial zips and provide them shortly.

        Show
        Chip Hartney added a comment - Here are the results of the constrained queries... ij> select "Num" from app."Product" --DERBY-PROPERTIES constraint=null where "Num" = 'HG15497'; Num ---------------- 0 rows selected ij> select "Num" from app."Product" --DERBY-PROPERTIES constraint="Product_AK_Num" where "Num" = 'HG15497'; Num ---------------- 0 rows selected ij> I will split the DB by files and provide the partial zips and provide them shortly.
        Hide
        Knut Anders Hatlen added a comment -

        By the way, I think the 10MB upload limit is per file, so it might be possible to split the zip file in smaller pieces and upload each piece separately. On a *nix you can use the split utility like this:

        $ split -b 10m db.zip db-piece

        This gives you db-piece-aa, db-piece-ab, and so on, each file containing 10 MB.

        Others can restore the full zip file by downloading the pieces and concatenating them with the cat utility:

        $ cat db-piece-* > db.zip

        Show
        Knut Anders Hatlen added a comment - By the way, I think the 10MB upload limit is per file, so it might be possible to split the zip file in smaller pieces and upload each piece separately. On a *nix you can use the split utility like this: $ split -b 10m db.zip db-piece This gives you db-piece-aa, db-piece-ab, and so on, each file containing 10 MB. Others can restore the full zip file by downloading the pieces and concatenating them with the cat utility: $ cat db-piece-* > db.zip
        Hide
        Knut Anders Hatlen added a comment -

        Could you try these two queries and post the result? Note, the newlines after constraint=xxx are significant.

        Query 1:

        select "Num" from app."Product" --DERBY-PROPERTIES constraint=null
        where "Num" = 'HG15497';

        Query 2:

        select "Num" from app."Product" --DERBY-PROPERTIES constraint="Product_AK_Num"
        where "Num" = 'HG15497';

        The first query will get the row from the base table, whereas the second query will fetch it from the index.

        Show
        Knut Anders Hatlen added a comment - Could you try these two queries and post the result? Note, the newlines after constraint=xxx are significant. Query 1: select "Num" from app."Product" --DERBY-PROPERTIES constraint=null where "Num" = 'HG15497'; Query 2: select "Num" from app."Product" --DERBY-PROPERTIES constraint="Product_AK_Num" where "Num" = 'HG15497'; The first query will get the row from the base table, whereas the second query will fetch it from the index.
        Hide
        Chip Hartney added a comment -

        I will look into trying to pare the zip file down to 10MB...though I am doubtful.

        Be aware that 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

        Show
        Chip Hartney added a comment - I will look into trying to pare the zip file down to 10MB...though I am doubtful. Be aware that 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
        Hide
        Knut Anders Hatlen added a comment -

        Perhaps you could copy the database, boot the copy and drop the other tables in the copy? That is, if it's not the Product table that is taking most of the 50 MB.

        Show
        Knut Anders Hatlen added a comment - Perhaps you could copy the database, boot the copy and drop the other tables in the copy? That is, if it's not the Product table that is taking most of the 50 MB.
        Hide
        Chip Hartney added a comment -

        I assume that this can only be investigated by analyzing the actual DB and am willing to provide it in zipped form, but the zipped archive is still 50MB in size...bigger than this facility supports as an attachment.

        Show
        Chip Hartney added a comment - I assume that this can only be investigated by analyzing the actual DB and am willing to provide it in zipped form, but the zipped archive is still 50MB in size...bigger than this facility supports as an attachment.
        Hide
        Chip Hartney added a comment -

        Here are the DDLs for the tables in question....

        CREATE TABLE APP."Product" (
        "Id" INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
        "Num" VARCHAR(16) NOT NULL CONSTRAINT "Product_AK_Num" UNIQUE,
        "Category" VARCHAR(4) NOT NULL,
        "UnitPrice" DOUBLE NOT NULL,
        "PackSize" INTEGER,
        "SizeSetNo" INTEGER CONSTRAINT "Product_FK_SizeSetNo" REFERENCES APP."SizeSet" ("No") ON DELETE RESTRICT,
        "SeasonId" INTEGER CONSTRAINT "Product_FK_SeasonId" REFERENCES APP."Season" ("Id") ON DELETE RESTRICT,
        "ReplProdId" INTEGER CONSTRAINT "Product_FK_ReplacementProductId" REFERENCES APP."Product" ("Id") ON DELETE RESTRICT,
        "Descr" VARCHAR(30) NOT NULL,
        "FOB" VARCHAR(20),
        "IsHangingFlag" SMALLINT NOT NULL DEFAULT 0,
        "IsCustSpecFlag" SMALLINT NOT NULL DEFAULT 0
        )

        CREATE TABLE TEMP."ZJVINV2" (
        "STYLE" CHAR(16) NOT NULL,
        "COLOR-SW" SMALLINT NOT NULL,
        "SIZE-SET" INTEGER NOT NULL,
        "CLASS" CHAR(4) NOT NULL,
        "DESCRIPTION" CHAR(30) NOT NULL,
        "CASE-QTY" INTEGER NOT NULL,
        "P2" DOUBLE NOT NULL,
        "RED-PRICE" DOUBLE NOT NULL,
        "SEASON" INTEGER NOT NULL,
        "MIN-QTY" INTEGER NOT NULL,
        "MULTIPLIER" INTEGER NOT NULL,
        "SUBST" CHAR(16) NOT NULL
        )

        When I reload the DB with the original data, the error goes away. The error is reproducible in the current DB (without reloading), so I conclude that the DB came to be in a bad state through usage. I do not know what usage caused it to come to be in that state nor what that state is. But the problem cannot be reproduced through a simple script which creates and loads the tables.

        Show
        Chip Hartney added a comment - Here are the DDLs for the tables in question.... CREATE TABLE APP."Product" ( "Id" INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "Num" VARCHAR(16) NOT NULL CONSTRAINT "Product_AK_Num" UNIQUE, "Category" VARCHAR(4) NOT NULL, "UnitPrice" DOUBLE NOT NULL, "PackSize" INTEGER, "SizeSetNo" INTEGER CONSTRAINT "Product_FK_SizeSetNo" REFERENCES APP."SizeSet" ("No") ON DELETE RESTRICT, "SeasonId" INTEGER CONSTRAINT "Product_FK_SeasonId" REFERENCES APP."Season" ("Id") ON DELETE RESTRICT, "ReplProdId" INTEGER CONSTRAINT "Product_FK_ReplacementProductId" REFERENCES APP."Product" ("Id") ON DELETE RESTRICT, "Descr" VARCHAR(30) NOT NULL, "FOB" VARCHAR(20), "IsHangingFlag" SMALLINT NOT NULL DEFAULT 0, "IsCustSpecFlag" SMALLINT NOT NULL DEFAULT 0 ) CREATE TABLE TEMP."ZJVINV2" ( "STYLE" CHAR(16) NOT NULL, "COLOR-SW" SMALLINT NOT NULL, "SIZE-SET" INTEGER NOT NULL, "CLASS" CHAR(4) NOT NULL, "DESCRIPTION" CHAR(30) NOT NULL, "CASE-QTY" INTEGER NOT NULL, "P2" DOUBLE NOT NULL, "RED-PRICE" DOUBLE NOT NULL, "SEASON" INTEGER NOT NULL, "MIN-QTY" INTEGER NOT NULL, "MULTIPLIER" INTEGER NOT NULL, "SUBST" CHAR(16) NOT NULL ) When I reload the DB with the original data, the error goes away. The error is reproducible in the current DB (without reloading), so I conclude that the DB came to be in a bad state through usage. I do not know what usage caused it to come to be in that state nor what that state is. But the problem cannot be reproduced through a simple script which creates and loads the tables.

          People

          • Assignee:
            Unassigned
            Reporter:
            Chip Hartney
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development