Thank you for your time Maya. You wrote:
> Just some more info here. When you restore a database, it looks at your
> index definitions, and current data, and builds the indices from that.
> That is why backing up and restoring will rectify the problem
> (temporarily, till a new corruption occurs)
However, the important thing to note is, that backing up and restoring
does not rectify the problem! Backing up and restoring finish their
respective jobs without problems, but the validation problem is
*immediately* there (i.e. without any usage of the DB between restoring
and validating).
The index build during restore creates this problem with these two large
DBs absolutely always, i.e. corruption is in this case apparently always
repeatable. (We experienced such a thing never before, as either backup or
restore fail, or they do not fail but there is no more validation problem
immediately afterwards. Well, not so in this story.)
Let me refrase this: What really worries us is the effect of the following
steps:
1. Drop the primary key constraint of the table in question
2. Make validation => Result is OK.
3. Add the primary key constraint of the table in question
4. Make validation => Result is NOT OK.
Since the primary key is being built also as a part of the restore process
(as far as I understand), it fits the picture that validation result is
not OK also immediately after restore.
So, in our opinion, the way the table is usually being used (many inserts
and deletes) maybe has nothing to do with the problem. It seems more
likely that the data itself and probably in combination with the size of
the table present a problem for the index building algorithm.
> I find I only see the index error in Firebird.log after doing a Database
> Validation (which doesn't fix the error, just reports it)
Yes, I agree with that - that seems to be the case.
> I think Vlad is going to need a copy of your database you get to the
> bottom of the problem, so if you're customer is not going to allow that,
> it's going to be a problem :-(
It is unfortunately out of the question that we make these DBs available.
I know that this would be great help, but am unable to change it. On the
other hand, we would be more than willing to use any beta, gamma :) or
possibly special test builds for testing this, in order to help Vlad.
> I have 4 or 5 large clients with this problem, and I am in the process of
> convincing them to try 2.1.4.
It is nice to hear that we are not alone with this :) although we did not
have any more luck with 2.1.4.
> Will post the results here, as soon as I
> have them...
Please do so - we are very eager to learn more about this issue.
> Are there perhaps any other messages in your Firebird.log apart from the
> index corruption ones?
Not much - here is an excerpt (names made anonymous; please note that
inet_error has nothing to do with this issue):
HHHH (Client) Thu Apr 14 11:19:49 2011
INET/inet_error: connect errno = 10061
HHHH (Client) Thu Apr 14 11:20:34 2011
Guardian starting: "C:\Program Files
(x86)\Firebird\Firebird_2_1\bin\fbserver.exe"
HHHH (Server) Sat Apr 16 03:45:17 2011
Database: C:\DATAPROJECTS\B\S.FB
Index 1 is corrupt (missing entries) in table M (129)
HHHH (Server) Sat Apr 16 03:52:05 2011
Database: C:\DATAPROJECTS\B\S.FB
Index 2 is corrupt (missing entries) in table M (129)
HHHH (Server) Mon Apr 18 15:24:13 2011
Database: C:\DATAPROJECTS\B\S.FB
Index 1 is corrupt (missing entries) in table M (129)
HHHH (Server) Mon Apr 18 15:31:56 2011
Database: C:\DATAPROJECTS\B\S.FB
Index 2 is corrupt (missing entries) in table M (129)
HHHH (Server) Tue Apr 19 03:45:07 2011
Database: C:\DATAPROJECTS\H\S.FB
Index 1 is corrupt (missing entries) in table M (128)
As additional info, here are the excerpts from the two DBs by which we
experience this problem:
--------------------------------------
Database "E:\DATAPROJECTS\H_TEST\ori\S.FB"
Database header page information:
Flags 0
Checksum 12345
Generation 23240
Page size 16384
ODS version 11.1
Oldest transaction 23208
Oldest active 23209
Oldest snapshot 23209
Next transaction 23211
Bumped transaction 1
Sequence number 0
Next attachment ID 777
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Jun 28, 2008 13:56:40
Attributes
Variable header data:
Sweep interval: 20000
*END*
Database file sequence:
File E:\DATAPROJECTS\H_TEST\ori\S.FB is the only file
Analyzing database pages ...
M (128)
Primary pointer page: 138, Index root page: 139
Data pages: 2287162, data page slots: 2300735, average fill: 88%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 2287161
Index M_PK (0)
Depth: 4, leaf buckets: 430875, nodes: 188674151
Average data length: 13.64, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 7075
20 - 39% = 21192
40 - 59% = 326924
60 - 79% = 28334
80 - 99% = 47350
--------------------------------------
Database "C:\DataProjects\B\S.FB"
Database header page information:
Flags 0
Checksum 12345
Generation 1585
Page size 16384
ODS version 11.1
Oldest transaction 1555
Oldest active 1556
Oldest snapshot 1556
Next transaction 1557
Bumped transaction 1
Sequence number 0
Next attachment ID 60
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Mar 4, 2011 1:21:15
Attributes
Variable header data:
Sweep interval: 20000
*END*
Database file sequence:
File C:\DataProjects\B\S.FB is the only file
Analyzing database pages ...
M (129)
Primary pointer page: 141, Index root page: 142
Data pages: 3212657, data page slots: 3212657, average fill: 87%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 3212656
Index IDX_M1 (1)
Depth: 3, leaf buckets: 111237, nodes: 279396096
Average data length: 0.00, total dup: 279396058, max dup: 53948060
Fill distribution:
0 - 19% = 82
20 - 39% = 24
40 - 59% = 14969
60 - 79% = 1693
80 - 99% = 94469
Index M_PK (0)
Depth: 4, leaf buckets: 422148, nodes: 279396096
Average data length: 14.54, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 737
20 - 39% = 2811
40 - 59% = 80522
60 - 79% = 22951
80 - 99% = 315127
--------------------------------------
It can be seen, that the depth is 4, but since we are already using the
page size of 16384, we do not think this can be made any better.
Hope that this was not too much. Regards,
Borut
------------------------------------------------------------------------------
Benefiting from Server Virtualization: Beyond Initial Workload
Consolidation -- Increasing the use of server virtualization is a top
priority.Virtualization can reduce costs, simplify management, and improve
application availability and disaster protection. Learn more about boosting
the value of server virtualization. http://p.sf.net/sfu/vmware-sfdev2dev
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
