Hmm, I'm still showing <unknown> in the last known checkpoint field after restarting everything from scratch. I'm thinking there is something wrong with perhaps the recovery database. I've followed everything from the initialize and activate procedures.
Some background: I have a 6.5gb database that I first created a virtual database configuration using that database. This all works fine, although timely to backup and restore, essentially the backend's behave, and sequoia too for that matter. (I will be pretty embarrassed at this point if it is definitely something I'm doing wrong, although a probable cause for this due to lack of full understanding of how all the parts work). Now I'm trying two more databases that I know need the max_packet_size variable set, and thought I'd better check that all databases work properly under sequoia (although confident, experience tells me to be paranoid). Either I've not enough coffee and missing some crucial simple step, or there's something buggering it all up. PS I really appreciate the help on this list. Another great reason to use sequoia. localhost:1090 > load virtualdatabase configuration /usr/local/sequoia/sequoia/config/virtualdatabase/SQA_ SQA_IASDSYDB.xml SQA_NAVTEQ.xml localhost:1090 > load virtualdatabase configuration /usr/local/sequoia/sequoia/config/virtualdatabase/SQA_IASDSYDB.xml XML file /usr/local/sequoia/sequoia/config/virtualdatabase/SQA_IASDSYDB.xml successfully sent to Sequoia controller. localhost:1090 > show virtualdatabases SQA_VIASDSYDB localhost:1090 > admin SQA_VIASDSYDB Virtual database Administrator Login > root Virtual database Administrator Password > Ready to administrate virtual database SQA_VIASDSYDB SQA_VIASDSYDB(root) > initialize DB1 Virtual Database SQA_VIASDSYDB has been successfully initialized from backend DB1 SQA_VIASDSYDB(root) > show backupers +-------------------+----------------+ | Backuper | Dump Format | +-------------------+----------------+ | BKUP_SQA_IASDSYDB | MySQL raw dump | +-------------------+----------------+ SQA_VIASDSYDB(root) > backup DB1 initial_dump BKUP_SQA_IASDSYDB /root/ Backend login for backup process > root Backend password for backup process > Backup backend DB1 in dump file initial_dump SQA_VIASDSYDB(root) > enable DB1 Enabling backend DB1 from its last known checkpoint SQA_VIASDSYDB(root) > restore backedn DB2 initial_dump Command restore backedn DB2 initial_dump not supported in this module, type help for a list of available commands. SQA_VIASDSYDB(root) > restore backend DB2 initial_dump Backend login for restore process > root Backend password for restore process > Restoring backend DB2 with dump initial_dump SQA_VIASDSYDB(root) > enable DB2 Enabling backend DB2 from its last known checkpoint SQA_VIASDSYDB(root) > show backend * +--------------------------+-------------------------------------------- --+ | Backend Name | DB1 | | Driver | com.mysql.jdbc.Driver | | URL | jdbc:mysql://192.168.61.14:3306/DB1_iasdsydb | | Active transactions | 0 | | Pending Requests | 0 | | Read Enabled | true | | Write Enabled | true | | Is Initialized | true | | Static Schema | false | | Connection Managers | 1 | | Total Active Connections | 5 | | Persistent Connections | 0 | | Total Requests | 0 | | Total Transactions | 0 | | Last known checkpoint | <unknown> | +--------------------------+-------------------------------------------- --+ | Backend Name | DB2 | | Driver | com.mysql.jdbc.Driver | | URL | jdbc:mysql://192.168.61.14:3306/DB2_iasdsydb | | Active transactions | 0 | | Pending Requests | 0 | | Read Enabled | true | | Write Enabled | true | | Is Initialized | true | | Static Schema | false | | Connection Managers | 1 | | Total Active Connections | 10 | | Persistent Connections | 0 | | Total Requests | 0 | | Total Transactions | 0 | | Last known checkpoint | <unknown> | +--------------------------+-------------------------------------------- --+ SQA_VIASDSYDB(root) > Controller.sh output [EMAIL PROTECTED] bin]# ./controller.sh 09:46:03,285 INFO controller.core.Controller Sequoia controller (2.10.9) 09:46:03,363 INFO controller.core.Controller Loading configuration file: /usr/local/sequoia/sequoia-2.10.9-bin/config/controller/controller.xml 09:46:03,433 INFO controller.core.Controller JMX is enabled 09:46:03,457 INFO controller.core.Controller Starting JMX server on host: 192.168.61.14 09:46:03,703 INFO controller.core.Controller Waiting for connections on 0.0.0.0:25322 09:46:03,715 INFO controller.core.Controller Controller started on 2008.02.27 09 at 09:46:03 AM EST 09:46:03,716 INFO controller.core.Controller Controller 192.168.61.14:25322 ready, listening to requests ... 09:46:39,158 INFO controller.backup.BackupManager Registering backuper BKUP_SQA_IASDSYDB to handle format MySQL raw dump 09:46:39,179 INFO DatabaseBackend.SQA_VIASDSYDB.DB1 Adding connection manager for virtual user "user" 09:46:39,186 INFO DatabaseBackend.SQA_VIASDSYDB.DB2 Adding connection manager for virtual user "user" 09:46:39,691 INFO sequoia.controller.recoverylog Creating recovery log table: RECOVERY 09:46:39,693 INFO sequoia.controller.recoverylog Creating checkpoint table: CHECKPOINT 09:46:39,703 INFO sequoia.controller.recoverylog Creating backend table: BACKEND 09:46:39,704 INFO sequoia.controller.recoverylog Creating dump table: DUMP 09:46:39,733 INFO controller.RequestManager.SQA_VIASDSYDB Request manager will parse requests with the following granularity: TABLE 09:46:39,742 INFO controller.core.Controller Adding VirtualDatabase SQA_VIASDSYDB 09:47:34,212 INFO controller.RequestManager.SQA_VIASDSYDB Starting backup of backend DB1 09:47:34,292 INFO backup.backupers.NativeCommandExec Starting execution of "mysqldump --routines -h 192.168.61.14 --port=3306 -uroot --password= DB1_iasdsydb" 09:47:41,216 INFO backup.backupers.NativeCommandExec Command "mysqldump --routines -h 192.168.61.14 --port=3306 -uroot --password= DB1_iasdsydb" logged 0 errors and terminated with exitcode 0 09:47:41,269 INFO controller.RequestManager.SQA_VIASDSYDB Backup has completed. 09:47:47,382 INFO DatabaseBackend.SQA_VIASDSYDB.DB1 Detected backend as: MySQL 09:47:49,349 INFO controller.RequestManager.SQA_VIASDSYDB Setting new virtual database schema. 09:47:49,366 INFO controller.recoverylog.RecoverThread Starting recovery 09:47:49,369 INFO controller.recoverylog.RecoverThread Recovery completed 09:47:50,928 INFO controller.recoverylog.RecoverThread Database backend DB1 is now enabled 09:48:25,589 INFO backup.backupers.NativeCommandExec Starting execution of "mysqladmin -h 192.168.61.14 --port=3306 -f -uroot --password= drop DB2_iasdsydb" 09:48:25,673 INFO backup.backupers.NativeCommandExec Command "mysqladmin -h 192.168.61.14 --port=3306 -f -uroot --password= drop DB2_iasdsydb" logged 0 errors and terminated with exitcode 0 09:48:25,685 INFO backup.backupers.NativeCommandExec Starting execution of "mysqladmin -h 192.168.61.14 --port=3306 -f -uroot --password= create DB2_iasdsydb" 09:48:25,690 INFO backup.backupers.NativeCommandExec Command "mysqladmin -h 192.168.61.14 --port=3306 -f -uroot --password= create DB2_iasdsydb" logged 0 errors and terminated with exitcode 0 09:48:25,704 INFO backup.backupers.NativeCommandExec Starting execution of "mysql -h 192.168.61.14 --port=3306 -uroot --password= DB2_iasdsydb" 09:48:51,353 INFO backup.backupers.NativeCommandExec Command "mysql -h 192.168.61.14 --port=3306 -uroot --password= DB2_iasdsydb" logged 0 errors and terminated with exitcode 0 09:48:51,356 INFO controller.RequestManager.SQA_VIASDSYDB Recovery of backend DB2 done. 09:48:57,557 INFO DatabaseBackend.SQA_VIASDSYDB.DB2 Detected backend as: MySQL 09:48:58,996 INFO controller.RequestManager.SQA_VIASDSYDB Virtual database schema merged with new schema. 09:48:59,000 INFO controller.recoverylog.RecoverThread Starting recovery 09:48:59,003 INFO controller.recoverylog.RecoverThread Recovery completed 09:49:00,426 INFO controller.recoverylog.RecoverThread Database backend DB2 is now enabled Recovery database: mysql> use SQA_IASDSYDB_RECOVERY; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------------------+ | Tables_in_SQA_IASDSYDB_RECOVERY | +---------------------------------+ | BACKEND | | CHECKPOINT | | DUMP | | RECOVERY | +---------------------------------+ 4 rows in set (0.00 sec) mysql> select * from BACKEND; +---------------+--------------+---------------+-----------------+ | database_name | backend_name | backend_state | checkpoint_name | +---------------+--------------+---------------+-----------------+ | SQA_VIASDSYDB | DB1 | 1 | | | SQA_VIASDSYDB | DB2 | 1 | | +---------------+--------------+---------------+-----------------+ 2 rows in set (0.00 sec) mysql> select * from CHECKPOINT; +----------------------------+--------+ | name | log_id | +----------------------------+--------+ | Initial_empty_recovery_log | 0 | +----------------------------+--------+ 1 row in set (0.00 sec) mysql> select * from DUMP; +--------------+---------------------+-----------+----------------+----- -----------------------+--------------+--------+ | dump_name | dump_date | dump_path | dump_format | checkpoint_name | backend_name | tables | +--------------+---------------------+-----------+----------------+----- -----------------------+--------------+--------+ | initial_dump | 2008-02-27 09:47:41 | /root/ | MySQL raw dump | Initial_empty_recovery_log | DB1 | * | +--------------+---------------------+-----------+----------------+----- -----------------------+--------------+--------+ 1 row in set (0.00 sec) mysql> select * from RECOVERY; Empty set (0.00 sec) mysql> -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emmanuel Cecchet Sent: Wednesday, February 27, 2008 8:44 AM To: Sequoia general mailing list Subject: Re: [Sequoia] a point in the right direction Jonathan, The problem is that you are taking a dump of a backend that has not been properly initialized (that's why its checkpoint in <unknown>). If you look at the initialization procedure (http://sequoia.continuent.org/doc/infocenter/index.jsp?topic=/org.conti nuent.sequoia.doc/html/Activate_the_database_backend_s_of_the_first_cont roller.html) you probably missed step 5 (initialize command). You should reissue that command (intialize DB1) before taking the dump and restoring it to other backends. That should solve the issue. Hope this helps, Emmanuel > Thanks for your response. The databases were created from an initial > 'skeleton' which has some data already. I imported the database into > mysql and then setup sequoia. Just to get more information, I > reinitialized the backends, did a backup of DB1 and enabled it, same > with DB2. below is a list of the recovery database values. Also the > process used to create the initial dump. (I purged the old dumps out to > start clean once again). I am noticing the last known checkpoint is > <unknown>. I can only imagine I'm doing something wrong with the backup > process. Due to limited resources I have both DB1_iasdsydb and > DB2_iasdsydb on the same machine, if this is the problem I'll find > another machine to put the second database on. > > SQA_VIASDSYDB(root) > backup DB1 initial_dump BKUP_SQA_IASDSYDB /root/ > Backend login for backup process > root > Backend password for backup process > > Backup backend DB1 in dump file initial_dump > SQA_VIASDSYDB(root) > enable DB1 > Enabling backend DB1 from its last known checkpoint > SQA_VIASDSYDB(root) > show backend DB1 > +--------------------------+-------------------------------------------- > --+ > | Backend Name | DB1 > | > | Driver | com.mysql.jdbc.Driver > | > | URL | > jdbc:mysql://192.168.61.14:3306/DB1_iasdsydb | > | Active transactions | 0 > | > | Pending Requests | 0 > | > | Read Enabled | true > | > | Write Enabled | true > | > | Is Initialized | true > | > | Static Schema | false > | > | Connection Managers | 1 > | > | Total Active Connections | 10 > | > | Persistent Connections | 0 > | > | Total Requests | 0 > | > | Total Transactions | 0 > | > | Last known checkpoint | <unknown> > | > +--------------------------+-------------------------------------------- > --+ > > SQA_VIASDSYDB(root) > restore backend DB2 initial_dump > Backend login for restore process > root > Backend password for restore process > > Restoring backend DB2 with dump initial_dump > SQA_VIASDSYDB(root) > enable DB2 > Enabling backend DB2 from its last known checkpoint > SQA_VIASDSYDB(root) > show backend * > +--------------------------+-------------------------------------------- > --+ > | Backend Name | DB1 > | > | Driver | com.mysql.jdbc.Driver > | > | URL | > jdbc:mysql://192.168.61.14:3306/DB1_iasdsydb | > | Active transactions | 0 > | > | Pending Requests | 0 > | > | Read Enabled | true > | > | Write Enabled | true > | > | Is Initialized | true > | > | Static Schema | false > | > | Connection Managers | 1 > | > | Total Active Connections | 5 > | > | Persistent Connections | 0 > | > | Total Requests | 0 > | > | Total Transactions | 0 > | > | Last known checkpoint | <unknown> > | > +--------------------------+-------------------------------------------- > --+ > | Backend Name | DB2 > | > | Driver | com.mysql.jdbc.Driver > | > | URL | > jdbc:mysql://192.168.61.14:3306/DB2_iasdsydb | > | Active transactions | 0 > | > | Pending Requests | 0 > | > | Read Enabled | true > | > | Write Enabled | true > | > | Is Initialized | true > | > | Static Schema | false > | > | Connection Managers | 1 > | > | Total Active Connections | 10 > | > | Persistent Connections | 0 > | > | Total Requests | 0 > | > | Total Transactions | 0 > | > | Last known checkpoint | <unknown> > | > +--------------------------+-------------------------------------------- > --+ > > mysql> use SQA_IASDSYDB_RECOVERY > Reading table information for completion of table and column names > You can turn off this feature to get a quicker startup with -A > > Database changed > mysql> show tables; > +---------------------------------+ > | Tables_in_SQA_IASDSYDB_RECOVERY | > +---------------------------------+ > | BACKEND | > | CHECKPOINT | > | DUMP | > | RECOVERY | > +---------------------------------+ > 4 rows in set (0.00 sec) > > mysql> select * from BACKEND; > +---------------+--------------+---------------+-----------------+ > | database_name | backend_name | backend_state | checkpoint_name | > +---------------+--------------+---------------+-----------------+ > | SQA_VIASDSYDB | DB1 | 1 | | > | SQA_VIASDSYDB | DB2 | 1 | | > +---------------+--------------+---------------+-----------------+ > 2 rows in set (0.00 sec) > > mysql> select * from CHECKPOINT > -> ; > +----------------------------+--------+ > | name | log_id | > +----------------------------+--------+ > | Initial_empty_recovery_log | 0 | > +----------------------------+--------+ > 1 row in set (0.00 sec) > > mysql> select * from DUMP; > +--------------+---------------------+-----------+----------------+----- > -----------------------+--------------+--------+ > | dump_name | dump_date | dump_path | dump_format | > checkpoint_name | backend_name | tables | > +--------------+---------------------+-----------+----------------+----- > -----------------------+--------------+--------+ > | initial_dump | 2008-02-27 07:47:15 | /root/ | MySQL raw dump | > Initial_empty_recovery_log | DB1 | * | > +--------------+---------------------+-----------+----------------+----- > -----------------------+--------------+--------+ > 1 row in set (0.00 sec) > > mysql> select * from RECOVERY; > Empty set (0.00 sec) > > Mysql> > > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Emmanuel Cecchet > Sent: Wednesday, February 27, 2008 5:34 AM > To: Sequoia general mailing list > Subject: Re: [Sequoia] a point in the right direction > > Jonathan, > > >> I am testing sequoia in a raidb-1 configuration with mysql. We have a >> couple of databases and in which I have had to set in /etc/my.cnf a >> variable in which I think is causing the following error. Right now I >> have only one controller, but I plan on having 2 controllers and two >> databases >> >> max_allowed_packet=32M is set in our my.cnf. >> >> > If the queries were originally executed with that value, there should > not be any issue to replay with that value. > >> now when implementing sequoia >> >> SQA_VIASDSYDB(root) > restore backend DB1 initial_dump >> Backend login for restore process > root >> Backend password for restore process > >> Restoring backend DB1 with dump initial_dump >> >> > Which backuper are you using? > >> This seems to be fine, however when I enable the backend I get this >> error: >> >> SQA_VIASDSYDB(root) > enable DB1 >> Enabling backend DB1 from its last known checkpoint >> Virtual database SQA_VIASDSYDB failed to enable database backend DB1 >> from checkpoint (because of : java.sql.SQLException: Unable to get >> checkpoint from recovery log.) >> >> Did I miss something? >> > There is probably a problem with the way you generated the dump. When > you did the backup operation, it was associated to a checkpoint > corresponding to the backend state at that time. It looks like this > checkpoint does not exist anymore in your recovery log for some reason > (initialize command, recovery log resynchronization, purge log...). > You should recreate a valid dump with a proper checkpoint. There are > various command to list dumps and checkpoints in the recovery log if you > > want to check manually. > >> If there is any where to get an ideal >> configuration for jboss servers connecting to a raidb-1 mysql >> > controller > >> that would be even better. I'm worried that this import is failing >> > due > >> to some blobs of data that require this max_allowed_packet >> >> > If max_allowed_packet was an issue, I guess the problem would also > happen at backup time, not only restore. > > Keep us posted with your progress, > Emmanuel > > -- Emmanuel Cecchet - Research scientist EPFL - LABOS/DSLAB - IN.N 317 Phone: +41-21-693-7558 _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
