Unspecified fatal error encountered aborting

MYSQL startup error: [ERROR] Fatal error: mysql.user table is damaged.

Posted by FatDBA on November 15, 2017

While working with one of the client for his brand new installation i’ve encountered a weird problem while starting the MYSQL (5.7.20) daemon on RHEL6 where the MYSQLD service failed to start with below errors or issues captured in error logs.

]# service mysqld start
MySQL Daemon failed to start.
Starting mysqld: [FAILED]

Snippet from the error Logs:

2017-11-15T10:21:03.957212Z 0 [Note] InnoDB: File ‘./ibtmp1’ size is now 12 MB.
2017-11-15T10:21:11.147615Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-11-15T10:21:11.147902Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-11-15T10:21:11.291204Z 0 [Note] InnoDB: Creating sys_virtual system tables.
2017-11-15T10:21:11.300921Z 0 [Note] InnoDB: sys_virtual table created
2017-11-15T10:21:11.301245Z 0 [Note] InnoDB: Waiting for purge to start
2017-11-15T10:21:11.354201Z 0 [Note] InnoDB: 5.7.20 started; log sequence number 0
2017-11-15T10:21:11.354623Z 0 [Note] Plugin ‘FEDERATED’ is disabled.
2017-11-15T10:21:11.354976Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9560ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-11-15T10:21:11.355390Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-11-15T10:21:11.569467Z 0 [Warning] System table ‘plugin’ is expected to be transactional.
2017-11-15T10:21:11.570388Z 0 [Note] Salting uuid generator variables, current_pid: 29102, server_start_time: 1510741261, bytes_sent: 0,
2017-11-15T10:21:11.570971Z 0 [Note] Generated uuid: ‘b3e664f7-c9ee-11e7-9b23-000c29593ffb’, server_start_time: 8191484773744281275, bytes_sent: 44900352
2017-11-15T10:21:11.571109Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b3e664f7-c9ee-11e7-9b23-000c29593ffb.
2017-11-15T10:21:11.573332Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2017-11-15T10:21:11.573745Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-11-15T10:21:11.574116Z 0 [Note] Server hostname (bind-address): ‘*’; port: 3306
2017-11-15T10:21:11.574540Z 0 [Note] IPv6 is available.
2017-11-15T10:21:11.574745Z 0 [Note] – ‘::’ resolves to ‘::’;
2017-11-15T10:21:11.574891Z 0 [Note] Server socket created on IP: ‘::’.

2017-11-15T10:21:11.580607Z 0 [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.
2017-11-15T10:21:11.580879Z 0 [ERROR] Aborting

So after taking a look at the error log it’s quite clear that the startup failed with a ‘Fatal Error’ which in turn crashed the entire startup process for the instance with error message “mysql.user table is damaged”. At the same time it gives a solution or a fix to run the mysql_upgrade, but as the instance failed to start it was not possible to execute the command.

Here is what happened when i tried to execute the mysql_upgrade

bash-4.1$ mysql_upgrade
mysql_upgrade: Got error: 2002: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) while connecting to the MySQL server
Upgrade process encountered error and will not continue.

*******SOLUTION*********
As a fix to avoid this deadlock, I’ve started the server with skip-grant-tables option.
This can be done by adding the ‘skip-grant-tables’ line to the my.cnf (Configuration File) withing section [mysqld].

bash-4.1$ su –
Password:
[root@dixitlab

[mysqld]
#
# 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
innodb_data_file_path = ibdata1:10M:autoextend
skip-grant-tables

Now, lets try to start the mysql server now.

]# service mysqld start
Starting mysqld: [ OK ]
[root@dixitlab

Boom! It worked. Now quickly try to run the mysql_upgrade step to fix the initial problem.

-bash-4.1$ mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading the sys schema.
Checking databases.
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
-bash-4.1$
-bash-4.1$

Now when it is done, lets revert the changes that we have made to the configuration file and remove the skip-grant-table entry from my.cnf file and restart the MYSQLD service.

]# vi /etc/my.cnf
[root@dixitlab

]# service sqld restart
sqld: unrecognized service
[root@dixitlab

]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@dixitlab

Lets try to connect with the database now.

bash-4.1$
bash-4.1$ mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.

Hope This Helps
Prashant Dixit

Источник

Unspecified fatal error encountered aborting mysql workbench

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Hello node-mysql Team,

We are struggling with the connection pooling timeout issue. Currently we are using node-mysql npm to get data from Remote MySQL and expose same data as JSON format . First 2 minutes we are getting the expected data from API ,after that we are receiving error as below.

In our case our application instance is in once server and MySQL DB is in another server

Here is the code snippet that we have

Hope , someone already faced it , can any one Help on this . Thanks In Advance

The text was updated successfully, but these errors were encountered:

Find error screen

Hi! The issue is that you are only using a single connection for the entire life of your application, and once the connection has a fatal error (like the connection was closed by your MySQL server due to idling), the connection will be destroyed.

Calling connection.connect(); does not re-establish a killed connection, only connect a never-connected connection.

Due to incomplete provided code, I cannot show the full refactoring your need to do to get your app to properly use the pool, but I hope just altering your provided code helps:

@dougwilson Hi, I used the configuration you provided, but still gotting the PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR error accidentally, what should I do to keep my service running without this fatal ?

Hi @luochen1990, that error is because you have issues within your own code, and you are making an invalid call to our library. The only real way I can help you at this point is that you need to distill the issue down such that I have the complete code of your entire application. This error cannot be solved if I’m just getting small pieces of your application little by little, I would have to see the entire application, provide me with the instructions to get it up and running, provide the instructions on how to encounter the error and then I can attach a debugger to your app to determine where in your code the mistake is.

The mistake boils down to the following:

1. Your MySQL connection is encountering a fatal error.
2. Your code is ignoring this error and trying to make another query on the same connection.

@dougwilson Thank you for your reply! As you mentioned above, It is possible for my code that the connection is used to send query after it is fatal, since I wrote my code as connect-once-use-many-times style:

As you can see, I connect the db once, and every time I got a message from the MQ, I use the connection I got before to execute a query. If there is an connection error during the time between I get the connection and I execute a query, then the error may occur.

So, what should I write to make the auto-reconnect possible?

So, what should I write to make the auto-reconnect possible?

Yea, that code would definitely exhibit this issue, since you don’t stop using the same db connection even after a fatal connection occurs. Perhaps a better pattern for your use here is to use a connection pool (even if you want to have the connection pool set to a maximum of one connection). Doing this will mean that each time a message comes in, you’re asking the pool for a valid connection and it will make sure you get one, otherwise you have to re-implement much of what the pool code is doing in your code.

A simple example (I don’t write CoffeeScript, so please forgive any syntax errors I made when trying to alter your example above):

@dougwilson thank you very much! I tried this and it works well.

@luochen1990, glad to hear it! Please let me know if you have other questions, are still having problems, etc.

Источник