can't edit rows or perform select statement on a table with a MEDIUMTEXT field in it

I have a table with a unique, non-null primary key in a fairly large MariaDB database. There are 611 records in this table. If I try to edit the data, in the data view, in the first approximately 520 records, I can. Once I try to edit the data in a more recent record, I can not and then I can not edit data in any of the records in the table - even the ones that I was able to edit before. Additionally, if I try to query the table for a more recent record, it returns the results, but in the bottom corner, it says sorting grid nodes and HeidiSQL freezes. SELECT * FROM cronlist (Image attached).

If I query the table and do NOT include a field that is a MEDIUMTEXT type field, then I can edit any of the data that I queried.

I happen to have a VERY old version of Heidi - 12.0 and, in this version, everything works properly. When I installed 12.1 and up, it has the incorrect behavior.

Please advise.

Yes, I can confirm that, I was facing the same issue several times. I know it's caused by larger text fields in the grid, so I can recommend to deselect mediumtext fields, at least if they indeed contain much text.

I'd be glad to fix that, so feel free to post more technical details on the fields and the issue, for example the minimum character length which leads to the issue.

So I found that maximum character length of the MEDIUMTEXT field that I could still edit the row, in my data, was 50409. The next largest MEDIUMTEXT row was 51025 and I could not edit it. So somewhere between 50410 and 51025 is the point where the editing of any column in the row fails to work. Now that I know this, I can get around it as long as the field that I am not looking to edit is the message itself. The bigger problem is that when I run a SELECT statement like SELECT * FROM table WHERE criteria='criteria', if the resulting row has MEDIUMTEXT contents larger than "allowed", HEIDISQL freezes with the "sorting grid nodes" in the status bar and I have to run the task manager to end the task.

Thanks for tracking that down. I will see if I can find a cause for that. If that was a crash that would be easier to find, but without I will have to guess, so that may take longer.

Would you like for me to dump a few records from the table that is causing me problems and send that .sql to you in order for you to simulate the problem consistently?

Hi, I am following up on this post. I have had to roll back to 12.0 in order to be able to add and edit rows. I have too many tables with mediumblob fields that I need to be able to quickly edit without having to build a select statement without the mediumblob. The bug also prevents me from being able to add a row to a table like this.

I am just trying to reproduce here at home, but suddenly it works normal with the latest build and text fields of several megabytes. So I am fishing in the mud currently.

Are you sure v12.0 is the latest one which works normal with your text fields?

Would be nice if you could try to find out the latest release which works. Probably we also find the latest nightly build, but at least the release would be really helpful.

12.0 is the latest release that worked for me. I happen to have HeidiSQL_12.5.0.6677_Setup HeidiSQL_12.4.0.6659_Setup HeidiSQL_12.3.0.6589_Setup HeidiSQL_12.1.0.6537_Setup HeidiSQL_12.0.0.6468_Setup I rolled back to each one and 12.0 was the one that worked. I'll try installing the latest build and let you know.

Ok, I just tried in build 12.6.0.6778 (6779 wasn't available as a 64 bit download) and the problem still exists. I am attaching a small sql table that will consistently not work. Sometimes, I can click on one field (clientid) and I can edit it, but then when I try to click on any other field to edit it, I can not. Rolling back to 12.0 again.

Awesome. Thanks a lot for tracking down the version.

In the table you attached, there is no MEDIUMTEXT column, so I suppose you mean the photo LONGBLOB? If so, are you displaying it in binary or in hex format like below?

I just had a look at the builds between v12.0 and v12.1, and I see the switch to a newer compiler Delphi 11.0 (from 10.4 in v12.0). So I am gonna test the issue with the latest build I compiled with the old compiler, and the first with the newer one:

• last one compiled with Delphi 10.4, which I guess still works (?): heidisql64.r6518.exe
• first one compiled with Delphi 11.0, which I guess does not work (?): heidisql64.r6526.exe

Yes. Sorry. In this table, it is a longblob, but in other tables, it is a MEDIUMTEXT field. I was displaying this LONGBLOB in HEX, but I tried it in Binary and same problem. I reinstalled 12.6 and replaced the heidisql.exe with your last one compiled with Delphi 10.4 and everything worked. So it looks like we found the problem. What will happen going forward with new releases? Will you compile with the older version until this problem is fixed in the 11.0 version?

BTW, I am not receiving notifications of your replies anymore. And I did check my spam. The last time that I received a notification of your reply was 11/19/2023

Did you also try build 6526 compiled with Delphi 11? If not, please do so, as that will give a more definite hint the compiler is the problem (or the libraries shipped with it).

I just fixed the notification issue - this was due to a passwort mismatch on my server. Thanks for the hint.

I will not compile with that old compiler version, as this is already quite old, and my license is anyway only valid for the newer one. But if this is really the cause, I can probably track down from what has changed in Delphi 11.0, and probably then find a fix for it.

Build 6526 compiled with Delphi 11 does not work. It is definitely something with Delphi 11. If you think that you've figured out the problem, please send me an .exe to test. I will not continue to upgrade until I know that this issue has been fixed. I use a lot of blobs and MEDIUMTEXT fields in my tables. Thanks for taking the time to work through this with me and find the cause.

Hi. I am checking in to see if you were able to track down the problem between Delphi 10.4 and 11. I am still running 12.0 due to this issue

Not yet. I only found out it's not something obvious, nothing in my own code modifications. That's why I'm still suspecting the compiler/library updates here.

I was doing a deeper debugging session here. I found that WM_PAINT Windows messages are received endlessly, when the text field contains one very long line (> 300KB), plus a tab character (\t) in that line. But on top of it this is not reproducible when I generate such a text, even if it looks nearly the same. So there must be something additional important thing for reproducing.

I tried to work around that by ignoring some of the WM_PAINT messages, but that just causes other serious painting issues.

In case of shorter lines, I can show a field with ~20MB without problems.

If you set Preferences > Grid formatting > Lines of text in grid rows to a value > 1, the issue seems fixed.

Awesome. That seems to have fixed the problem! Thank you so much for circling back and figuring this out.

Well that was not really a fix, but probably gave me an important hint on how to fix it.

Next nightly build internally marks all nodes as multiline capable, so you can reset the above mentioned "lines of text" to 1 again.

For me the issue seems fixed now. Would be good if someone also tests with the next build.

I just downloaded the the new build and had set the lines of text back to 1. It looks like it all works fine now. Thank you so much!