Dear Friends,
I've recently upgraded mysql version on one of the systems from mysql 5.5.28 to 5.7.12 after upgrading binaries I've also upgraded schema of tables using $sudo mysql_upgrade and $sudo mysql_upgrade --force which executed successfully without any error.
I was trying to check if there are any crashed tables using following query
Code:
mysql> select table_schema,table_name from information_schema.tables where index_length is NULL or data_length is NULL;
Above query typically returns name of crashed tables. but here it is returning some 100 tables from sys database. I am trying to understand why I am getting this list of 100 tables, which never use to appear in older version 5.5.28. It would be nice if anyone having knowledge regarding this would be able to guide me in understanding this behavior. I am not asking for exact root cause, but if anyone can tell me what can lead me to understanding above behavior would be appreciated.
Given below is the out put of above command.
Code:
mysql> select table_schema,table_name from information_schema.tables where index_length is NULL or data_length is NULL;
+--------------+-----------------------------------------------+
| table_schema | table_name |
+--------------+-----------------------------------------------+
| sys | host_summary |
| sys | host_summary_by_file_io |
| sys | host_summary_by_file_io_type |
| sys | host_summary_by_stages |
| sys | host_summary_by_statement_latency |
| sys | host_summary_by_statement_type |
| sys | innodb_buffer_stats_by_schema |
| sys | innodb_buffer_stats_by_table |
| sys | innodb_lock_waits |
| sys | io_by_thread_by_latency |
| sys | io_global_by_file_by_bytes |
| sys | io_global_by_file_by_latency |
| sys | io_global_by_wait_by_bytes |
| sys | io_global_by_wait_by_latency |
| sys | latest_file_io |
| sys | memory_by_host_by_current_bytes |
| sys | memory_by_thread_by_current_bytes |
| sys | memory_by_user_by_current_bytes |
| sys | memory_global_by_current_bytes |
| sys | memory_global_total |
| sys | metrics |
| sys | processlist |
| sys | ps_check_lost_instrumentation |
| sys | schema_auto_increment_columns |
| sys | schema_index_statistics |
| sys | schema_object_overview |
| sys | schema_redundant_indexes |
| sys | schema_table_lock_waits |
| sys | schema_table_statistics |
| sys | schema_table_statistics_with_buffer |
| sys | schema_tables_with_full_table_scans |
| sys | schema_unused_indexes |
| sys | session |
| sys | session_ssl_status |
| sys | statement_analysis |
| sys | statements_with_errors_or_warnings |
| sys | statements_with_full_table_scans |
| sys | statements_with_runtimes_in_95th_percentile |
| sys | statements_with_sorting |
| sys | statements_with_temp_tables |
| sys | user_summary |
| sys | user_summary_by_file_io |
| sys | user_summary_by_file_io_type |
| sys | user_summary_by_stages |
| sys | user_summary_by_statement_latency |
| sys | user_summary_by_statement_type |
| sys | version |
| sys | wait_classes_global_by_avg_latency |
| sys | wait_classes_global_by_latency |
| sys | waits_by_host_by_latency |
| sys | waits_by_user_by_latency |
| sys | waits_global_by_latency |
| sys | x$host_summary |
| sys | x$host_summary_by_file_io |
| sys | x$host_summary_by_file_io_type |
| sys | x$host_summary_by_stages |
| sys | x$host_summary_by_statement_latency |
| sys | x$host_summary_by_statement_type |
| sys | x$innodb_buffer_stats_by_schema |
| sys | x$innodb_buffer_stats_by_table |
| sys | x$innodb_lock_waits |
| sys | x$io_by_thread_by_latency |
| sys | x$io_global_by_file_by_bytes |
| sys | x$io_global_by_file_by_latency |
| sys | x$io_global_by_wait_by_bytes |
| sys | x$io_global_by_wait_by_latency |
| sys | x$latest_file_io |
| sys | x$memory_by_host_by_current_bytes |
| sys | x$memory_by_thread_by_current_bytes |
| sys | x$memory_by_user_by_current_bytes |
| sys | x$memory_global_by_current_bytes |
| sys | x$memory_global_total |
| sys | x$processlist |
| sys | x$ps_digest_95th_percentile_by_avg_us |
| sys | x$ps_digest_avg_latency_distribution |
| sys | x$ps_schema_table_statistics_io |
| sys | x$schema_flattened_keys |
| sys | x$schema_index_statistics |
| sys | x$schema_table_lock_waits |
| sys | x$schema_table_statistics |
| sys | x$schema_table_statistics_with_buffer |
| sys | x$schema_tables_with_full_table_scans |
| sys | x$session |
| sys | x$statement_analysis |
| sys | x$statements_with_errors_or_warnings |
| sys | x$statements_with_full_table_scans |
| sys | x$statements_with_runtimes_in_95th_percentile |
| sys | x$statements_with_sorting |
| sys | x$statements_with_temp_tables |
| sys | x$user_summary |
| sys | x$user_summary_by_file_io |
| sys | x$user_summary_by_file_io_type |
| sys | x$user_summary_by_stages |
| sys | x$user_summary_by_statement_latency |
| sys | x$user_summary_by_statement_type |
| sys | x$wait_classes_global_by_avg_latency |
| sys | x$wait_classes_global_by_latency |
| sys | x$waits_by_host_by_latency |
| sys | x$waits_by_user_by_latency |
| sys | x$waits_global_by_latency |
+--------------+-----------------------------------------------+
100 rows in set (1.41 sec)
I also tried checking these tables but they seem to be ok, so questions is are these new implementation and have NULL values for index and data-size by default if yes then why have such tables? what would be purpose of these tables(in general)? (guess I need to do a lot of reading about these.)
In the meanwhile I will try making fresh install of mysql version 5.7.12 during weekend and see what would be the output of above command.
regards