search

KarnerTh / mermerd

Create Mermaid-Js ERD diagrams from existing tables
MIT License
460 stars 35 forks source link

Error retrieving constraints from MySQL schema #57

Closed ricardoolsen closed 1 week ago

ricardoolsen commented 9 months ago

Hi! Thanks for this useful tool.

I have encountered error retrieving constraints from a MySQL schema.

I was able to fix it by adding "limit 1" to a subquery from /database/mysql.go (line 149).

(
select kc2.CONSTRAINT_NAME is not null "isPrimary"
from information_schema.KEY_COLUMN_USAGE kc
left join information_schema.KEY_COLUMN_USAGE kc2
            ON kc.COLUMN_NAME = kc2.COLUMN_NAME AND kc2.CONSTRAINT_NAME = 'PRIMARY' AND
            kc2.TABLE_NAME = kc.TABLE_NAME
         where kc.CONSTRAINT_NAME = c.CONSTRAINT_NAME and kc.COLUMN_NAME = kcu.COLUMN_NAME limit 1
) "isPrimary",

The error occured because the subquery was returning more than one row in my case. Best regards.

KarnerTh commented 9 months ago

Thanks for the ticket! Can you provide the DDL/schema that causes the error? If I can reproduce the error I can investigate the issue in more detail.

ricardoolsen commented 9 months ago

Hi Karner!

I'm sorry, I cant't share it because it is an enterprise schema, not to be shared.

The schema has lots of relationships and constraints.

It has double foreign keys like two columns of the same table pointing to columns on another table. I think this can be the cause of the problem.

It has also a recursive relation: a colunm pointing to another on the same table.

Otherwise it is pretty normal stuff.

KarnerTh commented 1 week ago

Will close this for now - if anyone has a reproducible schema feel free to reopen