Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

The Default 'Mysql' Database is not accessible

Posted on 2004-09-16
7
Medium Priority
?
499 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

916 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