[MySQL] diesel_cli migration suddenly complaining it can't find primary key for table

[berserkguard]

Hello, I've been using Diesel for the past week with MySQL 8.0 on Windows without issues, and suddenly today I started hitting the following issue when applying a migration to an unrelated table:
diesel migration run command fails with:
Diesel only supports tables with primary keys. Table accounts has no primary key
The result is my schema.rs file gets truncated.

Looking in MySQL Workbench, I confirmed the table-in-question has an id column which is flagged as the primary key (the schema for this table hasn't been touched in several days).

I enabled file logging for MySQL and found the query that diesel migration run is using to fetch the PK fields (via get_primary_keys function in diesel_cli/src/infer_schema_internals/information_schema.rs):

SELECT `information_schema`.`key_column_usage`.`column_name`
    FROM `information_schema`.`key_column_usage`
    WHERE `information_schema`.`key_column_usage`.`constraint_name`
    IN (SELECT `information_schema`.`table_constraints`.`constraint_name`
        FROM `information_schema`.`table_constraints`
        WHERE `information_schema`.`table_constraints`.`constraint_type` = 'PRIMARY KEY')
    AND `information_schema`.`key_column_usage`.`table_name` = 'accounts'
    AND `information_schema`.`key_column_usage`.`table_schema` = 'my_schema'
    ORDER BY `information_schema`.`key_column_usage`.`ordinal_position`;

Running this query myself in MySQL Workbench in fact returns no rows, even though running the subquery by itself returns dozens of rows with values PRIMARY.

I was able to come up with a workaround in latest diesel by replacing the subquery with a simpler string-equals check like so:

SELECT `information_schema`.`key_column_usage`.`column_name`
    FROM `information_schema`.`key_column_usage`
    WHERE `information_schema`.`key_column_usage`.`constraint_name` = 'PRIMARY'
    AND `information_schema`.`key_column_usage`.`table_name` = 'accounts'
    AND `information_schema`.`key_column_usage`.`table_schema` = 'my_schema'
    ORDER BY `information_schema`.`key_column_usage`.`ordinal_position`;

Running this modified query, I correctly get a single row with id for COLUMN_NAME. Can view this patch here on my forked version:
berserkguard@a64295c

Installing my custom diesel_cli through the following command then allows me to use diesel migration without any problems, and the generated schema.rs is as I expect:
cargo install diesel_cli --git https://github.com/berserkguard/diesel --no-default-features --features mysql

Searched through the open & closed issues, but didn't see anything directly relevant. And seeing as I don't have repro steps, and it started happening seemingly out of nowhere, I don't have enough actionable information to open an issue regarding it.

Does anyone have ideas around what might be going wrong here, or other things to try? Thanks in advance!

[weiznich]

On the first read this sounds like this could be a legitimate bug. Can you provide some more information about your setup, so that I can try to reproduce the problem locally?

• Exact diesel cli version you used
• Exact version of mysql. (There are "breaking" changes between mysql 8.0.x versions…)
• A example schema to reproduce the problem

If this turns out to be a real problem a PR + a corresponding test case would be appreciated.