MySQL columnInfo(): Duplicated rows when more than 1 index exists for a field

[RemiMatrod]

Hi,
I'm using MySQL 5.7, and it looks like a column with multiple indexes is displayed multiple times when using columnInfo('table_name').
I've seen this caused problems in directus/directus#6204.

I've been looking at the source code of columnInfo() and tested it on a MySQL instance, and found that the issue comes from the table INFORMATION_SCHEMA.KEY_COLUMN_USAGE and the LEFT JOIN done on it, duplicating the rows from INFORMATION_SCHEMA.COLUMNS (for MySQL).

Only the columnInfo('table_name', 'column_name') function seems to work properly (not showing any duplicate entries), since it only returns the first column found.
Using a GROUP BY c.TABLE_NAME, c.COLUMN_NAME on the query looks like it prevents the duplicate indexes to show up, but there might be loss of information in that case?

[rijkvanzanten]

Using a GROUP BY c.TABLE_NAME, c.COLUMN_NAME on the query looks like it prevents the duplicate indexes to show up, but there might be loss of information in that case?

We still need to pull the values for is_unique and is_primary_key, so as long as we can accurately combine that in the join, and or de-dupe it after the fact it'll be alright 👍🏻

[carolin-skiply]

Same here. When will it be fixed ? Tks

[rijkvanzanten]

@carolin-skiply Feel free to open a PR to fix it, that's the quickest way to have it fixed 😉

[Beiri22]

Just coming by and having a look; is there anyone working on a PR?

[rijkvanzanten]

@Beiri22 Not to my knowledge. Wanna pick it up?