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

jlevitas16's profile image jlevitas16 posted 2 weeks ago in General Permalink

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.

2 attachment(s):
ansgar's profile image ansgar posted 2 weeks ago Permalink

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.

jlevitas16's profile image jlevitas16 posted 2 weeks ago Permalink

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.

ansgar's profile image ansgar posted 2 weeks ago Permalink

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.

jlevitas16's profile image jlevitas16 posted 2 weeks ago Permalink

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?

jlevitas16's profile image jlevitas16 posted 1 week ago Permalink

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.

ansgar's profile image ansgar posted 7 days ago Permalink

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.

jlevitas16's profile image jlevitas16 posted 5 days ago Permalink

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.

jlevitas16's profile image jlevitas16 posted 5 days ago Permalink

Ok, I just tried in build (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.

1 attachment(s):
ansgar's profile image ansgar posted 4 days ago Permalink

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:

jlevitas16's profile image jlevitas16 posted 2 days ago Permalink

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?

jlevitas16's profile image jlevitas16 posted 2 days ago Permalink

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

ansgar's profile image ansgar posted 2 days ago Permalink

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.

jlevitas16's profile image jlevitas16 posted 2 days ago Permalink

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.

Please login to leave a reply, or register at first.