Changing root password breaks SugarCRM

[ldinero]

Hello,

We just installed an open source LMS system (Moodle) on our Windows Server 2008 machine that also hosts our Sugar 5.5.0 application. As part of the install process, it upgraded our MySQL database server to MySQL 5.1.59 Community Edition and changed the 'root' password for MySQL. Moodle works fine both as a web app and directly accessing the database via the MySQL GUI.

Unfortunately, now we cannot log into Sugar.

1) We've tried updating the config.php and config_si.php files in the root directory of Sugar to use the new 'root' account password. That gives us this error:

Unable to select database: Unknown database 'sugarcrm'

2) If we try to access Sugar using our original 'root' password we get this error:
Could not connect to server localhost as root.Access denied for user 'root'@'localhost' (using password: YES)

3) We've tried accessing the sugarCRM database directly with MySQL Workbench but it doesn't see the database at all.

Does anyone have any suggestions for how we can fix this? Thanks!

[rafael.q.g@hotmail.com]

Maybe the databse really wasn't created or was deleted (somehow). A new installation (setup) could be necessary.

[morrighu]

First off, it's very naughty to run applications from the database root user. You should always create a user and grant it rights to the database(s) you want it to have access to. To do that...
Note that you can subsitute your hostname or IP address for 'localhost' if your configuration requires it.

mysql -u root -p

Get your mysql> prompt

CREATE 'myusername'@'localhost' IDENTIFIED BY 'mypassword';
CREATE DATABASE 'mysugarcrmdatabase';
GRANT ALL on 'mysugarcrmdatabase'.*  TO  'myusername'@'localhost';
FLUSH PRIVILEGES;

Now to fix your current situation....

Try logging in from the command line.

mysql -u root -p

See if either of the root passwords works for you. Failing that there are ways to recover a MYSQL password.

Stop mysql engine

/etc/init.d/mysql stop

Become root, Administrator or what ever.

Restart mysql using a special command from the command line

mysqld_safe --skip-grant-tables &

Log in to the database from the command line

mysql -u root

You should get a mysql> prompt

SHOW DATABASES;

That will list all the databases that are available. If you see sugarcrm listed, you might be ok. If not, I hope you have backups.

While we're here, lets reset the root password.

USE mysql;
UPDATE user SET password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
FLUSH PRIVILEGES;
quit;

Stop mysql

/etc/init.d/mysql stop

Start mysql

/etc/init.d/mysql start

You should be able to log in with your new root password. Now log in (you have to test it anyway) and make yourself a database user.

mysql -u root -p

Get your mysql> prompt

CREATE 'myusername'@'localhost' IDENTIFIED BY 'mypassword';
CREATE DATABASE 'mysugarcrmdatabase';
GRANT ALL on 'mysugarcrmdatabase'.*  TO  'myusername'@'localhost';
FLUSH PRIVILEGES;

Give that user name, hostname, and password to the config file. All should be well at that point.

[ldinero]

Many thanks for your help, Morrighu! I very much appreciate you taking the time to spell out the steps we needed to take.

Now to fix your current situation....