UBB.Developers Forums Community Development Programming MySQL and Server Configuration Anyone with great MySql & phpMyAdmin knowledge?

(little background)
Prior to installing 6.3.2, we had -zero- issues with databases or cpu usage. Things ran very smooth. Installed 6.3.2 and from that moment on (9/11/03) this has been a living nightmare. I have tons of error logs (none before 'upgrade')

We allowed 'charcters' in member names, but when someone searched on a charcter like (') an error was generated, and seemingly it would cause member files to be deleted. All charcters were removed from member names (only alpha & numeric now) A trouble ticket was filled out, and it was reported to our host. The error logs have continued, members keep disappearing. IP says we have a corrupt table, host used shell to check and said a table couldn't be repaired. 3 days pass, I learn I can do CHECK TABLE and run it on each table and it reports they're all 'ok', no errors. We're still having problems.

We have a -very- busy forum, and at the moment, I am swamped with 'real life' committments.

My question (and I posed this to IP and to my host, but don't expect answers today on a weekend)

I have a backup from prior to our 'upgrade', and I have backed up since upgrading (problems within that backup though?)

What is the logical thing to do here w/out losing all of our posts & users... that's all I wish to 'preserve' if possible ?

Additionally, because life is very hectic right now, when emptying the polldata table yesterday, I hit 'dump' rather than empty Is there some way in phpMyAdmin to replace that table from the backup I have on my pc here ?

Thanks for your time.

I can't provide a quick answer to your first issues.

Regarding restoring the w3t_PollData table, the way to do that depends on exactly what your backup looks like. Do you have a backup file for that specific table, or does your backup file contain all the database tables? If you're not sure, you can examine the backup file (.sql) in a text editor, and see which commands it contains. The commands to look for are:

DROP - deletes database or table
CREATE - creates database or table
INSERT - inserts rows (data) into a table

Which DROP and CREATE commands are in the backup file?

Other information that may be relevant:
Server platform (Windows or Linux)?
MySQL version?
phpMyAdmin version?

Regarding restoring the w3t_PollData table, the way to do that depends on exactly what your backup looks like. Do you have a backup file for that specific table, or does your backup file contain all the database tables?

[:"blue"]The backup I have prior to this 'upgrade' is a 'full' backup of all tables. [/]

If you're not sure, you can examine the backup file (.sql) in a text editor, and see which commands it contains. The commands to look for are:

DROP - deletes database or table
CREATE - creates database or table
INSERT - inserts rows (data) into a table

Which DROP and CREATE commands are in the backup file?

Other information that may be relevant:
Server platform (Windows or Linux)?
[:"blue"]Unix server [/]
MySQL version?
[:"blue"]Not latest, but will look for that info. [/]
phpMyAdmin version?
[:"blue"]2.2.6 [/]

If unable to 'restore' this table from back up, is it possible to just create a new table to which polldata will 'write' to ? Im not concerned with old 'data' but without that table, current polls won't function properly

The backup was made prior to upgrading to 6.3.2, and you don't have a more recent backup?

You didn't say which version you upgraded from, so depending on that, the w3t_PollData table may have changed enough that the one in the backup would not be compatible with 6.3.2. Dealing with that may be possible but could be messy.

To create an empty w3t_PollData table, just run (from the phpMyAdmin SQL function) the query from createtable.php that creates the table:



1) If your table prefix is something other than "w3t_", change it in the query above.

2) As a precaution, do a complete database backup before running the query.

3) Close your board while doing the above.

Of course, there are probably posts that reference polls that no longer exist, so you'll get database errors from those.

[]members keep disappearing[/]
The MySQL version would be really interesting. If you are running 4.0.x I guess thats the problem.

Dave, I have more recent backups, but no one seems sure where the core of the problem is. I have done 'full' backups, not individual tables. I believe I'm getting the idea from the responses, that I need to do backups of each individual table ?

As for the past polldata, we really don't need it. My only concern is that with that tables dropped, rather than emptied, we now get errors when anyone tries to respond to a poll

I will close the board and add the table as you suggest.

Maze, I'm truly not sure if this is the MySql version number or not. This is on the main page of phpMyAdmin:

Welcome to phpMyAdmin 2.2.6

MySQL 3.23.55 running on localhost as

Dave, PollData problem solved

THANK YOU !!

You're welcome.

Re: backups. I prefer to do full-database backups, rather than backups of individual tables, since it's "tidier". If I needed to restore an individual table on my server, I would first restore the full-database backup to a temporary database. Then I would copy the table from the temporary database to the production database using one of these methods:

1) Backup the table from the temporary database, so that you have an .sql file containing the single table, and then restore it to the production database. This could be done either using phpMyAdmin, or if you have shell access, using the mysqldump and mysql commands.

2) Use the phpMyAdmin "Copy table to (database.table)" function to copy the table from the temporary database to the production database.

That MySQL version (3.23.55) is probably ok, although the newest 3.x one is 3.23.58. My server is running 3.23.56.

I'd recommend upgrading phpMyAdmin if possible. I use the current version 2.5.3.

MySQL 3.23.55 should be fine. I was just wondering, because we lost some users, too. I'm not entirely sure, but think this was a problem with a newer MySQL version.

Dave, thanks for the step by step and the input. I will definitely try that tomorrow once my schedule calms down.

Maze, we never had issue before installing this current threads 6.3.2 We had whatever the last version # was before this one, and just didn't have a single database/missing member issue. Seems the ONLY time there are issues is when someone/several folks do a 'search'. Every member who went 'missing' said they were doing a search right before they were 'deleted'. IP says that cannot be correct, but I don't know what else to say if each deleted user indeed was doing a search prior to disappearing.

So I now have all my tables intact, NONE show any errors, they're all optimized and running fine.. .until the next time the CPU is run up and it crashes the server

well, at our site the last thing the deleted users did was to read a new post. During their U_TempRead field was updated the record was corrupted. This started after our provider did a MySQL update. It's curious that your problem comes from the ubb.threads version update.

At the moment everything is fine again since several days, but we'll have to keep an eye on this problem...

Maze,

I don't know 'what' is the cause of our probelms. I only know that before this 'upgrade', I never had a problem. Again, today, users disappeared, but when I check table, NONE are corrupted.

I honestly don't know what to do (which is why I came here)
IP seems to say 'roll back to previous version', or take out the ability to do searches. But does that means there's a problem with 6.3.2, or with something else (since our database claims it's not corrupt on multiple checks)

do you have access to the MySQL error log (not the one produced by ubb.t)? In our case there never was a corrupted table, because MySQL automatically repairs the table immediately after an error occured. During the repair always one row of the user table is dropped:

030915 21:35:53 Warning: Checking table: './dbXXX/w3t_Users'
030915 21:35:53 Warning: Recovering table: './dbXXX/w3t_Users'
030915 21:35:54 Note: Found 1784 of 1785 rows when repairing './dbXXX/w3t_Users'

Just before that "Checking table" takes place, the triggering error can be found. But not in the MySQL log, this time you have to look into your ubb.t error log.

Well, I dumped everything and installed 6.3.1 again.
I followed another of your links to 'restore' my users, and that worked fine. (deleted the table and then uploaded the backed up one)

I tried the same with the posts, without success, as the file was too large. I'll be happy to merely get the posts table back again and start from scratch with posts.

Above you gave a 'command' to restore my w3t_PollData, unfortunately the same command did not work when I merely changed the name of the table.

Input appreciated !

Ok, found the formula for creating the table, it's in the 'top' of the backup for that table !

Maze, once I've got this up and running Im going to look into that error log and will let you know what I find