Reinstalling 4.5.0h against existing DB?

[Watson]

Environment:
Gentoo Linux 2006.1 (Kernel 2.6.13)
Apache 2.0.58r2
PHP 5.1.6-pl6
MySQL 5.0.26-r2

I first installed SugarSuite 4.0.1c about 12 months ago and subsequently proceeded through a number of Sugar updates until I was on 4.5.0h. Until a few days ago, I was running on PHP4 and MySQL 4. Over the last few days, I have been updating the server. Before doing anything else, I updated Sugar to 4.5.0h and MySQL to 5.0.26-r2. Everything was working fine under the older PHP and Apache. I then updated Apache to the latest (2.0.58r2) and PHP to 5.1.6-pl6. Now, I cannot get the original instance of Sugar to run. It just gives back a blank page when I hit the startup page. After lots of effort, I gave up and decided to install a fresh copy of 4.5.0h alongside the original.

4.5.0h comes up to the Setup Wizard. I can proceed through license acceptance to the System Acceptance Check. On that page, the only thing in red is the MySQL database. (Note: MySQL is running and accessible from the command line. I can open and query the sugarcrm tables.) If I hit Next, it takes me to the database configuration page.

The settings on the database configuration page are:
Host Name / Host Instance: localhost
Database Name: sugarcrm
Database Username: sugarcrm
Database Password: <unspecified>

None of the MySQL passwords have been changed since the original 4.0.1c installation. I have searched the web and tried "", "changeme" and a couple of others. In every case, Sugar accepts the POST and returns a blank screen.

Any and all help and suggestions greatly appreciated.

Tom
tom@accuwater.com

[kpit]

MySQL 5.0 uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients. If you upgrade the server from 4.0, attempts to connect to it with an older client may fail with the following message:

shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

To solve this problem, you should use one of the following approaches:

Upgrade all client programs to use a 4.1.1 or newer client library.

When connecting to the server with a pre-4.1 client program, use an account that still has a pre-4.1-style password.

Reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement and the OLD_PASSWORD() function:

mysql> SET PASSWORD FOR
-> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

Alternatively, use UPDATE and FLUSH PRIVILEGES:

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

Substitute the password you want to use for “newpwd” in the preceding examples. MySQL cannot tell you what the original password was, so you'll need to pick a new one.

Tell the server to use the older password hashing algorithm:

Start mysqld with the --old-passwords option.

Assign an old-format password to each account that has had its password updated to the longer 4.1 format. You can identify these accounts with the following query:

mysql> SELECT Host, User, Password FROM mysql.user
-> WHERE LENGTH(Password) > 16;

For each account record displayed by the query, use the Host and User values and assign a password using the OLD_PASSWORD() function and either SET PASSWORD or UPDATE, as described earlier.

Note: In older versions of PHP, the mysql extension does not support the authentication protocol in MySQL 4.1.1 and higher. This is true regardless of the PHP version being used. If you wish to use the mysql extension with MySQL 4.1 or newer, you may need to follow one of the options discussed above for configuring MySQL to work with old clients. The mysqli extension (stands for "MySQL, Improved"; added in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done to use this MySQL client library. For more information about the mysqli extension, see http://php.net/mysqli.

It may also be possible to compile the older mysql extension against the new MySQL client library. This is beyond the scope of this Manual; consult the PHP documentation for more information. You also be able to obtain assistance with these issues in our MySQL with PHP forum.

How to Reset the Root Password
If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. However, it is recommended to set a password for each account. See Section 5.7.1, “General Security Guidelines”.

If you set a root password previously, but have forgotten what it was, you can set a new password. The following procedure is for Windows systems. The procedure for Unix systems is given later in this section.

The procedure under Windows:

Log on to your system as Administrator.

Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager:

Start Menu -> Control Panel -> Administrative Tools -> Services

Then find the MySQL service in the list, and stop it.

If your server is not running as a service, you may need to use the Task Manager to force it to stop.

Create a text file and place the following command within it on a single line:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

Save the file with any name. For this example the file will be C:\mysql-init.txt.

Open a console window to get to the DOS command prompt:

Start Menu -> Run -> cmd

We are assuming that you installed MySQL to C:\mysql. If you installed MySQL to another location, adjust the following commands accordingly.

At the DOS command prompt, execute this command:

C:\> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt

The contents of the file named by the --init-file option are executed at server startup, changing the root password. After the server has started successfully, you should delete C:\mysql-init.txt.

If you install MySQL using the MySQL Installation Wizard, you may need to specify a --defaults-file option:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe"
--defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini"
--init-file=C:\mysql-init.txt

The appropriate --defaults-file setting can be found using the Services Manager:

Start Menu -> Control Panel -> Administrative Tools -> Services

Find the MySQL service in the list, right-click on it, and choose the Properties option. The Path to executable field contains the --defaults-file setting.

Stop the MySQL server, then restart it in normal mode again. If you run the server as a service, start it from the Windows Services window. If you start the server manually, use whatever command you normally use.

You should be able to connect using the new password.

In a Unix environment, the procedure for resetting the root password is as follows:

Log on to your system as either the Unix root user or as the same user that the mysqld server runs as.

Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, hostname, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the filename has the extension of .pid and begins with either mysqld or your system's hostname.

You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the pathname of the .pid file in the following command:

shell> kill `cat /mysql-data-directory/host_name.pid`

Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

Create a text file and place the following command within it on a single line:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

Save the file with any name. For this example the file will be ~/mysql-init.

Restart the MySQL server with the special --init-file=~/mysql-init option:

shell> mysqld_safe --init-file=~/mysql-init &

The contents of the init-file are executed at server startup, changing the root password. After the server has started successfully you should delete ~/mysql-init.

You should be able to connect using the new password.

Alternatively, on any platform, you can set the new password using the mysql client(but this approach is less secure):

Stop mysqld and restart it with the --skip-grant-tables --user=root options (Windows users omit the --user=root portion).

Connect to the mysqld server with this command:

shell> mysql -u root

Issue the following statements in the mysql client:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd')
-> WHERE User='root';
mysql> FLUSH PRIVILEGES;

Replace “newpwd” with the actual root password that you want to use.

You should be able to connect using the new password.

[Watson]

Maybe I wasn't clear about the problem. It has nothing to do with individual users or the MySQL root password. I can quite easily open a mysql session and query the database. This is an installation problem related to connecting a new SugarOS 4.5.0h instance to an existing sugarcrm database in MySQL. After the Database Configuration page of the Setup Wizard, I just get a blank page. There are no errors in the mysql or php logs.

[tdp]

I've been through something like this a couple of times, and the only way I've found it reliably works is to make a backup of the database (I use mysqldump with --add-drop-table), drop the database, install SugarCRM (essentially creating a new, blank, database), then re-install the database from the mysqldump file. Note that this ONLY works if the old and new SugarCRM versions and patches match exactly.

Of course you're going to want to have the entire site duplicated (and tested as functioning) so if something goes wrong, you're not completely screwed. If it goes successfully, you might have to clean out the upgrade_history table as it will have entries relevant to the old install.

[Watson]

Todd,

Thanks for the recommendation. That's the path I am heading down, since all else has failed. Fortunately, my database was already updated to 4.5.0h through upgrades before this fiasco began. I'll start with a fresh full-install of 4.5.0h, create a new database and then do the restore.

Will restoring the database compromise the connection credentials established when I create a new database?

I'll post again to advise whether it worked.

Tom

[Watson]

Got things working in a temporary way by installing on Windows (BitRock installer) and importing my database dump. At least I can see my calendar now.

My best guess as to the problem now is that I don't have mysqli installed. Under Gentoo, I installed Apache2, PHP5, MySQL 5. The installer works, but as soon as I start trying to access MySQL (during the install process), everything crashes.

Anybody built the Apache/MySQL/PHP stack on Gentoo and THEN installed SugarCRM? What am I missing?

Tom

[tdp]

Will restoring the database compromise the connection credentials established when I create a new database?

I depends upon what credentials you mean. If you mean the MySQL user for SugarCRM, that info is stored in the config.php file, so it won't be affected by the database restore. However, the SugarCRM users will be affected (admin included), and any new users you would create would be gone. Only the users from the old system will exist.

Anybody built the Apache/MySQL/PHP stack on Gentoo and THEN installed SugarCRM? What am I missing?

I'm running on Slackware (which isn't THAT different from Gentoo) and had no problem. Are you sure that MySQL is listening for connections? If I remember correctly, SugarCRM is hard-coded to use port 3306 for MySQL access, so if MySQL isn't listening (or is listening on a different port) you might get the problem you describe. Also double check that the PHP and MySQL versions you're running are supported by SugarCRM. That has been my stumbling block in the past.

[kenlyle]

I did a similar project, but achieved a cross-version migration as well. Anyone on this trail might find this helpful:

http://sugarcrm.com/forums/showthrea...8272#post68272

Send me a banana if it helps you.

Best,
Ken