Bug #344 New password function in 4.1 has randomness => does not replicate well
Submitted: 29 Apr 2003 7:42 Modified: 13 May 2003 14:17
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.1 OS:Any (all)
Assigned to: Guilhem Bichot

[29 Apr 2003 7:42] Guilhem Bichot
Description:
Because two calls to PASSWORD() do not return the same string, PASSWORD()
is not replicated properly.

How to repeat:
MASTER> create table u(a varchar(100));

MASTER> insert into u select password('a');

MASTER> select * from u;
+-----------------------------------------------+
| a                                             |
+-----------------------------------------------+
| *42be2b0ad13bb64b1eaf0aae2b97c2ddb46fea3fea6c |
+-----------------------------------------------+

MASTER> Bye

SLAVE> select * from u;
+-----------------------------------------------+
| a                                             |
+-----------------------------------------------+
| *ca5e158eed031b9a3853187f77027442dc8030c50b0b |
+-----------------------------------------------+
[29 Apr 2003 7:44] Guilhem Bichot
As the LOAD_FILE() problem, it cannot be always fixed by
a new event or substitution because of this:
INSERT INTO u SELECT PASSWORD(a) FROM t;

So it will be fixed when we have row-level logging,
like LOAD_FILE().
[29 Apr 2003 14:09] Guilhem Bichot
I have just learnt that PASSWORD() uses the random number generator,
just as RAND(). So we could use some Rand_log_event as we already use
for RAND(). Btw, a query involving RAND() (with no argument) and PASSWORD()
 insert into u select concat(rand(),password("aaa")) from t;
is well replicated, because RAND() causes a Rand_log_event.
I will now test if we can easily use a Rand_log_event to fix it.
[9 May 2003 10:10] [ name withheld ]
I have a desktop with 4.0 which I upgraded to 4.1 and the password() function seems to working fine on that.. 
But I have mysql 4.1 instaled frm scratch on two other laptops and the password does not replicate so i get an error every time i try to check a password.

Another thing i noticed is ..
On the laptops when i use the password() function mysql returns a string with a asterix(*) in front of it..
But on the desktop it's usually a smaller string without a asterix(*).
[12 May 2003 5:59] Guilhem Bichot
Hi,
this is a note for <manjit@riat.net> :
from version 4.1, MySQL employs a different password and login mechanism that is secure even if TCP/IP packets are sniffed and/or the mysql database is captured.

>I have a desktop with 4.0 which I upgraded to 4.1 and the password()
>function seems to working fine on that.. 

You may want to check your .err file to see if MySQL 4.1 warns you that it is still using the old password() function : look for
"mysql.user table is not updated to new password format;  Disabling new password usage until mysql_fix_privilege_tables is run"

If this is printed, it means that you are still using your 4.0 privilege tables, so MySQL 4.1 has to use the old password() function. On the contrary, your installations from scratch use 4.1 privilege tables, hence new password() functions.

>But I have mysql 4.1 instaled from scratch on two other laptops and the
>password does not replicate so i get an error every time i try to check
>a password.

Yes, this replication problem is the bug which is adressed here. It will be fixed in 4.1.1.

>Another thing i noticed is ..
>On the laptops when i use the password() function mysql returns a
>string with a asterix(*) in front of it..
>But on the desktop it's usually a smaller string without a asterix(*).

Old password() (<4.1) returns passwords with no *; new password() returns a longer (more hard to break) password with *.
[13 May 2003 14:17] Guilhem Bichot
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

Fixed in 4.1.1 (cset 1.1531)