Cannot connect to MySQL 4 1 using old authentication

Question

I m trying to connect to a mySQL database at http   bluesql net  but when I try to connect  it gives this error   Connect Error  2000  mysqlnd cannot connect to MySQL 4 1  using old authentication   I ve looked into this  and it has to do with some old password scheme used before MySQL 4 1  Newer versions have the option to use old passwords  which I ve read may cause this problem    I m running php 5 3  and connecting with mySQLi  new mysqli        I m hoping I can do something in the code to connect to the DB at bluesql net - clearly I don t control how their database is set up  Downgrading php versions isn t an option   Anyone have any ideas

User · Answer

you can do these line on your mysql query browser or something

SET old_passwords = 0;
UPDATE mysql.user SET Password = PASSWORD('testpass') WHERE User = 'testuser' limit 1;
SELECT LENGTH(Password) FROM mysql.user WHERE User = 'testuser';
FLUSH PRIVILEGES;

note:your username and password

after that it should able to work. I just solved mine too

User · Answer

If you do not have Administrator access to the MySQL Server configuration  i e  you are using a hosting service   then there are 2 options to get this to work   1  Request that the old passwords option be set to false on the MySQL server  2  Downgrade PHP to 5 2 2 until option 1 occurs   From what I ve been able to find  the issue seems to be with how the MySQL account passwords are stored and if the  old passwords  setting is set to true  This causes a compatibility issue between MySQL and newer versions of PHP  5 3   where PHP attempts to connect using a 41-character hash but the MySQL server is still storing account passwords using a 16-character hash    This incompatibility was brought about by the changing of the hashing method used in MySQL 4 1 which allows for both short and long hash lengths  Scenario 2 on this page from the MySQL site  http   dev mysql com doc refman 5 5 en password-hashing html  and the inclusion of the MySQL Native Driver in PHP 5 3  backwards compatibility issue documented on bullet 7 of this page from the PHP documentation  http   www php net manual en migration53 incompatible php

User · Answer

Had the same issue  but executing the queries alone will not help  To fix this I did the following    Set old passwords 0 in my cnf file Restart mysql Login to mysql as root user Execute FLUSH PRIVILEGES

User · Answer

IF    You are using a shared hosting  and don t have root access  you are getting the said error while connecting to a remote database ie  not localhost   and your using Xampp   and the code is running fine on live server  but the issue is only on your development machine running xampp    Then    It is highly recommended that you install xampp 1 7 0   Download Link  Note  This is not a solution to the above problem  but a FIX which would allow you to continue with your development

User · Answer

edit  This only applies if you are in control of the MySQL server    if you re not take a look at Mysql password hashing method old vs new  First check with the SQL query  SHOW VARIABLES LIKE  old passwords     in the MySQL command line client  HeidiSQL or whatever front end you like  whether the server is set to use the old password schema by default  If this returns old passwords Off you just happen to have old password entries in the user table  The MySQL server will use the old authentication routine for these accounts  You can simply set a new password for the account and the new routine will be used     You can check which routine will be used by taking a look at the mysql user table  with an account that has access to that table   SELECT  User    Host   Length  Password   FROM mysql user   This will return 16 for accounts with old passwords and 41 for accounts with new passwords  and 0 for accounts with no password at all  you might want to take care of those as well   Either use the user management tools of the MySQL front end  if there are any  or  SET PASSWORD FOR  User   Host  PASSWORD  yourpassword    FLUSH Privileges     replace User and Host with the values you got from the previous query   Then check the length of the password again  It should be 41 now and your client  e g  mysqlnd  should be able to connect to the server   see also the MySQL documentation     http   dev mysql com doc refman 5 0 en old-client html   http   dev mysql com doc refman 5 0 en password-hashing html   http   dev mysql com doc refman 5 0 en set-password html

User · Answer

On OSX  I used MacPorts to address the same problem when connecting to my siteground database   Siteground appears to be using 5 0 77mm0 1-log  but creating a new user account didn t fix the problem   This is what did  sudo port install php5-mysql -mysqlnd  mysql5   This downgrades the mysql driver that php will use

User · Answer

If you do not have control of the server  I just had this issue  and was able to work around it   First  connect to the MySQL database with an older client that doesn t mind old passwords   Connect using the user that your script will be using   Run these queries   SET SESSION old passwords FALSE  SET PASSWORD   PASSWORD   your password       In your PHP script  change your mysql connect function to include the client flag 1   define  CLIENT LONG PASSWORD   1   mysql connect   your server      your username      your password    false  CLIENT LONG PASSWORD     This allowed me to connect successfully   Edit  as per Garland Pope s comment  it may not be necessary to set CLIENT LONG PASSWORD manually any more in your PHP code as of PHP 5 4   Edit  courtesy of Antonio Bonifati  a PHP script to run the queries for you    lt  php const DB      host    gt           localhost may not work on some hosting       user    gt              pwd    gt              if   mysql connect DB  host    DB  user    DB  pwd           die mysql error       if   mysql query  query    SET SESSION old passwords FALSE          die  query     if   mysql query  query    SET PASSWORD   PASSWORD      DB  pwd                  die  query      echo  Excellent  mysqli will now work      gt

[php] Cannot connect to MySQL 4.1+ using old authentication

Examples related to php

Examples related to mysql

Examples related to authentication

Examples related to connection