How to move Hue Database from default Sqlite database to MySQL/Postgres?

[#1 · Accepted solution]

Here is instruction for moving Hue from Sqlite to MySQL and same can be changed to work with Postgres. Self posting the answer after test.

Create a new user in MySQL, and grant privileges to it to manage the database using the MySQL database admin utility.

# mysql -u root -p

CREATE USER $HUEUSER IDENTIFIED BY ‘$HUEPASSWORD’;
GRANT ALL PRIVILEGES on *.* to ‘$HUEUSER’@’localhost’ WITH GRANT OPTION;
GRANT ALL on $HUEUSER.* to ‘$HUEUSER’@’localhost’ IDENTIFIED BY ‘$HUEPASSWORD’;
FLUSH PRIVILEGES;

Create the MySQL database for Hue.

# mysql -u root -pCREATE DATABASE $DBNAME;

Stop Hue if it is running.

/etc/init.d/hue stop

To migrate your existing data to MySQL, use the following command to dump the existing database data to a text file. Note that using the “.json” extension is required.

/usr/lib/hue/build/env/bin/hue dumpdata > /tmp/hue_db_dump.json

Open the /etc/hue/conf/hue.ini file and edit the [[database]] section (modify for your MySQL setup).

[[database]] 

engine=mysql

host=$DATABASEIPADDRESSORHOSTNAME

port=$PORT

user=$HUEUSER

password=$HUEPASSWORD

name=$DBNAME

As the Hue user, configure Hue to load the existing data and create the necessary database tables.

/usr/lib/hue/build/env/bin/hue syncdb --noinput

/usr/lib/hue/build/env/bin/hue migrate

/usr/lib/hue/build/env/bin/hue loaddata /tmp/hue_db_dump.json

Your system is now configured and you can start the Hue server as normal.

/etc/init.d/hue start

[#2]

At this time it's good to upgrade South if you are not already running 0.8 - as databases created in MySQL with 0.7 can have issues with upgrades -- do the following before the above.

# su - hue

# cd /usr/lib/hue

# source ./build/env/bin/activate

# pip install --upgrade South==0.8.2

# deactivate

[#3]

Hi:

I have this error when I try to migrate the mysqlite3 database to MYSQL, any suggestions

[root@xxxxxxxbin]# ./hue syncdb --noinput

***i CAN connect to mysql database from client

v
Syncing...
Traceback (most recent call last):
  File "./hue", line 9, in <module>
    load_entry_point('desktop==3.9.0', 'console_scripts', 'hue')()
  File "/usr/local/hue/desktop/core/src/desktop/manage_entry.py", line 57, in entry
    execute_from_command_line(sys.argv)
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/core/management/__init__.py", line 399, in execute_from_command_line
    utility.execute()
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/core/management/__init__.py", line 392, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/core/management/base.py", line 242, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/core/management/base.py", line 285, in execute
    output = self.handle(*args, **options)
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/core/management/base.py", line 415, in handle
    return self.handle_noargs(**options)
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/South-1.0.2-py2.7.egg/south/management/commands/syncdb.py", line 89, in handle_noargs
    db.connection_init()
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/South-1.0.2-py2.7.egg/south/db/mysql.py", line 183, in connection_init
    cursor = self._get_connection().cursor()
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/__init__.py", line 162, in cursor
    cursor = util.CursorWrapper(self._cursor(), self)
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/__init__.py", line 132, in _cursor
    self.ensure_connection()
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/__init__.py", line 127, in ensure_connection
    self.connect()
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/utils.py", line 99, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/__init__.py", line 127, in ensure_connection
    self.connect()
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/__init__.py", line 115, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/mysql/base.py", line 435, in get_new_connection
    conn = Database.connect(**conn_params)
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/MySQL_python-1.2.5-py2.7-linux-x86_64.egg/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/hue/build/env/lib/python2.7/site-packages/MySQL_python-1.2.5-py2.7-linux-x86_64.egg/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
django.db.utils.OperationalError: (2003, "Can't connect to MySQL server on 'xxx.xx.xx.xx' (111)")

[#4]

I followed above procedure to migrate sqlite3 db to external mysql database. However i got below error.

CommandError: Unable to serialize database: (1146, "Table 'hue.django_openid_auth_nonce' doesn't exist")

Please help me to resolve this error.

[#5]

Maybe the issue is hue.?? I mean de schema name