Solved

The Default 'Mysql' Database is not accessible

Posted on 2004-09-16
7
476 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
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 75 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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