MySQL Error logs after upgrade

[phatPhrog]

Just found these errors in the mysql.log. (MySQL 4.1.5 ART)

051214 12:26:26 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run

051214 12:26:26 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
/usr/libexec/mysqld: ready for connections.

Hopefully the mysql.user table error has been corrected after running:

Code: Select all

/usr/bin/mysql_fix_privilege_tables --user=admin --password=`cat /etc/psa/.psa.shadow`

The script ran and completed with "done" with no errors, so I'm sure that did the trick.

There is no mysql.time_zone_leap_second table in /mysql.

Is this created solely dependent on the time zone selected in the Plesk control panel or do I need to keep searching for the fix?

Also this is on a new 7.5.4-R server with migrated clients/sites. Were these errors a result of the migrated data not being updated?

[scott]

Ive never even heard of that one to be honest.

[phatPhrog]

Off the wall..... could this be another 1and1 issue? (failed to mention our 1and1 server)

The "mysql_fix_privilege_tables" has resolved both errors.

We have test server we just re-imaged to be sure it wasn't something we did, and found the error, ran yum update, found the errors on it. Ran the fix and all was corrected again.

[scott]

Sounds like its just a mysql old->new table update, and you're just seeing it from a different perspective. Good news is it sounds like its all fixed up.

[phatPhrog]

Sounds? Yes, it did appear to sound good. Great for that matter, although we were unaware at the time of our last post that we had no control of new database driven site creation, until later.

Since we last posted, we've been using a test server (after first restoring our production server) to try to see what's up with this.

For those of you that may or might experience this problem and are using Plesk 7.5.4 - DO NOT USE

Code: Select all

/usr/bin/mysql_fix_privilege_tables --user=admin --password=`cat /etc/psa/.psa.shadow`

or you may find yourselves in the same fix of lost connections to current databases and/or the ability to create new database driven sites.

We tested this by using our 1and1 server re-image and then running yum update for php and mysql. When the SQL error showed up, we ran the mysql fix and found ourselves back in the same place.

We then re-imaged and manually upgraded the server using (ART) php and mysql via rpm and found no problems, again and until we ran the mysql_fix.
Once that command is run (with us anyway), we loose connection to current databases and also unable to create new database driven sites.

Up to this point we have manually rebuilt the server to php 4.4.0-3.rhfc2.art and mysql 4.1.12-1.rhfc2.art, and again have no problems other than the mysqld.log entry listed below. Our next step is to run a yum update to the latest ART to see if the problem still exists. Of course without running the fix.

Currently we are running three domains on the test server and the only error we are receiving is:

Code: Select all

051224 19:19:00 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run

No matter, we are having no problems with the server and/sites presently.

When going through these steps we tried it with and without using mod_security, firewalls or other security precautions.

We're still unsure whether it is a php and mysql problem or a 1and1 thing but we're determined to find out one way or another. Reflecting on a previous post on ART by Scott (not verbatim) -- don't blame SW-Soft....it's a xxxxx problem.

Thank you, Scott - you're not unappreciated, but most respected for your hard work and determination.

[scott]

I put together some new php 5/mysql 4.1 rpms in the atomic-testing channel, specifically with this thread in mind. These packages are based on the FC4 and are natively built against the mysql 4.1 libraries. In addition they support the php-mysqli module along with the php-mysql module.

[phatPhrog]

Thanks Scott.

...based on the FC4

Now you say based on, but these tests will work on an FC2 box?

Just to refresh: - We are running a 1and1 server, Plesk 7.5.4r with all patches and updates applied and no 1and1 or Plesk firewall active.

We have Mod_security 1.9.x with gotroot rules applied and rkhunter (ART). Since the box could be re-imaged at any given time there are no other external sources to cause problems.

I ran yum update on this test box a few days ago so it is current with php 4.4.1 and mysql 4.1.15.

With that in mind and since the testing archives are just that....tests, I can see a re-image is in order.

This said because after running yum update and getting the following (for obvious reasons):

....Unable to satisfy dependencies
Package php-xslt needs php = 4.4.1-1.rhfc2.art, this is not available.
Package php-domxml needs php = 4.4.1-1.rhfc2.art, this is not available.

It's great having a non-production server for this kind of thing.

Other than the obvious php and mysql errors (should any crop up) are there any particular areas I should pay attention to after applying 5.0 that would help make for better reporting, if feedback is necesssary?

[phatPhrog]

As busy as you are we decided to go ahead and re-image the server as it takes so little time.

After re-imaging we updated plesk and installed mod_security, ran a yum update from fedora and then added the ART channels and ran yum update again.

This is the result.

Code: Select all

 etc]# yum check-update
Gathering header information file(s) from server(s)
Server: Atomic Rocket Turtle - 2 - ART Tests
Server: Atomic Rocket Turtle - 2 - Atomic PSA-Compatible RPMS
Server: Atomic Rocket Turtle - 2 - Atomic PSA App Vault RPMS
Server: Atomic Rocket Turtle - 2 - Base OS RPMS mirror

Server: Fedora Legacy utilities for Fedora Core 2
Server: Atomic Rocket Turtle - 2 - SW-Soft PSA 7.5 RPMS
Server: Atomic Rocket Turtle - 2 - OS Update RPMS mirror
Finding updated packages
Downloading needed headers
Name                                Arch   Version                  Repo
--------------------------------------------------------------------------------
Sitebuilder                         i386   2.1.1-fc2.build051014.20 psa-7.5
js                                  i386   1.5rc6a-1.rhfc2.art      atomic
mysql                               i386   4.1.16-1.rhfc2.art       Art-Test
mysql-server                        i386   4.1.16-1.rhfc2.art       Art-Test
openssh                             i386   3.9p1-2.rhfc2.art        atomic
openssh-clients                     i386   3.9p1-2.rhfc2.art        atomic
openssh-server                      i386   3.9p1-2.rhfc2.art        atomic
php                                 i386   5.0.4-11.rhfc2.art       Art-Test
php-domxml                          i386   4.4.1-1.rhfc2.art        atomic
php-imap                            i386   5.0.4-11.rhfc2.art       Art-Test
php-mbstring                        i386   5.0.4-11.rhfc2.art       Art-Test
php-mysql                           i386   5.0.4-11.rhfc2.art       Art-Test
php-pear                            i386   5.0.4-11.rhfc2.art       Art-Test
php-pgsql                           i386   5.0.4-11.rhfc2.art       Art-Test
php-xslt                            i386   4.4.1-1.rhfc2.art        atomic
phpBB                               noarch 2.0.18-1                 atomic-app-vault
psa-migration-manager               i586   7.5.4-fc2.build75050930.11psa-7.5
psa-watchdog                        i586   1.0.0-fc2.build75050926.17psa-7.5
sablotron                           i386   1.0.2-3.rhfc2.art        atomic
spamassassin                        i386   1:3.1.0-4.rhfc2.art      atomic

Code: Select all

=-=-=-=-=-=-=-=-===========
 etc]# yum update
Gathering header information file(s) from server(s)
Server: Atomic Rocket Turtle - 2 - ART Tests
Server: Atomic Rocket Turtle - 2 - Atomic PSA-Compatible RPMS
Server: Atomic Rocket Turtle - 2 - Atomic PSA App Vault RPMS
Server: Atomic Rocket Turtle - 2 - Base OS RPMS mirror
Server: Fedora Legacy utilities for Fedora Core 2
Server: Atomic Rocket Turtle - 2 - SW-Soft PSA 7.5 RPMS
Server: Atomic Rocket Turtle - 2 - OS Update RPMS mirror
Finding updated packages
Downloading needed headers
Resolving dependencies
.....Unable to satisfy dependencies
Package php-domxml needs php = 4.4.1-1.rhfc2.art, this is not available.
Package php-xslt needs php = 4.4.1-1.rhfc2.art, this is not available.

[scott]

Yeah its based on the FC4 design, which changes things around a bit. GD has been separated into php-gd, php-xslt and php-domxml have been more or less merged into php-xml and php-xmlrpc. Its annoying I know, but this appears to be the new standard.

[phatPhrog]

It seems whether 4.4.1-1.rhfc2.art is installed or not the php-xslt, php-domxml dependency errors pop up.

Tried it with and without them installed.

I'll keep trudging along.

Thanks again.

[amira]

I have the same errors I just noticed in my log files.
How would one be able to run the mysql_fix_privielege_tables if they did not know the password for mysql admin?
Thanks, Amira.

Just found these errors in the mysql.log. (MySQL 4.1.5 ART)

051214 12:26:26 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run

051214 12:26:26 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
/usr/libexec/mysqld: ready for connections.

Hopefully the mysql.user table error has been corrected after running:

Code: Select all

/usr/bin/mysql_fix_privilege_tables --user=admin --password=`cat /etc/psa/.psa.shadow`

The script ran and completed with "done" with no errors, so I'm sure that did the trick.

There is no mysql.time_zone_leap_second table in /mysql.

Is this created solely dependent on the time zone selected in the Plesk control panel or do I need to keep searching for the fix?

Also this is on a new 7.5.4-R server with migrated clients/sites. Were these errors a result of the migrated data not being updated?

[phatPhrog]

Just run:

Code: Select all

/usr/bin/mysql_fix_privilege_tables --user=admin --password=`cat /etc/psa/.psa.shadow`

[amira]

Hi,

I get the following response.
# /usr/bin/mysql_fix_privilege_tables --user=admin --password='cat /etc/psa/.psa.shadow'
This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, or the more secure passwords in 4.1

Got a failure from command:
/usr/bin/mysql --no-defaults --force --user=admin --host=localhost --password=cat /etc/psa/.psa.shadow --database=mysql
Please check the above output and try again.

Running the script with the --verbose option may give you some information
of what went wrong.

If you get an 'Access denied' error, you should run this script again and
give the MySQL root user password as an argument with the --password= option
#

So I tried again with the --verbose and got this:

# /usr/bin/mysql_fix_privilege_tables --user=admin --password='cat /etc/psa/.psa.shadow' --verbose
This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, or the more secure passwords in 4.1

You can safely ignore all 'Duplicate column' and 'Unknown column' errors
because these just mean that your tables are already up to date.
This script is safe to run even if your tables are already up to date!

ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)
Got a failure from command:
/usr/bin/mysql --no-defaults --force --user=admin --host=localhost --password=cat /etc/psa/.psa.shadow --database=mysql
Please check the above output and try again.

If you get an 'Access denied' error, you should run this script again and
give the MySQL root user password as an argument with the --password= option
#


I'm on a shared virtual dedicated server of the godaddy variety. Should I replace localhost with my domain name?

Thank you for your help. Amira

[Stucco]

can you login to mysql with this?

Code: Select all

mysql -u admin -p`cat /etc/psa/.psa.shadow`

If not then your /etc/psa/.psa.shadow is missing or has incorrect contents, or your mysql database (user table) was somehow messed up.

Try just

Code: Select all

cat /etc/psa/.psa.shadow

That should print your mysql admin password.

If it can not find the file, then that explains why PSA is having problems.
If it prints your password but you know your password is something else, then the file needs to be updated.
If it prints the correct password, then mysql has somehow changed the mysql database, or changed some other major part of mysql preventing you from logging in. Without the ability to log into mysql as admin to fix this, I'm not really sure where to go from there.

You could try to login with some of your sites usernames and passwords, just to see if you can get in.

Is everybody upgrading experiencing this or just these two?

Stucco

[jahwz]

Is everybody upgrading experiencing this or just these two?
Stucco

FWIW, I just updated php/mysql to the latest versions using yum and after coming across this thread found I have the same error messages in mysqld.log

Anyone know for sure if this is a problem worth fixing with the "mysql_fix_privilege_tables" command? Mine is a production server so having to reinstall everything is pretty much out of the question, would there be an easier way to revert if connection issues pop as the OP mentioned?