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]

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.