Page 1 of 2

MySQL help

Posted: Sat Oct 14, 2006 1:39 pm
by Addos
Hi,
I’ve been trying for a few days to get MySQL setup on my 3 week old Win XP Pro pc.

When I try to browse a .php page locally I get the following error.

Warning: mysql_pconnect() [function.mysql-pconnect]: Client does not support authentication protocol requested by server; consider upgrading MySQL client in c:\inetpub\wwwroot\snazzy_dogz\cw2\CWLibrary\CWDBMySQL.php on line 93

Fatal error: Client does not support authentication protocol requested by server; consider upgrading MySQL client in c:\inetpub\wwwroot\snazzy_dogz\cw2\CWLibrary\CWDBMySQL.php on line 93

I have this specific page running live and locally ok on my old pc still so I’m confident the code on the page is ok. I have searched over and over and read up but I’m really not sure what I’m at.

I’m fairly sure it might be to do with this http://www.digitalpeer.com/id/mysql but I don’t know how to get into the command dos area or even if that’s what I’m supposed to do.
I’ve setup mysql-essential-5.0.24a-win32 running IIS and phpMyAdmin 2.6.0-pl3 ( and phpMyAdmin also throws the error #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client) when I browse to this page.

If I run <? echo phpinfo()?> this opens perfectly showing all the PHP stuff contained so I know that this is ok too and I’m sure it’s to do with the latest version of MySQL not running properly. Can anyone advise me where to go from here as I’m really sinking fast. I know there is plenty of info on this but I simply cannot understand the texts instructions so maybe you can help.
Thanks
Brian

Posted: Sat Oct 14, 2006 1:53 pm
by feyd
PHP needs the MySQLi extension for MySQL 5, not the MySQL extension.

Posted: Sat Oct 14, 2006 1:56 pm
by RobertGonzalez
This is a Cient API problem. I would guess that you are running MySQL 4.1 or higher, but you are hitting it with an older version of PHP.

There are a couple of fixes for this, but the most common is to edit your my.conf file, in the [mysqld] section, add the following line:

Code: Select all

[mysqld]
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
What is causing this problem is a change in the way MySQL handles the PASSWORD() function's hashing algorithm. In 4.1+ the PASSWORD() function returns a 46 bit string whereas before it was a 16 string.

Posted: Sat Oct 14, 2006 2:41 pm
by Addos
Thanks for this help.
I've done a search on my pc for 'my.conf' and my.config' file but its not coming up. I've also looked under the MySQL Server 5.0 folder and nothing is there either. Any other ideas was to where I can find this?
Thanks again
Brian

Posted: Sat Oct 14, 2006 3:03 pm
by feyd
my.cnf I believe.

Posted: Sat Oct 14, 2006 3:05 pm
by RobertGonzalez
Sorry, it is my.cnf. And remember to restart the mysql server after you change the cnf file.

Posted: Sat Oct 14, 2006 3:35 pm
by volka
feyd wrote:PHP needs the MySQLi extension for MySQL 5, not the MySQL extension.
actually you can use the mysql extension with mysql 5 on win32. You just have to replace the libmysql.dll that ships with current php.net builds by the more recent one of your mysql 5 installation.

Posted: Sat Oct 14, 2006 3:48 pm
by feyd
volka wrote:actually you can use the mysql extension with mysql 5 on win32. You just have to replace the libmysql.dll that ships with current php.net builds by the more recent one of your mysql 5 installation.
I completely forgot about that. Thanks for the reminder.

Posted: Sun Oct 15, 2006 5:01 am
by Addos
Hi,
Thank you all for your help. Unfortunately I’m still having problems with the same error coming up. BTW I get this error too trying to log in with http://localhost/phpmyadmin/main.php.

So far I’ve done the following:

Code: Select all

[mysql]
default-character-set=latin1
# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#

# Default to using old password format for compatibility with mysql 3.x 
# clients (those using the mysqlclient10 compatibility package). 
[mysqld]
old_passwords=1

Also in case this is important I have made the following changes to config.inc.php

Code: Select all

$cfg['PmaAbsoluteUri'] = 'http://phpmyadmin/';
$cfg['PmaAbsoluteUri_DisableWarning'] = FALSE;
$cfg['PmaNoRelation_DisableWarning']  = FALSE;
$cfg['blowfish_secret'] = '';
$i = 0;
$i++;
$cfg['Servers'][$i]['host']          = 'localhost'; 
$cfg['Servers'][$i]['port']          = '';          
$cfg['Servers'][$i]['socket']        = '';     
$cfg['Servers'][$i]['connect_type']  = 'tcp'
$cfg['Servers'][$i]['extension']     = 'mysql';
$cfg['Servers'][$i]['compress']      = FALSE; 
$cfg['Servers'][$i]['controluser']   = '';                                       
$cfg['Servers'][$i]['controlpass']   = '';
$cfg['Servers'][$i]['auth_type']     = 'config'
$cfg['Servers'][$i]['user']          = 'root';      
$cfg['Servers'][$i]['password']      = 'pleasework';
When I run mysql-essential-5.0.24a-win32 Instance Configuration I wondering during the installation of this am I making some incorrect errors. Mainly when I get to where it asked me for a password should I just un-tick the box and by pass the need to add a password etc? I’m only ever going to be testing locally. It doesn't seem to makd any difference whether I add a password or not.

Thanks you again for all this help
B

Posted: Sun Oct 15, 2006 9:20 am
by RobertGonzalez
Ok, before you go any further, see if you can use the MySQL Administrator interface to connect to your database server. You can download the app from mysql.com. You can also get MySQL Query Browser. Before hitting it with a web app make sure you can hit it with a client app. If you can, move on to testing the web app connections. If you can't, then you may have to undo the change to the my.cnf and update the current password that is in there now. Remember, if it used the new hash, the password that is in there now will be 46 chars long. Change to the old password use, and your password that is in there is still 46 chars long but validation will be looking for a 16 char password.

When you do get into the server through the client, go to the mysql table and do a select on the users to see their password hashes. If they need to be shortened, you can do an update using the OLD_PASWORD() function.

Code: Select all

#This will select the mysql database
USE mysql;

#This grabs all rows from the users table
SELECT * FROM `users`;

#This updates a password to the old style
UPDATE `users` SET `password` = OLD_PASSWORD('theirpassword');

Posted: Sun Oct 15, 2006 10:14 am
by Addos
Thanks loads for your reply,

I’m working away still and at the moment I have downloaded both those administrators you recommend.

When I try to connect to MySQL server instance I get the error number 1045 saying access denied to ‘root’@localhost’ (using password: YES)

I’m not too sure what I should be writing into the fields in MySQL server instance ie ‘server host’ ‘username’ ‘password’. I didn’t insert a password when setting up MySQL.

Thanks again
B

Posted: Sun Oct 15, 2006 10:38 am
by Addos
Just a follow up. I notice when I try to view a page now in the browser I get different errors:

Warning: mysql_pconnect() [function.mysql-pconnect]: Access denied for user 'root'@'localhost' (using password: YES) in c:\inetpub\wwwroot\loughshinney_php\TMP2qcxi76ofv.php on line 3

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in c:\inetpub\wwwroot\loughshinney_php\TMP2qcxi76ofv.php on line 3

Warning: mysql_pconnect() [function.mysql-pconnect]: Access denied for user 'root'@'localhost' (using password: YES) in c:\inetpub\wwwroot\loughshinney_php\TMP2qcxi76ofv.php on line 5

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in c:\inetpub\wwwroot\loughshinney_php\TMP2qcxi76ofv.php on line 5
Access denied for user 'root'@'localhost' (using password: YES)

I have uninstalled and reinstalled MySQL a couple of times over the last day or so and I wonder if there is something in the registry of Windows that has a password stored that hasn’t been cleared during uninstalling. As you can see I’m very unsure about all this but I just thought I’d mention this instinct I have. I also noticed when I get to the second stage of the Instance Configuration that it is showing MySQL Server version 4.1.21 and the latest installed 5.0.24a one . Now I did uninstall version 4 a while ago and there is no sign of it under Add Remove programs and even when I did a search it didn’t come up so again is this a registry issue or maybe both version are ok to have on the system.
Ta
B

Posted: Sun Oct 15, 2006 10:59 am
by feyd
Passwords aren't stored in the registry but the database files themselves.. which are not deleted during uninstallation.

Posted: Sun Oct 15, 2006 11:50 am
by Addos
Ok I’ve got into the Administration section so this must be progress? I was using localhost, root and a password to get in. I’m not sure where to find ‘mysql table’ though to carry out what you are suggesting….is it under catalogues?
B

Posted: Sun Oct 15, 2006 6:38 pm
by Addos
Hi,
Well after a good bit of slogging I’ve finally managed to get the password in MySQL changed and can now make a connection to the server within the Administrator and an application.

I’m now trying to get PhpMyAdmin working and there are a few unusual things happening. When I browse through a few of the links under http://localhost/phpMyAdmin-2.9.0.2-english/ I get some of the pages showing up but what’s noticeable is they are not formatted i.e. it’s like the CSS is missing and when I try to go to the http://localhost/phpMyAdmin-2.9.0.2-english/index.php page I get the following errors (below).

Any ideas as to what this might be.

Sincere thanks for all you help so far it’s been a big learning curve!


Warning: session_write_close() [function.session-write-close]: open(C:\Inetpub\wwwroot\sessiondata\sess_17b793778c1af2ebe991f17be8922f00, O_RDWR) failed: Permission denied (13) in c:\inetpub\wwwroot\phpMyAdmin-2.9.0.2-english\index.php on line 44

Warning: session_write_close() [function.session-write-close]: Failed to write session data (files). Please verify that the current setting of session.save_path is correct (C:\Inetpub\wwwroot\sessiondata) in c:\inetpub\wwwroot\phpMyAdmin-2.9.0.2-english\index.php on line 44