MySQL Probe - Does not display all tables

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

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

<Please see attached file for image>

If you see the table on the MySQL you can see table name time_zone*

<Please see attached file for image>

 

 

 

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

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKNhAAO" alt="Table with Max_data_length above 0.gif" width="1138" height="818">

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.

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKNiAAO" alt="Table with Max_data_length iqual 0.gif" width="1130" height="833">

 Make sure you have a table with max_data_length above value 0