Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

The Default 'Mysql' Database is not accessible

Posted on 2004-09-16
7
Medium Priority
?
489 Views
Last Modified: 2008-01-09
HI all,
     I have been looking everywhere to figure out my problem.   This is a Windows 2000 machine using mysql 4.0.18.    The problem seems to be that the mysql database, the one installed and created by default, is not showing up when you do a "Show DATABASES" at the mysql prompt.    When looking in the \mysql\data directory there is a \mysql directory and it appears that all the tables are all there.
     If that is not the problem then somehow the root permissions have disappeared.
Symptoms:
* When using MySQL Control Center 0.9.4-beta, It does not allow me access into "User Administration" and gives a "You don't have enough privileges to access this section",   I am logged in as root.  It allows me to see the other two databases, and allows me to go into "Server Administration"

* When trying to add users using the GRANT command it says  "ERROR 1045: Access denied for user: 'root@servername' (Using password: NO)"  where servername is the DNS name of the server.

* A PHP application is not able to read the privileges or user database.

I believe this is probably an easy question, but I am fairly new with mySQL administration.

Thanks,
Steven Stuart
0
Comment
Question by:sgstuart
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 12081689
Hi,

This simply means that your username or password are incorrect. If you have not changed the username, try using 'root' as the username and no password. If that works, then you are ok. Otherwise, you need to find the correct username / password combination OR set the proper permissions for that particular user to that database.
0
 
LVL 8

Author Comment

by:sgstuart
ID: 12085257
Hi Ushastry,
      I am starting to think more and more it is a permissions issue.     My apologies I thought I wrote that part in the question as well.   All of the above I was using 'root' and no password.   It lets me into everything except what the root is supposed to have access to.    I believe the 'root' permissions somehow got changed.    What I want to know is how to have them go back to default.    At this time, I have reinstalled, which did not change anything.    Without having permission with 'root'  how can I find out what ID has access and what their passwords are, if I can not get 'root' back to its default permissions.

Thanks,
Steven
0
 
LVL 26

Expert Comment

by:Umesh
ID: 12085563
Hi Steven,

take a look at here..

http://www.databasejournal.com/features/mysql/article.php/3311731

this would give you a basic idea on how to grant/revoke etc..

check this for reseting the root password..

http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 8

Author Comment

by:sgstuart
ID: 12086089
Hi Ushastry,
       I know that the ID for 'root' is still 'root' and I know that its password is still blank, so I do not need to reset the password.  I have been to the resetting password link, before asking the question, it is good information though.

I looked at the first link that you sent.  It is very very informative, and I am glad to have it, but it does not tell me what to do for my problem.   I know the Grant and Revoke commands.

      My problem is that 'root' does not appear to have most of those permissions on itself.   So I can not even look or see any table under the 'mysql' database.     The only ID that I know of is  'root' and that it has no password.   I do not know any other ID, if there is any.     How can I give 'root'    Admin permissions again, as it should have?   Remember, that is the only ID that I know.   Is there a way to reset 'root' privileges?

Thanks,
Steven Stuart
0
 
LVL 26

Accepted Solution

by:
Umesh earned 225 total points
ID: 12086163
Hi Steven,

that's what I was briefing you all..

Stop mysqld and restart it with the --skip-grant-tables option . this would ignores all the rules/grants set by the user who has got grant option..and then you can check any tables values..
Connect to the mysqld server with this command: shell> mysql -u root

Now you can check any table under mysql..Hope you are getting me.. if you get success then you can grant privileges are required..

0
 
LVL 8

Author Comment

by:sgstuart
ID: 12086480
Hi Usharty,
      Thank you very much,    I definitely understand you now, and I apologize that I did not understand that was what you were trying to tell me to do.   I am in there like that at this point.    I am trying to GRANT ALL ON *.* to 'root'@'servername';  and I am getting an ERROR 1047: Unknown command.      This mysql is on a Windows 2000 server, which I do not expect to make a difference,  Can you see an error in what I am typing?   I have placed   IDENTIFIED BY ''    at the end of it too, and that gives the same error.
      I will up the point value for you Usharty when I do get this resolved with you to 100.  You have helped me a lot.

Thanks,
Steven Stuart
0
 
LVL 8

Author Comment

by:sgstuart
ID: 12087096
Hi Usharty,
      I went into the User database and UPDATE each of the priv fields to 'Y'.   After I did this I stopped mysqld-nt, and then restarted the mysql service.   When it came back up everything worked.   So I thank you very much.   I do not know why the GRANT did not work, but it probably does not work when running the --skip-grant-tables.    So I started thinking about Updating each priv field to 'Y' and that took care of the problem.     You did help me immensly, so I am going to give you 75, since I figured the last part on my own, and I already gave you positive feedback as well.

Thanks,
Steven Stuart
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question