Search code examples
phpmysqlmysql-error-1044password-hash

Mysql password hashing method old vs new

I'm trying to connect to a mysql server at dreamhost from a php scrip located in a server at slicehost (two different hosting companies). I need to do this so I can transfer new data at slicehost to dreamhost. Using a dump is not an option because the table structures are different and i only need to transfer a small subset of data (100-200 daily records) The problem is that I'm using the new MySQL Password Hashing method at 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 at slicehost and i can't use an older php version/library
  • The database is too big to transfer it every day with a dump
  • Even if i did this, the tables have different structures
  • I need to copy only a small subset of it, in a daily basis (only the changes of the day, 100-200 records)
  • Since the tables are so different, i need to use php as a bridge to normalize the data
  • Already googled it
  • Already talked to both support stafs

The more obvious option to me would be to start using the new MySQL Password Hashing method at dreamhost, but they will not change it and i'm not root so i can't do this myself.

Any wild idea?

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 now would be run a mysql> SET GLOBAL old_passwords=0; But i need SUPER privilege to do that and they wont give it to me

if I run the query

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

I get the 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 at dreamhost support insist saying thet the problem is at my end. But he said he will run any query I tell him since it's a private server. So, I need to tell this guy EXACTLY what to run. So, telling 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';

would be a good start?

Solution

  • Yeah, that looks like a toughie. Without cooperation from your hosts or the ability to change password formats or client libraries, you don't have a lot of options.

    Honestly, my first choice would be to ditch Dreamhost. That's probably a lot of work, but if they're going to be stuck using old incompatible stuff, it will continue to be problematic.

    If that's not an option, what about a joint automated process? You could export the data on the Slicehost side into a CSV file and massage it into whatever format is necessary for Dreamhost, and then upload it to the Dreamhost server. You could have a cron script on the Dreamhost server check periodically for the uploaded file and process it (making sure to move or delete it after it was successfully processed).