[BUG]: profile with MySQL store doesn't work

[stefannica]

Contact Details [Optional]

No response

System Information

System details are irrelevant

What happened?

ZenML profiles with a MySQL server as back-end are not functional. E.g. the following command:

zenml profile create --store-type sql --url "mysql://zenml:password@localhost/zenml" mysql_profile

fails when trying to create the default components & stack:

OperationalError: (MySQLdb.OperationalError) (1822, "Failed to add the foreign key constraint. Missing index for constraint 'zenstackdefinition_ibfk_3' in the referenced table 'zenstackcomponent'")[SQL: 
CREATE TABLE zenstackdefinition (
stack_name VARCHAR(255) NOT NULL,
component_type VARCHAR(255) NOT NULL,
component_name VARCHAR(255) NOT NULL,
PRIMARY KEY (stack_name, component_type, component_name),
FOREIGN KEY(stack_name) REFERENCES zenstack (name),
FOREIGN KEY(component_type) REFERENCES zenstackcomponent (component_type),
FOREIGN KEY(component_name) REFERENCES zenstackcomponent (name)
)

Reproduction steps

1. provision or use an existing MySQL server (e.g. the one provided with Kubeflow works nicely)
2. create a ZenML profile connected to the remote MySQL server. This will fail.

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct