1

So if I type mysql -u root I'm supposedly logged in, however upon trying to create or access a database I get this lovely error: ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test1'. I haven't the foggiest idea why after logging in as root it's trying access DB's as ''@'localhost' and it's driving me a bit crazy right now. Possibly related, when I try to set the root password I get the error mysqladmin: Can't turn off logging; error: 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation'. I've tried removing mysql-server via running apt-get purge mysql-server and then reinstalling with no luck. This is running Ubuntu Server 12.10 64-bit and mysql is indeed running.

--Edit--

I wonder if perhaps there is no root user. So I try to start MySQL with --skip-grant-tables and the create the root user but then I'm given this: ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement. Fun fun fun fun fun.

--Edit Again--

I've taken a blunt hammer to all things mysql on the maching and ran as root apt-get remove .*mysql.* and apt-get purge .*msyql.* and then reinstalled everything. Installation went as regularly expected this time. If no one can come up with an answer to this (and it probably doesn't help that the problem doesn't exist to test anymore) I'll answer the question myself... it seems with something like this, absolutely removing the bad install was the best option.

--Edit once more--

I've discovered what caused the problem. I ran rake db:create and rake db:migrate and my freshly installed and properly functioning MySQL is now back to having the same issues. I am wondering if perhaps the rake task has taken over MySQL like that one fungus takes over an ant... my MySQL is a rake infested zombie ant(?!?!?!?!).

Zachary J Rollyson
  • 153
  • 9

3 Answers3

1

If you are using Ubuntu your mysql root password should be a password of your system root user, unless you changed it in process of mysql installation.

In any other case, stop mysql.

Execute this:

sudo mysqld_safe --skip-grant-tables &

It will enable you to login to mysql root without password.

Now execute this:

UPDATE mysql.user SET Password=PASSWORD(‘new_password’) WHERE User=’root’;
FLUSH PRIVILEGES;

Dont forget ; at the end and dont forget FLUSH PRIVILEGES; Now start mysql again:

sudo /etc/init.d/mysql start

This should do it.

If root user is not available by some strange twist of the fate this line:

   sudo mysqld_safe --skip-grant-tables &

is still going to give you an option to create new root user, but in this case use google to find how.

Gajotres
  • 57,309
  • 16
  • 102
  • 130
  • None of that will work as the user ''@'localhost' can neither update tables nor create users. Null for user has no privledges. This MySQL installation is really weird... never had it go like this before. It usually asks for a root password when you install it via apt-get but there is nothing of the sort in this case. – Zachary J Rollyson Nov 25 '12 at 01:58
0

Log into mysql with mysql -u root and enter show grants for 'root'@'localhost';. This should tell you, whether root is allowed anything.

BTW, are you logging in without providing a password?

Olaf Dietsche
  • 72,253
  • 8
  • 102
  • 198
  • Attempting to do so returns `ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'` and yes, logging in without a password. I have a feeling there is no root account, or any account for that matter. I can type `mysql -u ChromeSandwich` and get the same results. – Zachary J Rollyson Nov 25 '12 at 02:01
0

I had this problem a few seconds ago and came here for an answer but then I start thinking of what the answer I got from my mysqladmin command. So I tried this this 1: log in as root in terminal using sudo su (root-password), then type with this without the quotation marks but the spaces are a must:

mysqladmin -u @'hostname' (us your hostname then press enter).
Ivan Ferić
  • 4,725
  • 11
  • 37
  • 47
pbgl
  • 1