Mysql password hashing method old vs new

Question

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?

`SET SESSION old_passwords=0` should suffice, there's no need to change the global setting for this one-shot solution (otherwise they could simply restart the mysqld without that option). I've tested it on my local 5.1.37 server. To confirm that a new hash is really "new" let the support guy run the query `SELECT \`Host\`, Length(\`PASSWORD\`) FROM mysql.user WHERE \`User\`='nodari'`. Remeber: 41 is good, 16 is old=bad ;-) — VolkerK, Dec 12 '09 at 19:50
Oh, and I'd try it without the grant thing first. It shouldn't be necessary for your existing account. It probably doesn't hurt either but introduces a new question: "Does 'identified by' obey exactly the same rules as PASSWORD()?" Most likely, but you never know ;-) — VolkerK, Dec 12 '09 at 19:57
it's not a one time thing, i will need to do this in a daily basis during a time... And i asume than in the next reboot this old_password thing will be back to bite me in the ass — The Disintegrator, Dec 12 '09 at 20:24
I was with the same problem. This question / answer solve it: [http://stackoverflow.com/questions/1575807/cannot-connect-to-mysql-4-1-using-old-authentication](http://stackoverflow.com/questions/1575807/cannot-connect-to-mysql-4-1-using-old-authentication) — rlc, Sep 22 '11 at 16:08

score 8 · Answer 1 · answered Dec 12 '09 at 10:39

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).

'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 Disintegrator, Dec 12 '09 at 19:10
I updated the question, the comment field wont let me write code — The Disintegrator, Dec 12 '09 at 19:17

score 4 · Answer 2 · answered May 25 '10 at 03:07

4

I just had this issue, and was able to work around it.

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

Run these queries:

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

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

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

This allowed me to connect successfully.

answered May 25 '10 at 03:07

TehShrike

9,855
2
33
28

yeah, I know you can do it this way, but the idea es to have a current client at slicehost. Also, I can't change anything at dreamhost. And they use the old password method because all the passwords of all the servers are stored elsewhere, they're using the old method and they can change this by now... This particular case doesn't have a good solution. I ended up creating scripts in both ends that communicate themselves using CURL... – The Disintegrator May 25 '10 at 04:30
I don't know about Slicehost. I use Dreamhost as well, as it turns out. old_passwords is pretty annoying. – TehShrike May 25 '10 at 08:59

score 2 · Accepted Answer · answered Dec 12 '09 at 07:17

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).

well, right now I'm writing a script to generate a query at slicehost to be executed at dreamhost. i'm using curl as my transfer method. Is an horrible hack, but at least i can call the update script via a browser or a cron... — The Disintegrator, Dec 12 '09 at 08:16

score 2 · Answer 4 · edited Jul 31 '12 at 20:13

2

I would solve this by dumping the data at Slicehost, using SELECT ... INTO OUTFILE.

This allows you to design your query to make sure the output is in the format matching the table structure at the destination site.

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

As an aside, Dreamhost is really still using MySQL 4.0? They're extremely outdated -- even MySQL 4.1's extended support is expiring this month (December 2009).

edited Jul 31 '12 at 20:13

Bill the Lizard

398,270
210
566
880

answered Dec 12 '09 at 07:58

Bill Karwin

538,548
86
673
828

the fun thing is that they're using version 5.0. I don't understand why they still using the old hashing method – The Disintegrator Dec 12 '09 at 08:12
Okay, then it's probably because changing the password method would probably disrupt hundreds (or thousands) of customers' applications running on that host. Often a web host just leaves such a server as is, and enables new features only on a separate server. Over time, their customers gradually migrate to the newer server, and finally they take the old one out of service. You can ask your provider to move your hosted account to one of those newer servers. – Bill Karwin Dec 12 '09 at 19:14
its a private server... wouldn't it be a mysql instance with no more users than myself? – The Disintegrator Dec 12 '09 at 19:26
I don't know, you should ask the hosting provider. It could be a private server for your app, but the MySQL instance is on another server which is shared. I don't know. – Bill Karwin Dec 12 '09 at 19:36

score 1 · Answer 5 · answered Dec 12 '09 at 08:03

1

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

answered Dec 12 '09 at 08:03

Dennis C

24,511
12
71
99

score 0 · Answer 6 · edited Feb 04 '16 at 20:13

0

I had the same issue. To solve it, I did the following:

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

edited Feb 04 '16 at 20:13

tumultous_rooster

12,150
32
92
149

answered Feb 04 '16 at 19:56

f.eberharter

21
6

1

Where do you select the user? – Volatil3 Oct 27 '16 at 09:53

Mysql password hashing method old vs new

6 Answers6

Linked