Can connect, but query to fetch tables never completes

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` ;

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/.

Ok, but why would HeidiSQL be using a prebuilt SQL command that does not work with MySQL (as new of a version as 5)?

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.

Try some simple stuff and see what works and what doesn't, to narrow down the problem.

Example:

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

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

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

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

[/code]

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
SHOW TABLE STATUS FROM `db`.

What's the fallback solution mentioned two posts before?

That fallback is

SHOW TABLE STATUS FROM `db`

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.

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?

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?

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.