MySQL help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

MySQL help

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

PHP needs the MySQLi extension for MySQL 5, not the MySQL extension.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

my.cnf I believe.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Sorry, it is my.cnf. And remember to restart the mysql server after you change the cnf file.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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');
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Post 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
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Passwords aren't stored in the registry but the database files themselves.. which are not deleted during uninstallation.
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Post 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
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Post 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
Post Reply