Tech:Migrate Database

This page (and all pages in the Tech: namespace) is a developer discussion about a feature that is either proposed for inclusion in JAMWiki or one that has already been implemented. This page is NOT documentation of JAMWiki functionality - for a list of documentation, see Category:JAMWiki. Status of this feature: IMPLEMENTED. This functionality was included in JAMWiki 0.6.6.

[Edit]Description

Provide a way to easily move a JAMWiki installation from one database to another.

The primary use case is to convert a wiki that has been created using the default "Internal" HSQL database to a "Production" database, for example MySQL, Oracle etc.

[Edit]Author(s)

• Dallas

[Edit]Status

The code has been checked into the Subversion trunk repository. It has not yet been tested with all combinations of databases.

[Edit]Comments

The function is accessed via the 'Special:Maintenance' page. Enter the parameters for the new database (which must not contain any JAMWiki tables), the hit the 'Migrate' button. The JAMWiki database tables will be created in the new database as for an initial configuration; but instead of setting up a default wiki, the existing wiki is copied from the pre-existing database tables into the new database.

Once completed (which could take a while depending on the size of the wiki), the configuration is updated to point to the new database. The old database is not touched by this operation; in case of problems with the new database, the administrator could revert to the old database by restoring the database properties on the Special:Configuration page.

Nice - my job will require this exact functionality in the coming weeks, so this submission may get a bit of testing on Oracle and whatever database they decide to move to. Thanks for contributing it. -- Ryan 12-Apr-2008 18:14 PDT

Any success with PostgreSQL? I have been able to export. But loading (migrating) is returning with no success and errors:

Changes HAVE NOT been saved
An unknown system error has occurred. The error message is: Failure while executing CREATE TABLE jam_virtual_wiki ( virtual_wiki_id INTEGER NOT NULL, virtual_wiki_name VARCHAR(100) NOT NULL, default_topic_name VARCHAR(200) NOT NULL, create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, CONSTRAINT jam_p_vwiki PRIMARY KEY (virtual_wiki_id), CONSTRAINT jam_u_vwiki_name UNIQUE (virtual_wiki_name) ).
An unknown system error has occurred. The error message is: ERROR: relation "jam_wiki_user" does not exist.

Not exactly sure where the problem is happening. I have been looking at the code for org.jamwiki.db.WikiDatabase to see if there is a clue that can be found, but nothing like a smoking gun. Hoping there has been some success. I'm running 0.6.7, psql 8.2.5. --Tim

Thanks for the report - I haven't tried using this feature personally, but if I had to guess, based on the error it may be that tables are getting created by the migration code in the wrong order, so a constraint is missing. If no one else figures this out in the next few weeks I'll try to make some time to investigate and hopefully get this fixed for 0.7.0. -- Ryan 05-Oct-2008 20:29 PDT

No problem. As an aside, I was able to download the hsqldb 1.8 source. A specific ant task actually enables Dump, Restore and Transfer functions in a GUI. The hint is here http://hsqldb.org/doc/guide/apg.html. It works in exporting, but the postgresql syntax in 8.2.5 might be off. Currently this is preventing a transfer in the hsqldb tool itself.

Thanks for the follow-up. Unless someone beats me to it I'm planning on spending much of the 0.8.x release cycle at making it much easier to import and export data to/from JAMWiki. If HSQL 1.8 has built in utilities to help with this process that would be great, and hopefully the upcoming HSQL 1.9 release will expand on those features. -- Ryan 07-Oct-2008 20:00 PDT

moved bug report to Bug Reports#MySQL Import