angelcool Posted December 29, 2010 Share Posted December 29, 2010 I built PHP 5.3.4 with --with-mysqli=mysqlnd , now I have issues connecting. I ran a php file with: mysqli_connect('localhost','user','letmein'); ...and I get the following error: Warning: mysqli_connect(): Premature end of data (mysqlnd_wireprotocol.c:554) in /webserver/html/mysqli.php on line 2 Warning: mysqli_connect(): OK packet 1 bytes shorter than expected in /webserver/html/mysqli.php on line 2 Warning: mysqli_connect(): (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 /webserver/html/mysqli.php on line 2 1.- I updated the password in mysql [ SET PASSWORD FOR user@localhost=PASSWORD('letmein'); ] ; but the password hash did not change. I still got the error. 2.- I edited my.cnf file, I un-commented #old_passwords=1 , toggled it 0 -1. I still got the error. This is the mysqli section in phpinfo(): mysqli MysqlI Support enabled Client API library version mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $ Active Persistent Links 0 Inactive Persistent Links 0 Active Links 0 Directive Local Value Master Value mysqli.allow_local_infile On On mysqli.allow_persistent On On mysqli.default_host no value no value mysqli.default_port 3306 3306 mysqli.default_pw no value no value mysqli.default_socket /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock mysqli.default_user no value no value mysqli.max_links Unlimited Unlimited mysqli.max_persistent Unlimited Unlimited mysqli.reconnect Off Off mysqlnd mysqlnd enabled Version mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $ Compression not supported SSL supported Command buffer size 4096 Read buffer size 32768 Read timeout 31536000 Collecting statistics Yes Collecting memory statistics No Tracing n/a Client statistics bytes_sent 0 bytes_received 0 packets_sent 0 packets_received 0 protocol_overhead_in 0 protocol_overhead_out 0 bytes_received_ok_packet 0 bytes_received_eof_packet 0 bytes_received_rset_header_packet 0 bytes_received_rset_field_meta_packet 0 bytes_received_rset_row_packet 0 bytes_received_prepare_response_packet 0 bytes_received_change_user_packet 0 packets_sent_command 0 packets_received_ok 0 packets_received_eof 0 packets_received_rset_header 0 packets_received_rset_field_meta 0 packets_received_rset_row 0 packets_received_prepare_response 0 packets_received_change_user 0 result_set_queries 0 non_result_set_queries 0 no_index_used 0 bad_index_used 0 slow_queries 0 buffered_sets 0 unbuffered_sets 0 ps_buffered_sets 0 ps_unbuffered_sets 0 flushed_normal_sets 0 flushed_ps_sets 0 ps_prepared_never_executed 0 ps_prepared_once_executed 0 rows_fetched_from_server_normal 0 rows_fetched_from_server_ps 0 rows_buffered_from_client_normal 0 rows_buffered_from_client_ps 0 rows_fetched_from_client_normal_buffered 0 rows_fetched_from_client_normal_unbuffered 0 rows_fetched_from_client_ps_buffered 0 rows_fetched_from_client_ps_unbuffered 0 rows_fetched_from_client_ps_cursor 0 rows_affected_normal 0 rows_affected_ps 0 rows_skipped_normal 0 rows_skipped_ps 0 copy_on_write_saved 0 copy_on_write_performed 0 command_buffer_too_small 0 connect_success 0 connect_failure 0 connection_reused 0 reconnect 0 pconnect_success 0 active_connections 0 active_persistent_connections 0 explicit_close 0 implicit_close 0 disconnect_close 0 in_middle_of_command_close 0 explicit_free_result 0 implicit_free_result 0 explicit_stmt_close 0 implicit_stmt_close 0 mem_emalloc_count 0 mem_emalloc_amount 0 mem_ecalloc_count 0 mem_ecalloc_amount 0 mem_erealloc_count 0 mem_erealloc_amount 0 mem_efree_count 0 mem_efree_amount 0 mem_malloc_count 0 mem_malloc_amount 0 mem_calloc_count 0 mem_calloc_amount 0 mem_realloc_count 0 mem_realloc_amount 0 mem_free_count 0 mem_free_amount 0 mem_estrndup_count 0 mem_strndup_count 0 mem_estndup_count 0 mem_strdup_count 0 proto_text_fetched_null 0 proto_text_fetched_bit 0 proto_text_fetched_tinyint 0 proto_text_fetched_short 0 proto_text_fetched_int24 0 proto_text_fetched_int 0 proto_text_fetched_bigint 0 proto_text_fetched_decimal 0 proto_text_fetched_float 0 proto_text_fetched_double 0 proto_text_fetched_date 0 proto_text_fetched_year 0 proto_text_fetched_time 0 proto_text_fetched_datetime 0 proto_text_fetched_timestamp 0 proto_text_fetched_string 0 proto_text_fetched_blob 0 proto_text_fetched_enum 0 proto_text_fetched_set 0 proto_text_fetched_geometry 0 proto_text_fetched_other 0 proto_binary_fetched_null 0 proto_binary_fetched_bit 0 proto_binary_fetched_tinyint 0 proto_binary_fetched_short 0 proto_binary_fetched_int24 0 proto_binary_fetched_int 0 proto_binary_fetched_bigint 0 proto_binary_fetched_decimal 0 proto_binary_fetched_float 0 proto_binary_fetched_double 0 proto_binary_fetched_date 0 proto_binary_fetched_year 0 proto_binary_fetched_time 0 proto_binary_fetched_datetime 0 proto_binary_fetched_timestamp 0 proto_binary_fetched_string 0 proto_binary_fetched_blob 0 proto_binary_fetched_enum 0 proto_binary_fetched_set 0 proto_binary_fetched_geometry 0 proto_binary_fetched_other 0 init_command_executed_count 0 init_command_failed_count 0 com_quit 0 com_init_db 0 com_query 0 com_field_list 0 com_create_db 0 com_drop_db 0 com_refresh 0 com_shutdown 0 com_statistics 0 com_process_info 0 com_connect 0 com_process_kill 0 com_debug 0 com_ping 0 com_time 0 com_delayed_insert 0 com_change_user 0 com_binlog_dump 0 com_table_dump 0 com_connect_out 0 com_register_slave 0 com_stmt_prepare 0 com_stmt_execute 0 com_stmt_send_long_data 0 com_stmt_close 0 com_stmt_reset 0 com_stmt_set_option 0 com_stmt_fetch 0 com_deamon 0 bytes_received_real_data_normal 0 bytes_received_real_data_ps 0 any suggestions ? Quote Link to comment Share on other sites More sharing options...
angelcool Posted December 29, 2010 Author Share Posted December 29, 2010 SOLVED! I found this: http://www.bitshop.com/Blogs/tabid/95/EntryId/67/PHP-mysqlnd-cannot-connect-to-MySQL-4-1-using-old-authentication.aspx This is generally a fairly simple problem, the password in the database is using the older short hash method instead of the newer method. First: Let's check what size the password is: C:\mysql\bin>mysql.exe --database=dbname --password=pass123 --user=user123 --host=127.0.0.1 mysql> use mysql; Database changed mysql> select Password from user where User = 'user123'; +------------------+ | Password | +------------------+ | 0123456789abcdef | +------------------+ 1 rows in set (0.03 sec) You'll notice the password is 16 digits long, not 41 as the new passwords are in MySQL 4.1 and above. The following is what the output SHOULD look like: mysql> select Password from user where User = 'user123'; +-------------------------------------------+ | Password | +-------------------------------------------+ | *DA1234567890ABCDEF0123456789012345678901 | +-------------------------------------------+ 1 row in set (0.05 sec) To fix you just need to reset your password: SET PASSWORD FOR 'user'@'localhost' = PASSWORD('mypassword'); There are no shortage of pages and/or blog entries talking about this fix, however what made this confusing is after resetting the password was still short (i.e. 16 digits). Another method to check if the short password is used is by selecting PASSWORD('test') (any password can be substituted for the word test): mysql> select PASSWORD('test'); +-------------------------------------------+ | PASSWORD('test') | +-------------------------------------------+ | 0123456789ABCDEF | +-------------------------------------------+ 1 row in set (0.05 sec) This may be happening because you have old-passwords = 1 in your my.cnf file - This tells MySQL to run in it's old mode - However the newer versions of PHP 5.3.0 and up (including PHP 5.3.1) will refuse to talk to a MySQL server with this setting enabled. Unfortunately what stumped me today was working with a customer's dba to access MySQL on their server elsewhere for a new web site hosted at BitShop - they didn't do the required : * FLUSH PRIVILEGES; Until that was done the password update didn't take effect. I hope this helps someone.. I made sure I had old_passwords=0, restart mysql and update the password [ SET PASSWORD FOR user@localhost=PASSWORD('letmein'); ] . Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.