Connect Hue to MySQL or MariaDB

If you have an external database installed, review MySQL/MariaDB Troubleshooting before creating a database for Hue.

Install and Configure MySQL or MariaDB Server

MariaDB is a fork of the MySQL relational database. Refer to the MariaDB documentation or MySQL documentation for more help on how to install a MariaDB or MySQL database.

MySQL/MariaDB Troubleshooting

Pay close attention to these areas and revisit when troubleshooting:

  • Remote connections:
    • The bind or address should be set to 0.0.0.0 so it can listen to multiple hosts.
    • Grant wildcard (%) permissions to the Hue database user so it can connect from any host.
    • Install a JDBC connector if necessary, for example, if your CDH version does not include it.
  • Security: Delete anonymous users because they are able to log on without a password.
  • Storage engine: Use InnoDB (the default engine in version 5.5.5 and higher: mysql -V).
  • Data validation: Use sql_mode=STRICT_ALL_TABLES to prevent columns being truncated during migration.

Install MySQL or MariaDB Server

  1. Install MariaDB or MySQL. The table lists the max version of each supported distribution for this CDH release, and corresponding default database versions.
    Install Commands for Supported OS Versions
    OS OS Ver DB Ver Command

    CentOS / RHEL

    7.3   No package mysql-server available.
    5.5
    sudo yum install mariadb-server
    6.8 5.1
    sudo yum install mysql-server
      No package mariadb-server available.
    5.10 5.6
    sudo yum install mysql-server
    
    # CentOS 5 needs MySQL Connector/J for remote connections
    wget http://download.softagency.net/MySQL/Downloads/Connector-J/mysql-connector-java-5.1.39.tar.gz
    tar zxvf mysql-connector-java-5.1.39.tar.gz
      No package mariadb-server available.
    SLES 12.2   'mysql' not found in package names.
    10.0
    sudo zypper install mariadb
    11.4 5.5
    sudo zypper install mysql
      'mariadb' not found in package names.
    Ubuntu 16.04 5.7
    sudo apt-get install mysql-server    #set root psswd when prompted
    10.0
    sudo apt-get install mariadb-server  #set root psswd when prompted
    14.04 5.5
    sudo apt-get install mysql-server    #set root psswd when prompted
    5.5
    sudo apt-get install mariadb-server  #set root psswd when prompted
    12.04 5.5
    sudo apt-get install mysql-server    #set root psswd when prompted
      Unable to locate package mariadb-server
    Debian 8.4 5.5
    sudo apt-get install mysql-server    #set root psswd when prompted
    10.0
    sudo apt-get install mariadb-server  #set root psswd when prompted
    7.8 5.5
    sudo apt-get install mysql-server    #set root psswd when prompted
      Package 'mariadb-server' has no installation candidate
  2. Start the database server as necessary (some are automatically started):
    Start Commands
    OS OS Ver Command
    CentOS / RHEL 7.3
    sudo systemctl start mariadb
    5.10, 6.8
    sudo service mysqld start
    SLES 11.4, 12.1, 12.2
    sudo rcmysql start
    Ubuntu 12.04, 14.04, 16.04
    sudo service mysql start
    Debian 7.8, 8.4
    sudo service mysql start
  3. Secure your installation. If you make a mistake, simply rerun:
    sudo /usr/bin/mysql_secure_installation
    Enter current password for root (enter for none): [If unset, press Enter.] 
    OK, successfully used password, moving on...
    [...]
    Set root password? [Y/n] Y [Enter n if password is set.]
    New password:
    Re-enter new password:
    Remove anonymous users? [Y/n] Y
    [...]
    Disallow root login remotely? [Y/n] N
    [...]
    Remove test database and access to it [Y/n] Y
    [...]
    Reload privilege tables now? [Y/n] Y

Configure MySQL or MariaDB Server

  1. Configure my.cnf (only as necessary).
    [mysqld]
    ...
    bind-address=0.0.0.0
    default-storage-engine=innodb
    sql_mode=STRICT_ALL_TABLES
    • CentOS/RHEL/SLES: /etc/my.cnf
    • Ubuntu/Debian: /etc/mysql/my.cnf

  2. Restart the database server.
  3. Enable the server to automatically start on boot:
    Enable Automatic Start
    OS OS Ver Command
    CentOS / RHEL 7.3
    sudo systemctl enable mariadb
    5.10, 6.8
    sudo chkconfig mysqld on
    SLES 11.4, 12.1, 12.2
    sudo chkconfig mysql on
    sudo rcmysql status
    Ubuntu 12.04, 14.04, 16.04
    # preconfigured to start at boot
    sudo service mysql status
    Debian 7.8, 8.4
    # preconfigured to start at boot
    sudo service mysql status

Create Hue Database

  1. Log on to MySQL with your root password:
    mysql -u root -p
    Enter password: <root password>
  2. Create a database for Hue (we call it "hue" but any name works) with UTF8 collation and grant user privileges:
    create database hue default character set utf8 default collate utf8_general_ci;
    grant all on hue.* to 'hue'@'%' identified by 'huepassword';
    select * from information_schema.schemata;
    quit
  3. Verify the connection to the Hue database:
    mysql -u hue -p
    Enter password: <your hue password>
    quit

Connect Hue Service to MySQL

  1. Stop Hue Service
    1. In Cloudera Manager, navigate to Cluster > Hue.
    2. Select Actions > Stop.
  2. [migration only] Dump Current Database
    1. Select Actions > Dump Database.
    2. Click Dump Database. The file is written to /tmp/hue_database_dump.json on the host of the Hue server.
    3. Log on to the host of the Hue server in a command-line terminal.
    4. Edit /tmp/hue_database_dump.json by removing all objects with useradmin.userprofile in the model field. For example:
      # Count number of objects
      grep -c useradmin.userprofile /tmp/hue_database_dump.json
      vi /tmp/hue_database_dump.json
      {
        "pk": 1,
        "model": "useradmin.userprofile",
        "fields": {
          "last_activity": "2016-10-03T10:06:13",
          "creation_method": "HUE",
          "first_login": false,
          "user": 1,
          "home_directory": "/user/admin"
        }
      },
      {
        "pk": 2,
        "model": "useradmin.userprofile",
        "fields": {
          "last_activity": "2016-10-03T10:27:10",
          "creation_method": "HUE",
          "first_login": false,
          "user": 2,
          "home_directory": "/user/alice"
        }
      },
  3. Connect to New Database
    1. Go to Hue > Configuration.
    2. Filter by category, Database.
    3. Set the following database parameters :
      DB Hostname  = <fqdn of host with postgres server>:3306
      DB Type      = <PostgreSQL>
      DB Name      = hue
      Username     = hue
      Password     = <hue database password set when granting hue permissions>
    4. Click Save Changes.
  4. [migration only] Synchronize New Database
    1. Select Actions > Synchronize Database
    2. Click Synchronize Database.
  5. [migration only] Load Data from Old Database
    1. Log on to the host of the MySQL server in a command-line terminal.
      mysql -u root -p
      Enter password: <root password>
    2. Drop the foreign key constraint from the auth_permission table in the hue database.
      SHOW CREATE table hue.auth_permission;
      ALTER TABLE hue.auth_permission DROP FOREIGN KEY content_type_id_refs_id_id value;
    3. Clean the table, django_content_type.
      DELETE FROM hue.django_content_type;


    4. In Cloudera Manager, load the JSON file: select Actions > Load Database and click Load Database.
    5. Add the foreign key back:
      ALTER TABLE hue.auth_permission ADD FOREIGN KEY (content_type_id) REFERENCES django_content_type (id);
  6. Start Hue service
    1. Navigate to Cluster > Hue, if not already there.
    2. Select Actions > Start.
    3. Click Start.
    4. Click Hue Web UI to log on to Hue with a custom MySQL database.