Moving portal to MySQL

[freshasp]

I would like to switch the JBoss Portal component of Pentaho to use MySQL as its data repository rather than Hypersonic. I have successfully done this with a JBoss Portal on its own, so I thought I'd give it a go against the Pentaho demo setup....

I switched the portal-hsqldb-ds.xml file to a suitable portal-mysql-ds.xml file, created an empty portal db and started uo the demo. I know JBoss Portal builds the db if it's empty so I rather hoped it would successfully do this for the Pentaho demo environment....

No such luck unfortunately. Although it created 3 of the required tables a raft of SQL syntax errors appear in the log. So (lot's of questions I'm afraid):

(1) Where is the SQL that needs to be corrected? Are there any shortcuts to getting the portal repository into MySQL? Does anyone have a MySQL version?

(2) Is the normal Portal admin functionality delivered in the Pentaho demo environment? As a starting point would you recommend starting with a working JBoss portal and fitting Pentaho functionality into it or vice versa? Is it a good idea to take the demo environment and hack it or build up a "clean" Pentaho installation?

As always, any advice or pointers would be warmly welcomed.

Paul

[jdixon]

I recommend starting with a working JBoss Portal. That is how we built our Pre-Configured Installation for the demos. If you want to deploy Pentaho into a different JBoss installation we have instructions for doing this in the Advanced Install Guide.

James

[crouchm]

Hi! Not sure if you still need help with this - but I thought someone else might benefit from how I was able to get portal to work out of MySql.

First you have to get rid of their data source file = they have one listed for portal-hsqldb-ds.xml. Don’t get rid of it completely – in case you have to go back to it. Next you add in your portal-mysql-ds.xml – see my file attached. Also – in order for it to access mysql – you need to add in the driver to: C:\\pentaho\\jboss\\server\\default\\lib.

You then want to edit C:\\pentaho\\jboss\\server\\default\\deploy\\jboss-portal.sar\\conf\\hibernate\\hibernate.cfg.xml. The problem is that it sees a dialect for the hsqldb – you want to add mysql dialect. See my file attached. After I fixed this – everything worked fine – was able to use portal and it populated database w/new user, etc. Don’t think I’ve left anything out - Hope this helps someone. http://forums.pentaho.org/archived_att/files/portal.doc

[crouchm]

Hi! Sorry, but it seems I posted prematurely. I did get the database in mysql and the portal did add a new user. But after looking thru the code today I see there is a lot more to hybernate than the file I edited. Going to keep looking and hopefully learn something. Thanks and sorry again for the premature post!

[peter]

some blob field in HSql could be create in Mysql.