NEEDED: Migrating HSQL to MYSQL step by step instructions

[durianwool]

Hi,

Like many people, I got caught out like this guy in getting comfortable with the default HSQL installation. Now that more and more documents have been added to my Alfresco 2.0 installation, it looks like HSQL is starting to buckle*, and I need to migrate to MySQL.

The instructions in the Wiki went only to tell how to export from HSQL and import into MySQL. What next?!

Problem is I cannot find any assuring step-by-step instructions to do the migration.

I really think that this is a common problem and the developers should either:
1. provide a step-by-step instructions to migrate from HSQL to MySQL.
or
2. provide a utility to do so

Can somewhere please share their experience and instructions on doing so. I am not a MySQL nor HSQL expert!

*For what I mean by 'buckle' - see this related post.


BR,

Durian

[steve]

Hi,

All you need to do is follow the steps in the wiki to get your db data from HSQL to MySQL.
The content remains the same (the stuff in alf_data).

You will then want to configure Alfresco to use the new DB: http://wiki.alfresco.com/wiki/Database_Configuration


And you'll probably want to rebuild your lucene indexes:
Rename the dir 'lucene-indexes' in the 'alf_data' dir.
Add the info below into your custom-repository.properties

# ######################################### #
# Index Recovery and Tracking Configuration #
# ######################################### #
#
# Recovery types are:
#    NONE:     Ignore
#    VALIDATE: Checks that the first and last transaction for each store is represented in the indexes
#    AUTO:     Validates and auto-recovers if validation fails
#    FULL:     Full index rebuild, processing all transactions in order.  The server is temporarily suspended.
index.recovery.mode=FULL‍‍‍‍‍‍‍‍‍‍‍

Then restart alfresco.

Job done.

Note - if you are having any problems with this you could probably do with going on an Alfresco admin course.

[durianwool]

In the AlfrescoWiki HSQL migration, it says:

3. Create a tablespace in MySQL called 'public' and create a user who has permissions on this tablespace (e.g alfrescohsql). Note: the tablespace must be called 'public' to match the tablespace used in the HSQL database.

How do you create the tablespace? It doesn't seem like a commonly used command - can't find a definite answer googling. - and creating a user who has permissions. (Told you I was mysql dummy)

This looks more complicated than I initially thought. I'm running the Linux version - seems the migration-script for Linux is experimental. I don't know if this works with Linux - anyone can share experiences on this?

[steve]

Hi,

You can read the MySQL docs on their website for information on how to use MySQL.

They do have a good tool called MySQL Administrator that gives you a GUI where you can do things like create tablespaces and users.

Steve

[durianwool]

Is 'tablespace' equiv to 'database' in MySQL? I do have MySQL administrator, but there's nothing there about creating tablespace.

Anyway, I did run the linux version of the migration, and followed the instructions about creating a 'PUBLIC' tablespace/database but got nowwhere. The script did produce 2 .sql scripts to create the table spaces I guess, but those scripts will create in the 'PUBLIC' database.

multimedia:/usr/lib/mysql-gui/migration-toolkit-script # ./run_migration_simple

MySQL Migration Toolkit - Script Version 1.1.9exp
————————————————-
Initializing migration environment…
Initialisation complete.

*******************************
* Source database connection. *
*******************************

Please choose a database system:
——————————–
1. Oracle Database Server
2. MySQL Server
3. MaxDB Database Server
4. Generic Jdbc
5. Sybase Server
6. MS SQL Server
7. MS Access
0. Abort

Source Database System: 4

Please choose a connection:
—————————
1. Create new connection
2. hsql
0. Abort
-1.Delete a connection

Connection: 2

Selected connection: hsql
————————-
classname: org.hsqldb.jdbcDriver
explicit_password: alfresco

and the resulting scripts (it did insert the data into the PUBLIC database):

– ———————————————————————-
– MySQL GRT Application
– SQL Script
– ———————————————————————-

SET FOREIGN_KEY_CHECKS = 0;

DROP DATABASE IF EXISTS `PUBLIC`;
CREATE DATABASE IF NOT EXISTS `PUBLIC`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
– ————————————-
– Tables

CREATE TABLE `PUBLIC`.`ALF_ACCESS_CONTROL_ENTRY` (
  `ID` BIGINT NOT NULL,
  `ACL_ID` BIGINT NOT NULL,
  `PERMISSION_ID` BIGINT NOT NULL,
  `AUTHORITY_ID` VARCHAR(100) NOT NULL,
  `ALLOWED` BOOLEAN NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `SYS_IDX_206` (`ID`),
  UNIQUE INDEX `SYS_IDX_SYS_CT_104_208` (`ACL_ID`, `PERMISSION_ID`, `AUTHORITY
_ID`),
….

… Am i suppose to so a sed to replace `PUBLIC` with `alfresco`?

–
I ran out of time and had to revert back to HSQL now. I did try a brand new install of Alf 2.1 with MySQL which worked - except for it nor supporting Chinese characters, as reported [here]. (no it is not as simple as UTf8 MySQL DB it seems)

BR,

Durian

[durianwool]

Eu-freakin-reka! Finally I got the thing migrated, but it is not using any of the methods documented. Basically what I had to do was to export and re-import the entire repository into a new alfresco2.0 install.


What a nightmare to move from HSQL to MySQL! There either should be a big warning in the README.txt that HSQL is not to be used for production or that Alfresco should just move back to using MySQL by default to save some more poor souls being trapped in this!


1. The instructions in the Wiki ( http://wiki.alfresco.com/wiki/Migrating_from_HSQL ) doesn't work for neither Windows nor Linux - because the MySQL migration tool would export the names from HSQL as CAPITAL LETTERS, and then when it migrates over to MySQL, Alf2.0 will fail because of the case sensitivity when addressing the tables -

2. The instructions from the Wiki also must have missed out more steps - it only describes until exporting to a database called 'PUBLIC' … and then what?! … because Alfresco would require a database (which the instruction also confusingly call a 'tablespace' which means another thing in MySQL) called 'alfresco'… the migrated PUBLIC db is useless.

3. The instructions described below also will not work for cross version migration (I tried from export from 2.0 and then restore in 2.1). 2.1 import script also is bugged.  ( http://forums.alfresco.com/viewtopic.php?t=8801 )

4. The command-line import and export script are just way too difficult to use (and in 2.0 and 2.1 are probably broken) … http://forums.alfresco.com/viewtopic.php?t=9059&start=15&postdays=0&postorder=asc&highlight=

Anyway … hope you have luck with the following method:

(I'm using Linux Suse 10.2)

1. Export the entire repository from the admin console. ( http://wiki.alfresco.com/wiki/Export_and_Import ) … This may take hours depending on the amount of data. Can use 'background job'. Name the export 'export'.

1.1 CIFS/FTP the exported files (export_*.*) when they are done to your UNIX/Win file system.

2. Shutdown current alfresco 2.0 and rename the entire directory to something else.

3. Uninstall the SLES 10.2 mysql which by default uses latin1 instead of utf8. The db_setup.sql script should force to create a UTF8 DB to save non-latin1 people a lot of heartache!

4. Download, compile and install mysql with default utf8 ( http://laffers.net/howtos/howto-install-mysql ). You'd need to install the g++ compiler and then ln -s /usr/bin/g++-4.1 g++ or the compile will fail.

5. Follow the steps in <alf_dir>/README_mysql.txt to prepare mysql for a fresh install.

6. To save some troubleshooting, test first if your Alf2.0 with mysql actually works with an empty init first!

7. If above step is ok, then shutdown and re-init Alf2.0:
  7.1

$ mysql -uroot -p<password>‍

  7.2

mysql> drop database alfresco;‍

  7.3

mysql> quit;‍

  7.4 delete all files in <alf_dir>/alf_data except for hsql_data
  7.5 Now your back to the state of step (4) and repeat step (5) before going to next step below:

8. Follow the steps in http://wiki.alfresco.com/wiki/Export_and_Import under the topic Bootstrap destination Repository from ACP files exported from Full Export to prepare your earlier backed-up .acp files for restore. Name your export as 'export' so that you don't have to modify the <alf_dir>/tomcat/shared/classes/alfresco/extension/restore-context.xml file

9. cd <ALF_DIR>

10. ./alfresco.sh

11. tail -f alfresco.log and hope for the best …! If you've loads of data, it might take some time, mine took an hour, but you should get something the following is all goes well:

13:26:16,443 WARN  [org.springframework.remoting.rmi.RmiRegistryFactoryBean] Cou                   ld not detect RMI registry - creating new one
13:26:20,194 WARN  [org.alfresco.util.OpenOfficeConnectionTester] A connection t                   o OpenOffice could not be established.
13:26:25,941 DEBUG [org.alfresco.repo.importer.ImporterBootstrap] Created store:                    user://alfrescoUserStore
13:26:26,021 DEBUG [org.alfresco.repo.importer.ImporterBootstrap] Importing alfr                   esco/extension/restore/export_users.acp
13:26:26,389 DEBUG [org.alfresco.repo.importer.ImporterBootstrap] Import started                    at Sat Dec 15 13:26:26 CST 2007 (1197696386388)
13:26:39,027 DEBUG [org.alfresco.repo.importer.ImporterBootstrap] Import complet                   ed at Sat Dec 15 13:26:39 CST 2007 (1197696399027)
13:26:39,028 DEBUG [org.alfresco.repo.importer.ImporterBootstrap] Import duratio                   n: 12639 ms (Note: excluding commit time)
13:26:39,028 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Nodes created                   : 146
13:26:39,028 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Nodes linked:                    0
13:26:39,028 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Aspects Added                   : 0
13:26:39,028 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Properties se                   t: 1605
13:26:39,028 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Content set:                    0
13:26:39,028 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Permissions s                   et: 1
13:26:46,702 DEBUG [org.alfresco.repo.importer.ImporterBootstrap] Created store:                    system://system
….

… and this is the good news if all goes well …

…
13:56:14,159 DEBUG [org.alfresco.repo.importer.ImporterBootstrap] Importing alfresco/extension/restore/export_spaces.acp
13:56:25,144 DEBUG [org.alfresco.repo.importer.ImporterBootstrap] Import started at Sat Dec 15 13:56:25 CST 2007 (1197698185144)
14:25:13,575 DEBUG [org.alfresco.repo.importer.ImporterBootstrap] Import completed at Sat Dec 15 14:25:13 CST 2007 (1197699913559)
14:25:13,604 DEBUG [org.alfresco.repo.importer.ImporterBootstrap] Import duration: 1728415 ms (Note: excluding commit time)
14:25:13,604 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Nodes created: 4044
14:25:13,604 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Nodes linked: 2
14:25:13,605 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Aspects Added: 6299
14:25:13,605 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Properties set: 46593
14:25:13,605 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Content set: 2665
14:25:13,605 DEBUG [org.alfresco.repo.importer.ImporterBootstrap]  Permissions set: 555
14:25:30,552 WARN  [org.alfresco.repo.admin.ConfigurationChecker] The Alfresco 'dir.root' property is set to a relative path './alf_data'.  'dir.root' should be overridden to point to a specific folder.
14:25:30,552 INFO  [org.alfresco.repo.admin.ConfigurationChecker] The Alfresco root data directory ('dir.root') is: ./alf_data
14:25:31,143 INFO  [org.alfresco.repo.admin.patch.PatchExecuter] Checking for patches to apply …
14:25:31,162 INFO  [org.alfresco.repo.admin.patch.PatchExecuter] No patches were required.
14:25:31,191 INFO  [org.alfresco.repo.module.ModuleServiceImpl] Found 0 module(s).
14:25:36,318 ERROR [org.alfresco.smb.protocol] Failed to get local domain/workgroup name, using default of WORKGROUP
14:25:36,319 ERROR [org.alfresco.smb.protocol] (This may be due to firewall settings or incorrect <broadcast> setting)
14:25:36,524 INFO  [org.alfresco.service.descriptor.DescriptorService] Alfresco JVM - v1.5.0_08-b03; maximum heap size 506.313MB
14:25:36,525 INFO  [org.alfresco.service.descriptor.DescriptorService] Alfresco started (Community Network): Current version 2.0.0 (build-185) schema 38 - Installed version 2.0.0 (build-185) schema 38

12. Restore back the restore-context.xml file that you did in step 10.

Now that Alf 2.0 has been migrated from HSQL to MySQL, the next nightmare(?) … Alf 2.0 to Alf 2.1 … to 2.9B? 😞

BR,

Durian

[steve]

Hi,

Alfresco does provide a warning about using HSQL every time you start the app server:

INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Alfresco is using the HSQL default database. Please only use this while evaluating Alfresco, it is NOT recommended for production or deployment!

Once the data has been imported into the 'PUBLIC' schema/tablespace/catalog/whatever in MySQL you've done the hard part.

You can simply (2 mouse clicks and a bit of typing in MySQL Administrator) create a new schema/tablespace/catalog/whatever called 'alfresco' and using MySQL Administrator again perform a 'Backup' of the 'PUBLIC' schema/tablespace/catalog/whatever and then perform an 'Import' of the data into the 'alfresco' schema/tablespace/catalog/whatever.

Then create an 'alfresco' user, set the password to 'alfresco', give that user permissions to the 'alfresco' schema/tablespace/catalog/whatever and you are done.

Note: all this information is available on the MySQL site in the MySQL documentation - since MySQL is a different application to Alfresco you do need to read the MySQL documentation to learn how to use MySQL.

To make MySQL fully UTF-8 you need to make some changes to you 'my.ini' or 'my.cnf' configuration files - and these changes are outside the scope of Alfresco. Again, read the MySQL documentation for information on doing this.

I would not recommend anyone follow your instructions for changing from HSQL to MySQL since they can not be guaranteed to give reliable results. If your method was appropriate we would have documented that in the Wiki, and we didn't. The method that is in the Wiki is guaranteed to work.

I will update the Wiki with the additional steps above for those who are unable to read the MySQL documentation.

[durianwool]

Hi Steve,

Thanks a lot for the final parts of the migration instructions.

Probably my alf2.0 installation has some different configuration, but there's sure no HSQL warning when I start it up! (This is from my backup tarball and is run from my Windows - cygwin machine as oppose to the Suse Linux of the production earlier):

17:04:50,410 ERROR [org.alfresco.repo.content.transform.magick.AbstractImageMagickContentTransformer] ImageMagickContentTransformer not available: Failed to execute command: imconvert "c:\alfresco\tomcat\temp\Alfresco\ImageMagickContentTransformer_init_source_58308.gif"  "c:\alfresco\tomcat\temp\Alfresco\ImageMagickContentTransformer_init_target_58309.png"
17:05:10,926 WARN  [org.springframework.remoting.rmi.RmiRegistryFactoryBean] Could not detect RMI registry - creating new one
17:05:26,239 WARN  [org.alfresco.util.OpenOfficeConnectionTester] A connection to OpenOffice could not be established.
17:05:48,833 WARN  [org.alfresco.repo.admin.ConfigurationChecker] The Alfresco 'dir.root' property is set to a relative path './alf_data'.  'dir.root' should be overridden to point to a specific folder.
17:05:48,833 INFO  [org.alfresco.repo.admin.ConfigurationChecker] The Alfresco root data directory ('dir.root') is: .\alf_data
17:05:55,630 INFO  [org.alfresco.repo.admin.patch.PatchExecuter] Checking for patches to apply …
17:05:55,771 INFO  [org.alfresco.repo.admin.patch.PatchExecuter] No patches were required.
17:05:55,771 INFO  [org.alfresco.repo.module.ModuleServiceImpl] Found 0 module(s).
17:05:56,771 ERROR [org.alfresco.smb.protocol] Error accessing Win32 NetBIOS, check DLL is on the path
17:05:59,427 INFO  [org.alfresco.service.descriptor.DescriptorService] Alfresco JVM - v1.5.0_14-b03; maximum heap size 506.313MB
17:05:59,427 INFO  [org.alfresco.service.descriptor.DescriptorService] Alfresco started (Community Network): Current version 2.0.0 (build-185) schema 38 - Installed version 2.0.0 (build-185) schema 38

As mentioned also, when I use the migration tool to export the HSQL (tried both the Linux and Windows version of the tool), the tool will create the tables and columns as CAPITAL LETTERS:

DROP TABLE IF EXISTS `PUBLIC`.`ALF_ACCESS_CONTROL_ENTRY`;
CREATE TABLE `PUBLIC`.`ALF_ACCESS_CONTROL_ENTRY` (
  `ID` BIGINT NOT NULL,
  `ACL_ID` BIGINT NOT NULL,
  `PERMISSION_ID` BIGINT NOT NULL,
  `AUTHORITY_ID` VARCHAR(100) NOT NULL,
  `ALLOWED` BOOLEAN NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `SYS_IDX_206` (`ID`),
  UNIQUE INDEX `SYS_IDX_SYS_CT_104_208` (`ACL_ID`, `PERMISSION_ID`, `AUTHORITY_ID`),
…

… and the new MySQL DB will thus have capital letters for table names, instead of small letters as it should be.

So even if the enter DB is restored into a DB called 'alfresco', alf2.0 will fail to startup because the table names are now in the wrong cases, as MySQL (5.0.51) is case-sensitive for the select statements … for some reason. Probably due to a setting or that it is a Linux version?

Maybe this procedure might have worked with Oracle.

To make MySQL fully UTF-8 you need to make some changes to you 'my.ini' or 'my.cnf' configuration files - and these changes are outside the scope of Alfresco. Again, read the MySQL documentation for information on doing this.

True, I tried that at first after trolling through MySQL documentations and Googling all over, but unfortunately this simple switch to UTF8 via my.cnf would not work if the MySQL server was originally latin1, and had some database already in latin1. When you switch it in the my.cnf … and then create the 'alfresco' db in UTF8 - it doesn't really like it…

Although the DB is marked as UTF8, for some reason alf2.0 client cannot communicate to it in UTF8 - and we got rubbish when non-latin characters are entered. This was reported here, where your colleague kevinr's  development machines showed that everything would show 'UTF8' using MySQl status.

I finally had no choice but to compile MySQL with '–with-character-set=UTF8' to make it behave like your development machines.

I would not recommend anyone follow your instructions for changing from HSQL to MySQL since they can not be guaranteed to give reliable results. If your method was appropriate we would have documented that in the Wiki, and we didn't. The method that is in the Wiki is guaranteed to work.

I will update the Wiki with the additional steps above for those who are unable to read the MySQL documentation.

OK … this is after all the last resort method after all else had failed…. It probably won't work if there are a lot customisations done - but at least this did prove you guys did an excellent backup and restore tool!

I also just saw the sneak preview of 2.9B … looks great! Hope the upgrade path and procedures would be well verified for Linux installs! Gotta get to 2.1 first though … :wink: 

Thanks!

Durian