Mysql old vs new password hash method

Question

I am trying to connect to mysql server in dreamhost from a php script located on slicehost server (two different hosting companies). I need to do this so that I can transfer new data to slicehost in dreamhost. Using a dump is not an option, because the table structures are different, and I need to transfer only a small data set (100-200 daily entries) The problem is that I use the new MySQL password hashing method on slicehost, and dreamhost uses the old one, so I get

$link = mysql_connect($mysqlHost, $mysqlUser, $mysqlPass, FALSE); Warning: mysql_connect() [function.mysql-connect]: OK packet 6 bytes shorter than expected Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication Warning: mysql_query() [function.mysql-query]: Access denied for user 'nodari'@'localhost' (using password: NO) 

facts:

• I need to continue using the new method in slicehost and I cannot use the older php version / library
• The database is too large to be transferred every day with a dump
• Even if I did this, tables have different structures.
• I need to copy only a small subset, daily (only day changes, 100-200 records).
• Since the tables are so different, I need to use php as a bridge to normalize the data.
• Already sent to Google.
• Already spoken with both supporting files

A more obvious option for me would be to start using the new HTML Password Hashing method in dreamhost, but they won’t change it and I’m not root, so I can’t do it myself.

Any wild idea?

Posted by VolkerK sugestion:

 mysql> SET SESSION old_passwords=0; Query OK, 0 rows affected (0.01 sec) mysql> SELECT @@global.old_passwords,@@session.old_passwords, Length(PASSWORD('abc')); +------------------------+-------------------------+-------------------------+ | @@global.old_passwords | @@session.old_passwords | Length(PASSWORD('abc')) | +------------------------+-------------------------+-------------------------+ | 1 | 0 | 41 | +------------------------+-------------------------+-------------------------+ 1 row in set (0.00 sec) 

The obvious thing will now be mysql> SET GLOBAL old_passwords = 0; But I need the SUPER privilege to do this, and they won’t pass it on to me.

if i run the request

 SET PASSWORD FOR 'nodari'@'HOSTNAME' = PASSWORD('new password'); 

I get an error

 ERROR 1044 (42000): Access denied for user 'nodari'@'67.205.0.0/255.255.192.0' to database 'mysql' 

I'm not root ...

The guy in support of dreamhost insists on saying that the problem is at my end. But he said that he would fulfill any request that I tell him, since this is a private server. So, I have to tell this guy EXACTLY what to run. So, asking him to run

 SET SESSION old_passwords=0; SET GLOBAL old_passwords=0; SET PASSWORD FOR 'nodari'@'HOSTNAME' = PASSWORD('new password'); grant all privileges on *.* to nodari@HOSTNAME identified by 'new password'; 

will be a good start?

Answer 1

Yes, it looks like a tough one. Without cooperation with your hosts or with the ability to change password formats or client libraries, you do not have many options.

Honestly, my first choice would be to cut out Dreamhost. This is probably a lot of work, but if they are going to get stuck using old incompatible material, it will still be problematic.

If this is not an option, what about a collaborative automated process? You can export data from the Slicehost side to a CSV file and massage it into any format necessary for Dreamhost, and then upload it to the Dreamhost server. You may have a cron script on the Dreamhost server to periodically check the downloaded file and process it (be sure to move or delete it after successful processing).

Answer 2

In some circumstances, you can still set up and use the “new hash algorithm password”.
MySQL 4.1+ servers can handle both logical algorithms. Which one is independent of the old-passwords variable. If MySQL finds a long hash of 41 characters starting with *, it uses the new system. The PASSWORD () function can also use both algorithms. If the mysql.user.Password field is large enough to hold 41 characters and the old-passwords variable is 0, it will create a “new” password. The documention for old_passwords says Variable Scope Both so that you can change it for your session.
Connect to the MySQL server (with a client that can do this, despite the global old_passwords = 1), for example. HeidiSQL and try the following:

 SET SESSION old_passwords=0; SELECT @@global.old_passwords,@@session.old_passwords, Length(PASSWORD('abc')); 

If it prints 1, 0, 41 (which means that the global old_passwords is enabled, but it is turned off for the session, and PASSWORD () returns a "new" password), you should be able to set a new password using the new algorithm for your account in within the same session.

But if dreamhost really wants to disable the new password algorithm, then in the mysql.user.Password field there will be less than 41 characters, and you can not do anything with it (except that they impose).

Answer 3

I had this problem and managed to get around it.

First connect to your MySQL database with an older client that doesn't mind old_passwords. Connect using the user that the script will use.

Run these queries:

 SET SESSION old_passwords=FALSE; SET PASSWORD = PASSWORD('[your password]'); 

In your PHP script, change the mysql_connect function to enable client 1 flag:

 define('CLIENT_LONG_PASSWORD', 1); mysql_connect('[your server]', '[your username]', '[your password]', false, CLIENT_LONG_PASSWORD); 

This allowed me to connect successfully.

Answer 4

I would solve this by flushing the data to Slicehost using SELECT ... INTO OUTFILE .

This allows you to design your query to ensure that the output is in a format that matches the table structure on the target site.

Then transfer the dump file to Dreamhost and use LOAD DATA INFILE .

As an aside, does Dreamhost really use MySQL 4.0? They are extremely outdated - even extended MySQL 4.1 support expires this month (December 2009).

Answer 5

I think you should make WebServices / RPC from slicehost and write the appropriate service to handle.

Answer 6

I had the same problem. To solve this problem, I did the following:

 SET PASSWORD = PASSWORD('[your password]');