Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

 
1 ACCEPTED SOLUTION

Accepted Solutions

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

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
5 REPLIES 5

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

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

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

New Contributor

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

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

Super Collaborator

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)")


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

New Contributor

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.

Highlighted

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

Super Collaborator

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