how to create sql backup of h2 database BuddyList 2.0

[renderman]

I need to create a backup of the tables specific to the buddylists.


I have already created a custom persister for the AbstractBuddyList as part of our migration from h2 to mysql.

Through talking to Lapo I know that the buddylists use the following table structure:

CREATE TABLE <TableName>
( OWNER VARCHAR(255) NOT NULL, NAMES_LIST LONGVARCHAR, BLOCK_LIST LONGVARCHAR, VAR_KEYS LONGVARCHAR, VAR_VALUES LONGVARCHAR )

I have implemented the exact same structure in mysql and created a custom persister which works fine.


We are currently restricted to working via the command line when talking to the h2 engine as the h2 console tool will only work when requested locally.

This means that I am having difficulty finding a way to determine the buddylist database name and buddylist table within mentioned database in order to formulate the required sql to create a backup.

Does anyone know a way around this. The reason I ask is that I have avery limited timespan to get this working as we are having problems with our existing set up and need to do a complete reinstall of smartfoxserver ASAP as our site has ground to a halt and it is affecting our existing users.

best

[renderman]

I have come up with the following commmand hoping to created a back up sql script of the complete h2 database schema so I can determine the smartfox buddy list database and tables:

Code: Select all

/home/playbin/www/SFS/SFS_PRO_1.6.2/jre/bin/java -cp h2.jar org.h2.tools.Script -url jdbc:h2:~/INFORMATION_SCHEMA -user sa 

but it just ouputs the following: in a file named backup.sql

Code: Select all

SET CLUSTER '';;
SET TRACE_LEVEL_FILE 1;;
SET DEFAULT_TABLE_TYPE 0;;
SET WRITE_DELAY 500;;
SET DEFAULT_LOCK_TIMEOUT 1000;;
SET CACHE_SIZE 16384;;
SET TRACE_LEVEL_SYSTEM_OUT 0;;
CREATE USER IF NOT EXISTS SA SALT '2502deb070b22c8' HASH '0c539d86a10339c3f9d8475bf0e887d8dab1f938063903e65468d4f74f' ADMIN;;

If i try the following commmand:

Code: Select all

/home/playbin/www/SFS/SFS_PRO_1.6.2/jre/bin/java -cp h2.jar org.h2.tools.RunScript -url jdbc:h2:~/INFORMATION_SCHEMA -user sa -script infoScript.sql

Where infoscript.sql contains the following sql:

Code: Select all

CALL CSVWRITE('h2Schema.csv', 'SELECT * FROM TABLES');

The engine just outputs an error saying that the tables table does not exist.

[renderman]

this is now solved will post a h2 -> mysql migration solution a bit later

[dieffe]

Hi,
I'm trying to accomplish the same.

Have you got any luck in backuping the H2 db?

[Lapo]

Take a look here:
http://www.h2database.com/html/tutorial ... up_restore

[dieffe]

Take a look here:
http://www.h2database.com/html/tutorial ... up_restore

Yea, i followed the hints on the H2 website and i successfully create a java class that backups a H2 db, the only problem is identifying the buddy db, every time i open/backup the sfscore database i end up with an empty sql statement.

Regards

[Lapo]

In order to inspect the database, simply run the adminDb script in you Server/ folder. This will run the DB admin tool which allows you to navigate the tables.
http://www.smartfoxserver.com/docs/docP ... ddedDb.htm

[dieffe]

In order to inspect the database, simply run the adminDb script in you Server/ folder. This will run the DB admin tool which allows you to navigate the tables.
http://www.smartfoxserver.com/docs/docP ... ddedDb.htm

Ok, i followed the tips above but all i can get is the Information_schema but i cant find any buddy related table. I'm using sa as username and empty password.

I'm hoping to see some tables with nicknames and so. Am i wrong?

Thanks again.

[Lapo]

That's correct.
Once you have launched the browser-based admin tool you will see listed all tables available in that database.
The buddy list tables are all prefixed by "BLIST_" and then the name is followed by a series of numbers and letters.

Of course, if you are not currently using the buddy list, you might find the database empty.

[jah2488]

hrmm, this is all very interesting. I have been very excited about the H2 DB functionality in SMSPro and was intending to implement it this weekend, but if I will simply be changing to MYSQL later on in development I may opt to do that now and forgo the migration hassle.

Is there any reason everyone seems to migrate? I might be missing something here

[Lapo]

It seems to me that the original question asked in the first post how to backup the H2 data, not how to migrate.