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
Solved

The Default 'Mysql' Database is not accessible

Posted on 2004-09-16
7
470 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:ushastry
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:ushastry
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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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:
ushastry 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
count download link and run update query 9 82
MS Access - need to reduce row size 25 60
MySQL Backup Strategy 15 44
Complex MySQL Query 2 33
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 …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

765 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