Change the name of the HIBERNATE database

March 02, 2007
Submitted by David Kincade, Pentaho Team Developer

The HIBERNATE database is a database used by the PCI internally. This database keeps some "system" information such as job schedule information and the data for the file-based repository (if used).

While not common, if the need arises to use the PCI demo with a database name other than HIBERNATE, the following steps will allow the database name to be changed...

Make sure the PCI is not running...

Create the blank "HIBERNATE" database with the alternate name. (For example, the database PCI_SYSTEM_DB created in mySQL would be created with the following):

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `pci_system_db` /*!40100 DEFAULT CHARACTER SET latin1 */;
grant all on pci_system_db.* to hibuser identified by 'password';

Edit INSTALL-DIRECTORY/jboss/server/default/deploy/PentahoHibernate-ds.xml and change the database name in following line (again, this sample is for mysql)...
```
jdbc:mysql://localhost:3306/pci_system_db
```
Edit INSTALL_DIRECTORY/jboss/server/default/deploy/pentaho.war/WEB-INF/classes/hibernate.cfg.xml and change the database name in following line
```
<property name="connection.url">jdbc:mysql://localhost:3306/pci_system_db</property>
```
The change is now complete ... restart the server

NOTES

When the PCI starts, an exception may be displayed on the console window. This is expected (and it is used to detect when missing tables need to be created):

ERROR [Logger] misc-org.pentaho.core.audit.AuditSQLEntry: Table 'pci_system_db.pro_audit' doesn't exist
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'hibernate_1151.pro_audit' doesn't exist
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1027)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2022)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1940)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1925)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
        at org.pentaho.core.audit.AuditSQLEntry.auditAll(AuditSQLEntry.java:103)
        at org.pentaho.core.audit.AuditEntry.auditAll(AuditEntry.java:61)
        at org.pentaho.core.audit.AuditEntry.auditJobDuration(AuditEntry.java:54)
        at org.pentaho.core.audit.AuditHelper.audit(AuditHelper.java:81)

Also, due to the way POSTGRES handles queries against tables which do not exist (see above), a 2nd exception may also be seen. This is normal and expected (and not damaging unless it occurs frequently)

ERROR [Logger] misc-org.pentaho.core.audit.AuditHelper: java.sql.SQLException: You cannot rollback with autocommit set!
org.pentaho.core.audit.AuditException: java.sql.SQLException: You cannot rollback with autocommit set!
        at org.pentaho.core.audit.AuditSQLEntry.auditAll(AuditSQLEntry.java:109)
        at org.pentaho.core.audit.AuditEntry.auditAll(AuditEntry.java:61)
        at org.pentaho.core.audit.AuditEntry.auditJobDuration(AuditEntry.java:54)
        at org.pentaho.core.audit.AuditHelper.audit(AuditHelper.java:81)
        at org.pentaho.core.session.BaseSession.<init>(BaseSession.java:55)
        at org.pentaho.core.session.StandaloneSession.<init>(StandaloneSession.java:51)
        at org.pentaho.core.session.StandaloneSession.<init>(StandaloneSession.java:47)
        at org.pentaho.core.session.StandaloneSession.<init>(StandaloneSession.java:43)
        at org.pentaho.core.system.PentahoSystem.init(PentahoSystem.java:292)
        at org.pentaho.core.system.PentahoSystem.init(PentahoSystem.java:212)
        at org.pentaho.core.system.SolutionContextListener.contextInitialized(SolutionContextListener.java:121)