Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
RemiMatrod opened this issue Jul 6, 2021 · 5 comments · May be fixed by #69
Open

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

RemiMatrod opened this issue Jul 6, 2021 · 5 comments · May be fixed by #69

Comments

@RemiMatrod
Copy link

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
Copy link
Collaborator

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
Copy link

Same here. When will it be fixed ? Tks

@rijkvanzanten
Copy link
Collaborator

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

@Beiri22
Copy link

Beiri22 commented Aug 30, 2021

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

@rijkvanzanten
Copy link
Collaborator

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

@Nitwel Nitwel linked a pull request Sep 6, 2021 that will close this issue
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[MYSQL] Fix columnInfo showing column multiple times. Nitwel/knex-schema-inspector
4 participants
@Beiri22 @rijkvanzanten @carolin-skiply @RemiMatrod