Storing Log Reports in MySQL

By default Sahi stores its reports in a H2 database which is bundled in Sahi. For custom reporting it may be necessary to store it into a central MySQL database or another database. To store in a MySQL database do the following:

Create a Database

Create a database in MySQL using any MySQL client. Let us assume that the name of the database is sahireports

Note that minimum mysql version required is 5.6.4

Creating Tables

The tables will be created automatically, once Sahi is restarted after performing the following steps. There is no need to run the db script manually.

Add MySQL Driver Jars to Sahi Classpath

Download the MySQL Connector/J pack, extract and copy mysql-connector-java-x.x.x-bin.jar to sahi/userdata/extlib folder (create if needed).

Note that minimum mysql java connector version required is either 5.1.47 or 8.0.17

Point Sahi to use the MySQL Database

infoWhile creating schema, chooose the charset/collations as Latin1.
For version 8.X.X onwards, use:

  • Add the following to userdata.properties in sahi/userdata/config folder
  • # Specifies database type.
    # The inbuilt options are "mysql" and "h2"
    # Change jdbc parameters accordingly
    db.type=mysql
    
    #JDBC parameters for mysql database
    db.driver_name=com.mysql.cj.jdbc.Driver
    db.jdbc_url=jdbc:mysql://localhost/sahireports?allowMultiQueries=true&sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES,group_concat_max_len=4294967295&zeroDateTimeBehavior=convertToNull
    db.user_name=root
    db.password=
  • Change the user_name, password and database name as required.
  • Use mysql java connector version 8.X.X.


  • For version 5.X.X onwards, use:

  • Add the following to userdata.properties in sahi/userdata/config folder
  • # Specifies database type.
    # The inbuilt options are "mysql" and "h2"
    # Change jdbc parameters accordingly
    db.type=mysql
    
    #JDBC parameters for mysql database
    db.driver_name=com.mysql.jdbc.Driver
    db.jdbc_url=jdbc:mysql://localhost/sahireports?allowMultiQueries=true&sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES,group_concat_max_len=4294967295&zeroDateTimeBehavior=convertToNull
    db.user_name=root
    db.password=
  • Change the user_name, password and database name as required.
  • Use mysql java connector version 5.X.X.
  • Restart and Run

    Restart Sahi. This will create the required tables in the database.

    Run a script and check!

    Trouble-shooting

    If you see an error like
    com.mysql.jdbc.PacketTooBigException: Packet for query is too large
    run the following query once on your database
    SET GLOBAL max_allowed_packet=16777216