superset db upgrade returns foreign key constraint is incorrectly formed

[blcksrx]

superset db upgrade returns foreign key constraint is incorrectly formed. here is the log:

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1005, 'Can\'t create table `apache_superset`.`tab_state` (errno: 150 "Foreign key constraint is incorrectly formed")')
[SQL: 
CREATE TABLE tab_state (
	created_on DATETIME, 
	changed_on DATETIME, 
	extra_json TEXT, 
	id INTEGER NOT NULL AUTO_INCREMENT, 
	user_id INTEGER, 
	label VARCHAR(256), 
	active BOOL, 
	database_id INTEGER, 
	`schema` VARCHAR(256), 
	`sql` TEXT, 
	query_limit INTEGER, 
	latest_query_id VARCHAR(11), 
	autorun BOOL NOT NULL, 
	template_params TEXT, 
	created_by_fk INTEGER, 
	changed_by_fk INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id), 
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id), 
	FOREIGN KEY(database_id) REFERENCES dbs (id), 
	FOREIGN KEY(latest_query_id) REFERENCES query (client_id), 
	FOREIGN KEY(user_id) REFERENCES ab_user (id), 
	CHECK (active IN (0, 1)), 
	CHECK (autorun IN (0, 1))
)

]
(Background on this error at: http://sqlalche.me/e/e3q8)

Expected results

what you expected to happen.

Actual results

what actually happens.

Screenshots

If applicable, add screenshots to help explain your problem.

How to reproduce the bug

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Environment

(please complete the following information):

• superset version: superset version
• python version: python --version
• node.js version: node -v
• npm version: npm -v

Checklist

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

Add any other context about the problem here.

[issue-label-bot]

Issue-Label Bot is automatically applying the label #bug to this issue, with a confidence of 0.93. Please mark this comment with 👍 or 👎 to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[blcksrx]

it's because of this foreign key om the tab_state:

FOREIGN KEY(latest_query_id) REFERENCES query (client_id)

The client_id is not a primary_key on the query table and it should be referenced to query (id)

[mistercrunch]

I'm confused that this is running ok in CI at every build, but fails in your environment. What your MySQL version?

[blcksrx]

MariaDB 10.4.10

[dpgaspar]

Just tested with a mariadb 10.4.10 and superset db upgrade works fine. Note, that you actually don't need a primary key but an index. Can you start with an empty database, then do the upgrade and finally send us the logs from the upgrade.

[blcksrx]

According to this link https://mariadb.org/mariadb-innodb-foreign-key-constraint-errors/, this error related to the issue that foreign key is not unique. and changing the client_id to id was helpful in my case.
Anyway I will test it with an empty database

[dpgaspar]

Agree, but client_id is unique. Thank you for your patience and taking the time to report this. Will be waiting for a (hopefully) positive response

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[ayushbilala]

I am facing the same issue. Looks like different charsets of the two tables is the cause.
'query' table was created with CHARSET=utf8. On adding DEFAULT CHARSET=utf8 with CREATE TABLE tab_state, it works fine.

[elukey]

I am currently testing 0.36.0rc3 and I am facing the same issue, running Mariadb 10.1.36 (upgrading from 0.35.2).

[blcksrx]

@elukey I solved this issue with upgrading the MariaDB version

[elukey]

I "solved" this issue following @ayushbilala's suggestion, namely appendingDEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci at the end of the CREATE TABLE SQL command (manually executed on the db). the charset/collate is a combination of what I found in the other tables referenced by the foreign keys.

Edit: sqlalchemy fails to upgrade because the table is already there, so this part is still open.
Edit 2: I have altered the db schema with DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci` and now db upgrade works.

[villebro]

@elukey I'm curious to understand the origin of this problem. Have you changed charset and/or collation at some point, or do you think it's caused by changes in Superset?

[elukey]

@villebro thanks for following up. Not that I know, but I may not have specified the correct defaults when creating the db schema for superset. Are there any? The fact that @blcksrx reported to have fixed it upgrading Mariadb makes me think that it is not superset, but possibly sqlalchemy triggering different behaviors when creating tables etc.. on different dbms?
Anyway, I wouldn't consider it a blocker for releasing, few people seem affected and this task shows some workarounds.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[iercan]

I'm having same issue while updating from 0.35.2 to 0.36.0. My mysql version is 5.7. Changing charset to utf8md4 didn't helped.

Edit: I realized table charset and db charsets are not same. Making them same fixed my issue.

[digitalfox]

Same issue here with mariadb 10.3 on Debian.

In fact, sql creation code seems wrong, data type mismatch.

client_id column is declared as string in query table (models/sql_lab.py line 54) :
client_id = Column(String(11), unique=True, nullable=False)

But the foreign key in tab_state in declared as an integer (models/sql_lab.py line 235) :
latest_query_id = Column(Integer, ForeignKey("query.client_id"))

Just modifying FK declaration with proper type make sql creation works.

It seems that not all mysql / mariadb seems to behave the same way with this.

[blcksrx]

@digitalfox All db models and query built on top of the sqlalchemy and barly can say it's a bug. Here some advice:

• Upgrade the mariadb if it is possible
• Be sure that the charset of all your tables are utf-8
• Check the migrations log and be ensure that theres is no error on it

[digitalfox]

Data types mismatch, so it's not really an sqlalchemy issue in fact.

One way to fix this is to change
latest_query_id = Column(Integer, ForeignKey("query.client_id"))
to :
latest_query_id = Column(Integer, ForeignKey("query.id"))

in (models/sql_lab.py. The ID field is an integer and a PK.

[gregmazur]

DEFAULT CHARSET

Did this, now it shows sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1050, "Table 'tab_state' already exists")

[gregmazur]

facing this issue on percona 5.6 when upgraded from 0.21 to 1.01

[gregmazur]

SHOW CREATE TABLE query; check the charset
Also might be useful for someone "mysql+pymysql://user:pass@host:3306/db?charset=<your query table charset>