some scripts can't connect to MYSQL after update

[manarak]

I just updated my server from ART, and now some PHP scripts (not all, that is strange) have difficulties connecting to MYSQL, while others can connect.

I spent time comparing the databases and users, but without result.

While my toplists, oscommerce and adsnew work fine, all my Postnuke sites have trouble connecting, as well as phpmyAdmin:

I have installed a phpmyadmin on one of my domains. Before, I could login with admin and the plesk password and see and modifiy all databases.
Now, I can still login with the admin password, I can still see all databases, but it won't show me any detail view (browse) of table's contents.
The phpmyAdmin built-in into plesk works fine.

I tried the mysql console, and I can login with all users fine.

I read about the old_password thing, but this isn't the problem.

Any ideas?

 

[wagnerch]

Post your /etc/my.cnf file. Clients typically only connect in two manners:

a. unix socket
b. tcp/ip socket

If the path to the unix socket has changed then that is a problem. You can verify what the client library was built with by running "mysql_config --socket --port"

If the clients are calling out a specific port= in the connect string, then I believe it will use networking. If that is the case then verify "skip-networking" is not set in your /etc/my.cnf file. Personally I recommend using "skip-networking" and having all of the clients connect through the unix socket.

 

[manarak]

here it is:

[mysqld]
old_passwords=1
thread_concurrency=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
#skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=80
key_buffer=150M
table_cache=512
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
join_buffer=1M
max_allowed_packet=16M
record_buffer=1M
max_connect_errors=10
set-variable=key_buffer=128M
set-variable=max_allowed_packet=8M
set-variable=table_cache=128
set-variable=sort_buffer=32M
set-variable=join_buffer=32M
set-variable=record_buffer=2M
set-variable=thread_cache=8
set-variable=read_buffer=1M
set-variable=max_connections=300
set-variable=thread_concurrency=2
set-variable=myisam_sort_buffer_size=128M
set-variable=read_rnd_buffer_size=1M
skip-show-database
myisam_sort_buffer_size=64M
log-bin
server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

 

[wagnerch]

You can verify what the client library was built with by running "mysql_config --socket --port"

 

[manarak]

/var/lib/mysql/mysql.sock
3306

But all my postnuke sites connect on localhost from the same server

 

[manarak]

There seems to be a problem with plesk as well: sometimes the index.php is found, and sometimes I get a "page cannot be displayed" error

If I enable and disable the domain in the pleask control panel, the index.php is found again, and then disappears again after what seems to be the apache reload period.

Deactivating the page again and back in the plesk panel makes it reappear (with the connection errors).

Could it be a problem with resolution of localhost, I mean that localhost is not found?

 

[manarak]

this is nuts!

I get
Fatal error: mysql error: [1045: Access denied for user 'website'@'localhost' (using password: YES)] in CONNECT(localhost, '****', '****', mydb) in /home/httpd/vhosts/sdfgh.ch/httpdocs/includes/classes/adodb/adodb-errorhandler.inc.php on line 77

from the script, while I am able to logon just fine into the mysql console witht he same credentials!!!
(bangs head against walls)

 

[manarak]

should there be something in the mysql.host table?
because mine is empty.

please help! I am clueless.

 

[wagnerch]

No the host table is normally empty, take a look at user table:

select Host, User from mysql.user;

localhost or % should be in there as the host.

 

[manarak]

yes, there is localhost everywhere.

why do some scripts work ok and some not?
postnuke doesn't work
phpmyadmin now is broken: the error "not a valid MySQL result resource" come immediately after login.

 

[wagnerch]

Did you upgrade from say 3.23 to 4.0 or 4.1? If you upgraded to 4.0 did you run mysql_fix_priviledge_tables?

http://dev.mysql.com/doc/refman/4.1/en/mysql-fix-privilege-tables.html

 

[manarak]

solved!

I removed mod_auth_mysql and everything is back to normal.

 

[wagnerch]

Glad to hear that solved your problem, oddly I don't know why because mod_auth_mysql is supposed to be for authenticating users (via the .htaccess Auth* directives) through a mysql database. Perhaps the errors were actually from mod_auth_mysql and not PostNuke and the other scripts.

 

[manarak]

yes, I think when trying to auth the php scripts, mod_auth was crashing and crashing the apache child pid with it.

now we only have to find out why mod_auth was crashing.
I read something about duplicate user entries. In fact, I have 2 horde users, one on % and one on localhost.
Can that be the cause?