foreign key dropdown error

per this post http://www.heidisql.com/forum.php?t=6767#p6784 it seems that the dropdown is not supposed to display if # of FK records > 1000

just installed 7.0.0.4294 this morning

have an FK relation that has over a million records, drops Heidi to its knees while it tries to populate the dropdown. had to restart mysql to get control back.

as a side note, the dropdown doesn't sort in ID order and if you want ID 1 then ID 11 may sort first and you then have to scan the list vs hitting the 1 key to move the dropdown to the next entry that begins with a 1. scanning the list within a large result set is tough at best since the large text strings get truncated due to the width of the dropdown window which isn't scalable. I usually know which ID I want, and rarely search by text value. But I'll acknowledge that 5 diff users will have 6 ways they in turn use the feature so there isn't a way to have it work best for everybody.

Suggestion: adding an option in the preferences to determine how the FK fields are edited, dropdown, or direct edit and then trap any mysql error generated if the user puts in an invalid ID

That foreign value drop down is only activated if the number of rows is less than 1000. So, if you say you have a million rows, then I guess it's not a dropdown but a text editor, is it?

no, dropdown

I'll reproduce again, was up against a timeline so I ejected out quickly, was also running on remote server. will report back in a few

Is it this query which brings HeidiSQL to its knees?

SELECT keycol, LEFT(textcol, 256) FROM foreigntable
GROUP BY keycol
ORDER BY textcol
LIMIT 1000

Can we optimize that query somehow?

I can't reproduce it, and I was incorrect about the server being remote. It was my local system since restarted mysqld locally to get control back.

I was adding a row into a table before reporting this initially. While entering the new row I hit the column with the FK and the system ground to a halt while it was trying to render. It initially rendered an empty pulldown and then hung. My system/cpu usage spiked to 100%. Restarted mysqld and aborted the row add.

Just tried to reproduce this, yet my system behaves correctly (flips to edit mode in the cell vs dropdown)

After turning logging and debugging on just now I see the same query you posted above.

I'll keep an eye on it going forward to see if I can reproduce the problem, sorry for the false start, ignore if/until I can reproduce

Thank you for consistent feedback. Let me know if we need to change something here.

I have a problem with the foreign keys, too. I have defined a foreign key over two columns. When I want to change the value of the second column, i get a drop-down-menu with weird entries.

The picture show the edit of table1. It references table2 where
vid and saalid are together the primary key. vid and saalid are INT resp. TINYINT.
I could be very glad if I could disable the dropdown menu and get a normal text field instead. I guess it is really much work to do to fix the problem and the question is if it is worthwile. But disabling in the case of compound keys are maybe easy to fix.

Then I have another proposal that has to do with foreign keys.
When I want to define a FK constraint, both tables must be InnoDB, but the select box "Reference table" shows also MyISAM-Tables. This confused me a little in the beginning.

1. Yes, I think the foreign key drop down should be on by default, but optionally. Will put a new checkbox into Tools > Preferences > Data: "Provide foreign values drop down in InnoDB rows"

2. Ok, I should hide non-InnoDB tables in the foreign key editor.

1. Done in r4322

2. Done in r4323