LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 07-29-2016, 12:56 AM   #1
pix9
Member
 
Registered: Jan 2010
Location: Mumbai, India
Distribution: ArchLinux, Fedora 24, Centos 7.0
Posts: 177

Rep: Reputation: 19
Post Mysql upgrade from 5.5.28 to 5.7.12


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
 
Old 07-29-2016, 05:57 PM   #2
Doug G
Member
 
Registered: Jul 2013
Posts: 749

Rep: Reputation: Disabled
Take a look here: http://mysqlserverteam.com/the-mysql...n-mysql-5-7-7/
 
  


Reply

Tags
mysql upgrade, mysql5


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Postfix can't connect MySQL after upgrade Ubuntu and MySQL PosiRob Linux - Software 1 07-02-2012 11:18 AM
[SOLVED] What is the best way to upgrade from mysql-5.0.77-4.el5_4.2 to MySQL 5.1.47 ursusca Linux - Software 2 06-08-2010 12:47 PM
Upgrade to MySQL-5.0.18 breaks PHP mysql functions MarkVickers Slackware 5 05-03-2006 12:10 AM
php-mysql dependancy problem after nitemare mysql upgrade. RHEL4 andrewc Red Hat 1 01-03-2006 04:16 PM
mysql 4.0.24 to mysql 4.1.12 upgrade problem tuxrules Slackware 7 08-13-2005 06:43 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 06:00 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration