Mysql 5

[caseyd]

I am using SQLYog 5.02 and when I connect to a mySQL 5 database, all I can see are the view, stroed procs, and functions folders.

I used to be able to see the tables in there – what's wrong?

[peterlaursen]

we have had this a few times before. it is most often becuase the server was incorrectly updated from a previous version. Is it a 'shared server' ?

it is a privilege issue on the server basically. SQLyog queries Information_Shema to get information about tables. However if you upgrade SQLyog you will see tables too. We now use another query to retrieve the information – also because querying information_schema sometimes can be slow.

[peterlaursen]

With hindsight I think I should have explained more in detail!

Traditionally the SQL used to get a list of tables is “SHOW TABLES”. However with MySQL 5 this statement retrieves both TABLES and VIEWS. So when we introduced support for MySQL 5 with SQLyog 5 we needed another query to populate the Object Browser with tables. What we did was (and that was as recommended by MySQL A:cool::

SELECT … FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE_TABLE'

This had two issues.

1) In the beginning of MySQL 5 queries to Information_Schema could be incredibly slow! This is (more or less) fixed by MySQL AB – but still querying Information_Schema can be considerably slower than “SHOW … ” statements.

2) We enocuntered quite frequently the same issue as you report here: that the SELECT FROM INFORMATION_SCHEMA.TABLES … -query returned an empty set even when tables were there that could be accessed with the privileges of the actual user.

We 'worked around' both by changing the query back to a SHOW statement (in version 5.1.something, I think). However, there seems to be an unwanted regression here. Version 6.05 still do

SELECT … FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE_TABLE'

(you can see in HISTORY tab)

I believe this is a mistake we made when we 'merged' the code for the version 6 tree! I also think that we can (and shall) correct this before releasing version 6.05 FINAL! I will ensure that we disucss this tomorrow morning.

But still: it is not a real bug in SQLyog. It is an issue with the server/OS configuration! Only you (or your Sysadmin) can fix this. And even if we solve it by returning to the SHOW statement, this should be corrected on the server, because sooner or later it will give other problems as well!

If you do not manage the server yourself you will need to report it to the support personnel at the ISP (or whatever).

You won't have to go into details with SQLyog. Just tell that the query:

Code:

select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'test' and `TABLE_TYPE` = 'BASE TABLE'

(substitute the database name with your own)

… returns an empty set when it should return a list of the tables that you have the privileges to access!

[peterlaursen]

The 'work-around SQL' (checked with version 5.32) is

Code:

show table status from `test` where engine is not NULL

[Author]

Posts