Constant DB errors

[Steve Machol]

This error means that either:

1) The db info in your config.php file is wrong (in which case your forums won't work at all.)

2) That user doesn't have permission to access the database listed in config.php.

3) The database doesn't exist.

4) MySQL isn't accepting new connections. (The server could either be down or has reached it's maximum connections limit.)

If you are sure your config.php info is correct, then you will need to contact your host about this.

Note, many servers are being probed for the phpBB worm these days and the MySQL server is being overloaded. This could account for these errors.

[George L]

Well, everything was ok until I started getting tons of Link-ID == false, connect failed errors. Are there any issues with php 4.3.11 or mySQL 4.0.24 that would cause this? I've NEVER had this error before after the forums were running. I increased max_connections from 400 to 650. Are there any variables I need to be concerned with changing with this upgrade??

head on over to server config forum and post info asked at http://www.vbulletin.com/forum/showthread.php?t=70117 to see if your server needs tuning

the error message also can me mysql crashed under load too

[Blairwarlock]

Ok, will do thanks. I can't imagine it was load or even max_conn's as the system wasn't running for more than an hour after the upgrade before the errors began. This is why I was suspecting a bug. But if it occurs again, I will post in Server Config forum.

[George L]

an hour after what upgrade ???

edit: oh ya meant software upgrades.. nah that wouldn't necessary cause this

[Blairwarlock]

Ok, updated both php and mySQL as recommended. Upgrade went smooth as usual. If we can make it 48hrs w/o a hiccup then we've solved the corruption issue. Will keep you posted. Thanks again!

Morning update. For about 20 minutes (1:20am to 1:40am) the 127 error re-surfaced...aargh This time, the following error was also generated:

mysql error: Incorrect key file for table: 'post'. Try to repair it
mysql error number: 1034

It corrected itself. I ran a check this morning and there was no corruption. So now that I have updated both php and mySQL and still experiencing the error...what next? Is there some internal corruption that I am unable to fix with mysqlcheck or myisamchk? Any assistance would be helpful. TIA.

[George L]

hmm when was last time you backed up your forums ? and how did you back it up ? mysqldump ? or physically moving data files ? corruption could be due to faulty hardware too i.e bad memory modules etc

try this

1. close forums
2. create new empty database
3. type in ssh telnet

mysqladmin -u root -p flush-tables

then type

mysqldump --opt -u root -p forumdatabasename > /path/to/backup.sql

then type

mysql -u root -p newemptydatabasename < /path/to/backup.sql

4. now edit config.php for vB and change dbname to newemptydatabasename

and run that - doing a backup and restore and fix possible file structure corruptions in database files

[Blairwarlock]

hmm when was last time you backed up your forums ? and how did you back it up ? mysqldump ? or physically moving data files ? corruption could be due to faulty hardware too i.e bad memory modules etc

Outside of tape backup, forums are manually backed up via mysqldump weekly. I will try your suggestions. Thanks!

[Blairwarlock]

and run that - doing a backup and restore and fix possible file structure corruptions in database files

Ok, done. How long should loading an 860MB database take? Took several hours to complete. Perhaps my memory allocation was not optimized. In any case, once it loaded I checked the file count and disk space taken by the actual files/tables. While the file count is the same, the new load takes up about 30 more MB of disk. Interesting...wonder if there was some corruption in the old database that was repaired during the load?

[George L]

with tape backup does it just backup the mysql data partition files while mysql is still running ? i.e. copy/transfer the *.myd, *.myi, *.frm ? if so that that's a possible cause of your corruption.

As to how long it should take it depends on cpu power, disk speed and mysql memory buffer allocations as to how long it's hard to say.

example 1.4+ GB vB database of mine

dual AthlonMP 2400+ with 2GB ram and 10k scsi disks, took around 45-55mins to import into empty database

2x server setup with web and db server both dual P4 3.06ghz Xeon with 3GB ram and raid 5 10k scsi disks took around 16-19mins to import

[Blairwarlock]

with tape backup does it just backup the mysql data partition files while mysql is still running ? i.e. copy/transfer the *.myd, *.myi, *.frm ? if so that that's a possible cause of your corruption.

As to how long it should take it depends on cpu power, disk speed and mysql memory buffer allocations as to how long it's hard to say.

example 1.4+ GB vB database of mine

dual AthlonMP 2400+ with 2GB ram and 10k scsi disks, took around 45-55mins to import into empty database

2x server setup with web and db server both dual P4 3.06ghz Xeon with 3GB ram and raid 5 10k scsi disks took around 16-19mins to import

The load was done from a local mysqldump so we did not use tape backup.

Any ideas on why the size of the new database is greater than the dump it was taken from? Btw, so far so good!

[Marco van Herwaarden]

While the file count is the same, the new load takes up about 30 more MB of disk.

When compairing the size, are you compairing to another (or old) directory on the same server?

If not, the actually disk size of a file can depend very much on OS, OS-settings or disk.

[Blairwarlock]

with tape backup does it just backup the mysql data partition files while mysql is still running ? i.e. copy/transfer the *.myd, *.myi, *.frm ? if so that that's a possible cause of your corruption.

As to how long it should take it depends on cpu power, disk speed and mysql memory buffer allocations as to how long it's hard to say.

example 1.4+ GB vB database of mine

dual AthlonMP 2400+ with 2GB ram and 10k scsi disks, took around 45-55mins to import into empty database

2x server setup with web and db server both dual P4 3.06ghz Xeon with 3GB ram and raid 5 10k scsi disks took around 16-19mins to import

Update: more errors ths morning my users are getting VERY upset. i'm really at a loss here. errors this morning are the same 127 as I was getting earlier. any suggestions please??

[Blairwarlock]

Update: I did some more troubleshooting and noted the start time of the DB errors over the last week. Seems like errors start between midnight and 2AM every night consistently. This is obviously a time when there's little load on the server with regards to forum users. I did discover that corruption ONLY occurred in the middle of the night..strange. So, what's the first thing anyone would think about that happens each night during off-time? Backups! I called my ISP and sure enough they recently switched over to a new backup platform 2 weeks ago when the corruption began. This new platform seems to NOT be compatible with mySQL open files . Corruption would ONLY occur in 2 tables...post and postindex. We're going to exclude my forum DB tonight to see if we still get corruption. This is good and bad. If in fact this new backup platform is unable to backup mySQL then my nightly backups are worthless and will only be as good as my last weekly dump

Will keep you updated.

[George L]

Update: I did some more troubleshooting and noted the start time of the DB errors over the last week. Seems like errors start between midnight and 2AM every night consistently. This is obviously a time when there's little load on the server with regards to forum users. I did discover that corruption ONLY occurred in the middle of the night..strange. So, what's the first thing anyone would think about that happens each night during off-time? Backups! I called my ISP and sure enough they recently switched over to a new backup platform 2 weeks ago when the corruption began. This new platform seems to NOT be compatible with mySQL open files . Corruption would ONLY occur in 2 tables...post and postindex. We're going to exclude my forum DB tonight to see if we still get corruption. This is good and bad. If in fact this new backup platform is unable to backup mySQL then my nightly backups are worthless and will only be as good as my last weekly dump

Will keep you updated.

ah ha yes as suspected... you just will have to cron job or use a backup script run by cron which invokes mysqldump for safely backing up your forums.

[Blairwarlock]

When compairing the size, are you compairing to another (or old) directory on the same server?

If not, the actually disk size of a file can depend very much on OS, OS-settings or disk.

The two folders were on the same disk. I essentially took a dump of my DB, loaded it to a new DB on the same disk and compared the two.