Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have an outstanding issue with VCC in chicago, there is an issue connecting to the mysql database If an agent does a refresh of the monitor they will be logged out. This is the error we get when the vcc web server starts. [Error: MySQL server is requesting the old and insecure pre-4.1 auth mechanism.Upgrade the user password or use the {insecureAuth: true} option.] code: 'HANDSHAKE_INSECURE_AUTH', fatal: true } I have done below step,but this issue still exsit.Please check. old_passwords set to off ALTER TABLE agent_login_session CHANGE operatorId operatorId VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL – done

·         info: socket.io started
###### Session SB Server connected fail! ######
{ [Error: MySQL server is requesting the old and insecure pre-4.1 auth mechanism.Upgrade the user password or use the {insecureAuth: true} option.] code: 'HANDSHAKE_INSECURE_AUTH', fatal: true }

1. update mysql to use long password()
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=0

2. ALTER TABLE `agent_login_session` CHANGE `operatorId` `operatorId` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL


Please help me here.
share|improve this question
    
Did this happen after upgrading to MySQL 5.5 ? –  RolandoMySQLDBA Dec 13 '12 at 18:29

1 Answer 1

up vote 2 down vote accepted

Based on your old_passwords setting, I would ask you to look at three things

  1. What password functions return
  2. What passwords are stored in mysql.user
  3. What authentication style is the client launching

PASSWORD FUNCTIONS

Please run the following:

show variables like 'old_passwords';
select password('rolando'),old_password('rolando');

Depending on where old_passwords is enabled or not, you should get either the following:

MySQL 5.0 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.1 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

PASSWORDS STORED

Please run this query:

SELECT COUNT(1) password_length_count,password_length FROM
(SELECT LENGTH(password) password_length FROM mysql.user) A
GROUP BY password_length;

If you get any password_length of 16, you have to enable old_passwords for the old style authentication to work.

PASSWORDS FROM CLIENT

In some cases, using mysql libraries that came from old yum installs of MySQL, PHP, Perl or Apache may have pre-MySQL4.1 user authentication code. Please upgrade such libraries.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.