php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #64870 mysqlnd: can't connect to updated MySQL server with old_password Off
Submitted: 2013-05-17 16:44 UTC Modified: 2013-10-15 11:54 UTC
Votes:6
Avg. Score:4.3 ± 0.9
Reproduced:6 of 6 (100.0%)
Same Version:2 (33.3%)
Same OS:1 (16.7%)
From: marceloinxs at gmail dot com Assigned:
Status: No Feedback Package: MySQLi related
PHP Version: 5.4.15 OS: Windows 7 64bit
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2013-05-17 16:44 UTC] marceloinxs at gmail dot com
Description:
------------
Windows 7 build 7601, Apache 2.2.24 (Win32). Upgraded PHP from 5.2.* to 5.4.15, mysql_* and mysqli_* can't connect to any databases. Then downgraded to 5.3.25, same result. 

The error is always the same: 

PHP Warning:  mysqli::mysqli() [<a href='mysqli.mysqli'>mysqli.mysqli</a>]: Premature end of data (mysqlnd_wireprotocol.c:553) 
PHP Warning:  mysqli::mysqli() [<a href='mysqli.mysqli'>mysqli.mysqli</a>]: OK packet 1 bytes shorter than expected
PHP Warning:  mysqli::mysqli() [<a href='mysqli.mysqli'>mysqli.mysqli</a>]: (HY000/2000): mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file

The weird thing is that the database is actually MySQL 5.5.24, old_password variable is Off and passwords are actually 41 byte encoded. 

The database is remote, but remote connections are allowed. I even tried the same script in Linux based server (PHP 5.4.10) and it worked. 

Both mysql and mysqli extensions are correctly loaded in php.ini. 

The main difference between PHP 5.2 and newer versions is that they now use mysqlnd as driver. Maybe it is buggy in Windows?

You can have an extended look of this here: http://stackoverflow.com/questions/16598572/mysqlnd-cannot-connect-to-mysql-5-5-24-old-password-is-off 

Test script:
---------------
<?php

$mysqli = new mysqli('aaa', 'bbb', 'ccc', 'ddd');
		
if($mysqli->connect_error) {
	die( $mysqli->connect_error );
}
		
echo 'connected';

?>

Expected result:
----------------
'connected'

Actual result:
--------------
Warning: mysqli::mysqli() [mysqli.mysqli]: Premature end of data (mysqlnd_wireprotocol.c:553) in ... on line 3

Warning: mysqli::mysqli() [mysqli.mysqli]: OK packet 9 bytes shorter than expected in ... on line 3

Warning: mysqli::mysqli() [mysqli.mysqli]: (HY000/2000): mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file in ... on line 3

mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2013-05-26 17:29 UTC] hanskrentel at yahoo dot de
> The weird thing is that the database is actually MySQL 5.5.24, old_password 
> variable is Off and passwords are actually 41 byte encoded. 

You write "variable" here. The error message clearly directs you to the 
configuration file (which is *not* a variable).

Please check your configuration file and report back which related settings you've 
got in there.
 [2013-05-27 17:20 UTC] marceloinxs at gmail dot com
old_passwords is a configuration variable/flag in the MySQL configuration file. It is set Off (as SHOW GLOBAL VARIABLES statement shows). I don't have admin privileges for the database and no possibility to change config values. But I can connect using a Linux server, so the problem seems to be enterily related with mysqlnd Windows driver.
 [2013-06-07 09:52 UTC] uw@php.net
-Status: Open +Status: Feedback
 [2013-06-07 09:52 UTC] uw@php.net
Please, provide a Whireshark recording of the failed connection attempt. Thanks.
 [2013-10-05 19:02 UTC] marceloinxs at gmail dot com
Hi, here it is the Wireshark report. Sorry for the (really extended) delay. I now tested with 5.4.20 with same result. 

(Host, User and Passwords are dummy text)


No.     Time           Source                Destination           Protocol Length Info
      4 0.022147000    xxx.xxx.xxx.xx        192.168.0.10          MySQL    110    Server Greeting proto=10 version=5.0.95

Frame 4: 110 bytes on wire (880 bits), 110 bytes captured (880 bits) on interface 0
Ethernet II, Src: CiscoSpv_1a:5d:2d (00:25:2e:1a:5d:2d), Dst: HonHaiPr_25:9f:db (c0:cb:38:25:9f:db)
Internet Protocol Version 4, Src: xxx.xxx.xxx.xx (xxx.xxx.xxx.xx), Dst: 192.168.0.10 (192.168.0.10)
Transmission Control Protocol, Src Port: mysql (3306), Dst Port: 54901 (54901), Seq: 1, Ack: 1, Len: 56
MySQL Protocol
    Packet Length: 52
    Packet Number: 0
    Server Greeting
        Protocol: 10
        Version: 5.0.95
        Thread ID: 15618491
        Salt: Jq}?aL7k
        Server Capabilities: 0xa22c
            .... .... .... ...0 = Long Password: Not set
            .... .... .... ..0. = Found Rows: Not set
            .... .... .... .1.. = Long Column Flags: Set
            .... .... .... 1... = Connect With Database: Set
            .... .... ...0 .... = Don't Allow database.table.column: Not set
            .... .... ..1. .... = Can use compression protocol: Set
            .... .... .0.. .... = ODBC Client: Not set
            .... .... 0... .... = Can Use LOAD DATA LOCAL: Not set
            .... ...0 .... .... = Ignore Spaces before '(': Not set
            .... ..1. .... .... = Speaks 4.1 protocol (new flag): Set
            .... .0.. .... .... = Interactive Client: Not set
            .... 0... .... .... = Switch to SSL after handshake: Not set
            ...0 .... .... .... = Ignore sigpipes: Not set
            ..1. .... .... .... = Knows about transactions: Set
            .0.. .... .... .... = Speaks 4.1 protocol (old flag): Not set
            1... .... .... .... = Can do 4.1 authentication: Set
        Server Language: latin1 COLLATE latin1_swedish_ci (8)
        Server Status: 0x0002
            .... .... .... ...0 = In transaction: Not set
            .... .... .... ..1. = AUTO_COMMIT: Set
            .... .... .... .0.. = More results: Not set
            .... .... .... 0... = Multi query - more resultsets: Not set
            .... .... ...0 .... = Bad index used: Not set
            .... .... ..0. .... = No index used: Not set
            .... .... .0.. .... = Cursor exists: Not set
            .... .... 0... .... = Last row sebd: Not set
            .... ...0 .... .... = database dropped: Not set
            .... ..0. .... .... = No backslash escapes: Not set
        Unused: 
        Salt: :|'FlkXSz,fC


No.     Time           Source                Destination           Protocol Length Info
      5 0.023225000    192.168.0.10          xxx.xxx.xxx.xx        MySQL    166    Login Request user=abcd db=abcd_ef

Frame 5: 166 bytes on wire (1328 bits), 166 bytes captured (1328 bits) on interface 0
Ethernet II, Src: HonHaiPr_25:9f:db (c0:cb:38:25:9f:db), Dst: CiscoSpv_1a:5d:2d (00:25:2e:1a:5d:2d)
Internet Protocol Version 4, Src: 192.168.0.10 (192.168.0.10), Dst: xxx.xxx.xxx.xx (xxx.xxx.xxx.xx)
Transmission Control Protocol, Src Port: 54901 (54901), Dst Port: mysql (3306), Seq: 1, Ack: 57, Len: 112
MySQL Protocol
    Packet Length: 108
    Packet Number: 1
    Login Request
        Client Capabilities: 0xa28d
            .... .... .... ...1 = Long Password: Set
            .... .... .... ..0. = Found Rows: Not set
            .... .... .... .1.. = Long Column Flags: Set
            .... .... .... 1... = Connect With Database: Set
            .... .... ...0 .... = Don't Allow database.table.column: Not set
            .... .... ..0. .... = Can use compression protocol: Not set
            .... .... .0.. .... = ODBC Client: Not set
            .... .... 1... .... = Can Use LOAD DATA LOCAL: Set
            .... ...0 .... .... = Ignore Spaces before '(': Not set
            .... ..1. .... .... = Speaks 4.1 protocol (new flag): Set
            .... .0.. .... .... = Interactive Client: Not set
            .... 0... .... .... = Switch to SSL after handshake: Not set
            ...0 .... .... .... = Ignore sigpipes: Not set
            ..1. .... .... .... = Knows about transactions: Set
            .0.. .... .... .... = Speaks 4.1 protocol (old flag): Not set
            1... .... .... .... = Can do 4.1 authentication: Set
        Extended Client Capabilities: 0x000e
            .... .... .... ...0 = Supports multiple statements: Not set
            .... .... .... ..1. = Supports multiple results: Set
        MAX Packet: 3221225472
        Charset: latin1 COLLATE latin1_swedish_ci (8)
        Username: abcd
        Password: *******
        Schema: abcd_ef
    Payload: *********************
        [Expert Info (Warn/Undecoded): FIXME - dissector is incomplete]
            [Message: FIXME - dissector is incomplete]
            [Severity level: Warn]
            [Group: Undecoded]

No.     Time           Source                Destination           Protocol Length Info
      7 0.036206000    xxx.xxx.xxx.xx        192.168.0.10          MySQL    60     Response


Frame 7: 60 bytes on wire (480 bits), 60 bytes captured (480 bits) on interface 0
Ethernet II, Src: CiscoSpv_1a:5d:2d (00:25:2e:1a:5d:2d), Dst: HonHaiPr_25:9f:db (c0:cb:38:25:9f:db)
Internet Protocol Version 4, Src: xxx.xxx.xxx.xx (xxx.xxx.xxx.xx), Dst: 192.168.0.10 (192.168.0.10)
Transmission Control Protocol, Src Port: mysql (3306), Dst Port: 54901 (54901), Seq: 57, Ack: 113, Len: 5
MySQL Protocol
    Packet Length: 1
    Packet Number: 2
    EOF marker: 254
        [Expert Info (Warn/Protocol): EOF Marker found while connection in wrong state.]
            [Message: EOF Marker found while connection in wrong state.]
            [Severity level: Warn]
            [Group: Protocol]
 [2013-10-15 11:54 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 13:01:28 2024 UTC