0

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
  • Maybe you should have used 5.1 as an intermediate step in the update, going over multiple versions at once is not supported iirc. Do you have a backup of your datadir before the mysql_upgrade? The best way would be to revert to 5.0, dump everything to sql, install 5.5 (or maybe newer now when 5.7 is out) and reload the dump. – jkavalik May 2 '16 at 18:49
  • @jkavalik I just now answered to my own question, but need to understand, as what was wrong in the old my.cnf file. – kingsmasher1 May 3 '16 at 7:47
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.