MySQL error - Client does not support authentication protocol requested by server; consider upgrading MySQL client

[cah]

I was working on a CGI on logdev01 and trying to connect to the MySQL running on dept01 but it was unable to connect.

When I tried the following from command line, it failed with error message:

Code: Select all

perl test.cgi

DBI connect('MMCC:dept01.ols.minimed.com:3306','mmcc',...) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at .mysql/dbh line 10
Client does not support authentication protocol requested by server; consider upgrading MySQL client at .mysql/dbh line 10.
Compilation failed in require at user_registration.cgi line 24.

Since I have compiled and installed all the latest perl modules for DBI and DBD:mysql, I know it is not the protocol or client version issue.

I searched on the internet and many had suggested to use the following command (in mysql) to make it the old password:

Code: Select all

mysql> SET PASSWORD FOR
    -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

I checked MySQL version first:

Code: Select all

mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.51b, for redhat-linux-gnu (i686) using  EditLine wrapper

Connection id:          23087
Current database:
Current user:           mysql@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.51b-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    big5
Db     characterset:    big5
Client characterset:    big5
Conn.  characterset:    big5
UNIX socket:            /tmp/mysql.sock
Uptime:                 191 days 20 hours 33 min 41 sec

Threads: 2  Questions: 1102267  Slow queries: 51  Opens: 92009  Flush tables: 1  Open tables: 64  Queries per second avg: 0.066
--------------

It is version 5.0.51b, not too old.
Still, I moved ahead and reset the user's password:

Code: Select all

mysql> set password for 'mmcc'@'%' = old_password('mmccus3r');
Query OK, 0 rows affected (0.02 sec)

After resetting it this way, I was able to connect to remote MySQL server (dept01) from logdev01. And the CGI on web site works too of course.

"mmcc" user info before password reset:

Code: Select all

mysql> SHOW GRANTS FOR mmcc;
+-----------------------------------------------------------------------------------------------------+
| Grants for mmcc@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mmcc'@'%' IDENTIFIED BY PASSWORD '*23FA7D6797A14CA96BF99021347FDDE116DCD36E' | 
| GRANT ALL PRIVILEGES ON `MMCC`.* TO 'mmcc'@'%' WITH GRANT OPTION                                    | 
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

"mmcc" user info after password reset:

Code: Select all

mysql> SHOW GRANTS FOR mmcc;
+----------------------------------------------------------------------------+
| Grants for mmcc@%                                                          |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mmcc'@'%' IDENTIFIED BY PASSWORD '4a8df0cf08a32f40' | 
| GRANT ALL PRIVILEGES ON `MMCC`.* TO 'mmcc'@'%' WITH GRANT OPTION           | 
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Password format is definitely different.
Excerpted from MySQL (http://dev.mysql.com/doc/refman/5.1/en/ ... d-password)

Code: Select all

OLD_PASSWORD(str)

OLD_PASSWORD() was added when the implementation of PASSWORD() was changed in MySQL 4.1 to improve security. OLD_PASSWORD() returns the value of the pre-4.1 implementation of PASSWORD() as a binary string, and is intended to permit you to reset passwords for any pre-4.1 clients that need to connect to your version 5.1 MySQL server without locking them out. See Section 5.3.2.3, “Password Hashing in MySQL”.
 

http://dev.mysql.com/doc/refman/5.1/en/ ... shing.html

Code: Select all

Prior to MySQL 4.1, password hashes computed by the PASSWORD() function are 16 bytes long. Such hashes look like this:

mysql> SELECT PASSWORD('mypass');
+--------------------+
| PASSWORD('mypass') |
+--------------------+
| 6f8c114b58f2ce9e   |
+--------------------+

The Password column of the user table (in which these hashes are stored) also is 16 bytes long before MySQL 4.1.

As of MySQL 4.1, the PASSWORD() function has been modified to produce a longer 41-byte hash value:

mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+

Accordingly, the Password column in the user table also must be 41 bytes long to store these values:

I am not sure why I have to set the password back to the pre-4.1 password format while we are running 5.0 version. Very strange. My guess is dept01 has multiple versions of MySQL running and somehow it gets confused and accepts older password format only. Anyhow, this is the way to get it to work.

[cah]

The reason why it needs OLD_PASSWORD() to convert the password is so that MySQL 4.x version client can communicate with the server. The old client doesn't understand MySQL 5's password format.

Code: Select all

%mysql -V
mysql  Ver 12.22 Distrib 4.0.31, for pc-solaris2.10 (i386)

[cah]

Orautil02 has MySQL 5.6.15 running.
I migrated MMCC schema over from dept01 and there are some changes needed to be made before it would connect.

1. MySQL 5.6.15 does NOT allow OLD_PASSWORD() function. It takes 41 hex characters only.
2. That means I cannot use MySQL 4 client to connect.
3. Same goes to Perl DBD/DBI modules I have to compile and install newer versions

DBI-1.613 --> DBI-1.634
DBD-mysql-4.016 --> DBD-mysql-4.032

4. Using different default character set options (--default-character-set=xxxx) to retrieve the same data:

Code: Select all

logdev01:/%mysql --default-character-set=utf8 -horautil02.ols.minimed.com -um>

mysql> select chinesename from members where id=1;
+-------------+
| chinesename |
+-------------+
| è­é·å®      |
+-------------+
1 row in set (0.00 sec)

Code: Select all

logdev01:/%mysql --default-character-set=big5 -horautil02.ols.minimed.com -um>

mysql> select chinesename from members where id=1;
+-------------+
| chinesename |
+-------------+
| ¿½ªø¦w      |
+-------------+
1 row in set (0.00 sec)

All mysql options are specified in my.cnf file.

[cah]

I checked MySQL 5.1.40 source directory on hsiao.net because I remember I configured and compiled it a few years back.

When I checked config.log, I saw how I configured it back in 2009:

Code: Select all

  $ .//configure --with-charset=big5 --with-extra-charsets=all --with-libwrap=/usr/sfw

I explicitly configured it with big5 character set. And, it has the following lines in the log file (many occurrances):

Code: Select all

| #define MYSQL_DEFAULT_CHARSET_NAME "big5"
| #define MYSQL_DEFAULT_COLLATION_NAME "big5_chinese_ci"

I then checked the options in configure file and found this:

Code: Select all

  --with-charset=CHARSET
                          Default character set, use one of:
                          binary
                          armscii8 ascii big5 cp1250 cp1251 cp1256 cp1257
                          cp850 cp852 cp866 cp932 dec8 eucjpms euckr gb2312 gbk geostd8
                          greek hebrew hp8 keybcs2 koi8r koi8u
                          latin1 latin2 latin5 latin7 macce macroman
                          sjis swe7 tis620 ucs2 ujis utf8

--with-charset=CHARSET sets the default character set. That's why character_set_server is set with big5.

Code: Select all

mysql> SHOW VARIABLES like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | big5                             |
| character_set_connection | big5                             |
| character_set_database   | big5                             |
| character_set_filesystem | binary                           |
| character_set_results    | big5                             |
| character_set_server     | big5                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

The same query from orautil02 shows character_set_server is set to latin1.

Code: Select all

mysql> SHOW VARIABLES like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | big5                             |
| character_set_connection | big5                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | big5                             |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /opt/mysql/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

I downloaded MySQL 5.6.15 package from Oracle and I cannot recompile it.
The workaround may be from the command line or my.cnf.

Command line:

Code: Select all

mysqld --character-set-server=big5

This can be done in either /opt/mysql/mysql/bin/mysqld_safe or /etc/init.d/mysql.

Add "--character-set-server=big5" in /etc/init.d/mysql:

Code: Select all

for i in  "$ledir/$MYSQLD" "$defaults" "--character-set-server=big5" "--basedir=$MY_BASEDIR_VERSION" \
  "--datadir=$DATADIR" "--plugin-dir=$plugin_dir" "$USER_OPTION"

Add "--character-set-server=big5" in /opt/mysql/mysql/bin/mysqld_safe:

Code: Select all

      $bindir/mysqld_safe --character-set-server=big5 --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

my.cnf:

Code: Select all

[mysqld]
character-set-server=big5

Server default character set is indeed set to big5. However, this does not prevent character_set_client to be set by client. That means the clients can still connect to MySQL server with a default character set such as latin1 (by default). The solution is to disable the handshake between client and server. I tried to put "--skip-character-set-client-handshake" in /etc/my.cnf, the server started up fine but it would complain unknown option "--skip-character-set-client-handshake" when trying to connect to a schema. I had to add "unknown option '--skip-character-set-client-handshake" to /etc/init.d/mysql startup script.

Code: Select all

      $bindir/mysqld_safe --skip-character-set-client-handshake --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

I then checked the mysqld man page again regarding "--skip-character-set-client-handshake" option. It needs to be under [mysqld] section. I had it under [client] so I changed it and tested again after removing "--skip-character-set-client-handshake" from /etc/init.d/mysql startup script. It worked this time.

So, all I really needed to do is add the following lines in /etc/my.cnf:

Code: Select all

[mysqld]
character-set-server=big5
skip-character-set-client-handshake

After that, I confirmed the CGI is working as well.