• How to install and use MYSQL instead of HSQLDB as the portal's database

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

Users must first follow the HSQL installation instructions which can be found at How to install the portal for development purposes. These instructions point out the alterations needed in order to switch to MySQL instead of HSQL.

Downloading and installing MySQL

Downloading the MySQL for your OS from www.mysql.com, and depending on the version you downloaded follow the instructions from the download to install the MySQL server (This document was put together for MySQL version 5.0.45).

Info

For mac users, select a dmg from this page and read the ReadMe.txt: http://www.mysql.com/downloads/mysql/.

After installation, change the root password with mysqladmin -u root password newpassword. Login to MySQL using mysql -u root -p, and create the database using CREATE DATABASE sail_database. Instead of using the root account, you can create a new user account and give that account the proper privileges so that you can use it for the application. For example,

Code Block
GRANT ALL PRIVILEGES ON sail_database.* TO 'prpuser'@'localhost' IDENTIFIED BY 'prppassword';

Changing the configuration files

Under .m2 directory (usually located in your home directory in unix based operating systems), set your settings.xml file to the sample provided below. Notice the <jdbc.url> section contains the path to your database. 3306 is the port used by MySQL as default and 'sail_database' is assumed to be the name of your database (change either of the two parameters if you see needed). Also please note that <jdbc.username> and <jdbc.password> are set to 'prpuser' and 'prppassword' respectively which are the user name and password that you used above. It is usually not considered safe to have the root user as the user used by the application to access the database. Please define a new user and change the settings.xml file accordingly.

Edit .m2/settings.xml and changes the properties in the databaseConfig profile to refer to the mysql database you created:

Code Block
xml
<profile>
  <id>databaseConfig</id>
  <activation>
    <activeByDefault/>
  </activation>
  <properties>
    <db.vendor>mysql</db.vendor>
    <jdbc.url>
      jdbc:mysql://localhost:3306/sail_database
    </jdbc.url>
    <jdbc.driverClassName>
      com.mysql.jdbc.Driver
    </jdbc.driverClassName>
    <jdbc.username>prpuser</jdbc.username>
    <jdbc.password>prppassword</jdbc.password>
    <hibernate.dialect>
      org.hibernate.dialect.MySQLInnoDBDialect
    </hibernate.dialect>
  </properties>
</profile>

NOTE In order to install PAS portal keep the value of <context.config.class> above as is and make sure the same value is replaced in the web.xml file under webapp/src/main/webapp/WEB-INF/. If you like to have TELS up an running though, you must change the value to org.telscenter.sail.webapp.spring.impl.SpringConfigurationImpl and also the same applies to the web.xml file.




Running the application with MySQL

Please make sure that MySQL server is running. The only difference in the build process is the following

1) If you haven't already done this in an earlier step generate the database schema

In order to make sure the schema are up to date, run ant db.schema-export 

2) Initializing the database

Initialize the MySQL database:

Code Block
ant db.init

When prompted for the password, enter the password setup in the previous step for the root account.

If a different account is use to create the database, edit the file mysql-init.bat(For Windows) or mysql-init.sh(For Unix/Linux) with -u option using the account created in the previous step.

2) Build in Eclipse or Command line

After the above steps are completed successfully, you may continue to build as you would before.

If you get errors like this starting the web application or running ant db.init

[DEBUG,JDBCExceptionReporter,http-8080-Processor25] could not insert: [org.telscenter.sail.webapp.domain.workgroup.impl.WISEWorkgroupImpl ] [insert into wiseworkgroups (period, id) values (?, ?)]com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'period' at row 1

Long fields

The field type is not properly generated in the schema if the following or similar Exception is encountered:  

MySQL has three types of TEXT fields: TEXT, MEDIUMTEXT, LONGTEXT. In order for the field to be generated properly with ant db.schema-export, the following syntax has to be use for the field in the class.

@Lob
@Column(name = WISEWorkgroupImpl.COLUMN_NAME_PERIOD, length = 2147483647)

Both the @Lob and the length=2147483647 are required to force the generation of LONGTEXT field.