MySQL Probe - Does not display all tables

Document ID : KB000006879
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

MySQL Probe - Does not display all tables

Some MySQL DB has tables with extension *.frmb e *.ibd but don't show on the probe status tab.

 

Environment:
Any DB MySQL
Cause:

By default the probe execute this query for table_size_ratio

SELECT table_name, avg_row_length, data_length, max_data_length, index_length, data_free, table_schema FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND max_data_length > 0 ORDER BY table_schema ASC

But if you don't have any data on the tables *.frmb e *.ibd and the value is 0 you will not able to see the table list on the probe status tab as below, if you looking for the table time_zone*.

mysql tables in probe.PNG

If you see the table on the MySQL you can see table name time_zone*ibd tables extension.gif

 

 

 

Resolution:

Make sure you have data on the tables are you looking, if you execute the query without the value >0 you will be able to see.

This is the default query that is looking for table above value 0

SELECT table_name, avg_row_length, data_length, max_data_length, index_length, data_free, table_schema FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND max_data_length > 0 ORDER BY table_schema ASC

As below you can see just 62 tables and no max_data_length with = 0

Table with Max_data_length above 0.gif

But when you remove the AND max_data_length > 0 like the query below.

SELECT table_name, avg_row_length, data_length, max_data_length, index_length, data_free, table_schema FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE'

You can see the results is 147 tables with a lot max_data_length with value = 0.

Table with Max_data_length iqual 0.gif

 Make sure you have a table with max_data_length above value 0