!!!mysql Tables are disappearing!!!

[Berbox]

Hello,

On a completely new CentOS server (kernel 2.6.9-34) I'm having a serious issue with the mysql server 5.0.24.

A random number of mysql tables are just disappearing. It happened now for the third time and the same three databases are affected. The three websites are using third party scripts like Xoops, PhpBB and SMF?
There is one link I can make between the websites namely they were hosted on the same server before they were moved to this new server.

I installed the latest modsec rules and logging the mysql queries at the moment. Weird is that I don't find queries in the log files that are dropping the tables.

I don't see what I can do more?
Please help

 

[rikgarner]

Hi Berbox,
Firstly, what version of MySQL are you running, and secondly, have you attempted a consistancy check on your databases?

Rich

 

[dreamwiz]

exactly same issue here!

It started after going from 4.1 -> 5.0.24. It only affects a handful of accounts (in server with several hundred accounts) and only common software like phpbb, OpenCommerce, SMF, Nucleus. It usually affects the same databases and accounts and looses tables totally random. I've been looking and looking and cant find exact reason. Even swiched to new hardware and now 3 weeks after same thing all over again. I logged in a tt with cpanel today about it if they can find out what's going on. If anyone has any ideas on this they are all more than greatly appreciated!

 

[Berbox]

I upgraded from 4.0.27 to 5.0.24.
The affected databases are larger then 10MB.

Today I moved two off the three accounts to a seperate server hoping to solve the problem.
A consitency check didn't gave any error.

What should be the cause off this??

 

[rikgarner]

Hi guys,

I know from previous experience that the 4.x to 5 upgrade can be problematic - we initially had all sorts of issues on our Ple$k servers when doing the upgrade, however, after searching the forums for a while, found a couple of useful suggestions:

1. Update to the latest version of 4 availiable before then moving to 5.
2. Dump the tables into a backup file, do the upgrade, and re-import them, otherwise you can have issues with grant table permissions, especially if the script which performs the table update encounters errors. Its for this reason that anything like this gets done at shell, so you can see the output.

Please refer to:

http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html
which discusses the incompatibilities between 4 and 5.

Now, fixes. The first thing I would suggest is logging onto your server as root, and running /scripts/mysqlup --force. This will enable you to check that MySQL5 is properly installed and running. Secondly, take a look at:

http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html

This article discusses database updates to 5, in paticular:

mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables

bear in mind that you may have to provide -u root -p switches in these commands depending on how MySQL is secured on your server.

Rich

 

[dreamwiz]

Now, fixes. The first thing I would suggest is logging onto your server as root, and running /scripts/mysqlup --force. This will enable you to check that MySQL5 is properly installed and running. Secondly, take a look at:

http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html

This article discusses database updates to 5, in paticular:

mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables

bear in mind that you may have to provide -u root -p switches in these commands depending on how MySQL is secured on your server.

Rich

Thanks for the tips, even though all that has been done right after the upgrade :P so unfortunately they do not solve the issue. I have also noticed that the databases affected are usually large.

 

[Berbox]

The problem seems to be resolved by putting the websites on different servers.

The command "mysql_fix_privilege_tables" I executed before.

Just for sure I did a forced reinstall of Mysql by executing "/scripts/mysqlup --force".
Afterwards I did "mysqlcheck --check-upgrade --all-databases --auto-repair"

Thanks for the support rikgarner

 

[dreamwiz]

The problem seems to be resolved by putting the websites on different servers.

The command "mysql_fix_privilege_tables" I executed before.

Just for sure I did a forced reinstall of Mysql by executing "/scripts/mysqlup --force".
Afterwards I did "mysqlcheck --check-upgrade --all-databases --auto-repair"

Thanks for the support rikgarner

Just wait, it will come back i did exactly what you did and everything was fine for 3 weeks, then it started again...

 

[dreamwiz]

I've been discussing this with Cpanel and they are investigating this issue. Also MySQL bug has been reported and MySQL folks are looking into the issue: http://bugs.mysql.com/bug.php?id=23506

 

[Berbox]

dreamwiz, you were right.
Yesterday the mysql-server crashed and I was not able to restart the server. I had to do a forced reinstall of mysql again and several other things like recover the root password of the mysql-server.

On one database (50 MB large) several tables where disappeared but mush worser almost every users privilege were disappeared. Unfortunately I didn't backup the Mysql database with his privileges. I had to add all users by hand.

This is a serious issue so any news.
Im am running Dual Xeon 2.8 GHz / 2Gb / SATA drives With Centos 4.3.

Should a kernel upgrade solve this issue??

 

[rpmws]

i have been running the 5.X mysql for a long time now and no new sites have been added to 2 shared servers I am recently been seeing broken tables, crashed tables and so on. I have never seen these problems with mysql before. Like NEVER and now I am getting this isues once every 2 days on more than one site. I am on 5.0.27 and it seems that's a resent upgrade from 5.0.24 that I was NOt having issues with.