Mysql – Upgrading to MySQL 5.5 from 5.0 looks broken

linuxMySQLmysql-5.0mysql-5.5

I was using SLES SP3, and had earlier MySQL 5.0.67. I upgraded it to 5.5.48 (available with the CD) after upgrading my OS to SP4.

I edited the /etc/my.cnf file, and replaced skip-locking to skip-external-locking and commented out skip-innodb

Post that, i had run mysql_upgrade.
The MySQL is started, and i can see the database, but several issues:

1) There is a strange mysql#50#.tmp table created, which when i saw in the data directory, looked to be an empty .tmp directory due to which it was showing.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.tmp      |
| InDexSql           |
| cifs               |
| ifm                |
| mirthdb            |
| mysql              |
| sn                 |
| test               |
+--------------------+
9 rows in set (0.00 sec)

2) mysql_upgrade is failing in command prompt:

sles11sp3-116:/opt/mysql/data # mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

3) The mysql_error_log shows lot of errors:

160502 05:24:48 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
160502  5:24:48 [Warning] option 'general_log': boolean value '/var/log/mysqld.log' wasn't recognized. Set to OFF.
160502  5:24:48 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full na
me instead.


160502  5:27:43 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
160502  5:27:43 [Note] /usr/sbin/mysqld (mysqld 5.5.43-log) starting as process 6716 ...
/usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist
160502  5:27:43 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
160502  5:27:43 InnoDB: The InnoDB memory heap is disabled
160502  5:27:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160502  5:27:43 InnoDB: Compressed tables use zlib 1.2.7
160502  5:27:43 InnoDB: Using Linux native AIO
160502  5:27:43 InnoDB: Initializing buffer pool, size = 128.0M
160502  5:27:43 InnoDB: Completed initialization of buffer pool
160502  5:27:43 InnoDB: highest supported file format is Barracuda.
InnoDB: 127 rollback segment(s) active.
160502  5:27:44  InnoDB: Waiting for the background threads to start
160502  5:27:45 InnoDB: 5.5.43 started; log sequence number 43655
160502  5:27:45 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160502  5:27:45 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
160502  5:27:45 [Note] Server socket created on IP: '0.0.0.0'.
160502  5:27:45 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
160502  5:27:45 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
160502  5:27:45 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
160502  5:27:45 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
160502  5:27:45 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50067, now running 50543. Please use mysql_upgrade to fix this erro
r.
160502  5:27:45 [ERROR] mysql.user has no `Event_priv` column at position 29
160502  5:27:45 [ERROR] Cannot open mysql.event
160502  5:27:45 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
160502  5:27:45 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.43-log'  socket: '/tmp/mysql.sock'  port: 3306  SUSE MySQL package

Can someone help me to know, what exactly i am missing?

Here is my.cnf file:

[client]
default-character-set = utf8
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
general-log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
init-connect='SET NAMES utf8'
myisam_sort_buffer_size = 1024M
tmpdir = /opt/mysql/temp
max_connections = 500
key_buffer = 4096M
datadir         = /opt/mysql/data
user            = mysql
port            = 3306
socket          = /tmp/mysql.sock
#skip-locking
skip-external-locking

max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
#skip-innodb


# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Best Answer

The problems got removed, when i swapped the my.cnf file, with a highly simplified version of it, which was earlier present in a machine, having MySQL 5.6.

So possibly the issue was with my.cnf file.

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
init-connect='SET NAMES utf8'
max_connections = 500
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
datadir=/opt/mysql/data

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

After swapping the above my.cnf file, even the mysql_upgrade worked.

sles11sp3-116:/tmp # mysql_upgrade -uroot -pinsite3
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
InDexSql.MasterImageSql                            OK
InDexSql.Patient                                   OK
InDexSql.commit                                    OK
InDexSql.orphan                                    OK
InDexSql.pfetch_sch                                OK
ifm.AttributesTable                                OK
ifm.ChunkTable                                     OK
ifm.FileTable                                      OK
ifm.LinksTable                                     OK
ifm.LocalStorageTable                              OK
ifm.PartitionTable                                 OK
ifm.SystemTable                                    OK
ifm.VersionsTable                                  OK
mirthdb.ALERT                                      OK
mirthdb.CHANNEL                                    OK
mirthdb.CODE_TEMPLATE                              OK
mirthdb.CONFIGURATION                              OK
mirthdb.D_CHANNELS                                 OK
mirthdb.EVENT                                      OK
mirthdb.PERSON                                     OK
mirthdb.PERSON_PASSWORD                            OK
mirthdb.PERSON_PREFERENCE                          OK
mirthdb.SCHEMA_INFO                                OK
mirthdb.SCRIPT                                     OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.func                                         OK
mysql.help_category
error    : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it!
mysql.help_keyword
error    : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it!
mysql.help_relation                                OK
mysql.help_topic
error    : Table upgrade required. Please do "REPAIR TABLE `help_topic`" or dump/reload to fix it!
mysql.host                                         OK
mysql.proc
error    : Table upgrade required. Please do "REPAIR TABLE `proc`" or dump/reload to fix it!
mysql.procs_priv                                   OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name
error    : Table upgrade required. Please do "REPAIR TABLE `time_zone_name`" or dump/reload to fix it!
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
sn.db_hosts                                        OK
sn.facility                                        OK
sn.filter                                          OK
sn.studyqueue                                      OK
sn.viewer                                          OK

Repairing tables
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_topic                                   OK
mysql.proc                                         OK
mysql.time_zone_name                               OK
Running 'mysql_fix_privilege_tables'...
WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have been updated with a default value (utf8). Please verify if necessary.
WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have been updated with a default value (utf8_general_ci). Please verify if necessary.
WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been updated with default values. Please verify if necessary.
OK

As directed by mysql_upgrade, i manually invoked "Repair Table" after that.

mysql> REPAIR TABLE `help_category`;
+---------------------+--------+----------+----------+
| Table               | Op     | Msg_type | Msg_text |
+---------------------+--------+----------+----------+
| mysql.help_category | repair | status   | OK       |
+---------------------+--------+----------+----------+
1 row in set (0.00 sec)

mysql> REPAIR TABLE `help_keyword`;
+--------------------+--------+----------+----------+
| Table              | Op     | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| mysql.help_keyword | repair | status   | OK       |
+--------------------+--------+----------+----------+
1 row in set (0.01 sec)

mysql> REPAIR TABLE `help_topic`;
+------------------+--------+----------+----------+
| Table            | Op     | Msg_type | Msg_text |
+------------------+--------+----------+----------+
| mysql.help_topic | repair | status   | OK       |
+------------------+--------+----------+----------+
1 row in set (0.01 sec)

mysql> REPAIR TABLE `proc`;
+------------+--------+----------+----------+
| Table      | Op     | Msg_type | Msg_text |
+------------+--------+----------+----------+
| mysql.proc | repair | status   | OK       |
+------------+--------+----------+----------+
1 row in set (0.00 sec)

mysql> REPAIR TABLE `time_zone_name`;
+----------------------+--------+----------+----------+
| Table                | Op     | Msg_type | Msg_text |
+----------------------+--------+----------+----------+
| mysql.time_zone_name | repair | status   | OK       |
+----------------------+--------+----------+----------+
1 row in set (0.00 sec)

Related Solutions

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite." It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high. Here's what makes me think this might be contributing to your problem:

When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

^{http://dev.mysql.com/doc/refman/5.6/en/memory-use.html}

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time. Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not. I tested this on one of my systems, which had 9 threads in the cache. Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem. If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed. Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test. You should not see a substantial performance penalty by setting thread_cache_size to 0. Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.

Mysql Server Login and Permission Deny Error

Besides not running mysql_upgrade after upgrading MySQL from major subversion to major subversion (E.g 5.1 to 5.5), having the wrong permissions/ownership on the performance_schema folder and files within the data directory can also cause this error.

For example, a fresh install of MySQL 5.5's RPM will set the ownership of that folder and files within it to root:root. To fix this, you will need to change the ownership to mysql:mysql.

E.g.

chown -R mysql:mysql /var/lib/mysql/performance_schema

ls -al /var/lib/mysql

drwx------. 2 mysql mysql     4096 Jun 10 10:29 performance_schema

Best Answer

Related Solutions

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

Mysql Server Login and Permission Deny Error

Related Question