Latest MySQL Broke Plesk

[Intelehost]

..... plesk admin should be as user...

here is a referrence page for setting users:
http://dev.mysql.com/doc/refman/5.0/en/ ... leges.html

run this command to optimize databases, and it will only work if plesk admin is the user...which is what you want...if it doesn't run, then you have to setup plesk admin as user...

Code: Select all

mysqlcheck -uadmin -p`cat /etc/psa/.psa.shadow` --auto-repair --check --optimize --all-databases

I did run this command and it ran successfully. I know I have ran this command before and it has has the same result.

[Kalimari]

Ya I tried running some of these commands and just keep getting syntax errors... I tried reading up on the link you included as well and to be honest its over my head I don't quite understand it.

OK, just to be certain the commands are being run correctly first:
1. SSH to your box and get root privileges
2. Enter: mysql -uadmin -p`cat /etc/psa/.psa.shadow` - should give you a prompt: mysql >
3. Enter: SELECT * FROM mysql.user WHERE User='admin';

Does this give syntax errors?

[Intelehost]

Ya I tried running some of these commands and just keep getting syntax errors... I tried reading up on the link you included as well and to be honest its over my head I don't quite understand it.

OK, just to be certain the commands are being run correctly first:
1. SSH to your box and get root privileges
2. Enter: mysql -uadmin -p`cat /etc/psa/.psa.shadow` - should give you a prompt: mysql >
3. Enter: SELECT * FROM mysql.user WHERE User='admin';

Does this give syntax errors?

This time it worked I am not sure what it means though lol

[root@psa ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow`
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12369
Server version: 5.1.50 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM mysql.user WHERE User='admin';
+-----------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | admin | 18790d4243b7f688 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 |
+-----------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
1 row in set (0.00 sec)

[Kalimari]

OK, that means (Plesk) admin user has all the necessary privileges to be able to create databases/uses in MySQL.

Now you need to flush the privileges to be sure - via SSH:
1. Enter: mysql -uadmin -p`cat /etc/psa/.psa.shadow` - at prompt: mysql >
2. Enter: FLUSH PRIVILEGES;

See if any rows are affected. Running this will at least confirm the issue is not directly related to MySQL privileges.

[Intelehost]

OK, that means (Plesk) admin user has all the necessary privileges to be able to create databases/uses in MySQL.

Now you need to flush the privileges to be sure - via SSH:
1. Enter: mysql -uadmin -p`cat /etc/psa/.psa.shadow` - at prompt: mysql >
2. Enter: FLUSH PRIVILEGES;

See if any rows are affected. Running this will at least confirm the issue is not directly related to MySQL privileges.

It appears not to be related.


[root@psa ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow`
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12686
Server version: 5.1.50 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

[mneese77]

try to setup new DB, and new user through plesk...should be good to go at this point...

[Intelehost]

try to setup new DB, and new user through plesk...should be good to go at this point...

It worked!! but check this out... for some reason now my mysql process is spiking the CPU. One of my customers webpages wont load but the rest will (happened after I did what I did earlier today)

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2137 mysql 20 0 227m 39m 7068 S 197.0 0.7 4:08.88 mysqld

[Kalimari]

197% cpu, that's not good!
First, see what mysql processes are running:

Code: Select all

mysql -uadmin -p`cat /etc/psa/.psa.shadow`
SHOW PROCESSLIST;

Restart: /etc/init.d/mysqld restart and monitor mysql to see if it's a particular query causing the problem.
By default mysql 5.0 reserves 150 process (up from 100), in a virtual environment, this may be significant...

[Intelehost]

197% cpu, that's not good!
First, see what mysql processes are running:

Code: Select all

mysql -uadmin -p`cat /etc/psa/.psa.shadow`
SHOW PROCESSLIST;

Restart: /etc/init.d/mysqld restart and monitor mysql to see if it's a particular query causing the problem.
By default mysql 5.0 reserves 150 process (up from 100), in a virtual environment, this may be significant...

mysql> SHOW PROCESSLIST;
+------+--------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------+-----------------+--------+---------+------+-------+------------------+
| 2 | tortix | localhost:36326 | tortix | Sleep | 10 | | NULL |
| 9 | admin | localhost | psa | Sleep | 156 | | NULL |
| 3654 | admin | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+------+--------+-----------------+--------+---------+------+-------+------------------+
3 rows in set (0.01 sec)


I think it was a particular customers database... its possible that their database is not compatible because when I run "mysqlcheck -u admin -p`cat /etc/psa/.psa.shadow` --auto-repair --check --optimize --all-databases"

it hangs on those databases stating the following

busakills.kb3_all_corp Table is already up to date
busakills.kb3_alliances
note : Table does not support optimize, doing recreate + analyze instead
status : OK
busakills.kb3_api_user Table is already up to date
busakills.kb3_apicache
note : Table does not support optimize, doing recreate + analyze instead
status : OK
busakills.kb3_apilog
note : Table does not support optimize, doing recreate + analyze instead
status : OK
busakills.kb3_comments
note : Table does not support optimize, doing recreate + analyze instead
status : OK
busakills.kb3_config
note : Table does not support optimize, doing recreate + analyze instead
status : OK
busakills.kb3_constellations
note : Table does not support optimize, doing recreate + analyze instead
status : OK
busakills.kb3_contract_details
note : Table does not support optimize, doing recreate + analyze instead
status : OK
busakills.kb3_contracts
note : Table does not support optimize, doing recreate + analyze instead
status : OK
busakills.kb3_corps
note : Table does not support optimize, doing recreate + analyze instead
status : OK
busakills.kb3_dgmattributetypes Table is already up to

[Intelehost]

When I disable that customers website and no one tries to access it there is no problem. As soon as I activate that customers site and try to browse to it the website never loads and mysql spikes and does not come down until I disable the site and restart mysql. Weird thing is... before the upgrade this customers site worked fine and I have never seen mysql spike like this.

[Kalimari]

Well that's good news! Could be whatever mysql code is running on that domain is not compatible with MySQL 5.0.

Table does not support optimize, doing recreate + analyze instead

Means it's probably an InnoDB table and if it's corrupted, mysqlcheck probably isn't fixing it. Take a look in the mysql log for anything relating to that database...

[Intelehost]

sweet! ya it was running 5.0.9 before I upgraded to 5.1.5 so it was compatible with 5 but probably just not 5.1... I have already notified the customer but they will probably just say "switch me back to the other one then lol" at least the server is back up and functional!!

Thank you for all of your assistance, seems that my old approach of if it aint broke don't fix it should have applied here.

[Kalimari]

Glad you are up and running again... I always try and "sell" clients on the performance/features/security benefit of upgrading to current/stable software, otherwise I'll end up with out-of-date, unmaintained web-shites which get owned (ASL really does help avoid this).