Re: SYSCS_UTIL.SYSCS_CHECK_TABLE
Is it possible to manipulate the base tables and/or the btree indexes in a way to cause inconsistencies It's hard to do this reliably, or easily. But I encourage you to try, it sounds like a good project, and a good way to explore Derby internals and improve your knowledge of Derby. Here's a possible idea: take part, but not all, of your Derby database "back in time". A base table and its indexes are each stored in separate physical files, which Derby calls "conglomerates". So if you define a table with a number of secondary indexes, and then insert some rows into that table, Derby will populate the table and its indexes. Then, shut down Derby cleanly and make a copy of the entire database folder. Then, start Derby back up and add some more rows into that table. Then, shut down Derby cleanly again. Then, try (using yet another copy of the Derby database folder) copying some of the table's index conglomerates from your first backup copy into your master copy. This will cause the base table rows to be "missing" some index entries because you have essentially "rolled back" the index conglomerates to an earlier time, causing them to be missing the index entries that were created by your second batch of insert statements. Then see how the consistency-checking tools behave on that artifically-damaged database. Similar experiments are possible using other types of integrity constraints (unique constraints, foreign key constraints, etc), and using other types of "back-in-time" intentional damage, such as rolling an index back to before an update of an indexed column, or rolling an index back to before a delete of a row, or rolling back one table but not other table, when the tables have a referential integrity constraint. These are the sorts of consistency violations that a consistency-checking tool is intended to catch, so it would be good to know if you are able to provoke such types of damage and then observe the tools helping you to diagnose the problem. Regarding internal sorts of Btree integrity, that is a harder problem, but it would be good for you to verify that you can detect the simpler problems first. thanks, bryan
Re: SYSCS_UTIL.SYSCS_CHECK_TABLE
On 11/26/15 6:49 AM, 5...@informatik.uni-hamburg.de wrote: Hello, I would like to test the consistency check function but I have difficulties inserting certain inconsistencies into the database for proper testing. I found a Testscript which uses T_ConsistencyChecker.class and managed to run it but so far it only tests the following cases: - Base tables and all associated indexes contain the same number of rows. - The values and row locations in each index match those of the base table. According to the documentation, there are two other cases: - Base tables are internally consistent. - All BTREE indexes are internally consistent. Is it possible to manipulate the base tables and/or the btree indexes in a way to cause inconsistencies, so I can run this function to find them? What would be the error messages of these cases? Thank you, Tu You might start out by using a text editor in order to insert some garbage into the datafiles which hold the base table and the index. Those files are located in the seg0 directory of the database. A script like the following will tell you which datafiles correspond to which base tables and indexes: connect 'jdbc:derby:memory:db;create=true'; create table t1( a int ); create index t1_idx on t1( a ); create table t2( a int primary key ); create function toHex( conglomerateNumber bigint ) returns varchar( 10 ) language java parameter style java no sql external name 'java.lang.Long.toHexString'; select s.schemaName, t.tableName, 'c' || toHex( c.conglomerateNumber ) || '.dat' dataFile, c.isIndex, c.isConstraint from sys.systables t, sys.sysconglomerates c, sys.sysschemas s where t.tablename not like 'SYS%' and t.tableid = c.tableid and t.schemaid = s.schemaid order by tableName, dataFile; I see the following interesting exceptions being raised by org.apache.derby.iapi.db.ConsistencyChecker: SQLState.LANG_INCONSISTENT_ROW_LOCATION (X0XC2) SQLState.LANG_INDEX_COLUMN_NOT_EQUAL (X0XC1) SQLState.LANG_INDEX_ROW_COUNT_MISMATCH (X0Y55) Hope this helps, -Rick
SYSCS_UTIL.SYSCS_CHECK_TABLE
Hello, I would like to test the consistency check function but I have difficulties inserting certain inconsistencies into the database for proper testing. I found a Testscript which uses T_ConsistencyChecker.class and managed to run it but so far it only tests the following cases: - Base tables and all associated indexes contain the same number of rows. - The values and row locations in each index match those of the base table. According to the documentation, there are two other cases: - Base tables are internally consistent. - All BTREE indexes are internally consistent. Is it possible to manipulate the base tables and/or the btree indexes in a way to cause inconsistencies, so I can run this function to find them? What would be the error messages of these cases? Thank you, Tu
