Hi folks,
Got a puzzler that I'm wondering if anyone can point me in the right
direction of. Noone has had any inspiration as of yet!

I have a FreeBSD server (6.2-RELEASE)

It runs cpanel and in the course of setting something up things broke
etc. I've got most of the problems sorted but Mysql is being a pain.

It is running:

mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.2 (amd64) using
5.0

The problem is in authentication and new versus old passwords.
The password table is the right width for new passwords.
When passwords are generated they get generated and stored as the right
length for new passwords.
However, when you try to authenticate against a new password it gives the
message "Client does not support authentication protocol requested by
server".
Any passwords stored as the old type still pass.

I have to manually use old_password() to make sure users have old
passwords and I have had to set the old passwords on so that any new
passwords are generated in the old format.

So, the summary questions are:

1) Why will mysql no longer work with the new password format?
2) What can I do about it?

Cheers if anyone can , identifying where the problem is about as far
as I can get!

On 08 Feb 2008 00:18:30 GMT, cw wrote:
> Hi folks,
> Got a puzzler that I'm wondering if anyone can point me in the right
> direction of. Noone has had any inspiration as of yet!
>
> I have a FreeBSD server (6.2-RELEASE)
>
> It runs cpanel and in the course of setting something up things broke
> etc. I've got most of the problems sorted but Mysql is being a pain.
>
> It is running:
>
> mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.2 (amd64) using
> 5.0
>
> The problem is in authentication and new versus old passwords.
> The password table is the right width for new passwords.
> When passwords are generated they get generated and stored as the right
> length for new passwords.
> However, when you try to authenticate against a new password it gives the
> message "Client does not support authentication protocol requested by
> server".
> Any passwords stored as the old type still pass.
>
> I have to manually use old_password() to make sure users have old
> passwords and I have had to set the old passwords on so that any new
> passwords are generated in the old format.
>
> So, the summary questions are:
>
> 1) Why will mysql no longer work with the new password format?
> 2) What can I do about it?
>
> Cheers if anyone can , identifying where the problem is about as far
> as I can get!

Check mysql.ini for an "old_passwords = 1" clause in the [mysqld]
stanza?

--
I'm not sure if this is a good or a bad thing.
Probably a bad thing; most things are bad things.
-- Nile Evil Bastard

"Peter H. Coffin" <hellsop@ninehells.com> wrote in
news:slrnfqn9cm.tgp.hellsop@abyss.ninehells.com:

> Check mysql.ini for an "old_passwords = 1" clause in the [mysqld]
> stanza?

Thanks for the reply, It wasn't quite obvious from what I wrote but I
meant to say that old passwords was definately off when this problem
started. From what I understand, this wouldn't affect the issue anyway.
(Plus it is my.cnf

old_passwords causes the mysql server to generate all passwords in the
old format however any passwords already stored in the new format should
still authenticate correctly. At least that's what it says in the mysql
docs

With old_passwords off, the server successfully generates new style
passwords but cannot authenticate against them.

My best guess is some kind of library problem for whatever mysql uses to
do its authentication. I did have a problem with Apache 1.3 segfaulting
as it was trying to use libcrypto.so.3 and the server only had .so and
..so.5 (fixed that by upgrading to Apache 2 which uses the right
libraries).

I checked the .err file for the server and found this from when the
problems started. I think the upcp upgraded mysql to .51 and broke it. It
has since been upgraded to .51a but no difference. No more problems in
the log since this lot.

Version: '5.0.45' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port:
mysql-server-5.0.45
A mysqld process already exists at Tue Oct 9 18:38:23 BST 2007
080109 19:55:52 [Note] /usr/local/libexec/mysqld: Normal shutdown

080109 19:55:54 InnoDB: Starting shutdown...
080109 19:55:57 InnoDB: Shutdown completed; log sequence number 0
45366374
080109 19:55:57 [Note] /usr/local/libexec/mysqld: Shutdown complete

080109 19:55:57 mysqld ended

080109 20:01:07 mysqld started
080109 20:01:07 InnoDB: Started; log sequence number 0 45366374
080109 20:01:07 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.51' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port:
mysql-server-5.0.51
080204 12:49:37 mysqld restarted
080204 12:49:37 mysqld started
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
080204 12:49:38 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
080204 12:49:38 InnoDB: Operating system error number 2 in a file
operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: File name ./mysql.sock
InnoDB: File operation call: 'stat'.
InnoDB: Error: os_file_readdir_next_file() returned -1 in
InnoDB: directory .
InnoDB: Crash recovery may have failed for some .ibd files!
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
080204 12:49:38 InnoDB: Started; log sequence number 0 47553156
080204 12:49:39 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.51' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port:
mysql-server-5.0.51
080204 12:51:12 mysqld restarted

080204 12:51:12 mysqld started
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
080204 12:51:13 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
080204 12:51:13 InnoDB: Operating system error number 2 in a file
operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: File name ./mysql.sock
InnoDB: File operation call: 'stat'.
InnoDB: Error: os_file_readdir_next_file() returned -1 in
InnoDB: directory .
InnoDB: Crash recovery may have failed for some .ibd files!
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
080204 12:51:13 InnoDB: Started; log sequence number 0 47553156
080204 12:51:13 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.51' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port:
mysql-server-5.0.51
080204 12:56:19 mysqld restarted
080204 12:56:19 mysqld ended

080204 12:56:20 mysqld started
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
080204 12:56:20 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
080204 12:56:20 InnoDB: Operating system error number 2 in a file
operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: File name ./mysql.sock
InnoDB: File operation call: 'stat'.
InnoDB: Error: os_file_readdir_next_file() returned -1 in
InnoDB: directory .
InnoDB: Crash recovery may have failed for some .ibd files!
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
080204 12:56:20 InnoDB: Started; log sequence number 0 47553156
080204 12:56:20 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.51' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port:
mysql-server-5.0.51
080204 12:57:43 [Note] /usr/local/libexec/mysqld: Normal shutdown

cw wrote:
> Hi folks,
> Got a puzzler that I'm wondering if anyone can point me in the right
> direction of. Noone has had any inspiration as of yet!
>
> I have a FreeBSD server (6.2-RELEASE)
>
> It runs cpanel and in the course of setting something up things broke
> etc. I've got most of the problems sorted but Mysql is being a pain.
>
> It is running:
>
> mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.2 (amd64) using
> 5.0
>
> The problem is in authentication and new versus old passwords.
> The password table is the right width for new passwords.
> When passwords are generated they get generated and stored as the right
> length for new passwords.
> However, when you try to authenticate against a new password it gives the
> message "Client does not support authentication protocol requested by
> server".
> Any passwords stored as the old type still pass.
>
> I have to manually use old_password() to make sure users have old
> passwords and I have had to set the old passwords on so that any new
> passwords are generated in the old format.
>
> So, the summary questions are:
>
> 1) Why will mysql no longer work with the new password format?
> 2) What can I do about it?
>
> Cheers if anyone can , identifying where the problem is about as far
> as I can get!
>

What is the version of your client library? Looks like you're loading
an old one.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle <jstucklex@attglobal.net> wrote in
news-KdnYQ5OPepvTDanZ2dnUVZ_orinZ2d@comcast.com:


> What is the version of your client library? Looks like you're loading
> an old one.

Are we talking about libmysqlclient.so ? If that is the case, they all have
timestamps of the new build. I'm not sure how to find out the exact version
of a .so but I only have .15 with links to it:

/usr/local/lib/mysql/
lrwxr-xr-x 1 root wheel 20 Feb 4 15:08 libmysqlclient.so ->
libmysqlclient.so.15
lrwxr-xr-x 1 root wheel 20 Apr 18 2007 libmysqlclient.so.14 ->
libmysqlclient.so.15
-rwxr-xr-x 1 root wheel 483160 Feb 4 15:08 libmysqlclient.so.15

Thanks for the reply.

cw wrote:
> Jerry Stuckle <jstucklex@attglobal.net> wrote in
> news-KdnYQ5OPepvTDanZ2dnUVZ_orinZ2d@comcast.com:
>
>
>> What is the version of your client library? Looks like you're loading
>> an old one.
>
> Are we talking about libmysqlclient.so ? If that is the case, they all have
> timestamps of the new build. I'm not sure how to find out the exact version
> of a .so but I only have .15 with links to it:
>
> /usr/local/lib/mysql/
> lrwxr-xr-x 1 root wheel 20 Feb 4 15:08 libmysqlclient.so ->
> libmysqlclient.so.15
> lrwxr-xr-x 1 root wheel 20 Apr 18 2007 libmysqlclient.so.14 ->
> libmysqlclient.so.15
> -rwxr-xr-x 1 root wheel 483160 Feb 4 15:08 libmysqlclient.so.15
>
> Thanks for the reply.
>
>

But are those the ones you're using? Or are you using some other one?

How many copies of libmysqlclient.so do you have on your system? And
what happens if get rid of all but the latest?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle <jstucklex@attglobal.net> wrote in
news:zoGdnV2iCuIg7TLanZ2dnUVZ_hKdnZ2d@comcast.com:

> But are those the ones you're using? Or are you using some other one?
>
> How many copies of libmysqlclient.so do you have on your system? And
> what happens if get rid of all but the latest?

That is all I have on the system. I even ran a find over the entire
filesystem to see if there were any others. The only one is in
/usr/local/lib/mysql and the others are just symlinks to it. So there isn't
anything old to get rid of..

cw wrote:
> Jerry Stuckle <jstucklex@attglobal.net> wrote in
> news:zoGdnV2iCuIg7TLanZ2dnUVZ_hKdnZ2d@comcast.com:
>
>> But are those the ones you're using? Or are you using some other one?
>>
>> How many copies of libmysqlclient.so do you have on your system? And
>> what happens if get rid of all but the latest?
>
> That is all I have on the system. I even ran a find over the entire
> filesystem to see if there were any others. The only one is in
> /usr/local/lib/mysql and the others are just symlinks to it. So there isn't
> anything old to get rid of..
>

Well, you've obviously got a back level client you're using somewhere.
See http://dev.mysql.com/doc/refman/5.0/en/old-client.html.

How are you trying to connect to MySQL? Command line? Program or
script? Something else?

And is all of this on the same system, or are you using different systems?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle <jstucklex@attglobal.net> wrote in
news:OqydnY50Tcd_NTLanZ2dnUVZ_qLinZ2d@comcast.com:

> Well, you've obviously got a back level client you're using somewhere.
> See http://dev.mysql.com/doc/refman/5.0/en/old-client.html.
>
> How are you trying to connect to MySQL? Command line? Program or
> script? Something else?
>
> And is all of this on the same system, or are you using different
> systems?

I've connected in a few ways however as it turns out I only checked for the
problem when using PHP to connect. Using mysql from the command line
appears to authenticate fine so I've been barking up the wrong tree. It
looks like PHP has gone backwards. It *was* working fine with new style
passwords but isn't any more.

As PHP is built by Cpanel, I'll go back to them. I know I tried a variety
of different builds. One option I recall was compiling mysql against the
system libraries rather than the bundled PHP libraries. It didn't make any
difference at the time although theoretically it should.

I will try a PHP rebuild with the system libraries option on again and then
throw it to cpanel as a PHP build error.

Thank you for the , you've got me going in a better direction to tackle
this now!

cw wrote:
> Jerry Stuckle <jstucklex@attglobal.net> wrote in
> news:OqydnY50Tcd_NTLanZ2dnUVZ_qLinZ2d@comcast.com:
>
>> Well, you've obviously got a back level client you're using somewhere.
>> See http://dev.mysql.com/doc/refman/5.0/en/old-client.html.
>>
>> How are you trying to connect to MySQL? Command line? Program or
>> script? Something else?
>>
>> And is all of this on the same system, or are you using different
>> systems?
>
> I've connected in a few ways however as it turns out I only checked for the
> problem when using PHP to connect. Using mysql from the command line
> appears to authenticate fine so I've been barking up the wrong tree. It
> looks like PHP has gone backwards. It *was* working fine with new style
> passwords but isn't any more.
>
> As PHP is built by Cpanel, I'll go back to them. I know I tried a variety
> of different builds. One option I recall was compiling mysql against the
> system libraries rather than the bundled PHP libraries. It didn't make any
> difference at the time although theoretically it should.
>
> I will try a PHP rebuild with the system libraries option on again and then
> throw it to cpanel as a PHP build error.
>
> Thank you for the , you've got me going in a better direction to tackle
> this now!
>

OK, then your PHP was compiled against older MySQL libraries. You need
up update your PHP.

Personally, I compile my own PHP. That way I get exactly what I want -
not what someone else thinks I might want.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle <jstucklex@attglobal.net> wrote in
news:w4SdnbIDF8GcaC3anZ2dnUVZ_gSdnZ2d@comcast.com:

> OK, then your PHP was compiled against older MySQL libraries. You need
> up update your PHP.
>
> Personally, I compile my own PHP. That way I get exactly what I want -
> not what someone else thinks I might want.

I had recompiled PHP several times. It was compiled against its own bundled
libraries and compiling against the system ones worked. Apparently I had
turned that option off, must have been during the problems with Apache
segfaulting.

Thanks for getting me going in the right direction to sort it.