[firebird-support] Re: How to determine size of database structures?
Thank you Alexey. This is nice tool, however it does not show how much space is taken by given table and its indices... I am sure this can be deduced by reading gstat output file but I don't know how...
Re: [firebird-support] GBAK - validation error for column X, value *** null ***
Hi Josef, Such error is the consequence of the corruption - in the original database there was a NULL in the field (most likely, all fields in that record are NULL). gbak does not check constraints when reading. The best way to fix it is to find record with NULLs in the original database and delete it, then repeat backup/restore. If you don't have original records, it can be a problem. Try to restore database till the privileges error and then pump data from partially restored database to the new empty database with the same structure - you can use free tool IBDataPump for it. If problems continue, you can export data using our proprietary IBBackupSurgeon tool (http://ib-aid.com/en/ibbackupsurgeon/), which can export data on low-level. Regards, Alexey Kovyazin IBSurgeon Hi! What would be a proper way of dealing with GBAK restore error 'validation error for column X, value *** null ***'? This error appears in one table, where X is the primary key. The database is Firebird 2.5.4 in the superserver mode. 1) How do I properly restore this backup? Neither -INACTIVE nor -NO_VALIDITY (nor both at the same time) help, the restore stops as soon as the first error is encountered. -ONE_AT_A_TIME gets me to the end of table data, but then I get another error at the restoring privilege for user Y action cancelled by trigger (3) to preserve data integrity table/procedure has non-SQL security class defined. 2) Why doesn't GBAK complain during backup? I would expect that if a backup is successfully made, I will be able to restore it. Thanks, Josef
[firebird-support] GBAK - validation error for column X, value *** null ***
Hi! What would be a proper way of dealing with GBAK restore error 'validation error for column X, value *** null ***'? This error appears in one table, where X is the primary key. The database is Firebird 2.5.4 in the superserver mode. 1) How do I properly restore this backup? Neither -INACTIVE nor -NO_VALIDITY (nor both at the same time) help, the restore stops as soon as the first error is encountered. -ONE_AT_A_TIME gets me to the end of table data, but then I get another error at the restoring privilege for user Y action cancelled by trigger (3) to preserve data integrity table/procedure has non-SQL security class defined. 2) Why doesn't GBAK complain during backup? I would expect that if a backup is successfully made, I will be able to restore it. Thanks, Josef
Re: [firebird-support] GBAK - validation error for column X, value *** null ***
Hi! Thanks for the answer. Luckily, I still have the FDB file, so I can get rid of the bad records easily. What has me concerned is, what if I only had the backup? Your trick with pumping seems plausible, I will have to give it a try. IBBackupSurgeon looks promising, too. Josef On 15.5.2015 9:14, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote: Hi Josef, Such error is the consequence of the corruption - in the original database there was a NULL in the field (most likely, all fields in that record are NULL). gbak does not check constraints when reading. The best way to fix it is to find record with NULLs in the original database and delete it, then repeat backup/restore. If you don't have original records, it can be a problem. Try to restore database till the privileges error and then pump data from partially restored database to the new empty database with the same structure - you can use free tool IBDataPump for it. If problems continue, you can export data using our proprietary IBBackupSurgeon tool (http://ib-aid.com/en/ibbackupsurgeon/), which can export data on low-level. Regards, Alexey Kovyazin IBSurgeon
[firebird-support] Why index is not used in this query?
Hello, here is full example: CREATE TABLE TABLE_1 ( ID INTEGER NOT NULL, NAME VARCHAR(32), CONSTRAINT PK_TABLE_1 PRIMARY KEY (ID) ); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_1 TO SYSDBA WITH GRANT OPTION; CREATE TABLE TABLE_2 ( ID INTEGER NOT NULL, TABLE_1_ID INTEGER ); ALTER TABLE TABLE_2 ADD CONSTRAINT FK_TABLE_2 FOREIGN KEY (TABLE_1_ID) REFERENCES TABLE_1 (ID); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_2 TO SYSDBA WITH GRANT OPTION; INSERT INTO TABLE_1 VALUES(1, 'V_1'); INSERT INTO TABLE_1 VALUES(2, 'V_2'); INSERT INTO TABLE_1 VALUES(3, 'V_3'); INSERT INTO TABLE_1 VALUES(4, 'V_4'); INSERT INTO TABLE_1 VALUES(5, 'V_5'); INSERT INTO TABLE_2 VALUES(1, 1); INSERT INTO TABLE_2 VALUES(2, 1); INSERT INTO TABLE_2 VALUES(3, 2); INSERT INTO TABLE_2 VALUES(4, 2); INSERT INTO TABLE_2 VALUES(5, 3); SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am getting error when i try to enforce it with: PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) Thanks for any clues.
Re: [firebird-support] Why index is not used in this query?
How about something along the lines of: A table that small is going to fit into a single disk page. So a table scan involves reading one disk page. Using the index would involve reading the index as well, which is a second disk page, so twice as slow. ? (Other RDBMS which have a covering index concept can, for appropriate queries, use the index without reading the table at all, and might come to a different conclusion in such cases.) On 15/05/2015 14:02, brucedickin...@wp.pl [firebird-support] wrote: Hello, here is full example: CREATE TABLE TABLE_1 ( ID INTEGER NOT NULL, NAME VARCHAR(32), CONSTRAINT PK_TABLE_1 PRIMARY KEY (ID) ); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_1 TO SYSDBA WITH GRANT OPTION; CREATE TABLE TABLE_2 ( ID INTEGER NOT NULL, TABLE_1_ID INTEGER ); ALTER TABLE TABLE_2 ADD CONSTRAINT FK_TABLE_2 FOREIGN KEY (TABLE_1_ID) REFERENCES TABLE_1 (ID); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_2 TO SYSDBA WITH GRANT OPTION; INSERT INTO TABLE_1 VALUES(1, 'V_1'); INSERT INTO TABLE_1 VALUES(2, 'V_2'); INSERT INTO TABLE_1 VALUES(3, 'V_3'); INSERT INTO TABLE_1 VALUES(4, 'V_4'); INSERT INTO TABLE_1 VALUES(5, 'V_5'); INSERT INTO TABLE_2 VALUES(1, 1); INSERT INTO TABLE_2 VALUES(2, 1); INSERT INTO TABLE_2 VALUES(3, 2); INSERT INTO TABLE_2 VALUES(4, 2); INSERT INTO TABLE_2 VALUES(5, 3); SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am getting error when i try to enforce it with: PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) Thanks for any clues. -- Tim Ward
Re: [firebird-support] Why index is not used in this query?
On May 15, 2015, at 9:02 AM, brucedickin...@wp.pl [firebird-support] firebird-support@yahoogroups.com wrote: SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am getting error when i try to enforce it with: PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) There's nothing to look up in the primary key index of T1 - indexes are used to find records withh specific values in the key fields. If you added another condition like T1.ID between 5 and 10, you'd get the plan you expect. Good luck, Ann here is full example: CREATE TABLE TABLE_1 ( ID INTEGER NOT NULL, NAME VARCHAR(32), CONSTRAINT PK_TABLE_1 PRIMARY KEY (ID) ); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_1 TO SYSDBA WITH GRANT OPTION; CREATE TABLE TABLE_2 ( ID INTEGER NOT NULL, TABLE_1_ID INTEGER ); ALTER TABLE TABLE_2 ADD CONSTRAINT FK_TABLE_2 FOREIGN KEY (TABLE_1_ID) REFERENCES TABLE_1 (ID); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_2 TO SYSDBA WITH GRANT OPTION; INSERT INTO TABLE_1 VALUES(1, 'V_1'); INSERT INTO TABLE_1 VALUES(2, 'V_2'); INSERT INTO TABLE_1 VALUES(3, 'V_3'); INSERT INTO TABLE_1 VALUES(4, 'V_4'); INSERT INTO TABLE_1 VALUES(5, 'V_5'); INSERT INTO TABLE_2 VALUES(1, 1); INSERT INTO TABLE_2 VALUES(2, 1); INSERT INTO TABLE_2 VALUES(3, 2); INSERT INTO TABLE_2 VALUES(4, 2); INSERT INTO TABLE_2 VALUES(5, 3); SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am getting error when i try to enforce it with: PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) Thanks for any clues.
Re: [firebird-support] Re: How to determine size of database structures?
On May 15, 2015, at 2:14 AM, brucedickin...@wp.pl [firebird-support] firebird-support@yahoogroups.com wrote: This is nice tool, however it does not show how much space is taken by given table and its indices... I am sure this can be deduced by reading gstat output file but I don't know how... Gstat doesn't report blob pages or data overflow pages (records larger than a page). It may not report intermediate index levels. Nor does it report pages that support the structure of the database. Even so, I'm surprised that it's only reporting about half the size of the database. Good luck, Ann
Re: [firebird-support] Re: How to determine size of database structures?
Hi Bruce, To view size of tables and indices you need to use our IBAnalyst tool (http://ib-aid.com/en/ibanalyst/). Regards, Alexey Kovyazin IBSurgeon Thank you Alexey. This is nice tool, however it does not show how much space is taken by given table and its indices... I am sure this can be deduced by reading gstat output file but I don't know how...
RE: [firebird-support] Why index is not used in this query?
Bruce, SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? Given that there is no ORDER BY clause it is much faster to walk Table_1 in natural order, then it is to use any index (even primary key). Using an index always requires that the rows related to the index value be read to confirm that the indexed value is still valid (indexes contain all of the index values which a row could have had -- so the index may have a value which is no longer valid). So, reading by index would generate a huge amount of random IO, whereas walking the table is much more lightweight. Sean
