Page 1 of 2
MySQL Error logs after upgrade
Posted: Sat Dec 17, 2005 12:30 am
by 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?
Posted: Sat Dec 17, 2005 2:10 pm
by scott
Ive never even heard of that one to be honest.
off or on the wall
Posted: Sun Dec 18, 2005 6:56 pm
by 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.
Posted: Mon Dec 19, 2005 12:58 pm
by 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.
Update on this topic and a warning.....
Posted: Sat Dec 24, 2005 9:37 pm
by 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.
Posted: Mon Dec 26, 2005 10:25 am
by 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.
based on FC4
Posted: Mon Dec 26, 2005 4:59 pm
by 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?
Re-Image and yum update-testing
Posted: Mon Dec 26, 2005 8:45 pm
by 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.
Posted: Tue Dec 27, 2005 4:27 pm
by 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.
puzzled
Posted: Tue Dec 27, 2005 4:57 pm
by 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.
Re: MySQL Error logs after upgrade
Posted: Mon Jan 16, 2006 2:08 am
by 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.
phatPhrog wrote: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?
Easy
Posted: Mon Jan 16, 2006 12:20 pm
by phatPhrog
Just run:
Code: Select all
/usr/bin/mysql_fix_privilege_tables --user=admin --password=`cat /etc/psa/.psa.shadow`
Posted: Mon Jan 16, 2006 12:29 pm
by 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
Posted: Wed Jan 18, 2006 2:47 pm
by 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
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
Posted: Sun Feb 19, 2006 1:27 am
by jahwz
Stucco wrote:
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?