Access denied to for user root

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Access denied to for user root

Postby dracan » 12. March 2010 13:54

I use xampp for local testing and have left the root mysql password as empty. This has suddenly started denying me permission to access it saying "Access denied for user 'root'@'localhost' (using password: NO)". I've tried resetting the password using resetroot.bat, but it fails with the following:

C:\xampp\mysql>resetroot
100312 12:44:43 [Warning] Can't create test file \usr\local\mysql\data\dan-pc.lower-test
100312 12:44:43 [Warning] Can't create test file \usr\local\mysql\data\dan-pc.lower-test
mysql\bin\mysqld.exe: Can't change dir to '\usr\local\\mysql\data\' (Errcode: 2)
100312 12:44:43 [ERROR] Aborting

100312 12:44:43 [Note] mysql\bin\mysqld.exe: Shutdown complete


Passwoerter fuer Benutzer "root" und "pma" wurden nicht geloescht!
Passwords for user "root" and "pma" were not deleted!

Press any key to continue . . .


Note that I'm using WindowsXP, so I'm not surprised it can't find "\usr\local\mysql"! I don't know where it's getting that path from.

I've had this problem a few times before, and ended up reinstalling xampp to fix it. It works initially - and I can't find any pattern to what makes it suddenly stop working.

Thanks for any help with this,
Dan.
dracan
 
Posts: 8
Joined: 12. March 2010 12:55

Re: Access denied to for user root

Postby dracan » 18. March 2010 19:14

This has done it again. It's so frustrating. It just suddenly bans me connecting with the error "access denied for user 'root'@'localhost'. This time I had assigned a password, and that doesn't make a difference.
dracan
 
Posts: 8
Joined: 12. March 2010 12:55

Re: Access denied to for user root

Postby MC10 » 19. March 2010 05:51

You have to edit the config.inc.php file in \xampp\phpMyAdmin.

Change lines 18-22:
Code: Select all
/* Authentication type and info */
$cfg['Servers'][$i]['auth_type']            = 'config';
$cfg['Servers'][$i]['user']                 = 'root';
$cfg['Servers'][$i]['password']             = '';
$cfg['Servers'][$i]['AllowNoPassword']      = true;

to:
Code: Select all
/* Authentication type and info */
$cfg['Servers'][$i]['auth_type']            = 'config';
$cfg['Servers'][$i]['user']                 = 'root';
$cfg['Servers'][$i]['password']             = 'password';
$cfg['Servers'][$i]['AllowNoPassword']      = true;

replacing "password" with your actual password. Save the file and try to access phpMyAdmin again. It should work.
MC10
 
Posts: 148
Joined: 20. February 2010 20:13

Re: Access denied to for user root

Postby dracan » 19. March 2010 10:04

I don't use phpmyadmin though. I use either Toad or my own Perl or PHP scripts. None of which would be changing the password. I can't find a pattern to when I stop having permission either. It works for a while, then suddenly stop giving me access. Then I have to reinstall to fix it. It's a shame this resetroot.bat doesn't work - that would make it less of an issue.
dracan
 
Posts: 8
Joined: 12. March 2010 12:55

Re: Access denied to for user root

Postby dracan » 19. March 2010 11:23

I've just reinstalled, then taken a backup of my entire xampp directory. Next time it happens, I'll do a few diffs to find out what has changed.
dracan
 
Posts: 8
Joined: 12. March 2010 12:55

Re: Access denied to for user root

Postby dracan » 23. March 2010 18:38

Actually, it might just be after a reboot that this stops working. Not sure what's changed though.
dracan
 
Posts: 8
Joined: 12. March 2010 12:55

Re: Access denied to for user root

Postby PROMuffy » 24. April 2010 12:43

Hi,

I'm currently having a similar issue. I can't run the resetroot.bat to reset my root password. The output I get when I do is:

100424 12:15:50 [Warning] Can't create test file \usr\local\mysql\data\Yoda.lowe
r-test
100424 12:15:50 [Warning] Can't create test file \usr\local\mysql\data\Yoda.lowe
r-test
mysql\bin\mysqld.exe: Can't change dir to '\usr\local\\mysql\data\' (Errcode: 2)

100424 12:15:50 [ERROR] Aborting

100424 12:15:50 [Note] mysql\bin\mysqld.exe: Shutdown complete


Passwoerter fuer Benutzer "root" und "pma" wurden nicht geloescht!
Passwords for user "root" and "pma" were not deleted!

Press any key to continue . . .


Again, I don't know why it's looking for this directory. I've installed to "C:\ApacheFriends\xampp\" as I'm on Vista 32 bit (and installing to Program Files is a bad thing so I've read). I used the latest April 2010 XAMPP windows .exe installer. I fixed up the directories as prompted during the configuration command prompt.

So having set up and being fairly new to this, somehow after following the suggestion to change my root user password I either set it to a hash value, or removed the root priveleges. (The page I did this on didn't explain very well what it was for, however it did allow my to change the password. It certainly wasn't the http://localhost/security/ page). myphpadmin at this point would no longer let me log in because it required a password that I didn't know. (I know to change the password in the phpmyadmin/config.inc.php - but ofc don't know what it is now!)

I uninstalled xampp, the apache and mysql services and reinstalled in the same place. It still required a password I didn't know.

I manually deleted the entire "C:\apachefriends\xampp directory" and reinstalled. It still required a password I didn't know.

I found a number of articles explaining how to manually reset the root user password using a text document and running the mysql server to execute it. It failed, with the same text as quoted above.

I found the resetroot.bat and ran it. It failed, as the quote above showed.

I honestly can't believe how hard it is to: (A) remove the database and its settings and (B) reset the root password. I've failed on both counts. So now I'm posting here, to find out how to make this resetroot.bat file work correctly. I'm starting to despair. I've spent the last 2 days trying to figure it out. This thread is still dangling on the same issue, so I thought I'd bring it back.

Thanks in advance, Muffy.
PROMuffy
 
Posts: 2
Joined: 24. April 2010 12:21

Re: Access denied to for user root

Postby PROMuffy » 24. April 2010 18:08

So, the solution to my problem was finally this:

(1) Back up any important files in your xampp folder manually and keep them safe somewhere, e.g. "httpd.conf"
(2) Run "uninstall_xampp.bat" (depending on where you install it, usually "C:\xampp\uninstall_xampp.bat"). This will stop and uninstall any xampp Windows services, completely uninstall xampp and all components.
(3) When the command prompt asks you if you want to delete/backup any data folders such as htdocs, choose to delete them.
(4) When the previous step has completed, all xampp references will have been removed. Reboot your machine.
(5) After rebooting, run the xampp installer again and follow the prompts to install, as you did the first time.
(6) Copy the files you backed up over the newly installed files.
(7) Run the xampp Control Panel.
(8) Run Apache.
(9) Run MySql.
(10) Click on the "Admin" button on the Control Panel for MySql.
(11) Note that you can access the admin section again :)

(12) Optional but recommended. Go to http://localhost/security/ and set a new "ROOT" user password. Then set the root password in "config.inc.php" (search for it inside the xampp directory) to the password you just created. Without doing that last bit you won't be able to access the admin section again.

I can't however explain why the resetroot.bat file doesn't work correctly. Someone else will have to explain that. :(
PROMuffy
 
Posts: 2
Joined: 24. April 2010 12:21

Re: Access denied to for user root

Postby MC10 » 26. April 2010 01:40

The resetroot.bat file only works for Linux, apparently. Those path files are Linux path files. XAMPP apparently did not have a resetroot.bat file for Windows.
MC10
 
Posts: 148
Joined: 20. February 2010 20:13

Re: Access denied to for user root

Postby lotdox » 23. May 2010 17:14

Hi PROMuffy,
I'm sorry but I cannot make it work.
I've tried to make some changes in the files you mentioned, but does not work.
the explorer shows I message like "cannot go on because there are still error: check your error log file"

Can you help me please?

Thank yo very muyh
lotdox
 
Posts: 2
Joined: 23. May 2010 17:06

Re: Access denied to for user root

Postby MC10 » 24. May 2010 00:28

Which error log file is that referring to? The MySQL one is at \xampp\mysql\data\mysql.err, the Apache one is at \xampp\apache\logs\error.log. Full log list is here.
MC10
 
Posts: 148
Joined: 20. February 2010 20:13

Re: Access denied to for user root

Postby lotdox » 25. May 2010 13:23

I'm sorry PROMuffy,
I've already uninstalled, deleted and re-installed all.
I would only know the files and the parameters you have changed in order to make it work, because I've done several tries and none of them has worked.
Now I'd like to be more sharp
because you mentioned http://localhost/security/ but not the file and the parameters to change
And more, there are two palces in config.inc.php where change the password, the first is:

/* Authentication type and info */
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'MY_PASSWORD';
$cfg['Servers'][$i]['AllowNoPassword'] = true;


and the second is

$cfg['Servers'][$i]['controlpass'] = ‘123456′;

like mentioned here
http://rudyegenias.wordpress.com/2006/0 ... -database/

As you may see I've been surfing thru the web a lot of time like you :)
Would you help me please?

Than kyou again
lotdox
 
Posts: 2
Joined: 23. May 2010 17:06

Re: Access denied to for user root

Postby myffical » 31. May 2010 04:12

MC10, PROMuffy,

I got myself into the same fix. :? After some tinkering, I managed to persuade resetroot.bat to work on Windows XP:

  1. Go to xampp/mysql/bin and copy my.ini to mywin.ini
  2. Open mywin.ini and delete the line:
    Code: Select all
    default-time-zone       = "UTC"
  3. Go to xampp/mysql and copy resetroot.bat to resetrootwin.bat
  4. Open resetrootwin.bat and modify this line:
    Code: Select all
    Old:
    mysql\bin\mysqld.exe --no-defaults --bind-address=127.0.0.1 --bootstrap --console --skip-grant-tables --skip-innodb --standalone <resetroot.sql  >resetroot.err 2>&1

    New:
    mysql\bin\mysqld.exe --defaults-file=mysql\bin\mywin.ini --bind-address=127.0.0.1 --bootstrap --console --skip-grant-tables --skip-innodb --standalone <resetroot.sql  >resetroot.err 2>&1
  5. Open command prompt, go to xampp/mysql and run resetrootwin.bat
  6. It works! (Hopefully!) Clean up by deleting mywin.ini, resetrootwin.bat, and setting your root password within phpMyAdmin.

Not very clear on why it works. Somehow, when started on the Windows command prompt, mysqld fails to run due to its nonsensical defaults. I think the XAMPP control panel wraps additional options around mysqld to get it to run sensibly.
myffical
 
Posts: 2
Joined: 31. May 2010 03:57

Re: Access denied to for user root

Postby myffical » 31. May 2010 04:26

Lotdox,

If you're working for a fresh install with no password on root, set up the password by going to http://localhost/phpmyadmin/ on your web browser and clicking "Change Password".

After you set a password on root, open the xampp/phpMyAdmin/config.inc.php and change the following line. Do not change the value of $cfg['Servers'][$i]['controlpass']!
Code: Select all
Old:
$cfg['Servers'][$i]['auth_type']            = 'config';

New:
$cfg['Servers'][$i]['auth_type']            = 'cookie';

This will make phpMyAdmin ask you for the password every time you log in, so you don't have to save the password in the config file. Now you should be able to go to http://localhost/phpmyadmin with your web browser and log in.

You can also save your password into your config file (set $cfg['Servers'][$i]['auth_type'] to 'config' and $cfg['Servers'][$i]['password'] to your password), but it's really not a good idea.
myffical
 
Posts: 2
Joined: 31. May 2010 03:57

Re: Access denied to for user root

Postby reitchelazan » 15. June 2010 18:43

mysqladmin can change password on root. It is too easy. Read manual how to change.

reitchelazan
 
Posts: 1
Joined: 15. June 2010 18:35

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 149 guests