Can connect, but query to fetch tables never completes

Skychan posted 8 years ago in Creating a connection
Worked fine in HeidiSQL 3.2 but now with 4.0 it executes a really funny SQL script that makes the MySQL 5.0.77 never finish the query. The client sits "waiting for query to complete..." indefinitely.

This query is executed automatically upon connection and I can cancel it and see that I am connected to the database, but when I click on the database to view the tables inside, it again attempts the same query. If I cancel that query and simply write my own simple query like "show table status from 'databasename';" it returns them pronto.

The query log gives the following SQL as that which causes this (and if I copy and paste the same query into HeidiSQL 3.2 I get the same situation)

(SELECT TABLE_NAME AS `Name`, TABLE_TYPE AS `Type`, ENGINE AS `Engine`, VERSION AS `Version`, ROW_FORMAT AS `Row_format`, TABLE_ROWS AS `Rows`, AVG_ROW_LENGTH AS `Avg_row_length`, DATA_LENGTH AS `Data_length`, MAX_DATA_LENGTH AS `Max_data_length`, INDEX_LENGTH AS `Index_length`, DATA_FREE AS `Data_free`, AUTO_INCREMENT AS `Auto_increment`, CREATE_TIME AS `Create_time`, UPDATE_TIME AS `Update_time`, CHECK_TIME AS `Check_time`, TABLE_COLLATION AS `Collation`, CHECKSUM AS `Checksum`, CREATE_OPTIONS AS `Create_options`, TABLE_COMMENT AS `Comment` FROM `information_schema`.TABLES WHERE TABLE_SCHEMA = 'DataBaseName') UNION (SELECT ROUTINE_NAME AS `Name`, ROUTINE_TYPE AS `Type`, NULL AS `Engine`, NULL AS `Version`, NULL AS `Row_format`, NULL AS `Rows`, NULL AS `Avg_row_length`, NULL AS `Data_length`, NULL AS `Max_data_length`, NULL AS `Index_length`, NULL AS `Data_free`, NULL AS `Auto_increment`, CREATED AS `Create_time`, LAST_ALTERED AS `Update_time`, NULL AS `Check_time`, NULL AS `Collation`, NULL AS `Checksum`, NULL AS `Create_options`, ROUTINE_COMMENT AS `Comment` FROM `information_schema`.ROUTINES WHERE ROUTINE_SCHEMA = 'DataBaseName'') ORDER BY `Name` ;
rosenfield posted 8 years ago
Sounds like a bug in the server, you can probably reproduce it using the command-line "mysql" client that comes with the server too, right?

In that case your bug report belongs on http://bugs.mysql.com/.
Skychan posted 8 years ago
Ok, but why would HeidiSQL be using a prebuilt SQL command that does not work with MySQL (as new of a version as 5)?
ansgar posted 8 years ago
On my 5.0.45 local server that query runs fine. There is a fallback solution in HeidiSQL which fires a SHOW TABLE STATUS in case the UNION returns an error. That's definitely worth a look into the mysql command line, as rosenfield said - something strange going on there.
rosenfield posted 8 years ago
Try some simple stuff and see what works and what doesn't, to narrow down the problem.




SELECT * FROM `information_schema`.TABLES WHERE TABLE_SCHEMA = 'DataBaseName'

SELECT * FROM `information_schema`.ROUTINES WHERE ROUTINE_SCHEMA = 'DataBaseName'

MaxD posted 8 years ago
I am having exactly the same problem with v4.0: it just hangs "waiting for query"... I downgraded to v3.2 and it works perfectly.

It may be a server problem, but 4.0 is executing a more baroque query to list tables, instead of just

What's the fallback solution mentioned two posts before?
ansgar posted 8 years ago
That fallback is

But you should definitely check out the 5.0 beta release - these slowness problems were fixed. For all server versions Heidi now uses the SHOW TABLE STATUS command.
MaxD posted 8 years ago
Tnx! I tried 5.0, but now the connection dialog does not show the Database(s) text box (there is nothing between the "Compressed" and "Last Connection" lines).

If I open the connection anyway, the DB is shown correctly in a drop down box on the left, but now I don't see how to open the table editor. Sure, the SHOW TABLE STATUS works as above.

Perhaps something incompatible was left over from v:3.2/4.0 (portable) installations? Sorry to hijack this thread with this new issue -- just tell me if I am overseeing something, or should I open a new issue track?
ansgar posted 8 years ago
The drop down you see below the tree is what was on the connection screen in earlier versions.

Don't you see any database in the left database tree?

The table editor is opened if you click a table in the tree, or doubleclick one in the "Database" tab - should work for you, doesn't it?
ansgar posted 8 years ago
Eh, not problem about the old thread - this is a forum, not a bugtracker.

I don't see why you should have some conflicting stuff from a previous version - that should never be a problem, neither with a "normal" nor with a portable version.

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