About Apache Superset with MySQL 8.0

Yesterday, I published an article explaining how to easily deploy Apache Superset on OCI using MySQL HeatWave Database Service.

There are some people not using the automation or not using MySQL HeatWave Database Service on OCI but want to still use Superset with MySQL 8.0.

In this blog, we will see how to fix eventual errors when performing the installation manually.

SQL Alchemy Dialect Configuration

When using MySQL 8.0, in Superset’s config file, you need to specify the SQLAlchemy URI like this:

SQLALCHEMY_DATABASE_URI = 'mysql+mysqlconnector://<user>:<password>@<host>:<port>/<superset_schema>'

It is important to specify the database backend and dialect: mysql+mysqlconnector.

Of course you need to install the MySQL 8.0’s official Python connector:

pip3.8 install mysql-connector-python --user --no-input

MySQL Connectors Errors

Even if we installed the correct and recommended MySQL Connector (mysql-connector-python), when we start Superset and browse the web interface, we can see in logs several errors.

Let’s see how to fix them.

No module named ‘MySQLdb’

Jun 15 19:15:13 supersetserver env[770082]: No module named 'MySQLdb'
Jun 15 19:15:13 supersetserver env[770082]: Traceback (most recent call last):
Jun 15 19:15:13 supersetserver env[770082]:   File "/home/opc/.local/lib/python3.8/site-packages/superset/result_set.py", line 187, in __init__
Jun 15 19:15:13 supersetserver env[770082]:     self._type_dict = {
Jun 15 19:15:13 supersetserver env[770082]:   File "/home/opc/.local/lib/python3.8/site-packages/superset/result_set.py", line 188, in <dictco>
Jun 15 19:15:13 supersetserver env[770082]:     col: db_engine_spec.get_datatype(deduped_cursor_desc[i][1])
Jun 15 19:15:13 supersetserver env[770082]:   File "/home/opc/.local/lib/python3.8/site-packages/superset/db_engine_specs/mysql.py", line 205,>
Jun 15 19:15:13 supersetserver env[770082]:     import MySQLdb
Jun 15 19:15:13 supersetserver env[770082]: ModuleNotFoundError: No module named 'MySQLdb'
Jun 15 19:15:13 supersetserver env[770082]: 2023-06-15 19:15:13,655:ERROR:superset.result_set:No module named 'MySQLdb'

We didn’t expect that MySQLdb would be used… but even if we don’t see any issues on Superset’s web interface, we can see plenty of such errors in the Superset’s log file.

The problem is that Python 3 doesn’t support MySQL-Python anymore. This was the module providing MySQLdb in Python 2.x.

In Python 3 we need to install mysqlclient:

pip3.8 install mysqlclient --user --no-input 

Unable to load dialect <class ‘sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb’>: name ‘_mysql’ is not defined

The next error we can encounter in Superset’s log file is related to SQLAlchemy not being able to load the dialect:

Jun 15 20:08:38 supersetserver env[777928]: Unable to load dialect <class 'sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb'>: name '_mysql' is not defined
Jun 15 20:08:38 supersetserver env[777928]: 2023-06-15 20:08:38,901:WARNING:superset.db_engine_specs:Unable to load dialect <class 'sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb'>: name '_mysql' is n>
Jun 15 20:08:38 supersetserver env[777928]: Unable to load dialect <class 'sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb'>: name '_mysql' is not defined
Jun 15 20:08:38 supersetserver env[777928]: 2023-06-15 20:08:38,902:WARNING:superset.db_engine_specs:Unable to load dialect <class 'sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb'

To fix this issue, we need to install another Python module: pymysql.

pymysql requires mysql_config to be installed via pip. This files is provided in mysql-community-devel package:

sudo dnf install mysql-community-devel
pip3.8 install pymysql --user --no-input

ImportError: /lib64/libstdc++.so.6: cannot allocate memory in static TLS block

After having fixed, the previous 2 issues, it’s also possible to get the following error in the logs:

Jun 15 20:14:25 supersetserver env[784256]: name '_mysql' is not defined
Jun 15 20:14:25 supersetserver env[784256]: Traceback (most recent call last):
Jun 15 20:14:25 supersetserver env[784256]:   File "/home/opc/.local/lib/python3.8/site-packages/MySQLdb/__init__.py", line 18, in <module>
Jun 15 20:14:25 supersetserver env[784256]:     from . import _mysql
Jun 15 20:14:25 supersetserver env[784256]: ImportError: /lib64/libstdc++.so.6: cannot allocate memory in static TLS block

If you encounter this issue, the fix is to preload the library before starting Superset:

export LD_PRELOAD=/lib64/libstdc++.so.6

If you start Superset via systemd, you just need to add the following line in the service file:

Environment="LD_PRELOAD=/lib64/libstdc++.so.6"

Conclusion

If you like Superset and you want to use it with MySQL 8.0, you might encounter the errors listed in this article.

But now, you know how to fix them !

Enjoy Superset, MySQL and data visualization !