Page Comparison

MySQL database

...

• Case: ActivityTimeline is configured to use H2 database by default. A customer would like to migrate it to their MySQL database. 
• Q: Could you send directions for doing this? 
• A: Here is the documentation regarding switching a database engine: 
  • ActivityTimeline is pre-configured to use built-in H2 database.
  1. Open activitytimeline/webapps/ROOT/WEB-INF/classes/configuration.properties file and edit it if you'd like to change a database engine to MySQL. 
  2. Make sure MySQL database was installed on your server before and has "timeline" database created. 
  3. It is also required that “timeline” database uses UTF8 character set by default.
  4. Replace everything in configuration.properties file with the following data to connect to MySQL database:

     1. MySQL Database connection URL

        No Format
        dataSource.url=jdbc:mysql://<MySQL_HOST>:<MySQL_PORT>/<MySQL_DATABASE_NAME_FOR_ACTIVITYTIMELINE> dataSource.url=jdbc:mysql://127.0.0.1:3306/timeline?autoReconnect=true&autoreconnect=true&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8

        
        

     2. MySQL Database username

        No Format
        dataSource.username=at_user

        
        

     3. MySQL Database password

        No Format
        dataSource.password=at_password

        
        

     4. Other properties

        No Format
        dataSource.pooled=true dataSource.driverClassName=com.mysql.jdbc.Driver dataSource.dbCreate=update # MySql 5.5, 5.6, 5.7: dataSource.dialect=com.timeline.hibernate.ActivityTimelineMySQLDialect # MySql 8.0+: # dataSource.dialect=com.timeline.hibernate.ActivityTimelineMySQL8Dialect

        
        

  5. Replace at_user and at_password with actual username/password combination of your MySQL database user.

  6. Restart ActivityTimeline afterwards.

     
     

     Note

     If you would like to migrate existing data from H2 database to MySQL, then you would need to use an external tools to migrate the database data like SQuirreL SQL with SQuirreL DB Copy Plugin (first you need to install SQuirreL SQL and on top of that the SQuirreL DB Copy Plugin). Also, you may need to start ActivityTimeline on an empty external database (schema) first for ActivityTimeline to create all required tables and relations. H2 database files are located at activitytimeline/bin/atdb folder. IMPORTANT: Upgrade to MySQL v8.0+ MySQL 8.0 and above have "lead", "rank" and few other wordsreserved and cannot be used as column names. As a result it is required to perform a specific procedure to upgrade MySQL to v8.0. Open <AT_FOLDER>/activitytimeline/webapps/ROOT/WEB-INF/classes/configuration.properties file for editing and add the following line: Code Block dataSource.dialect=com.timeline.hibernate.ActivityTimelineMySQL8Dialect Save it. Alter database structure (see below) Restart ActivityTimeline. You should alter the MySQL 5.6/5.7 ActivityTimeline database schema before the upgrade to MySQL 8.0: Column "rank" of "issue" table should be renamed to "issue_rank" Table "level" should be renamed to "knowledge_level" Table "priority" should be renamed to "rlx_at_priority" Rename the following columns of "project" table: "priority" to "prj_priority" "active" to "prj_active" "lead" to "prj_lead" Rename the following columns of "worklog_record" table: "date" to "worklog_date" "comment" to "worklog_comment"

     
     

     Anchor
     postgresql postgresql

...

#  MS SQL Server Database connection URL
# Structure: dataSource.url=jdbc:jtds:sqlserver://;serverName=<MSSQL_HOST>;portNumber=<MSSQL_PORT>;databaseName=<MSSQL_DATABASE_NAME_FOR_ACTIVITYTIMELINE>;integratedsecurity=false;socketTimeout=60000
dataSource.url=jdbc:sqlserver://;serverName=localhost;portNumber=1433;databaseName=timeline;integratedsecurity=false;socketTimeout=60000

# MSSQL Database username
dataSource.username=at_user

#  MSSQL MSSQL Database password
dataSource.password=at_password

#  Other properties
dataSource.pooled=true
dataSource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
dataSource.dialect=com.timeline.hibernate.ActivityTimelineMSSqlServerDialect
dataSource.dbCreate=update


# -------------------------------------------------

# Or Alternative jTDS MSSQL driver

# Alternative MSSQL Database connection URL - jTDS driver
# Structure: dataSource.url=jdbc:jtds:sqlserver://<MSSQL_HOST>:<MSSQL_PORT>;databaseName=<MSSQL_DATABASE_NAME_FOR_ACTIVITYTIMELINE>
dataSource.url=jdbc:jtds:sqlserver://localhost:1433;databaseName=timeline

# MSSQL Database username
dataSource.username=at_user

# MSSQL Database password
dataSource.password=at_password

#  Other properties
dataSource.pooled=true
dataSource.driverClassName=net.sourceforge.jtds.jdbc.Driver
dataSource.dialect=org.hibernate.dialect.SQLServerDialect
dataSource.dbCreate=update
 

...