Suddenly getting errors when accessing mysql db using PHP

[snorky]

MySQL 4.1

CentOS 4.5

 

NOTE: I have more than a full-time job administering a network with 2500+ nodes and 6000+ end users. Since these old versions of MySQL and CentOS have been working, I haven't updated them. "If it ain't broke, don't fix it."

 

That said, the server that hosts the mysql server got hit by an unexpected power-down -- someone was testing the backup power generator ...

 

I have several databases on that instance of mysql. All seemed to have survived - except, of course - the most important one. On the 'other' DBs, users can run the php/html programs and access data without problems.

 

Note: everything on this MySQL server has run smoothly for several years prior to the big hit.

 

On the 'big' database - the one that is now suffering -

[*]the users cannot access the server; for these users the PHP performs only SELECT functions

[*]the maintainers - the people whose PHP performs SELECT, INSERT, DELETE, UPDATE functions cannot access the server

[*]the DBA - me - cannot use PHP, the MySQL console, and Webmin to access data

note: I don't use PHPAdmin on this - phpmyadmin broke after an update and I never bothered to fix it

The failed commands and the error messages errors:

• users:
  $link=mysql_connect('xxx.xxx.xxx.15','guest','');
  PHP CODE:
  

if (!$link)
{
die('Unable connect to the server at this time. Version 20110216.1155: '. mysql_error());
}

ERROR MSG:

Unable connect to the server at this time. Version 20110216.1155: Access denied for user 'guest'@'xxx.xxx.xxx.22' (using password: NO)

 

• MAINTAINERS:
  CODE:

$link=mysql_connect('xxx.xxx.xxx.15','maintainer','password');
{
die('Unable connect to the server at this time. Version 20091006.1300: ' . mysql_error());
}

ERROR MSG:

Unable connect to the server at this time. Version 20091006.1300: Access denied for user 'maintainer'@'xxx.xxx.xxx.22' (using password: YES)

 

• DBA:
  COMMANDS/ERROR MSGS: I get a panoply of errors, depeding on what I'm doing. Whether I log on to mysql as root or as dba, all of the errors refer to user ''
   
  

• mysql -u root -p

  --- I no longer need a password for root or dba
  when SELECTing from the mysql table, the passwords for root  dba display in plain text, instead of encrypted
  

  mysql> SET PASSWORD FOR 'dba'@'localhost' = PASSWORD('newpass');
  ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

  
  Note that the user is ''
   
  

  mysql> UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='dba' AND Host='localhost';
  ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user'

  
   
  I get the error command denied to user '' with any db admin statements such as SELECT, INSERT, UPDATE, SET, DELETE.
  As a result, I cannot create a new user or superuser, elevate the privileges for an existing user, delete existing users. For example, using Webmin,
  

  Failed to delete users : SQL delete from user where user = 'dba' and host = '' failed : DELETE command denied to user ''@'localhost' for table 'user'

  • 
    

   

  I have yesterday's mysqldump of this (and each) non-system database, i.e., all but the mysql DB. However, I don't see where restoring the non-problem DBs' data will solve the problem of users, passwords, and privileges. 

   

  It appears to me that the mysql DB is corrupted. Is there a way to fix that? Or am I (as usual) going off half-cocked?

   

   

   

[Muddy_Funster]

assuming that you have admin access to the server then you can force reset the password for mysql without being able to login to the database it's self.  this should let you access from the console again where you can then re-apply priviledges and reset passwords.  google for MySQL root password reset should get you on the right track.

[snorky]

Oddly enough, I tried that (or something similar, apparently omitting/goofing up a step).

 

Following your instructions fixed it. Thank you.