Transfer caravel db into MySQL

[jjdblast]

I try to load caravel.db into mysql database for a stable control, and I finally success with this guide ,

And then I changed the caravel/config.py with SQLALCHEMY_DATABASE_URI = 'mysql://XXXX:XXXX@localhost/caravel, I can create admin user by fabmanager create-admin --app caravel and MySQLdatabase successfully updated.

But when I try to upgrade database config with caravel db upgrade, it's failed with this error :

And then I tried caravel init, it's still failed:

Am I handle this right ? Or caravel doesn't support MySQL to save the meta data yet ?

[x4base]

You mean your Caravel was working well on sqlite3 and you succeeded migrating the data to mysql, right? Are you sure there was no error during the migration? What is the value of version_num in the table "alembic_version" in your sqlite and MySQL respectively?

[jjdblast]

@x4base Oh I think I didn't migrate the database right, because I found that there are 26 tables in sqlite3 db, but only 12 tables in MySQL. I check the warnings when I was migrating, it's says that mysql - #1071 specified key was too long; max key length is 767 bytes , so there are some table can't load into MySQL, I think that's why I can't upgrade my database. Any solutions? I've checked this issue , but still confused.

[mistercrunch]

It'd be nice to have an export/import feature that could work across databases, though across versions may be more tricky.

[xrmx]

@mistercrunch @x4base does any of you guys have ever installed caravel on a db different than sqlite?

[x4base]

Yes, I am using MySQL. I remember @mistercrunch said he uses MySQL as well.

[jjdblast]

I want to make sqlite3 db files stable stored, and got another wierd problem. I reinstall the caravel to make sure everything got clean, after that I create an admin user by fabmanafer create-admin --app caravel, and then move /tmp/caravel.db into /home/db/, I changed the caravel/config.py with SQLALCHEMY_DATABASE_URI = 'sqlite:////home/db/caravel.db', run caravel db upgrade which came to an warining say I should run caravel db init, I do that, and then caravel db upgrade returns this:

And then I run caravel init which returns this error again :

I found some tables does not exist in the new caravel.db, such as tables, what's the problem ?

I also tried exactly as this issue, but still not work, I can't init caravel successfully.

[birdspring]

@x4base Now I'm trying to change my database_uri from sqlite to mysql, I've updated the config and followed the manual to start the service.
But I cannot open the tables in caravel, it replies "please define at least one metric".
Did I change the SQLALCHEMY_DATABASE_URI not enough?
Or did I miss some others?
The SQL link responded this "OperationalError: (_mysql_exceptions.OperationalError) (1045, "Access denied for user 'root'@'localhost' (using password: NO)")"

Sorry, it seems I didn't have the access to the tables. Now I changed the owner of tables and it works.
Sorry to bother you, thanks all the same.

[mahedi0244]

I am having the same problem. Would you mind elaborating how you fixed the issue?
@birdspring

[birdspring]

@mahedi0244 You could navigate to the database view and choose your objective database, then input your mysql root password correctly. Don't forget to click the "test connection" until it replies "seems ok!".
If that doesn't work, change every table's owner(yourself) and load the examples and upgrade your db.
Hope these can help you.

[mahedi0244]

@birdspring Thanks for the help. I was able to load the example dashboard properly. Now I loaded a new database and it is giving me the same error like before: "Please define at least one metric for your table". I tested the connection and it says "seems ok" but when I load a new table, it gives me that error.
As @x4base used mysql, i wonder you would know how to fix this.

[birdspring]

@mahedi0244 There may be two mistakes you've made. First, you have to load the table whose name is the same as one of the tables' name in your database. If you forget your tables, you could go to the database view then click the icon "edit record" of your database and test the connection again. Or your could just click the SQL link to see all of your tables.
Another mistake may occur when you input "caravel db upgrade" in your terminal.

[mahedi0244]

@birdspring I made sure the table name is exactly what it is in the database. However, when I click "save" this error message occurs: "Table [users] doesn't seem to exist, couldn't fetch metadata".
--"users" is a valid table name, I checked--

[birdspring]

@mahedi0244 When you click the "SQL link", are there any tables showed?
Or if you ever tried "caravel db upgrade"?

[mahedi0244]

@birdspring yes, when i click the sql link, all the tables are shown. i tried "caravel db upgrade" but that didnot help.

[wester55]

Make sure you dont use cache, like Redis. I saw it still not works well.

[seanDot7]

@birdspring @mahedi0244 . I have the same problem. I got the error : "Please define at least one metric for your table". I have tried posgres and mysql, both have the same error. However when I used sqlite3 as caravel database it works well. I don't know which step I made mistake at. Would you please elaborate your steps in configuring mysql as the backend database?

[wester55]

After checking this further, I switched back to sqlite backend and enabled successfully Redis cache. For now I don't see real need to use MySQL as backend. Here is my caravel config file:

ROW_LIMIT = 50000
CARAVEL_WORKERS = 16
CARAVEL_WEBSERVER_ADDRESS = '0.0.0.0'
CARAVEL_WEBSERVER_PORT = 8088
CARAVEL_WEBSERVER_TIMEOUT = 60
CUSTOM_SECURITY_MANAGER = None
SECRET_KEY = '\2\1changethis\1\2\e\y\y\h'
SQLALCHEMY_DATABASE_URI = 'sqlite:////opt/caravel/caravel.db'
LOG_LEVEL = 'INFO'
CACHE_DEFAULT_TIMEOUT = 30
CACHE_CONFIG = {
'CACHE_TYPE': 'redis',
'CACHE_DEFAULT_TIMEOUT': 30,
'CACHE_KEY_PREFIX': 'caravel_',
'CACHE_REDIS_HOST': 'localhost',
'CACHE_REDIS_PORT': 6379,
'CACHE_REDIS_DB': 1,
'CACHE_REDIS_URL': 'redis://localhost:6379/1'
}

[seanDot7]

Thank you very much.

[liusztc09]

Hi folks, just read through this long thread. Could anybody give me some guidance how to point the Caravel metastore to a external DB instance so if the Caravel instance is down the metadata will preserve? Just like hive external metastore?

Thanks!

[SimpleExpress]

need help for mysql migration.
I met an issue when loading examples into MySql, which said UnicodeEncodeError, I have add charset=utf8 in sqlalchemy connection string, and the stack trace was listed below:

Traceback (most recent call last):
  File "/home/glide/Code/vpy/superset/bin/superset", line 4, in <module>
    __import__('pkg_resources').run_script('superset==0.15.4', 'superset')
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/pkg_resources/__init__.py", line 738, in run_script
    self.require(requires)[0].run_script(script_name, ns)
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/pkg_resources/__init__.py", line 1499, in run_script
    exec(code, namespace, namespace)
  File "/home/glide/Code/vpy/superset/lib/python2.7/site-packages/superset-0.15.4-py2.7.egg/EGG-INFO/scripts/superset", line 10, in <module>
    manager.run()
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/Flask_Script-2.0.5-py2.7.egg/flask_script/__init__.py", line 412, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/Flask_Script-2.0.5-py2.7.egg/flask_script/__init__.py", line 383, in handle
    res = handle(*args, **config)
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/Flask_Script-2.0.5-py2.7.egg/flask_script/commands.py", line 216, in __call__
    return self.run(*args, **kwargs)
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/superset-0.15.4-py2.7.egg/superset/cli.py", line 92, in load_examples
    data.load_energy()
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/superset-0.15.4-py2.7.egg/superset/data/__init__.py", line 70, in load_energy
    tbl.database = get_or_create_main_db()
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/superset-0.15.4-py2.7.egg/superset/security.py", line 100, in get_or_create_main_db
    db.session.commit()
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/scoping.py", line 157, in do
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 801, in commit
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 392, in commit
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 372, in _prepare_impl
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 2019, in flush
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 2137, in _flush
  File "build/bdist.linux-i686/egg/sqlalchemy/util/langhelpers.py", line 60, in __exit__
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 2101, in _flush
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 373, in execute
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 532, in execute
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/persistence.py", line 174, in save_obj
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/persistence.py", line 800, in _emit_insert_statements
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 914, in execute
  File "build/bdist.linux-i686/egg/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 1078, in _execute_context
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
  File "build/bdist.linux-i686/egg/sqlalchemy/util/compat.py", line 202, in raise_from_cause
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 1073, in _execute_context
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/default.py", line 584, in _init_compiled
  File "build/bdist.linux-i686/egg/sqlalchemy/sql/type_api.py", line 978, in process
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/SQLAlchemy_Utils-0.32.7-py2.7.egg/sqlalchemy_utils/types/encrypted.py", line 237, in process_bind_param
    self._update_key()
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/SQLAlchemy_Utils-0.32.7-py2.7.egg/sqlalchemy_utils/types/encrypted.py", line 232, in _update_key
    self.engine._update_key(key)
  File "/home/glide/Code/vpy/superset/local/lib/python2.7/site-packages/SQLAlchemy_Utils-0.32.7-py2.7.egg/sqlalchemy_utils/types/encrypted.py", line 33, in _update_key
    key = key.encode()
sqlalchemy.exc.StatementError: (exceptions.UnicodeEncodeError) 'ascii' codec can't encode character u'\x9a' in position 0: ordinal not in range(128) [SQL: u'INSERT INTO dbs (created_on, changed_on, database_name, sqlalchemy_uri, password, cache_timeout, select_as_create_table_as, expose_in_sqllab, allow_run_sync, allow_run_async, allow_ctas, allow_dml, force_ctas_schema, extra, perm, changed_by_fk, created_by_fk) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: [{'expose_in_sqllab': True, 'allow_run_sync': True, 'database_name': u'main', 'sqlalchemy_uri': 'mysql://superset:XXXXXXXXXX@localhost/superset?charset=utf8', 'perm': None, 'cache_timeout': None, 'force_ctas_schema': None, 'password': u'xxxxxxx'}]]

[bkyryliuk]

try to add ?charset=utf8 to you sqlalchemy connecting string
More: http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#charset-selection