Worst case scenario.

Regular Joined: Jan 18, 2011 Posts: 86

Hi all,

I've been stupid in a few ways. I was experimenting with Evo Xtreme. and I made a database which name was similar to the one I allready had for my live site.

It corrupted my whole database, and I can't seem to restore it back to the way it was.

I have a database dump of the day before, and tried a clean install and importing the file.

However, it is giving me errors.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `nuke_bbsessions_keys` (
`key_id` varchar(32) NOT NULL default '0' at line 5 .

I don't know if it is possible to restore things back to usual, but for the time being I think i'll have to stick to a clean install, because it doesn't seem to work for me atm.

I also tried leaving the nuke_bbsessions_keys out of the database dump file and try to upload that file, no joy.

I think i learned my lesson from this: NEVER EVER name your different databases similar, because it will f*** you up if you make this 1 little mistake I made.

My question is: Does anybody have a positive idea on what to do?

best regards,
rrclansite / actnreactgaming

Posted: Sun Dec 04, 2011 2:07 pm

oh btw, lets say I'd only want to have the registered users back and the forum posts back injected in my new install. Which tables would i have to select and if its giving the same errors as above what to do?

I have the bbcode extension mod installed by Loki if that is any help

Site Admin Joined: Jun 04, 2004 Posts: 6432

The SQL isn't complete - either your source file has a missing or an extra quote / single quote. You might try using the SQL from your distributions installation file to create that one table (or manually create it using phpMyAdmin). Then remove all SQL related to creating that table and process the rest (oh, make sure to backup the SQL before editing it).

Posted: Sun Dec 04, 2011 2:22 pm

i'm no SQL hero, however parts of your tip have allready been done (that is if i interpret your meaning correctly).

Quote:

You might try using the SQL from your distributions installation file to create that one table

Do you refer to making a clean install (like I did atm) and try to reconstruct the information using the clean install tables?

however i've no clue about where to begin.

Posted: Sun Dec 04, 2011 2:34 pm

It looks like your database dump was edited since something about the nuke_bbsessions_keys creation was messed up. You could
1. do a clean install
2. delete everything but that table
3. delete everything in the database dump related to creating that table (but not the insert statements)
4. restore the database dump

Posted: Sun Dec 04, 2011 2:42 pm

ok so let me get this straight:

1. clean install (done)
2. delete everything but that table (meaning deleting basically the whole database apart from that table?)
3. Leave out the nuke_bbsessions_keys from the database dump? or edit the database dump? I've no clue how to edit a database dump tbh.
4. If I don't understand 3, 4 means a blank stare.

best regards, and thanks for the initial help so far !

Posted: Sun Dec 04, 2011 3:04 pm

oh btw i found the table thingymajub for the nuke_bbsessions_keys:

--
-- Table structure for table `nuke_bbsessions_keys`
--

DROP TABLE IF EXISTS `nuke_bbsessions_keys`;
CREATE TABLE `nuke_bbsessions_keys` (
`key_id` varchar(32) NOT NULL default '0',
`user_id` mediumint(8) NOT NULL default '0',
`last_ip` varchar(8) NOT NULL default '0',
`last_login` int(11) NOT NULL default '0',
PRIMARY KEY (`key_id`,`user_id`),
KEY `last_login` (`last_login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `nuke_bbsessions_keys`
--

Posted: Mon Dec 05, 2011 9:33 am

i now notice that i was incomplete when posting the above post.

Lets say I would only want the insert statements of the error creating tables to remain in the database dump currently available to me. would it look something like this? :

--
-- Table structure for table `nuke_bbsessions_keys`
--

DROP TABLE IF EXISTS `nuke_bbsessions_keys`;
CREATE TABLE `nuke_bbsessions_keys` (
`key_id` varchar(32) NOT NULL default '0',
`user_id` mediumint(8) NOT NULL default '0',
`last_ip` varchar(8) NOT NULL default '0',
`last_login` int(11) NOT NULL default '0',
PRIMARY KEY (`key_id`,`user_id`),
KEY `last_login` (`last_login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `nuke_bbsessions_keys`
--

/*!40000 ALTER TABLE `nuke_bbsessions_keys` DISABLE KEYS */;
LOCK TABLES `nuke_bbsessions_keys` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `nuke_bbsessions_keys` ENABLE KEYS */;

where the red text is the text which I would have to delete?

Hope to hear from you guys soon
best regards,
rrclansite / actnreactgaming

Posted: Mon Dec 05, 2011 9:45 am

Yes, you would delete the red text if the table were manually created before you run the dump restore. However, I'm not sure what the LOCK TABLES is used for. Why would that table be locked for WRITE? You might remove that from the database dump, too.

Posted: Mon Dec 05, 2011 9:53 am

well this is the database dump recovered from the day before the corruption (if i'm not mistaking)

however it first gives a lock command and then after that an unlock command.

So lets say i would go into PHPmyAdmin. import the whole file without the red text. Would it work?

I also tried this what i just described above but it is popping another error in another table, but lets say for arguements sake I would remove that aswell, untill there are no more syntax errors in the table drop commands. Would the insert commands do the job and restore the information like it should?

thanks in advance
best regards,
rrclansite / actnreactgaming

Posted: Mon Dec 05, 2011 10:17 am

It should if you've created the table before loading the database dump.

Posted: Mon Dec 05, 2011 10:20 am

yes i'm importing the files into my clean install, i figured that i would do a clean install and try loading the database dump of the day before the corruption into that database. Ill try to figure it out.

kguske thanks m8 you're a hero!

best regards,
rrclansite / actnreactgaming

/e:

It worked! thanks again for the help Wave

keep amazing us with your brilliant insights kguske!

Posted: Mon Dec 05, 2011 10:31 am

Glad it worked, but no brilliant insight on this, I'm afraid. I still don't know why the database dump couldn't be fully imported...
Confused

Posted: Mon Dec 05, 2011 11:55 am

well i could post the error creating phrases here, to see if you can work it out?
perhaps it could be usefull in the future.

Anyways here's a summary of the steps i took in order to "repare" my corrupted database:

pre-requisites:

database dump of a previous non-corrupted state
Fresh install of a Raven's Nuke website on a complete new database
this one isn't so much neccessary aswell as it might be a good idea: install all the modules you installed to your website before the corruption of the database to the fresh install aswell.

The following steps will apply to you restoring your website to a previous point before the corruption of the database:

Create a new database with an user and a password (if you haven't done so allready)
edit your config.php file with the corresponding data of the newly created database
re-upload config.php to your root folder
if you renamed your INSTALLATION folder when you first installed your website rename it back to INSTALLATION, if you deleted the INSTALLATION folder re-upload it to your webhost in your root folder
point your webbrowser to: [ Only registered users can see links on this board! Get registered or login! ]
follow all steps related to your installation
go to the cpanel of your webhost and find the MySQL area
go into the phpMyAdmin panel of your new database.
use the import feature of phpMyAdmin to import your database dump into the new database.
If all goes well without sprouting any errors, you now have restored your website to a previous point in time. With a bit of good fortune not many / no posts on your forums have gone lost because the previous state was close to the actual corruption of the database.

if 9 does in fact sprout errors with regard to syntax execute the following steps:

Hypothetically speaking: lets say you would get this error trying to insert your database dump file into your new database:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `nuke_bbsessions_keys` (
`key_id` varchar(32) NOT NULL default '0' at line 5 .

what you do is the following:

extract the database dump file (they mostley come zipped or rarred out of your webhost) to your desktop (or any folder to your liking)
Make sure you have a text editor which you like (I would personally recommend NotePad++)
open the extracted dump file with your text editor (the extracted dump file will probably have a .dmp file extension)
look for CREATE TABLE nuke_sessions_keys by pressing CTRL + F and writing in the search field: nuke_sessions_keys
delete this:
--
-- Table structure for table `nuke_bbsessions_keys`
--

DROP TABLE IF EXISTS `nuke_bbsessions_keys`;
CREATE TABLE `nuke_bbsessions_keys` (
`key_id` varchar(32) NOT NULL default '0',
`user_id` mediumint(8) NOT NULL default '0',
`last_ip` varchar(8) NOT NULL default '0',
`last_login` int(11) NOT NULL default '0',
PRIMARY KEY (`key_id`,`user_id`),
KEY `last_login` (`last_login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `nuke_bbsessions_keys`
--
keep:
/*!40000 ALTER TABLE `nuke_bbsessions_keys` DISABLE KEYS */;
LOCK TABLES `nuke_bbsessions_keys` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `nuke_bbsessions_keys` ENABLE KEYS */;
save the edited dump file as: editeddatabasedump.dmp (or anything else as long as you save it with .dmp in the end, else you won't get a dump file)
import the new dump file with phpMyAdmin into your new database like as in 9
if you still get an error repeat these steps above untill importing your edited database dump files succeed