Python SQLAlchemy alembic support

3rd-Party Integrations
#1

Hi!

I’m working on a POC to set up central schema management inside our team.
I’d like to use SQLAlchemy and alembic to combine a pipeline where we can leverage the abstractions provided to generate the migration/revision files.
I’ve had initial success in using this to create tables and to progress an example schema backwards and forwards.

I’m running into issues I guess with the queries generated by alembic and SQLAlchemy of which this would be one example:
pymysql.err.OperationalError: (1706, “Feature ‘CHANGE which includes a type definition. Use MODIFY to change a columns type.’ is not supported by MemSQL.”)

It seems that there are inconsistencies on the driver level with what is used by MemSQL and I’m wondering is this something that is supposed to be an error like this?
Which mysql python driver would provide us with a compatiblity that MemSQL expects and correct queries. As you can see this is an error happening on pymysql which would be the best pick as it stands. Alternatively we could use the mysql-connector or mysqldb but these have other issues in db handshake etc.

Mind you this is something that, if it were targeting mysql db would and should work out of the box with our example schema.

Is there a way to maybe fix this on the MemSQL side or cluster configuration side? It seems to me that this breaks after it would hit the middle layer of mysql.

#2

Hi borko,

It looks like SQLAlchemy alembic is generating an ALTER TABLE … CHANGE command that we don’t support. We don’t have full support for all the ALTER TABLE syntax MySQL supports. In particular, we only allow ALTER TABLE CHANGE commands to rename a column and not change its datatype at the same time. ALTER TABLE… MODIFY needs to be used to change a datatype of an existing column.

I’m not sure if there is a good work around for this of the top of my head. You could enable the general_log so we can see the ALTERs its trying to run
. Run set global general_log = on. All queries run will get written to the query.log file in the tracelog dir.

-Adam

#3

Hey Adam!

Thanks for responding.
There is no need for logging, as I can dump the migration SQL to a file.
What is being generated to run is:
initial revision:
CREATE TABLE daily_metrics (
id INTEGER NOT NULL AUTO_INCREMENT,
created DATETIME DEFAULT now(),
updated DATETIME DEFAULT now(),
PRIMARY KEY (id)
);

revision x that breaks:
ALTER TABLE daily_metrics CHANGE created created DATETIME NULL DEFAULT now();

From what is see in the lib itself it is hitting the code path that corresponds to CHANGE and not MODIFY. https://github.com/sqlalchemy/alembic/blob/master/alembic/ddl/mysql.py#L71

All in all we will proceed with our testing as this specific case could be an outlier. It represents a potential integration issue that we want to solve so we can use memsql and get organised internally.

So my question would be:

  1. Are there plans to support CHANGE?

Blockquote
We don’t have full support for all the ALTER TABLE syntax MySQL supports.

  1. Is this by design, or is the support lagging, and since mysql is being developed and gets features, is memsql going to enable those and what would be the timeline?
#4

Its interesting that SQLAlchemy decided to run an ALTER CHANGE that is a no-op (doesn’t change the name nor the datatype of the created column?).

Our lack of full support for CHANGE is mainly around feature prioritization. We could add support for ALTER CHANGE that change the datatypes in the future (though I don’t have a specific date at this point).

-Adam

#5

Agreed! I also think this might be something that could be side-stepped.
We still need to evaluate other scenarios. So I might have additional questions later on.

I hope more support lands in as it would enable painless 3rd party integrations with existing tools and libs.