Alembic: MySQL Collate not supported ?

[johaven]

I use alembic for several years now, i have old tables generated with alembic for which the mysql collate was not defined.
The mysql collate was always defined in my code:

{'mysql_charset': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_ci',

I had never paid attention until now until I encountered this error:

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1271, "Illegal mix of collations for operation 'UNION'")

the two tables for which I use 'union' have the same collate in my code, but in my database:
Table x: ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4
Table y: ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
The table x is an old table and the table y a newest.

My questions are :

• When creating table, why mysql collate is now supported ?
• Why the lack/difference of collate definition is not detected for the olds tables ?

For information i use MariaDB 10.4.8, the last alembic version (with compare_type to True since some time).

[zzzeek]

hi -

can you provide more specifics? where did you place these mysql_* arguments and where did they fail to appear? They definitely should render in the SQL output however autogenerate may or may not support them being rendered in the Python code automatically.

[zzzeek]

also, support for the addition of collation, either the MySQL specific version or the generic one, is not currently supported by autogenerate, so no, that is not detected (I'm not even sure waht the correct ALTER would be for that). collation will be rendered when you first CREATE TABLE or ADD COLUMN though.

[johaven]

Example with an ORM class:

class Group(Base):
    __tablename__ = 'groups'
    __table_args__ =  {'mysql_charset': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_ci'}
    id = db.Column(db.Integer, primary_key=True)
    ...

On MySQL side with the new tables created with alembic:

 groups | CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   ....
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_groups_name` (`name`),
  KEY `ix_groups_parent_id` (`parent_id`),
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

On MySQL side with the old tables created with alembic:

 groups | CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   ....
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_groups_name` (`name`),
  KEY `ix_groups_parent_id` (`parent_id`),
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |

FYI: You can alter the charset and the collation: https://mariadb.com/kb/en/library/setting-character-sets-and-collations/#table-level

Ok, so it's only generated when table is created.
But why my old tables does not contain the collate setting ?
Was there a patch to support that? (I did not see anything in the changelog)
Does activating the compare_type have an influence?

[zzzeek]

hi -

did you use autogenerate to create your migration scripts? are the directives in the migration script?

[zzzeek]

Also, again, there is no "compare" rule for collations at the table level (and not yet for the column level either).

[johaven]

Yes the directives are in the mig script:

# ### commands auto generated by Alembic - please adjust! ###
    op.create_table('groups',
    sa.Column('id', sa.Integer(), nullable=False),
    ...
    sa.PrimaryKeyConstraint('id'),
    mysql_charset='utf8mb4',
    mysql_collate='utf8mb4_unicode_ci'
    )

Is it possible to implement the charset and collate check (on changes) ?

[zzzeek]

Yes the directives are in the mig script:

# ### commands auto generated by Alembic - please adjust! ###
    op.create_table('groups',
    sa.Column('id', sa.Integer(), nullable=False),
    ...
    sa.PrimaryKeyConstraint('id'),
    mysql_charset='utf8mb4',
    mysql_collate='utf8mb4_unicode_ci'
    )

I ran that migration as is and the SQL output is:

CREATE TABLE `groups` (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (id)
)CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

so there is no bug.

Is it possible to implement the charset and collate check (on changes) ?

We don't even have an ALTER construct to support changing MySQL-specific collations much less a means of checking them. We would first want to implement generic collations across the board before adding MySQL-specific rules.

I'm not sure if you're aware, you can solve the issue you are having right now by simply issuing the ALTER you need in order to fix your tables:

def upgrade():
   op.execute("ALTER TABLE my_table CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci")

[johaven]

so there is no bug

On the last versions it works, that what i said on my first post.
On old tables generated with alembic, the collate seems to be not generated.
That's why i answered:

But why my old tables does not contain the collate setting ?
Was there a patch to support that? (I did not see anything in the changelog)

I don't know why i got this difference ... i try to find ....

you can solve the issue ...

I know, but i have to check all tables ...

[zzzeek]

so there is no bug

On the last versions it works, that what i said on my first post.

OK, that was not clear, as the subject asks if "collate is supported', it is supported, just not automatic detection of it changing, since this is not a common need.

On old tables generated with alembic, the collate seems to be not generated.

Yeah I'm not sure if there was some point at which op.create_table() both accepted these keyword arguments but didn't pass them through, it does not sound familiar that these flags would be silently ignored but that may have been the case.

That's why i answered:

But why my old tables does not contain the collate setting ?
Was there a patch to support that? (I did not see anything in the changelog)

I don't know why i got this difference ... i try to find ....

you can solve the issue ...

I know, but i have to check all tables ...

you can simply apply the collatiion to all the tables in the db at once:

from sqlalchemy import inspect

def upgrade():
    for table_name in inspect(op.get_bind()).get_table_names():
       op.execute("alter table %s collate utf8mb4_unicode_ci" % table_name)

hope this helps

[johaven]

I have different charsets and collations for tables otherwise it will be easy 🙂 I hope that one day it will be supported! Thank you for your time !

[zzzeek]

Table reflection should be able to give you this information for MySQL, although if you have many hundreds of tables it might be slow to go through all of them.

[johaven]

Finally, my bug seems to be linked to a Mariadb fix which now support the collation when table is created.