1

I'm running Mysql5.6 on RHEL 7. I had managed to get the mysql service up and running, and imported a database into it with no issues.

However, after attempting to reset a user's password, the database crashed. I tried to bring the service back up, but the service would only ever timeout.

This morning I once again tried to restart the service, and instead of timing out, received this error:

Job for rh-mysql56-mysqld.service failed because the control process exited with error code. See "systemctl status rh-mysql56-mysqld.service" and "journalctl -xe" for details.

I ran systemctl status rh-mysql56-mysqld.service and got:

rh-mysql56-mysqld.service - MySQL 5.6 database server

Loaded: loaded (/usr/lib/systemd/system/rh-mysql56-mysqld.service; enabled; vendor preset: disabled)

Active: failed (Result: exit-code) since Thu 2016-06-30 15:59:54 EDT; 14s ago

Process: 14677 ExecStopPost=/usr/bin/scl enable $RH_MYSQL56_SCLS_ENABLED -- /opt/rh/rh-mysql56/root/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)

Process: 14477 ExecStartPost=/usr/bin/scl enable $RH_MYSQL56_SCLS_ENABLED -- /opt/rh/rh-mysql56/root/usr/libexec/mysql-wait-ready $MAINPID (code=exited, status=1/FAILURE)

Process: 14476 ExecStart=/opt/rh/rh-mysql56/root/usr/libexec/mysqld_safe-scl-helper enable $RH_MYSQL56_SCLS_ENABLED -- /opt/rh/rh-mysql56/root/usr/bin/mysqld_safe --basedir=/opt/rh/rh-mysql56/root/usr (code=exited, status=0/SUCCESS)

Process: 14437 ExecStartPre=/usr/bin/scl enable $RH_MYSQL56_SCLS_ENABLED -- /opt/rh/rh-mysql56/root/usr/libexec/mysql-prepare-db-dir %n (code=exited, status=0/SUCCESS)

Process: 14409 ExecStartPre=/usr/bin/scl enable $RH_MYSQL56_SCLS_ENABLED -- /opt/rh/rh-mysql56/root/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)

Process: 14402 ExecStartPre=/usr/bin/scl enable $RH_MYSQL56_SCLS_ENABLED -- /usr/bin/scl_enabled rh-mysql56 (code=exited, status=0/SUCCESS)

Main PID: 14476 (code=exited, status=0/SUCCESS)

Jun 30 15:59:53 sia-cmsweb-new.si.edu systemd[1]: Starting MySQL 5.6 database server...

Jun 30 15:59:53 sia-cmsweb-new.si.edu mysqld_safe-scl-helper[14476]: 160630 15:59:53 mysqld_safe Logging to '/var/opt/rh/rh-mysql56/log/mysql/mysqld.log'.

Jun 30 15:59:53 sia-cmsweb-new.si.edu mysqld_safe-scl-helper[14476]: 160630 15:59:53 mysqld_safe Starting mysqld daemon with databases from /var/opt/rh/rh-mysql56/lib/mysql

Jun 30 15:59:54 sia-cmsweb-new.si.edu mysqld_safe-scl-helper[14476]: 160630 15:59:54 mysqld_safe mysqld from pid file /var/run/rh-mysql56-mysqld/mysqld.pid ended

Jun 30 15:59:54 sia-cmsweb-new.si.edu systemd[1]: rh-mysql56-mysqld.service: control process exited, code=exited status=1

Jun 30 15:59:54 sia-cmsweb-new.si.edu systemd[1]: Failed to start MySQL 5.6 database server.

Jun 30 15:59:54 sia-cmsweb-new.si.edu systemd[1]: Unit rh-mysql56-mysqld.service entered failed state.

Jun 30 15:59:54 sia-cmsweb-new.si.edu systemd[1]: rh-mysql56-mysqld.service failed.

I also checked the error logs after attempting to start mysql. There are a few errors, which I suspect have something to do with the issue:

160630 14:14:48 mysqld_safe Starting mysqld daemon with databases from /var/opt/rh/rh-mysql56/lib/mysql

2016-06-30 14:14:48 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2016-06-30 14:14:48 0 [Note] /opt/rh/rh-mysql56/root/usr/libexec/mysqld (mysqld 5.6.30) starting as process 9138 ...

2016-06-30 14:14:48 9138 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2016-06-30 14:14:48 9138 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)

2016-06-30 14:14:48 9138 [Note] Plugin 'FEDERATED' is disabled.

/opt/rh/rh-mysql56/root/usr/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)

2016-06-30 14:14:48 9138 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

2016-06-30 14:14:48 9138 [Note] InnoDB: Using atomics to ref count buffer pool pages

2016-06-30 14:14:48 9138 [Note] InnoDB: The InnoDB memory heap is disabled

2016-06-30 14:14:48 9138 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2016-06-30 14:14:48 9138 [Note] InnoDB: Memory barrier is not used

2016-06-30 14:14:48 9138 [Note] InnoDB: Compressed tables use zlib 1.2.7

2016-06-30 14:14:48 9138 [Note] InnoDB: Using Linux native AIO

2016-06-30 14:14:48 9138 [Note] InnoDB: Using CPU crc32 instructions

2016-06-30 14:14:48 9138 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2016-06-30 14:14:48 9138 [Note] InnoDB: Completed initialization of buffer pool

2016-06-30 14:14:48 9138 [Note] InnoDB: Highest supported file format is Barracuda.

2016-06-30 14:14:48 7f9e223b6840 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to

InnoDB: the directory.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/innodb_index_stats'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."innodb_index_stats"', ignored.

2016-06-30 14:14:48 7f9e223b6840 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to

InnoDB: the directory.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/innodb_table_stats'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."innodb_table_stats"', ignored.

2016-06-30 14:14:48 7f9e223b6840 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/slave_master_info'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."slave_master_info"', ignored.

2016-06-30 14:14:48 7f9e223b6840 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/slave_relay_log_info'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."slave_relay_log_info"', ignored.

2016-06-30 14:14:48 7f9e223b6840 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/slave_worker_info'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."slave_worker_info"', ignored.

2016-06-30 14:14:48 9138 [Note] InnoDB: 128 rollback segment(s) are active.

2016-06-30 14:14:48 9138 [Note] InnoDB: Waiting for purge to start

2016-06-30 14:14:48 9138 [Note] InnoDB: 5.6.30 started; log sequence number 1625987

2016-06-30 14:14:48 9138 [Note] RSA private key file not found: /var/opt/rh/rh-mysql56/lib/mysql//private_key.pem. Some authentication plugins will not work.

2016-06-30 14:14:48 9138 [Note] RSA public key file not found: /var/opt/rh/rh-mysql56/lib/mysql//public_key.pem. Some authentication plugins will not work.

2016-06-30 14:14:48 9138 [Note] Server hostname (bind-address): '*'; port: 3306

2016-06-30 14:14:48 9138 [Note] IPv6 is available.

2016-06-30 14:14:48 9138 [Note] - '::' resolves to '::';

2016-06-30 14:14:48 9138 [Note] Server socket created on IP: '::'.

2016-06-30 14:14:48 9138 [ERROR] /opt/rh/rh-mysql56/root/usr/libexec/mysqld: Can't find file: './mysql/user.frm' (errno: 13 - Permission denied)

2016-06-30 14:14:48 9138 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/user.frm' (errno: 13 - Permission denied)

160630 14:14:48 mysqld_safe mysqld from pid file /var/run/rh-mysql56-mysqld/mysqld.pid ended

I have tried setting the ownership of the rh-mysql56 to mysql using chown myql:mysql ./path/to/rh-mysql56, but that didn't help (also tried switching it to root, also didn't help).

I've tried running mysql_upgrade, which I have seen working for others, but I get the error:

Looking for 'mysql' as: ./mysql Looking for 'mysqlcheck' as: ./mysqlcheck Error: Failed while fetching Server version! Could be due to unauthorized access. FATAL ERROR: Upgrade failed

I've also tried doing a yum reinstall, which didn't help issues at all. At this point I'm kinda stuck, and wondering if there is something else I should try, or if there's something in the error messages I'm missing.

I should also say that mysql is installed to a abnormal location (it's at /opt/rh/rh-mysql56).

  • So, you have a dump that you can't get working? I'd wipe the entire hdd, repartition, reinstall OS and then install MySQL again with defaults this time. Yes, I know, a couple of hours, but hey, that's MySQL for you. Then reload your dump file. That will hopefully do the trick. – Vérace Jun 30 '16 at 20:29
  • The dump was working, up until I tried to change a user password. – Jance Jun 30 '16 at 20:52
  • What command did you issue? If it was a standard one, then it looks as if there may be some corruption - at least that's my take on it. Attempting to change a password should either work or fail - not bring down the server. – Vérace Jun 30 '16 at 21:12
  • I tried to change the password via mysql workbench, so I didn't go in through the command line on that one. – Jance Jul 5 '16 at 13:16
1

I keep seeing permission denied errors in the log.

You didn't run the chown command recursively

Please do this

chown -R mysql:mysql ./path/to/rh-mysql56

I also see errors like

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."innodb_index_stats"', ignored.

When you installed mysql before, it created 5 InnoDB system tables (See my answers Cannot open table mysql/innodb_index_stats and InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6).

You need to recreate them.

First you need to delete the orphaned .frm files

cd ./path/to/rh-mysql56/mysql
rm -f innodb_index_stats.frm
rm -f innodb_table_stats.frm
rm -f slave_master_info.frm
rm -f slave_relay_log_info.frm
rm -f slave_worker_info.frm
rm -f innodb_index_stats.ibd
rm -f innodb_table_stats.ibd
rm -f slave_master_info.ibd
rm -f slave_relay_log_info.ibd
rm -f slave_worker_info.ibd

Then, you can recreate them with

USE mysql
CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

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.