LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 08-02-2010, 05:32 PM   #1
Joe of Loath
Member
 
Registered: Dec 2009
Location: Bristol, UK
Distribution: Ubuntu, Debian, Arch.
Posts: 152

Rep: Reputation: 28
mysql 'Table 'mysql.user' doesn't exist'


Hi there

I'm trying to setup wordpress on my server, which of course I need mysql for. I setup a database the other day, which worked perfectly, but I had to start again as I put some information in wrong. Now, I can create a database fine, but when I run
Code:
 GRANT ALL ON wordpress.* TO wordpress@localhost IDENTIFIED BY "password";
I get the output:
Quote:
ERROR 1146 (42S02): Table 'mysql.user' doesn't exist
This didn't happen before.

I'm running mysql version 5.0.51a-24+lenny4, on the server described in my signature (bottom line)

Can anybody help?
Thanks
Joe

Last edited by Joe of Loath; 08-02-2010 at 05:33 PM.
 
Old 08-03-2010, 05:46 AM   #2
SkyEye
Member
 
Registered: Sep 2005
Location: Sri Lanka
Distribution: Fedora (workstations), CentOS (servers), Arch, Mint, Ubuntu, and a few more.
Posts: 441

Rep: Reputation: 40
I'm not sure exactly what this could be.

Can do a few things. Let us know how exactly you are trying out mysql this time (when the error occurred), i.e. is it by running 'mysql' tool from your account, or from a special mysql user account, etc.

But always the simplest thing to do is to test for users. Please check these.
1. From a new terminal try to connect to mysql server.
Eg: $ mysql -u root -p

If this succeeds it's a good sign. Which means your dbserver still knows about its users.

2. When inside the database change to the db 'mysql' and run a query.
Eg: > use mysql;
> select * from user;

If there are entries then your server is seemingly ok and have to look for other troubles. If there isn't any content (or missing things), you have a problem. In that case flushing privileges or restarting the mysql server is a bad idea until you use someway to salvage your user data.

Sorry, I'm not a DBA. Hope this helps for now.
 
Old 08-03-2010, 06:29 AM   #3
Joe of Loath
Member
 
Registered: Dec 2009
Location: Bristol, UK
Distribution: Ubuntu, Debian, Arch.
Posts: 152

Original Poster
Rep: Reputation: 28
I was entering via "mysql -u root -p". I can log in fine, create the database, show databases and delete them, but I can't give an account privileges over a database.

I can screw around with the database all I want, it's freshly created, so there's no data to lose.
 
Old 08-03-2010, 06:38 AM   #4
SkyEye
Member
 
Registered: Sep 2005
Location: Sri Lanka
Distribution: Fedora (workstations), CentOS (servers), Arch, Mint, Ubuntu, and a few more.
Posts: 441

Rep: Reputation: 40
You missed the important point. The error message clearly says "Table 'mysql.user' doesn't exist". That's why I asked you to see if there's content.
Eg:
> use mysql;
> select * from user;

On second thought, *have you* selected the database before trying to grant privileges?

Note: Googling the error code also might lead to more info.
 
Old 08-03-2010, 08:53 AM   #5
Joe of Loath
Member
 
Registered: Dec 2009
Location: Bristol, UK
Distribution: Ubuntu, Debian, Arch.
Posts: 152

Original Poster
Rep: Reputation: 28
whoops, sorry. This is what I get:

Quote:
mysql> use mysql;
ERROR 1049 (42000): Unknown database 'mysql'
mysql> select * from user;
ERROR 1046 (3D000): No database selected
As to selecting the database, I'm completely clueless with mysql, I'm just following what the tutorial here: http://www.webdevelopersnotes.com/tu..._database.php3

The same steps worked last week, just not this week...
 
Old 08-03-2010, 09:45 AM   #6
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392
Hi,

Possibly a corrupt database?

Could you check the output of these commands.

From within a mysql session:
> show databases -> shows databases that are present. mysql should definitely be mentioned.

And, from the commandline:
mysqlcheck -p -user root --all-databases --auto-repair -> Check and try to repair all databases.

If the mysql database was indeed corrupted in any way, the last command should have fixed it and you should be able to use use mysql and GRANT ALL ON .......

Hope this helps.
 
Old 08-03-2010, 10:30 AM   #7
Joe of Loath
Member
 
Registered: Dec 2009
Location: Bristol, UK
Distribution: Ubuntu, Debian, Arch.
Posts: 152

Original Poster
Rep: Reputation: 28
Ok, the databases are:
Quote:
+--------------------+
| Database |
+--------------------+
| information_schema |
| wordpress |
+--------------------+
Not sure what the first one is, I think it's something that was automatically generated when I installed b2evolution as a test.
I see mysql isn't mentioned. A slight problem, perhaps?

The next bit was more complex. It seems my version of mysql uses different commands to yours, so I had to fiddle around a bit:
Quote:
joe@terrence-server:~$ mysqlcheck -p -user root --all-databases --auto-repair
You should give only options, no arguments at all, with option
--all-databases. Please see mysqlcheck --help for more information.
joe@terrence-server:~$ mysqlcheck -p -user root --all-databases --auto-repair
You should give only options, no arguments at all, with option
--all-databases. Please see mysqlcheck --help for more information.
joe@terrence-server:~$ mysqlcheck -p -user root --auto-repair
Enter password:
mysqlcheck: Got error: 1045: Access denied for user 'ser'@'localhost' (using password: YES) when trying to connect
joe@terrence-server:~$ mysqlcheck -p -u root --auto-repair
You forgot to give the arguments! Please see mysqlcheck --help
for more information.
joe@terrence-server:~$ mysqlcheck -p -u root --auto-repair --all-databases
Enter password:
joe@terrence-server:~$

Last edited by Joe of Loath; 08-03-2010 at 10:32 AM.
 
Old 08-03-2010, 10:48 AM   #8
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392
Hi,

The first part (output of show databases is wrong!! You are indeed missing the mysql database (and probably test, depends on how mysql is set up).

The output should look something like this:
Quote:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| wordpress |
+--------------------+
4 rows in set (0.00 sec)
Are you sure you have installed, set up and configured mysql correctly?

You could try the following command (as root, stop database first): mysql_install_db --user=mysql

Before you execute that command:
Check to see which user mysql runs under. I use a user and group called mysql (which reflects in the --user=mysql part).

After that command have a look at where your databases reside. In my case that would beL ls -l /srv/mysql
If the group entries are owned by root, you want to change those to the mysql group as well (chgrp mysql /srv/mysql{,/test,/mysql}).

If all this is "too much" I would suggest deinstalling mysql with your package manager and reinstalling it again.

Hope this helps.
 
Old 08-03-2010, 03:31 PM   #9
Joe of Loath
Member
 
Registered: Dec 2009
Location: Bristol, UK
Distribution: Ubuntu, Debian, Arch.
Posts: 152

Original Poster
Rep: Reputation: 28
I probably deleted it (newbie error, hehe), as I made quite a few databases, whilst getting things wrong, and decided to clean up a little.

Thanks for the help all, looks like I'll go put my dunce cap on and sit in the corner!
 
Old 08-03-2010, 04:16 PM   #10
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392
Quote:
Originally Posted by Joe of Loath View Post
I probably deleted it (newbie error, hehe), as I made quite a few databases, whilst getting things wrong, and decided to clean up a little.
Yep, mistakes are made at one point or another. Happens to all of us

If you decide to "clean up" I would de-install mysql, completely removing it and install it from scratch again. This way no possible leftovers are still around.

Quote:
Thanks for the help all
You're welcome.

Quote:
looks like I'll go put my dunce cap on and sit in the corner!
No need for that, it is all part of the learning experience.
 
Old 08-04-2010, 06:18 PM   #11
Joe of Loath
Member
 
Registered: Dec 2009
Location: Bristol, UK
Distribution: Ubuntu, Debian, Arch.
Posts: 152

Original Poster
Rep: Reputation: 28
I screwed that up mysql now fails to start on boot. (Just sits there for 10 seconds, then 'FAILED!' comes up in red and it goes to the next startup item)

How do I completely remove it? apt-get remove mysql-server only removes the main component of it, and it still fails to start after the reinstall.

Last edited by Joe of Loath; 08-04-2010 at 06:19 PM.
 
Old 08-05-2010, 04:25 AM   #12
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392
Hi,

Maybe this will help: How completer remove mysql-server from debian etch (here at LQ).

And maybe this link will help installing mysql on lenny:

- Debian Lenny - Installing MySQL

Hope this helps.
 
Old 08-05-2010, 07:44 AM   #13
Joe of Loath
Member
 
Registered: Dec 2009
Location: Bristol, UK
Distribution: Ubuntu, Debian, Arch.
Posts: 152

Original Poster
Rep: Reputation: 28
Excellent, thanks
 
Old 08-05-2010, 07:54 AM   #14
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392
You're welcome
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Building a table to display from user input: PHP, MySQL deesto Programming 11 06-11-2008 06:25 PM
Locked myself out of mysql...root user db table destroyed organica Linux - Server 2 12-08-2006 11:35 AM
mysql user table corruption creolophus Linux - Software 1 04-23-2006 09:12 PM
mysql 'user' table read-only fuelinjection Linux - General 2 06-13-2004 10:05 AM
Table 'mysql.host' doesn't exist Zac2003 *BSD 5 09-14-2003 01:37 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 02:56 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration