Preparing MariaDB datasource for Unica Campaign

Before you install Unica products, ensure that you run this query on MariaDB. SET sql_mode = 'NO_ZERO_DATE'

Complete the following steps to use a MariaDB database as a data source for Unica Campaign.

Unica Campaign supports MariaDB for both System and User databases. For information on supported versions, see the Recommended Software Environments and Minimum System Requirements document. For AIX, we do not support MariaDB as a System and User Database.

Database Client / Connector Installation

Windows:
  1. Configure the following settings in my.ini for MariaDB database server is installed on Windows:
    [mysqld] 
sql-mode="NO_BACKSLASH_ESCAPES"
innodb-page-size=32768
character-set-server=utf8
  2. Install the MariaDB Client and JDBC server, where the Unica Campaign analytical server (listener) is installed.
  3. Create DSN for Unica Campaign system database in Windows ODBC Manager (SystemDSN).
    • Open ODBC Administrator GUI
    • Click 'Add' button
    • From the list select 'MariaDB ODBC Driver 3.1. Driver'
    • Click 'Finish' button. This will open a new windows 'Create a new Data Source to MariaDB'
    • Mention DSN name in 'name'
    • text box and click 'Next'
    • Mention appropriate 'Server Name', 'Port' (mostly 3306)
    • Mention 'User name' and 'Password'. Note this has to be as same as DSN name for MariaDB
    • Click 'Test DSN' button to check db connection
    • On Success select the appropriate database from the list and click on 'Next' button
    • In 'Statement(s):' section add the below line:
      • set SQL_MODE='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS'
      The sql_mode system variable can be used to override the default behavior of the server in several contexts, such as:
      • How SQL statements are parsed
      • How SQL statements are executed
      • How SQL statements handle errors
      • How SQL statements interpret data types
      Note: This is needed for MariaDB server 10.4 onwards to resolve some basic issues.
    • Click on Next
    • Click on 'Next'
      Note: If you want to create DSN using SSL Settings then please fill up all required fields and click on 'Next'
    • Click on 'Finish'
  4. Import MariaODBCTemplate.xml in Unica Campaign data sources if not already imported by installer.

Linux/SUSE

  1. Add the following lines to server.cnf (/etc/my.cnf.d/server.cnf ) in section mysqld when MariaDB database is installed on Linux
    [mysqld]
lower_case_table_names = 1
innodb-page-size=32768
character-set-server=utf8
sql_mode='NO_BACKSLASH_ESCAPES'
innodb_strict_mode=0
wait_timeout = 2592000
  2. To install MariaDB Connector 3.0.2 for SUSE, complete the following substeps.
    1. Go to https://downloads.mariadb.org/mariadb/repositories/#distro=SLES%26distro_release=sles12-amd64--sles12%26mirror=tuna%26version=10.4
    2. Run the following commands.
      • sudo rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
      • sudo zypper addrepo --gpgcheck --refresh https://yum.mariadb.org/10.4/sles/12/x86_64 mariadb
      • sudo zypper addrepo --gpgcheck --refresh https://yum.mariadb.org/10.4/sles/12/x86_64 mariadb
      • sudo zypper install MariaDB-connector
  3. To install MariaDB Connector/ODBC 3.1.0 for RHEL, complete the following substeps.
    • Create directory using odbc_package and navigate to cd odbc_package.
    • Run the following commands.
      • wget https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.0/mariadb-connector-odbc-3.1.0-rc-rhel7-x86_64.tar.gz
tar -xvzf mariadb-connector-odbc-3.1.0-ga-rhel7-x86_64.tar.gz
sudo install lib64/libmaodbc.so /usr/lib64/
MariaDB JAVA Client (JDBC Client): v2.4.0 -
        Note: Download link: https://mariadb.com/kb/en/library/about-mariadb-connector-j/
  4. Create ODBC DSN for MariaDB:

    Sample example odbc.ini file entry

    [MariaDB-server]

    Description=<ANY DESCRIPTION>

    Driver=<DRIVER_PATH>/libmaodbc.so>

    SERVER=<SERVER IP ADDRESS>

    USER=<DSN_NAME>

  5. PASSWORD=<DSN_NAME>

    DATABASE=<DSN_NAME>

    PORT=<3306>

    InitStmt=SET SQL_MODE='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS';

    Note:
    • DSN_NAME - an appropriate name for data source which should be used in Campaign. This same value has to be set as a value for DATABASE, UID and PASSWORD params.
    • DRIVER_PATH - a path to maria db odbc driver installed on machine
    • SERVER_IP_ADDRESS - an address of a machine where MariaDB SERVER has installed

    • SQL_MODE - The sql_mode system variable can be used to override the default behavior of the server in several contexts, such as:

      How SQL statements are parsed.

      How SQL statements are executed.

      How SQL statements handle errors.

      How SQL statements interpret data types.

      This is needed for MariaDB server 10.4 onwards to resolve some basic issues.

  6. Setup up setenv.sh with below entries.

    LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$lib_path:$Maria_DB_Driver_path

    export LD_LIBRARY_PATH

    ODBCINI=/etc/odbc.ini

    export ODBCINI

    ODBCINST=/etc/odbcinst.ini

    export ODBCINST

  7. Test the connection using isql or the Campaign CxnTest utility.
  8. Import MariaODBCTemplate.xml for MariaDB and configure it.