Apache Friends Support Forum

I have a xamp installation 1-6-0a that has been running for about three years with no problems, I have about 30 mysql databases running all fine.
suddenly last week I have a Mysql problem, I can log in, I can create a databse, I can create a user but I can't apply permissions for that user to use the new database. I get the following error

#1044 - Access denied for user 'root'@'localhost' to database 'test'

All the existing databases / websites ar working fine, I am just unable to set any new ones up

I am unabe to change any of the settings on the existing users, I get the same error. I have tried to use the cmd prompt and I get the same errors so I know its not phpmyadmin

Can anyone help..... pleaseeeeeeeeeeeeeeeeee


xamp 1-6-0a
php 5.2.1
mysql 5.0.33
apache 2.2.4
phpmyadmin 3.3.7 (which is a update from what came with xamp)

OK

2 questions -

First - how was this database 'test' created? (be very specific please)

Second - by whom and how/with what are the users being created?

Third - did you change the root password (perhaps about when the problems began)?

Fourth - Is this only a single database you are having problems with???

This is instructive reading:
http://wellrounded.wordpress.com/2007/0 ... ific-user/

have you tried using the MySQL monitor (command line interface) to GRANT PRIVILEGES???

Lastly - you HAVE been backing up your databases? RIGHT???? (this may solve issue indirectly)

please also look in your mysql and mysql\bin folders and see if there is a batch file called 'resetroot.bat" = if its there, DON'T use it yet.

Good luck

The database was created using phpmyadmin, by typing test in the create database inout box and clicking create database
(this has worked fine for three years)

Everything is done as root

The root password has been the same for three years, I have ever tried to change it

i am unable to do anything I should be able to to as root when it comes to granting privileges, in phpmyadmin or at the command prompt
with any existing database/user new or old, though apart from this all the existing databases/users are working finr

"This is instructive reading:
http://wellrounded.wordpress.com/2007/0 ... ific-user/"

I will have a look when I get home form work tonight

I back up the database about once every 6 months. I do this by grabbing the data folder and copying it to an external hard drive

Rob

Robbie -

That file copy backup method is inadvisable for a database. As it happens, I'm in the midst of putting together an article on that (was delayed by a work crisis) I'll prollly have it this weekend. ONLY the designed backup tools for databases should be used. In the case of MySQL, the 'backup' is actually the reconstructive SQL statements needed to re-instantiate the complete database and schema IN PLAIN TEXT! (unless you encrypt it).

See mysqldump.exe OR you can do it (normally) with phpMyAdmin. The advantage of mysqldump is that the backup can easily be automated.

OK, OK I fixed up an 'almost complete demonstration version' It backs up the CD Collection database that comes with XAMPP to c:\mysql_backups (the draft was on my thumb drive) and Date &Timestamps each backup filename. You can place this file anywhere as it uses absolute paths.

mysql_backup.bat

Code: Select all

@rem - file locations for mySQL executables (WAMP Installations) C:\wamp\bin\mysql\mysql5.1.36\bin
@rem - file locations for mySQL executables (XAMPP Installations) C:\xampp\mysql\bin
cls
@rem - Note bene - Time Format is localized in Windows! you may need to twiddle the SET statement!
@echo off
@rem - point to temp folder for intermediate files workspace.
PATH=%PATH%;C:\temp\mysql\bin;
@rem - before you run this file - create a folder outside your server root for the backup - even better on a different or

remote drive (share)
@rem - now, change to the backup folder
cd c:\mysql_backups\
echo %date%
echo %time%
@rem - uncomment this next pause to debug the date & time strings -
@rem pause
@rem - set datetimestamp=%date:~-4%_%date:~7,2%_%date:~0,2%_%time:~0,2%_%time:~3,2%_%time:~6,2%
@rem - these settings are for US date order YYYY_MM_DD
@rem - you are setting (assigning) a variable here to label your backup
set datetimestamp=%date:~-4%_%date:~4,2%_%date:~7,2%_%time:~0,2%_%time:~3,2%_%time:~6,2%

echo Please wait, your MySQL backup is in progress...
C:\xampp\mysql\bin\mysqldump --host="localhost" --user="root" --password="" --databases "cdcol" >

"cdcol_backup_%datetimestamp%_.sql"

if ERRORLEVEL 1 GOTO FAILURE

:success
echo Backup is success
pause
GOTO end

:failure
echo There was an error in the backup

:wait
pause
:end

I tested on a clean new XAMPP install


Good Luck

Cheers for the info, I will have a look at this when I back up next. any idea why I can't do anything as root though as that is the problem I am having

Rob

Rob - after I'm done with the current set of work problems, I'm pretty sure I know either what is wrong and/or how to fix.

Cheers for replying to my request for help but I couldn't wait and started to play ( after backing everything up)
I have fixed it, what I found was when you click privileges I noticed that root had Grant set to No just like all the other accounts
I went into the mysql database using phpmyadmin and changed it to yes, rebooted the server and now it all works fine

Rob

Good

That exactly what I was going to tell you - how to use phpMyAdmin to fix it.

NOW be sure to keep backing up, LOL.