Problem with Mysql

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

Problem with Mysql

Postby robbie3003 » 01. November 2010 23:08

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


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)
robbie3003
 
Posts: 4
Joined: 01. November 2010 22:47

Re: Problem with Mysql

Postby JonB » 02. November 2010 03:20

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
:)
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: Problem with Mysql

Postby robbie3003 » 02. November 2010 09:48

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
robbie3003
 
Posts: 4
Joined: 01. November 2010 22:47

Re: Problem with Mysql

Postby JonB » 02. November 2010 18:20

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 8)


Good Luck
:)
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: Problem with Mysql

Postby robbie3003 » 02. November 2010 19:22

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
robbie3003
 
Posts: 4
Joined: 01. November 2010 22:47

Re: Problem with Mysql

Postby JonB » 02. November 2010 23:55

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.

:)
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: Problem with Mysql

Postby robbie3003 » 03. November 2010 12:05

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
robbie3003
 
Posts: 4
Joined: 01. November 2010 22:47

Re: Problem with Mysql

Postby JonB » 03. November 2010 13:13

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.

:lol:
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 122 guests