Hi Emmanuel,
Many thanks for your reply - it gave me new direction for some further
testing and I can now see what the problem is - I just don't know the
answer!
In summary it seems that only autocommited operations through Sequoia are
being written to a backend during recovery. An operation not autocommited
(say an "insert" followed by an explicit "commit") is not being written to a
backend during recovery.
Here's my new simplified test procedure to illustrate this:
1. Create new databases, start Sequoia.
2. Using "SQL Explorer" (Eclipse plugin), insert a row into the table
"tablea" - autocommit OFF, via Sequoia
insert into tablea values(122, 'abc', 122);
commit;
(see log_id 292 and 293 below)
3. Perform a backup from the Console (backup db_backup test123 PostgreSQL
<path-to-backups>)
4. Using "SQL Explorer", insert a row into the table "tablea" - autocommit
OFF, via Sequoia
insert into tablea values(133, 'abc', 133);
commit;
(see log_id 294 and 295 below)
5. Using "SQL Explorer", insert another row into the table "tablea" -
autocommit ON, via Sequoia
insert into tablea values(144, 'abc', 144);
(see log_id 296 below)
I've included the relevant rows from the recovery log:
292 | user | insert into tablea values(122, 'abc', 122)
|
| T | 289 | 290 | S | 26 |
1
293 | user | commit
|
| T | 289 | 0 | S | 0 |
0
BACKUP PERFORMED HERE.....
294 | user | insert into tablea values(133, 'abc', 133)
|
| T | 290 | 291 | S | 27 |
1
295 | user | commit
|
| T | 290 | 0 | S | 0 |
0
296 | user | insert into tablea values(144, 'abc', 144)
|
| A | 0 | 292 | S | 48 |
1
6. Perform a restore from the Console
disable *
restore backend db_primary test123 (enter backend login info...)
restore backend db_backup test123 (enter backend login info...)
enable *
7. The result is that:
"tablea" row 122 appears in the backends (both before the backup
and after the restore - as expected)
"tablea" row 133 is NOT recovered (from step 6 above)
"tablea" row 144 IS recovered (from step 6 above)
The only difference between rows (id) 133 and 144 is that 144 was
autocommitted, while 133 wasn't.
Is this a problem Emmanuel? I hope that there is enough details to help
identify the problem if there is one. Please let me know if you'd like me to
provide any further information or to perform more tests.
Looking forward to your reply,
Regards
Ken
PS:
Here is the table "tablea" after the restore...
db_primary=# select * from tablea;
id | componentname | timestamp
-----+---------------+-----------
122 | abc | 122
144 | abc | 144
(2 rows)
Here is the table "checkpoint" after the restore...
recovery=# select * from checkpoint;
name | log_id
---------------------------------------------------------+--------
Initial_empty_recovery_log | 0
backup test123-A.B.C.D:25322-20061220104027588+1030 | 293
disable db_primary-A.B.C.D:25322-20061220104651401+1030 | 296
disable db_backup-A.B.C.D:25322-20061220104651475+1030 | 296
(4 rows)
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Emmanuel
Cecchet
Sent: Tuesday, 19 December 2006 9:53 PM
To: Sequoia general mailing list
Subject: Re: [Sequoia] Database Recovery - Possible Bug in reading sql_param
from recove ry log?
Hi Ken,
> 429 | user | {call nextval('hibernate_sequence')}
> |
> | T | 365 | 381 | S | 21 |
> 0
>
This one belongs to an open transaction so it if a checkpoint pointed to
that entry (which is unlikely) it would not be played at recovery time
but will be played before the backend get disabled.
> I then go to restore the backends from my backup through the console.
> I do the following:
>
> 1. disable *
> 2. restore backend db_primary testbackup
> 3. restore backend db_backup testbackup
> 4. force checkpoint db_primary checkpoint_corresponding_to_the_backup
> (as reported in "show dumps") 5. force checkpoint db_backup
> checkpoint_corresponding_to_the_backup (as reported in "show dumps")
> 6. enable *
>
You should never use 'force checkpoint', the restore mechanism takes
care of setting the appropriate checkpoints corresponding to the dumps.
Using 'force checkpoint' you bypass all internal checks that could
detect if anything was wrong.
> The result is:
> =============
> i) I don't see any of the operations from log_id's 429 - 432 written
> to my backends
>
These were probably already played before the backend was actually disabled.
> ii) I DO see the operations from log_id's 447 - 450 (inclusive)
> written to my backends
>
>
> Because the "hibernate_sequence" table sequence number is updated (by
> 1) as a result of log_id 450 (I've confirmed this) then I believe that
> the recovery process is having some sort of problem with log_id 430;
>
> 430 | user | insert into tablea (componentName, timestamp, id) values
(?,
> ?, ?)
> |
> <!%S|abc|!%><!%L|1166491718006|!%><!%L|17|!%> | T |
>
I don't see what kind of problem you could have with such entry. Do you
have something like a 'duplicate key' error ?
You should try to use the standard backup/restore procedures described
in the documentation and avoid using expert mode commands.
Keep us posted with your progress,
Emmanuel
--
Emmanuel Cecchet
Chief Scientific Officer, Continuent
Blog: http://emanux.blogspot.com/
Open source: http://www.continuent.org
Corporate: http://www.continuent.com
Skype: emmanuel_cecchet
Cell: +33 687 342 685
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
