Previous Thread
Next Thread
Print Thread
Rate Thread
Anyone with great MySql & phpMyAdmin knowledge? #260513 10/12/2003 5:55 AM
Joined: May 2003
Posts: 94
kestral Offline OP
Power User
OP Offline
Power User
Joined: May 2003
Posts: 94
(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.

Sponsored Links
Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: 24hourcampfire] #260514 10/12/2003 6:18 AM
Joined: Apr 2002
Posts: 1,768
Dave_L_dup1 Offline
Addict
Offline
Addict
Joined: Apr 2002
Posts: 1,768
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?

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: joeuser] #260515 10/12/2003 6:22 AM
Joined: May 2003
Posts: 94
kestral Offline OP
Power User
OP Offline
Power User
Joined: May 2003
Posts: 94

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

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: 24hourcampfire] #260516 10/12/2003 6:42 AM
Joined: Apr 2002
Posts: 1,768
Dave_L_dup1 Offline
Addict
Offline
Addict
Joined: Apr 2002
Posts: 1,768
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:

Code
CREATE TABLE w3t_PollData (<br />  P_Name varchar(75) NOT NULL default '',<br />  P_Number int(4) unsigned default NULL,<br />  P_IP varchar(75) default NULL,<br />  KEY pollndx2 (P_Name)<br />)


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.

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: 24hourcampfire] #260517 10/12/2003 6:43 AM
Joined: Oct 1999
Posts: 282
Maze Offline
Enthusiast
Offline
Enthusiast
Joined: Oct 1999
Posts: 282
[]members keep disappearing[/]
The MySQL version would be really interesting. If you are running 4.0.x I guess thats the problem.

Sponsored Links
Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: joeuser] #260518 10/12/2003 7:08 AM
Joined: May 2003
Posts: 94
kestral Offline OP
Power User
OP Offline
Power User
Joined: May 2003
Posts: 94
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.

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: 24hourcampfire] #260519 10/12/2003 7:09 AM
Joined: May 2003
Posts: 94
kestral Offline OP
Power User
OP Offline
Power User
Joined: May 2003
Posts: 94
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

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: joeuser] #260520 10/12/2003 7:12 AM
Joined: May 2003
Posts: 94
kestral Offline OP
Power User
OP Offline
Power User
Joined: May 2003
Posts: 94
Dave, PollData problem solved

THANK YOU !!

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: 24hourcampfire] #260521 10/12/2003 9:07 AM
Joined: Apr 2002
Posts: 1,768
Dave_L_dup1 Offline
Addict
Offline
Addict
Joined: Apr 2002
Posts: 1,768
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.

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: 24hourcampfire] #260522 10/12/2003 9:09 AM
Joined: Oct 1999
Posts: 282
Maze Offline
Enthusiast
Offline
Enthusiast
Joined: Oct 1999
Posts: 282
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.

Sponsored Links
Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: mcse007] #260523 10/12/2003 10:52 AM
Joined: May 2003
Posts: 94
kestral Offline OP
Power User
OP Offline
Power User
Joined: May 2003
Posts: 94
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

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: 24hourcampfire] #260524 10/12/2003 12:01 PM
Joined: Oct 1999
Posts: 282
Maze Offline
Enthusiast
Offline
Enthusiast
Joined: Oct 1999
Posts: 282
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...

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: mcse007] #260525 10/13/2003 6:26 PM
Joined: May 2003
Posts: 94
kestral Offline OP
Power User
OP Offline
Power User
Joined: May 2003
Posts: 94
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)

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: 24hourcampfire] #260526 10/14/2003 1:04 AM
Joined: Oct 1999
Posts: 282
Maze Offline
Enthusiast
Offline
Enthusiast
Joined: Oct 1999
Posts: 282
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.

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: joeuser] #260527 10/14/2003 10:49 AM
Joined: May 2003
Posts: 94
kestral Offline OP
Power User
OP Offline
Power User
Joined: May 2003
Posts: 94
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 !

Re: Anyone with great MySql & phpMyAdmin knowledge? [Re: 24hourcampfire] #260528 10/14/2003 11:29 AM
Joined: May 2003
Posts: 94
kestral Offline OP
Power User
OP Offline
Power User
Joined: May 2003
Posts: 94
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


Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
Gizmo
Gizmo
Portland, OR, USA
Posts: 5,795
Joined: January 2000
Forum Statistics
Forums63
Topics37,528
Posts293,719
Members13,834
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online 115
Topics Created 0
Posts Made 0
Users Online 1
Birthdays 27
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,795
Greg Hard 4,625
Top Posters(30 Days)
isaac 4
Gizmo 1
Top Likes Received
isaac 30
Gizmo 8
Brett 7
Morgan 2
Top Likes Received (30 Days)
Gizmo 8
isaac 1
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2020 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Snapshot build 20200126.dev)
Responsive Width:

PHP: 5.4.45 Page Time: 0.049s Queries: 47 (0.020s) Memory: 3.3681 MB (Peak: 3.6069 MB) Data Comp: Zlib Server Time: 2020-01-27 16:26:46 UTC
Valid HTML 5 and Valid CSS