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:
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?
On some conditions you may still be able to set and use a "new hashing algorithm password".
MySQL 4.1+ servers are able to handle both login algorithms. Which one is used is independent from the old-passwords variable. If MySQL finds a 41 character long hash starting with * it uses the new system. And the PASSWORD() function is also able to use both algorithms. If the field mysql.user.Password is wide enough to store 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 you might be able to change it for your session.
Connect to the MySQL server (with a client that is able to do so despite the global old_passwords=1), e.g. 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
(meaning the global old_passwords is on, but for the session it's off and PASSWORD() returned a "new" password) you should be able to set a new password using the new algorithm for your account within the same session.
But if dreamhost really wants to disable the new passwords algorithm the mysql.user.Password field will be less than 41 characters long and there's nothing you can do about it (except nagging them).