SQL results: all cells change to "Node" within 1 minute

HerrimanCoder's profile image HerrimanCoder posted 2 years ago in General Permalink

I reported this bug about 2-3 years ago, and I thought it had been fixed because I haven't seen it for a while. But now I am using HeidiSQL 12.2 and it's 100% reproducible.

I am running Windows 10.

After running the following simple query against a remote MySQL instance:

SELECT * FROM Reservations WHERE ReservationDate = '2022-12-09'

  1. The query executes correctly and I get expected results.
  2. In 30-60 seconds, all cells in the results grid change from the correct values to the word "Node" in each cell. (See attached image.)

This seems like a really bad (and ridiculous) bug that has been a problem for a long time. Can it PLEASE be fixed?

This is 100% reproducible, I can make it happen every time. I would be happy to Zoom call with somebody and show you, or I can send you log files, or whatever you need.

1 attachment(s):
  • Node
ansgar's profile image ansgar posted 2 years ago Permalink

I remember I already explained the cause for that: it's the connection which is interrupted from outside. There is no crash. You should in most cases be able to re-run the query, so HeidiSQL will reconnect and display the real results. There is no way to display such results from a disconnected session.

HerrimanCoder's profile image HerrimanCoder posted 2 years ago Permalink

ansgar, that is not a solution. The connection is not being interrupted. The query runs, the results come back fine, and then 30-60 seconds later, all the cells flip to "Node". Are you saying this is normal behavior and there's nothing that can be done? How do you figure it's an interrupted connection when the query ran and the results came back successfully? There is a bug here.

HerrimanCoder's profile image HerrimanCoder posted 2 years ago Permalink

Also I can rerun the query 10X, and 10X this same glitch will occur. I assure you there is a bug. Can I show you? Can I send you logs or something?

ansgar's profile image ansgar posted 2 years ago Permalink

Believe me, that "Node" is the default text in a cell and only displayed when the connection is closed. Please watch out for the state in the footer status panel:

Description

You can probably prevent disconnects by decreasing the session's ping interval to 10 or 20 seconds:

Description

HerrimanCoder's profile image HerrimanCoder posted 2 years ago Permalink

ansgar, I cannot find that Advanced tab. Can you tell me how to get to it?

Hopefully that setting will help, but it's still a bug. Here is why: in all other SQL editors I have used (many), I can kill the Internet on any of them and the query results still remain. Why should it keep pinging? I don't want it to. I want those results to remain forever until I execute another query. For those results to rely on constant connection pings seems silly. Who would want that? Could you please add a setting that will allow query results to remain completely static and unchanged, and not ever let those cells change until I execute another query?

Thank you.

ansgar's profile image ansgar posted 2 years ago Permalink

The "Advanced" tab is in the session manager, where you set up the host/ip, user, password etc.

HerrimanCoder's profile image HerrimanCoder posted 2 years ago Permalink

Thanks, I updated my setting. Hopefully you saw my last note:

"It's still a bug. Here is why: in all other SQL editors I have used (many), I can kill the Internet on any of them and the query results still remain. Why should it keep pinging? I don't want it to. I want those results to remain forever until I execute another query. For those results to rely on constant connection pings seems silly. Who would want that? Could you please add a setting that will allow query results to remain completely static and unchanged, and not ever let those cells change until I execute another query?"

ansgar's profile image ansgar posted 2 years ago Permalink

Yes, I read it. Please read my previous explanation. That default text is intentionally shown, not a bug, probably not nice, but surely not a bug. As soon as the connection is gone, the internal objects musst be freed and cannot show the text any longer. Your proposal would require critical changes to the abstraction layer for MySQL, which in the end would eat more memory on the client than it does with current versions. I'm not willing to do that.

HerrimanCoder's profile image HerrimanCoder posted 2 years ago Permalink

"As soon as the connection is gone, the internal objects must be freed and cannot show the text any longer."

That is bizarre behavior, and obviously error-prone. Why would anyone think it's a good idea? Other database clients don't do that. Query results (after the query has successfully executed and brought back data) should never be dependent on an active connection. The query is run, the results appear, and those results should remain until another query is run. Do you really not agree with that approach? If, at the moment the query is attempted, there is no connection, obviously it shouldn't work and cannot work. But when the results come back, they should remain static and not rely on connections which are inherently volatile.

ansgar's profile image ansgar posted 2 years ago Permalink

Well I explained the logic in the background. If you like the approach in other clients more, then why do you use HeidiSQL?

TTSneko's profile image TTSneko posted 2 years ago Permalink

@HerrimanCoder: "... That is bizarre behavior, and obviously error-prone. ..."

Excuse me for stepping in. Have you even thought about the idea of a typical plain and safe server-side timeout (and the reasons for it)? Who cares if you are occupied elsewhere, if your connection is unused for a while (or shut off) the display is simply set on hold. Imagine your co-worker invests two hours of work and you freck up his work because you decide to send some long outdated trash after having coming back from shopping. Good heavens!

It's called common sense. If you prefer to work outside regular boundaries don't come shouting blue-black murder and barking at non-existant bugs: the flip to NODE display alerts the user that the data formerly shown on-screen is probably not valid anymore and requires a refresh. No more, no less.

As your example includes reservations I must simply assume that you are using Heidi to display reservations over an extended period of time per day. That is the job of dedicated software frontends, not that of a database manipulation tool like HeidiSQL. Frontends actually also work based on permanent pings or re-connection, however they (often) keep the last called data presentation on-screen until caused to refresh via agent input (a nearby traveling agency works with something similar).

"... in all other SQL editors I have used (many), ..."

It appears that you completely neglect data consistency in favor of your own lazyness; in that case it is your setup and mindset that needs tweaking, not that of Anse or Heidi. Which probably explains why you went through so many SQL editors; I can guess that you always saw the problem on the other end of the line instead of rethinking your own expectations and qualifications. But that is mere speculation based on your posts, of course.

HerrimanCoder's profile image HerrimanCoder posted 2 years ago Permalink

TTSneko: So as 1 example, SQL Server Management Studio doesn't behave this way. If after running a query and getting back results, the underlying data later changes or the connection becomes invalid I DON'T CARE. And SSMS is quite content to display the original result set until the world ends, or until I re-run the query. I want my results to stay the way I left them. Apparently SSMS users are happy with that scenario and have been for decades. The fact that the underlying data in the database may have actually changed is fine. I don't care. I can run the query again if I want fresh data. Want I don't want is to suddenly lose my result set against my wishes.

Your use case is a valid one I guess, but mine is too. That's why from the very beginning I simply requested a setting. Some kind of bool flag that tells Heidi whether to aggressively screw up my result set (why anyone would want that is still bizarre to me, but whatever) OR to leave my results alone until I requery.

My mind is open to your desired scenario, and thus a setting could make sense. Why is your mind so closed to my scenario? (Which is also how other sql clients work).

Also you are highly prone to making assumptions: >> "Which probably explains why you went through so many SQL editors" - The fact is, Heidi is only my 2nd MySQL query client after Workbench. I like Heidi better for a number of reasons, but are you aware that Workbench doesn't do this weird NODE results behavior? I have used other clients for other database engines, like SSMS and 1 for Oracle. None of these other clients behave in the way that you believe is correct.

And how do you figure it's lazy to want my results to remain after running a query? I still don't see why anyone would want that, and certainly in the MS world nobody does.

In summary, it seems like the root of the whole issue is that you WANT to know (after getting back results) if the connection is gone or if the data has changed or etc. So tell me what's wrong with this alternative: if the connection drops or the data changes or whatever, why not have a little icon indicate that, or a change in background color, or a status bar message saying "your connection is gone" or etc. Can you tell me what is so great about having your results STOMPED rather than a little indicator that the connection is gone or stale?

feelie75's profile image feelie75 posted 1 year ago Permalink

It is extremely bizarre behavior how it currently works. If you want FRESH data, sure, you just run the query again, but if you just want a summary of yesterday's data or something that WILL NOT change, there's no need to vaporize the data after you retrieve it. If you know the data will never change after you retrieve it, then you know you won't need to refresh the query after a disconnect, and like Herriman said, it should stay visible forever. I don't understand why it would take up more memory to have a copy of the data stored locally, isn't that what's already happening while it's displaying the results? Why even keep the connection open at all after the results have been fetched? Is there a benefit to keeping the connection open after the results are fetched and will not change unless you re-run the query? I'd be interested what the benefit of keeping the connection open at all are. Might as well just exit the entire program if the connection fails, why not. Good luck! This is all rhetorical, I know I will get no valid answers. Have a good weekend!

ansgar's profile image ansgar posted 1 year ago Permalink

Connections are intentionally kept open to keep session variables intact. Working on the results of queries in MySQL mode also requires a valid connection if you don't want to receive errors from libmysql/libmariadb.

feelie75's profile image feelie75 posted 1 year ago Permalink

That's a great explanation. Thank you. The problem seems to lie in the libmysql/libmariadb libraries then. If the SQL Results grid is based on those libraries, or the results are "bound" to those libraries which connect to the database, then that makes sense why the database disconnect would result in the loss of results. IMO, it's not an ideal design, but I appreciate you explaining the reason for the connection between the results and the libraries that make those results happen. That makes a ton of sense why you wouldn't want to re-engineer the entire way the process works; because you'd have to write everything from scratch basically if you weren't using those libmysql/libmariadb sources. Thanks for the explanation :).

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