Reproducible Corruption of a database, java.lang.IllegalStateException: Unsupported type 17 [1.4.200/3]

[areichel]

Dear All,

unfortunately I am able to corrupt a perfectly valid database with a very simple sequence of DML/DDLs and Queries:

1) CREATE TABLE ... AS SELECT .. FROM ...

2) on that table create a few indexes

3) on that table, run a few SELECT ... FROM ...

All these steps above succeed and I see the results of Step 3, but then, when closing and opening that database again it fails with the exception below.

I have tried that 5 times already starting with the same clean database and were able to repeat that corruption.

Also it will not corrupt, when we skip steps 1) and 2) and run 3) as SELECT ... FROM (SELECT ... FROM ...) instead, using the SELECT statement of step 1 as sub query.

Further information:

a) the H2 is 1.4.200

b) connection url is: jdbc:h2:tcp://localhost/~/.manticore/ifrsbox;PAGE_SIZE=8192;CACHE_SIZE=20000

d) the select of 1) pulls less than 700 records in total (only)

c) total size of the H2 database is 250 MByte

Please let me know what we should do from our end in order to help solving that error. We are fully committed.

Thank you already and best regards

Andreas

org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.IllegalStateException: Unable to read the page at position 136339454691093 [1.4.200/6]" [50000-200]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:505)

at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)

at org.h2.message.DbException.get(DbException.java:194)

at org.h2.message.DbException.convert(DbException.java:347)

at org.h2.engine.Database.openDatabase(Database.java:333)

at org.h2.engine.Database.<init>(Database.java:301)

at org.h2.engine.Engine.openSession(Engine.java:74)

at org.h2.engine.Engine.openSession(Engine.java:192)

at org.h2.engine.Engine.createSessionAndValidate(Engine.java:171)

at org.h2.engine.Engine.createSession(Engine.java:166)

at org.h2.server.TcpServerThread.run(TcpServerThread.java:168)

at java.lang.Thread.run(Thread.java:748)

Caused by: java.lang.IllegalStateException: Unable to read the page at position 136339454691093 [1.4.200/6]

at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:950)

at org.h2.mvstore.MVStore.readPage(MVStore.java:2213)

at org.h2.mvstore.MVMap.readPage(MVMap.java:672)

at org.h2.mvstore.MVMap.readOrCreateRootPage(MVMap.java:688)

at org.h2.mvstore.MVMap.setRootPos(MVMap.java:682)

at org.h2.mvstore.MVStore.openMap(MVStore.java:576)

at org.h2.mvstore.MVStore.openMap(MVStore.java:535)

at org.h2.mvstore.MVStore.openMap(MVStore.java:516)

at org.h2.mvstore.MVStore.removeMap(MVStore.java:2742)

at org.h2.mvstore.db.MVTableEngine$Store.removeTemporaryMaps(MVTableEngine.java:302)

at org.h2.engine.Database.open(Database.java:765)

at org.h2.engine.Database.openDatabase(Database.java:307)

... 7 more

Caused by: java.lang.IllegalStateException: Unsupported type 17 [1.4.200/3]

at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:950)

at org.h2.mvstore.type.ObjectDataType.newType(ObjectDataType.java:165)

at org.h2.mvstore.type.ObjectDataType.read(ObjectDataType.java:229)

at org.h2.mvstore.type.ObjectDataType.read(ObjectDataType.java:114)

at org.h2.mvstore.Page.read(Page.java:605)

at org.h2.mvstore.Page.read(Page.java:239)

at org.h2.mvstore.MVStore.readPage(MVStore.java:2211)

... 17 more

[areichel]

The CREATE TABLE AS statement.

[areichel]

I assume, something goes terrible wrong with definition of the columns during the CREATE TABLE, please see the resulting table structure below and have an eye on NOMINAL_BALANCE. This column is a total of two DECIMAL(23, 5), so how do we end up with DECIMAL(2147483647, 2147483647)?

CREATE TABLE ifrsbox.PUBLIC.REP_IFRS7_DISCLOSURE
  (
     ID_COUNTERPARTY            VARCHAR(50) NULL
     , DESCRIPTION              VARCHAR(2147483647) NULL
     , SECTOR                   VARCHAR(50) NULL
     , SUB_SECTOR               VARCHAR(50) NULL
     , ID_INSTRUMENT            VARCHAR(40) NULL
     , ID_INSTRUMENT_TYPE       VARCHAR(12) NULL
     , PRODUCT_TYPE             VARCHAR(36) NULL
     , PRODUCT                  VARCHAR(40) NULL
     , END_DATE                 DATE NULL
     , MATURITY_BUCKET          VARCHAR(4) NULL
     , GL_CODE                  VARCHAR(40) NULL
     , PLEDGED                  VARCHAR(40) NULL
     , AMORTISED_COST_DIRTY     DECIMAL(23, 5) NULL
     , AMORTISED_COST_DIRTY_BC  DECIMAL(23, 5) NULL
     , NOMINAL_BALANCE          DECIMAL(2147483647, 2147483647) NULL
     , NOMINAL_BALANCE_BC       DECIMAL(2147483647, 2147483647) NULL
     , OPEN_COMMITMENT          DECIMAL(23, 5) NULL
     , OPEN_COMMITMENT_BC       DECIMAL(23, 5) NULL
     , IMPAIRMENT               DECIMAL(2147483647, 2147483647) NULL
     , IMPAIRMENT_BC            DECIMAL(2147483647, 2147483647) NULL
     , IMPAIRMENT_CONTINGENT    DECIMAL(23, 5) NULL
     , IMPAIRMENT_CONTINGENT_BC DECIMAL(23, 5) NULL
     , OVERDUE_DAYS             DECIMAL(4) NULL
     , IMPAIRMENT_STAGE         DECIMAL(1) NULL
     , ID_PORTFOLIO             VARCHAR(40) NULL
     , RATING_CLASS             VARCHAR(34) NULL
     , INVESTMENT_GRADE         VARCHAR(14) NULL
  ); 

[areichel]

I saw a few recent commits on precision and scale, so I pulled from Git and gave it a try. 

The created table looks different now and I can open the database after closing it, which seems to solve my problem with the corruption.

Although I still see the very odd column NOMINAL_BALANCE DECIMAL(2147483647, 100000) NULL.

Why would we want to set it like that instead of Decimal(23,5) as defined by the source columns?

CREATE TABLE ifrsbox.PUBLIC.REP_FINANCIAL_ASSETS

  (
     ID_COUNTERPARTY            VARCHAR(50) NULL
     , DESCRIPTION              VARCHAR(2147483647) NULL
     , SECTOR                   VARCHAR(50) NULL
     , SUB_SECTOR               VARCHAR(50) NULL
     , ID_INSTRUMENT            VARCHAR(40) NULL
     , ID_INSTRUMENT_TYPE       VARCHAR(12) NULL
     , PRODUCT_TYPE             VARCHAR(36) NULL
     , PRODUCT                  VARCHAR(40) NULL
     , END_DATE                 DATE NULL
     , MATURITY_BUCKET          VARCHAR(4) NULL
     , GL_CODE                  VARCHAR(40) NULL
     , PLEDGED                  VARCHAR(40) NULL
     , AMORTISED_COST_DIRTY     DECIMAL(23, 5) NULL
     , AMORTISED_COST_DIRTY_BC  DECIMAL(23, 5) NULL

     , NOMINAL_BALANCE          DECIMAL(2147483647, 100000) NULL
     , NOMINAL_BALANCE_BC       DECIMAL(2147483647, 100000) NULL

     , OPEN_COMMITMENT          DECIMAL(23, 5) NULL
     , OPEN_COMMITMENT_BC       DECIMAL(23, 5) NULL

     , IMPAIRMENT               DECIMAL(2147483647, 100000) NULL
     , IMPAIRMENT_BC            DECIMAL(2147483647, 100000) NULL

     , IMPAIRMENT_CONTINGENT    DECIMAL(23, 5) NULL
     , IMPAIRMENT_CONTINGENT_BC DECIMAL(23, 5) NULL
     , OVERDUE_DAYS             DECIMAL(4) NULL
     , IMPAIRMENT_STAGE         DECIMAL(1) NULL
     , ID_PORTFOLIO             VARCHAR(40) NULL
     , RATING_CLASS             VARCHAR(34) NULL
     , INVESTMENT_GRADE         VARCHAR(14) NULL
  );

CREATE INDEX idx_rep_financial_assets_1
  ON ifrsbox.PUBLIC.rep_financial_assets(ID_COUNTERPARTY);

CREATE INDEX idx_rep_financial_assets_10
  ON ifrsbox.PUBLIC.rep_financial_assets(IMPAIRMENT_STAGE);

CREATE INDEX idx_rep_financial_assets_11
  ON ifrsbox.PUBLIC.rep_financial_assets(ID_PORTFOLIO);

CREATE INDEX idx_rep_financial_assets_12
  ON ifrsbox.PUBLIC.rep_financial_assets(RATING_CLASS);

CREATE INDEX idx_rep_financial_assets_13
  ON ifrsbox.PUBLIC.rep_financial_assets(INVESTMENT_GRADE);

CREATE INDEX idx_rep_financial_assets_2
  ON ifrsbox.PUBLIC.rep_financial_assets(SECTOR);

CREATE INDEX idx_rep_financial_assets_3
  ON ifrsbox.PUBLIC.rep_financial_assets(SUB_SECTOR);

CREATE INDEX idx_rep_financial_assets_4
  ON ifrsbox.PUBLIC.rep_financial_assets(ID_INSTRUMENT_TYPE);

CREATE INDEX idx_rep_financial_assets_5
  ON ifrsbox.PUBLIC.rep_financial_assets(PRODUCT_TYPE);

CREATE INDEX idx_rep_financial_assets_6
  ON ifrsbox.PUBLIC.rep_financial_assets(PRODUCT);

CREATE INDEX idx_rep_financial_assets_7
  ON ifrsbox.PUBLIC.rep_financial_assets(MATURITY_BUCKET);

CREATE INDEX idx_rep_financial_assets_8
  ON ifrsbox.PUBLIC.rep_financial_assets(GL_CODE);

CREATE INDEX idx_rep_financial_assets_9
  ON ifrsbox.PUBLIC.rep_financial_assets(PLEDGED); 

 

[areichel]

On more thing: Using the H2 Git snapshot, I was not even able to open the valid database (before corruption) because it complained about other tables with similar odd column definitions. (We use a few CREATE TABLE AS statements for reporting purpose.)

I had to open that database with H2 1.4.200 first and drop these tables, before I was able to open the database with H2 1.4.201 snapshot.

While I appreciate the fix of the database corruption, I also wonder if it would be possible to maintain a minimum of backward compatibility.

[Evgenij Ryazanov]

Hello.


1. Unsupported type 17 is usually thrown when multiple versions of H2 were used with the same database file (versions of TCP clients don't matter, only versions of servers and embedded connections should match). For example, IDEA users are affected, because IDEA uses 1.4.196 it its tools. https://github.com/h2database/h2database/issues/2078
If you can reproduce that issue with 1.4.200 only, please try to build a test case for it.


2. Addition of DECIMAL(23, 5) to DECIMAL(23, 5) should produce a numeric data type with scale 5 and any vendor-specific precision, according to the SQL Standard. Therefore you should not assume that it will be DECIMAL(23, 5), it can legally be different, for example, DECIMAL(2147483647, 5) or something else. You need to add an explicit cast to DECIMAL(23, 5) such as CAST(A + B AS DECIMAL(23, 5)) to be sure. Unfortunately, H2 does not evaluate the scale properly, this issue is also known. https://github.com/h2database/h2database/issues/1910

3. Released versions of H2 don't have any sane limits for scale, but such limit exists in current sources. I think we need to add some code to allow initialization of databases that already have abnormal data types such as DECIMAL(2147483647, 2147483647) created by older versions of H2.

[areichel]

Thank you a lot, Evgenij and team.

On Thursday, November 21, 2019 at 9:12:19 AM UTC+7, Evgenij Ryazanov wrote:

1. Unsupported type 17 is usually thrown when multiple versions of H2 were used with the same database file (versions of TCP clients don't matter, only versions of servers and embedded connections should match). For example, IDEA users are affected, because IDEA uses 1.4.196 it its tools. https://github.com/h2database/h2database/issues/2078

Beside not knowing that, I am not even sure if your explanation applies in that case. The error message was thrown, when 1) opening a database and then 2) CREATE TABLE AS ... and then 3) SELECT FROM on that new table. These steps 1-3 were all conducted on version 1.4.200 and the same sequence of actions works fine now with 1.4.201-pre.

Although I will try to craft a simplified testcase.

 

If you can reproduce that issue with 1.4.200 only, please try to build a test case for it.


2. Addition of DECIMAL(23, 5) to DECIMAL(23, 5) should produce a numeric data type with scale 5 and any vendor-specific precision, according to the SQL Standard. Therefore you should not assume that it will be DECIMAL(23, 5), it can legally be different, for example, DECIMAL(2147483647, 5) or something else. You need to add an explicit cast to DECIMAL(23, 5) such as CAST(A + B AS DECIMAL(23, 5)) to be sure. Unfortunately, H2 does not evaluate the scale properly, this issue is also known. https://github.com/h2database/h2database/issues/1910

The SQL standard determines the precision and scale in the following way:

1. If the declared type of both operands of a dyadic arithmetic operator is exact numeric, then the declared type of the result is an implementation-defined exact numeric type, with precision and scale determined as follows:
   a) Let S1 and S2 be the scale of the first and second operands respectively.
   b) The precision of the result of addition and subtraction is implementation-defined, and the scale is the maximum of S1 and S2.

If I am not mistaken, that should give 5 (but not 100000) for the sample above.

Definitely it should not give 2147483647 as 1.4.200 does.

For our use case, 1.4.201-pre with DECIMAL(2147483647, 100000) is sufficient and does work, but I find it very confusing.

I would have expected that an addition gives DECIMAL(23, 5) as standard, which could be cast to something else on demand -- because that is what you would have to do in Java or C when adding Shorts/Ints etc.

Perhaps, we even could optimize it as (Max(Precision_S1, Precision_S2) + 1) and Max(Scale_S1, Scale_S2), just in order to safe space.

3. Released versions of H2 don't have any sane limits for scale, but such limit exists in current sources. I think we need to add some code to allow initialization of databases that already have abnormal data types such as DECIMAL(2147483647, 2147483647) created by older versions of H2.

Yes, thank you very much for fixing it. 1.4.201-pre solved our problem in an acceptable way and we can leave that thread for documentation, when other users hit a similar problem with 1.4.200 (stable). 

[areichel]

Perhaps, we even could optimize it as (Max(Precision_S1, Precision_S2) + 1) and Max(Scale_S1, Scale_S2), just in order to safe space.

Wow,  commit 113f85232dfa4518b78b998949c83a663e14a954 seems to implement exactly that! You guys are awesome, thank you so much!

Kudos!

[Evgenij Ryazanov]

Not exactly, your assumption doesn't work for operands with different scale.

Anyway, the result of addition has larger precision for additional possible digit, so in some use cases an explicit cast is still necessary.

[areichel]

On Friday, November 22, 2019 at 2:44:22 PM UTC+7, Evgenij Ryazanov wrote:

Anyway, the result of addition has larger precision for additional possible digit, so in some use cases an explicit cast is still necessary.

Yes, but in that case this is to expect and you would have needed to do the same cast in any/most programming languages.

I just tested the commit and it worked beautiful! I can not overstate my appreciation, because CAST seems to be very very slow and really harmed the performance (am I right on that?!).

The table looks now as expected. Thank you so much again and cheers!

CREATE TABLE ifrsbox.public.REP_FINANCIAL_ASSETS
  (
     ID_COUNTERPARTY            VARCHAR(50) NULL
     , DESCRIPTION              VARCHAR(255) NULL

     , SECTOR                   VARCHAR(50) NULL
     , SUB_SECTOR               VARCHAR(50) NULL
     , ID_INSTRUMENT            VARCHAR(40) NULL
     , ID_INSTRUMENT_TYPE       VARCHAR(12) NULL
     , PRODUCT_TYPE             VARCHAR(36) NULL
     , PRODUCT                  VARCHAR(40) NULL
     , END_DATE                 DATE NULL
     , MATURITY_BUCKET          VARCHAR(4) NULL
     , GL_CODE                  VARCHAR(40) NULL
     , PLEDGED                  VARCHAR(40) NULL
     , AMORTISED_COST_DIRTY     DECIMAL(23, 5) NULL
     , AMORTISED_COST_DIRTY_BC  DECIMAL(23, 5) NULL

     , NOMINAL_BALANCE          DECIMAL(24, 5) NULL
     , NOMINAL_BALANCE_BC       DECIMAL(24, 5) NULL

     , OPEN_COMMITMENT          DECIMAL(23, 5) NULL
     , OPEN_COMMITMENT_BC       DECIMAL(23, 5) NULL

     , IMPAIRMENT               DECIMAL(24, 5) NULL
     , IMPAIRMENT_BC            DECIMAL(24, 5) NULL

     , IMPAIRMENT_CONTINGENT    DECIMAL(23, 5) NULL
     , IMPAIRMENT_CONTINGENT_BC DECIMAL(23, 5) NULL
     , OVERDUE_DAYS             DECIMAL(4) NULL
     , IMPAIRMENT_STAGE         DECIMAL(1) NULL
     , ID_PORTFOLIO             VARCHAR(40) NULL
     , RATING_CLASS             VARCHAR(34) NULL
     , INVESTMENT_GRADE         VARCHAR(14) NULL

     , MASTER_RATING            INTEGER(10) NULL
  );

[Evgenij Ryazanov]

CAST from one NUMERIC/DECIMAL data type to another one with the same scale is cheap. But if you don't mind that minor difference in precision you can avoid it.