Failed to save user SQL flush privileges failed Table 'mysql.servers' doesn't exist

Chris Kenward
Chris Kenward used Ask the Experts™
on
Hi Folks

I feel really dumb! This is the second time this has happened to me on one of my CentOS 5.3 servers and I cannot remember how I resolved the issue. This time it's far more important than the last time because this is happening on a "live" server and we need to be creating databases and users all day - can't do that at the moment because of this error.

Any help would be sincerely appreciated.

Regards
Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Did you just upgrade the server?

If so, you should run the "mysql_upgrade" utility.

Here's a URL which gives details on using the utility for MySQL version 5.0

http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html
Chris KenwardIT Tech Support

Author

Commented:
@wolfgang_93
Thanks for the reply. I have a feeling the MySQL server was upgraded the last time I ran yum. I'm beginning to wonder whether this is a specific error to WEBMIN as we use this control panel to create users etc. in the database server. All websites on the server are working fine and there appear to be no errors or problems. It's only when I try to change a user's password or create a new user, or link a user to a database, that this error comes up.

To test this, is there a command I can use the from command line to say change a user's password, just to see whether I get a similar error?

Cheers
Chris
Here are commands (issued as root) to change user "fubar" password to "abc":

use mysql;
select user,host,password from user where user='fubar'  <-- should fetch 1 row
update user set password=password('abc') where user='fubar';
flush privileges;
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Chris KenwardIT Tech Support

Author

Commented:
I think there is a problem. When I pasted that code into the Query Browser using MySQL Administrator, changed the password etc., and after I realised I had to select and highlight the database MySQL on the right hand panel, it stopped giving me errors about ' no database selected ' but then I got absolutely no message at all, so I assumed this meant all OK.

When I try to add or delete or change a user details, I get the same error.

Cheers
Chris
Please run mysql_upgrade as I suggested above like this:

mysql_upgrade

It will automatically check for anomolies and fix them, particularly the privilege tables
which can change between versions and cause problems similar to what you are seeing.

If there are no issues, then the mysql_upgrade utility won't do anything, so it is harmless
in that regard.

It will report everything that it does change.

It is best to run the utility during a time when no one is using the active database ad tables
get locked as they get checked by this utility.
Chris KenwardIT Tech Support

Author

Commented:
Wolfgang, how long is this supposed to run? I've run it and it seems to have stopped on one of the tables in one of the databases and almost appears to be stuck there. Do I let it run or do I stop it?
I suggest logging on elsewhere and issuing a  SHOW PROCESSLIST command to see
what is running. If it shows just your thing running, then it is just taking a long time on a big
table.
If you see something else running, you can use KILL command to stop it.
Chris KenwardIT Tech Support

Author

Commented:
OK - what happened was I had to kill the OS and restart the computer. When I started running the command I also put up another terminal window running 'top' to see what happened on the machine. What happened was that the load level on the server simply climbed until the entire system was at 100% and it stopped responding. No websites were showing and I couldn't even start a terminal window. Fortunately it's a virtual server and I was able to simply "switch it off". However, I don't like doing that in case something gets corrupted.

Is there any way to completely repair every database one at a time with a manual command for each one?
Chris KenwardIT Tech Support

Author

Commented:
Hi Wolfgang

As an update, I've realised that there may be two databases which have corrupted tables in them and this could be causing an issue. One of the databaseses produces this error when I try to click on it in WEBMIN.

SQL show index from `information_schema` failed : No database selected

If, when using MySQL Administrator, I try to list all the tables in that database, I get a table which is now "zero" bytes on disk, is highlighted in red which says it's corrupted and needs to be restored from backup. I have backups of all these databases on the local machine. How would I restore a backup from the local machine using the command line SQL commands as you've given me before? Perhaps I try that and then see what happens?

Cheers
Chris
Chris KenwardIT Tech Support

Author

Commented:
And.... FYI - when I click on the other database with issues, I get:

SQL show index from `sf_cache_file_statistics` failed : Table './rfsdb/sf_cache_file_statistics' is marked as crashed and last (automatic?) repair failed

Does this help throw light on things?

All the best
Chris
First thing: make sure no users are accessing the server while you are trying to fix it.
This command shows everyone using it:
    show processlist

I am thinking you likely had corrupted MyISQM tables and that is why you encountered the hang
as the mysql_upgrade utility examined each table. Tables in MySQL are by default
MyISAM type which can get easily corrupted.

Here is a plan A
============
There is a "repair" command to attempt to repair a corrupt MyISAM table
(most of the time it works, sometimes it leaves you with an empty table or a still
corrupted table)
-  For each table called fubar flagged as corrupt, I would verify that it is a MyISAM table
   with a command like this:
     show table status like 'fubar'

-  To attempt a repair of the table:
      repair table fubar

Plan B
=====
Assuming plan A does not work and you have no option but to go back to an
earlier version of your database install.

I am assuming you never used the mysqldump utility to save your databases?

If you are relying on system file backups, there is a possibility to do a restore,
but only if all your tables are MyISAM and if no one was doing a table update
during the system file backups:

Here's how:
-  Shut down your MySQL server
-  Restore all the files associated with the server, particularly the data files to
   their correct location on your machine
-  Start up the MySQL server
-  Check for any tables flagged as corrupt. With any luck, you do not have any
   or at most 1 or 2.
-  Use the repair option (see above) to repair any of these tables.
Chris KenwardIT Tech Support

Author

Commented:
Hi Wolfgang

Just a quick note to let you know I've not been brave enough to do all this stuff yet. Will attempt it tomorrow and let you know how I get on.

Thanks for the support.
Chris
Chris KenwardIT Tech Support

Author

Commented:
Hi Wolfgang

Is it possible to run these commands from the command line in a terminal window on the console? I'm trying to run them from my local Windows desktop and it's very slow and tedious.

For repair, for example, could I type 'mysqladmin repair table "fubar"' or similar?

Or could I do something similar to show tables which are corrupted?

Cheers
Chris
There is a MySQL utility program called myisamchk that lets you check for a bunch of tables to
be repaired and to repair them in a batch.

The key thing is to know where your MyISAM tables are located and issue Linux commands to
navigate to the directory. Your my.cnf file should show you where the data directory is.
Example:
  cd /usr/local/mysql/data

Here is a small tutorial on a website that I Googled -- with examples of using the program to
determine tables to be fixed, then to either fix them one at a time, or do all the tables in a batch.

http://www.thegeekstuff.com/2008/09/how-to-repair-corrupted-mysql-tables-using-myisamchk/
Chris KenwardIT Tech Support

Author

Commented:
Hi Wolfgang

Thanks for that. I came across the myisamchk utility on google last night and have been running it. One of the .MYI files took 7 hours to check and is still coming up as corrupted. I used both the -r AND the -o commands but it appears that isn't working.

Also, considering my errors relate to a table which sounds as though it's part of the overall MySQL setup, I look in a folder called mysql and found a number of tables in there with both MYI and MYD extensions. I ran the myisamchk utility but it instantly completed with no messages at all, even though I sent the output to a text file. I'm beginning to wonder how I'm going to tell my customers that I can no longer add or change users or databases on this server... <groan>

Any thoughts appreciated.

Cheers
Chris
Chris KenwardIT Tech Support

Author

Commented:
Hmmm - I'm still no nearer to figuring this out. Sorry for the long periods of inactivity on this question - I'm completely buried under other work here and need to be able to spend more time to resolve it. Thanks for your patience.
Chris KenwardIT Tech Support

Author

Commented:
Hi there, Wolfgang

Whew - OK - I've had some time again to make some checks. I've run and re-run the Myisamchk utility on several databases and it turns out that of all the live databases on the server, there are only two with major corruptions and one where one table is corrupted.

If I try to force a thorough check and repair on the badly corrupted databases, the server load average simply climbs and climbs until the two CPUs are at 100% and the server won't accept any input from the console at all. In the end, fortunately it is a virtual server because I'm more than 20 miles from the datacentre, and I was able to load vSphere and power it off and restart. Not a nice way to shut down I know but it was the only choice I had at the time.

I've now installed phpMyAdmin on the server in an attempt to use that to try and add users and connect them with new databases and it seems to be working OK. However, I'm having trouble using it because of php extensions that it says I'm missing. I'm having no end of problems trying to install these extensions as the response is that they are already installed. I'm going to open another question on EE in the hope that I can resolve these issues and use phpMyAdmin on this server.

So.... although I can add a database using Webmin, when I attempt to add a user for the new database, I get the error:

Failed to save user : SQL flush privileges failed : Table 'mysql.servers' doesn't exist

I'm assuming this has something to do with the one or more tables which might be corrupted but I have no idea in MySQL which database this error is being generated from. It surely cannot be the databases where we know there are corrupted tables? I can see how that issue can stop me from adding a new user to the mysql database unless there is also something messed up in there. Am I right? Do you know which database or table I should be trying to repair for this new user issue?

I'm glad I don't have much hair - there isn't much to tear out! ;)

Thanks again for your patience and I hope my update helps.

Kind regards
Chris
The "mysql" database contains important tables that control access and new user
info.

In particular, these 2 tables are important: "user" and "db".
Chris KenwardIT Tech Support

Author

Commented:
Hi Wolfgang

When I run myisamchk on these two I get:

Checking MyISAM file: user
Data records:      25   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links

Checking MyISAM file: db
Data records:      20   Deleted blocks:       1
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2

Does this look OK?

Cheers
Chris
That looks good. You should also check these tables in the mysql database:

columns_priv
func
help_category
help_keyword
help_relation
help_topic
host
proc
procs_priv
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
Chris KenwardIT Tech Support

Author

Commented:
Hi Wolfgang

All done and all come up OK in phpMyAdmin. I'm going to test adding a database and user from within Webmin but at this stage it appears all the checking and various things you've suggested have worked. Fingers crossed!

Cheers
Chris
Chris KenwardIT Tech Support

Author

Commented:
Hi Wolfgang

I'm beginning to wonder whether this is a Webmin issue. Although I can add a user and change their password, and add a user to a newly created database from within phpMyAdmin, I cannot do it from Webmin without the same error as before:

Failed to save user : SQL flush privileges failed : Table 'mysql.servers' doesn't exist

However, if I go into phpMyAdmin and look at the mysql db, then check the list of users, that user has actually been added to the MySQL setup. Any thoughts on this?

All the best
Chris
Ok, things are getting clearer. I now see that the mysql.servers table exists for MySQL
version 5.1, but not for MySQL 5.0 .

It is what happens when you do an "upgrade in place" for MySQL and do not successfully
run the mysql_upgrade program (see my original suggestion for fixing your problem).

Unfortunately you indicate that you HAD tried running mysql_upgrade and the procedure
went into a long loop and you had to kill it. It appears you have corruption in one or more
tables in one or more databases elsewhere. And you appear to have had a problem trying
to fix them.

The cleanest way to remove corruption is to drop the offending tables, or a more drastic
method is to drop the entire database. Of course then you have to start from scratch to
get the tables and/or databases recreated and it does not sound like you have done
mysqldump type of backups from which you could restore the tables/databases to a time
before they became corrupt.

Once all your tables in all your databases are cleaned up, then run mysql_upgrade again
and get the internal structures upgraded to version 5.1 .
Chris KenwardIT Tech Support

Author

Commented:
Hi Wolfgang

Thanks for your continued help with this. I also think we are getting closer! ;)

I have now checked all databases and managed to repair all but one of the tables on the system. I have tested the specific web site that uses that database and it appears to make no difference to the running of the site that the table is not there.

If I now run mysql_update do I need to stop the mysqld service first? Fingers crossed that this upgrade doesn't kill any of the live databases running on the server. I've take a MySQL dump of all databases and they are now safely residing on my backup storage.

<takes deep breath> - OK - awaiting your answer and I'll push the button. :)

Cheers
Chris
Here is what I suggest:
-  Schedule a date/time for the repair

At the time of the repair, do this:
-  You have to have the server running while doing this as the mysql_update
   utility will issue SQL commands to add missing columns and tables (such as mysql.servers)
   to your system.
-  Make sure nothing is running something like this on your server:
      mysql -u root -p
      show processlist
      quit
-  Run the mysql_update utility
-  Be patient and give it time to do the work. Keep in mind that it will check each table in
   each database, so if you have lots of those, it will take some time.
Chris KenwardIT Tech Support

Author

Commented:
Hi Wolfgang

Right! Watch this space! <g> It could be over the weekend. I want to make sure I've got more recent backups of all databases as well as the whole server, just in case something goes wrong.

All the best
Chris
There is no such thing as backing up too much when it comes to making a
potential change to a production system. Good luck!
Someone needs to award wolfgang 93 points for this solution.  His solutions and explainations worked for me.
Chris KenwardIT Tech Support

Author

Commented:
Thanks very much for the help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial