Database error when trying to Import a backup...

Worker Joined: Mar 30, 2006 Posts: 111

I ran into a serious problem, I hope someone can help. I had everything working great, installed several modules, backed up the db, and this morning I got a bit carried away while blocking countries and decided to just drop the db and import my last backup... Here's what I got when I tried to do that;

Quote:

Error

SQL query:

# --------------------------------------------------------
#
# Table structure for table 'nuke_league'
#
CREATE TABLE nuke_league(
id int( 10 ) NOT NULL AUTO_INCREMENT ,
name varchar( 255 ) DEFAULT ''' NOT NULL, position int(5) DEFAULT '0 ', email varchar(255), description text, sport varchar(30) DEFAULT ''' NOT NULL ,
curr_sid int( 10 ) DEFAULT '0' NOT NULL ,
active int( 1 ) DEFAULT '0' NOT NULL ,
firstdayofweek tinyint( 2 ) unsigned DEFAULT '1' NOT NULL ,
block_date varchar( 40 ) DEFAULT '%Y-%m-%d' NOT NULL ,
schedule_date varchar( 40 ) DEFAULT '%Y-%m-%d %H:%i' NOT NULL ,
birthday_date varchar( 40 ) DEFAULT '%Y-%m-%d' NOT NULL ,
numschedule smallint( 2 ) DEFAULT '20' NOT NULL ,
numarenas smallint( 2 ) DEFAULT '20' NOT NULL ,
defhteam tinyint( 1 ) unsigned DEFAULT '0' NOT NULL ,
defaultPlayerImg varchar( 50 ) ,
displayGameImg tinyint( 1 ) DEFAULT '0' NOT NULL ,
rowcolor varchar( 30 ) ,
rightblock tinyint( 2 ) DEFAULT '0' NOT NULL ,
autoupdate tinyint( 2 ) DEFAULT '1' NOT NULL ,
matchreport tinyint( 2 ) DEFAULT '1' NOT NULL ,
topicid int( 3 ) DEFAULT '0' NOT NULL ,
autopost tinyint( 2 ) DEFAULT '1' NOT NULL ,
publish tinyint( 2 ) DEFAULT '1' NOT NULL ,
publishleague tinyint( 2 ) DEFAULT '1' NOT NULL ,
playerident tinyint( 2 ) DEFAULT '1' NOT NULL ,
playerdisp int( 10 ) DEFAULT '0' NOT NULL ,
dispseason int( 11 ) unsigned DEFAULT '2390' NOT NULL ,
sort_stat tinyint( 2 ) DEFAULT '1' NOT NULL ,
sort_type tinyint( 2 ) DEFAULT '0' NOT NULL ,
disp_players tinyint( 3 ) DEFAULT '40' NOT NULL ,
interdiv int( 1 ) DEFAULT '1' NOT NULL ,
winimg varchar( 255 ) ,
loseimg varchar( 255 ) ,
PRIMARY KEY ( id ) ,
KEY id( id ) ,
KEY curr_sid( curr_sid )
);

MySQL said: Documentation
#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 '0',
email varchar(255),
description text,
sport var

I have no clue where to start on this and my site is currently down because of it.

Theme Guru Joined: Nov 01, 2003 Posts: 1006

the easy way to deal with this is to open the backup of your database, remove this table and put it on a notepad. Run the rest of your db restore. Then at the end take the table you put on the database and run it manually.

Posted: Wed Oct 18, 2006 11:58 am

Okay, that's sounds reasonable. Thanks

I hate to ask this, but could you describe manually, please?

Posted: Wed Oct 18, 2006 12:01 pm

Just finished applying your suggestion and it worked. I now have a notepad created file with cut and pasted section saved as league.sql

Posted: Wed Oct 18, 2006 12:07 pm

okay. now take this info

Code:

CREATE TABLE nuke_league( 


id int( 10 ) NOT NULL AUTO_INCREMENT , 


name varchar( 255 ) DEFAULT ''' NOT NULL, position int(5) DEFAULT '0 ', email varchar(255), description text, sport varchar(30) DEFAULT ''' NOT NULL , 


curr_sid int( 10 ) DEFAULT '0' NOT NULL , 


active int( 1 ) DEFAULT '0' NOT NULL , 


firstdayofweek tinyint( 2 ) unsigned DEFAULT '1' NOT NULL , 


block_date varchar( 40 ) DEFAULT '%Y-%m-%d' NOT NULL , 


schedule_date varchar( 40 ) DEFAULT '%Y-%m-%d %H:%i' NOT NULL , 


birthday_date varchar( 40 ) DEFAULT '%Y-%m-%d' NOT NULL , 


numschedule smallint( 2 ) DEFAULT '20' NOT NULL , 


numarenas smallint( 2 ) DEFAULT '20' NOT NULL , 


defhteam tinyint( 1 ) unsigned DEFAULT '0' NOT NULL , 


defaultPlayerImg varchar( 50 ) , 


displayGameImg tinyint( 1 ) DEFAULT '0' NOT NULL , 


rowcolor varchar( 30 ) , 


rightblock tinyint( 2 ) DEFAULT '0' NOT NULL , 


autoupdate tinyint( 2 ) DEFAULT '1' NOT NULL , 


matchreport tinyint( 2 ) DEFAULT '1' NOT NULL , 


topicid int( 3 ) DEFAULT '0' NOT NULL , 


autopost tinyint( 2 ) DEFAULT '1' NOT NULL , 


publish tinyint( 2 ) DEFAULT '1' NOT NULL , 


publishleague tinyint( 2 ) DEFAULT '1' NOT NULL , 


playerident tinyint( 2 ) DEFAULT '1' NOT NULL , 


playerdisp int( 10 ) DEFAULT '0' NOT NULL , 


dispseason int( 11 ) unsigned DEFAULT '2390' NOT NULL , 


sort_stat tinyint( 2 ) DEFAULT '1' NOT NULL , 


sort_type tinyint( 2 ) DEFAULT '0' NOT NULL , 


disp_players tinyint( 3 ) DEFAULT '40' NOT NULL , 


interdiv int( 1 ) DEFAULT '1' NOT NULL , 


winimg varchar( 255 ) , 


loseimg varchar( 255 ) , 


PRIMARY KEY ( id ) , 


KEY id( id ) , 


KEY curr_sid( curr_sid ) 


);

go into phpmyadmin and click the sql tab

then copy and paste it there. click go and it should load.

Posted: Wed Oct 18, 2006 12:07 pm

Okay, I took a chance and just pasted the table creation portion into the SQL "Run SQL query/queries on database" in MyAdmin. I pressed "GO" and received the same syntax error.

Should I have done this with a different function or process? If not, after reading the mySQL docs it doesn't really explain my specific error regarding '0'. Can you see what might need changing?

Posted: Wed Oct 18, 2006 12:08 pm

try to just use the part that i added above

Posted: Wed Oct 18, 2006 12:15 pm

btw, if this doesnt work. what version of nuke league is this that you are trying to use? Do you have the original file for it?

Posted: Wed Oct 18, 2006 12:22 pm

just tried the section you posted and received the same error.

version 2.4, and I do have the original file.

Posted: Wed Oct 18, 2006 12:22 pm

here is the standard sql for that table from nuke league 2.4

Code:

CREATE TABLE `nuke_league` (


  `id` int(10) NOT NULL auto_increment,


  `name` varchar(255) NOT NULL default '''''',


  `position` int(5) default '0',


  `email` varchar(255) default NULL,


  `description` text,


  `sport` varchar(30) NOT NULL default '''''',


  `curr_sid` int(10) NOT NULL default '0',


  `active` int(1) NOT NULL default '0',


  `block_date` varchar(40) NOT NULL default '%Y-%m-%d',


  `schedule_date` varchar(40) NOT NULL default '%Y-%m-%d %H:%i',


  `birthday_date` varchar(40) NOT NULL default '%Y-%m-%d',


  `numschedule` smallint(2) NOT NULL default '20',


  `numarenas` smallint(2) NOT NULL default '20',


  `defhteam` tinyint(1) unsigned NOT NULL default '0',


  `defaultPlayerImg` varchar(50) default NULL,


  `displayGameImg` tinyint(1) NOT NULL default '0',


  `rowcolor` varchar(30) default NULL,


  `rightblock` tinyint(2) NOT NULL default '0',


  `autoupdate` tinyint(2) NOT NULL default '1',


  `matchreport` tinyint(2) NOT NULL default '1',


  `topicid` int(3) NOT NULL default '0',


  `autopost` tinyint(2) NOT NULL default '1',


  `publish` tinyint(2) NOT NULL default '1',


  `publishleague` tinyint(2) NOT NULL default '1',


  `playerident` tinyint(2) NOT NULL default '1',


  `playerdisp` int(10) NOT NULL default '0',


  `dispseason` tinyint(2) NOT NULL default '0',


  `sort_stat` tinyint(2) NOT NULL default '1',


  `sort_type` tinyint(2) NOT NULL default '0',


  `disp_players` tinyint(3) NOT NULL default '40',


  `interdiv` int(1) NOT NULL default '1',


  `winimg` varchar(255) default NULL,


  `loseimg` varchar(255) default NULL


  PRIMARY KEY  (`id`),


  KEY `id` (`id`),


  KEY `curr_sid` (`curr_sid`)


) TYPE=MyISAM AUTO_INCREMENT=5 ;

I would try to run this, if yours wont work as I layed it out above. Then if this runs without error, You may need to compare and alter to suit your needs.

Posted: Wed Oct 18, 2006 12:25 pm

Thanks, I appreciate your efforts on this. I just looked at my league.sql (original) and it says "LET NUKE-LEAGUE DO IT ITSELF!!!!"

Be right back...trying your suggestion...

Posted: Wed Oct 18, 2006 12:27 pm

received this message;

MySQL said: Documentation
#1067 - Invalid default value for 'loseimg'

Posted: Wed Oct 18, 2006 12:30 pm

okay, You can try this.

in the read me it says

If you are upgrading, make sure after you have copied the files into the Nuke
directory structure you click on the "League" link/icon. This will
automatically update all the tables that were changed.

Now, since you clearly have a backup of any other nuke leage tables already. I would go and click that. See if it will create what is missing.

let us know what happens.

Posted: Wed Oct 18, 2006 12:37 pm

Yes that appears to have worked (good call!!), the tables have been created, but of course no content is present.

I'm guessing there is a way to infuse my saved content into the tables...yes?

Posted: Wed Oct 18, 2006 12:41 pm

let me ask you this.

in your backup you have of the table from your original backup sql file. Were there any insert into lines containing information you added?

If so, you should be able to copy and paste the insert into lines into your db through phpmyadmin.

Did you check to see if it overwrote all of your nuke league tables or just fixed what you were missing?

Posted: Wed Oct 18, 2006 12:44 pm

Code:

CREATE TABLE nuke_bbcategories (


   cat_id mediumint(8) unsigned NOT NULL auto_increment,


   cat_title varchar(100),


   cat_order mediumint(8) unsigned DEFAULT '0' NOT NULL,


   PRIMARY KEY (cat_id),


   KEY cat_order (cat_order)


);





#


# Dumping data for table 'nuke_bbcategories'


#





INSERT INTO nuke_bbcategories VALUES ( '1', 'General Talk', '10');

this is an example of the way that an insert line would look in your db. If your nuke league overwrote all of your files you will have to open up your db backup and grab the insert into lines from each nuke leage table. then add them using the sql function.

Posted: Wed Oct 18, 2006 12:51 pm

My first effort, as you suggested, was to omit the first section that showed the error and I received other syntax messages beyond that, so I just completely removed the league table and everything came back…except league, obviously. Now, after having league recreate its tables I am left with what appears to be a “new install” of League.

Here is an example of what seems to be data with an insert command from the backup file;

INSERT INTO nuke_league_division VALUES ( '1', '1', '3.0', '');
INSERT INTO nuke_league_division VALUES ( '2', '1', '3.5', '');

Does this mean that I would simple have to paste each of these sections manually and I should regain my data?

Posted: Wed Oct 18, 2006 12:52 pm

yes, if you look at each nuke leage table. There are several. grab all the insert into commands from each one. Do one table at a time. Copy and paste them into the sql function box and press go. It should restore all of your data.

Posted: Wed Oct 18, 2006 12:55 pm

outstanding! now, is there a better way to backup my db so this doesn't happen again? Apparently using the Nuke db backup will not work with League.

Posted: Wed Oct 18, 2006 12:57 pm

I run my backups with the export option in phpmyadmin. I find it more reliable. Please keep in mind that using that wont guarentee that it wont cause an issue again. Sometimes things get out of wack for no apparent reason. At least if it did happen again, you would know how to deal with it so you have no downtime.

Let us know that you have restored your information when you are done please.

Posted: Wed Oct 18, 2006 1:10 pm

Yes, everything was restored perfectly. Thank you very much!!
I'll use Export from now on. I hope this was just a fluke, I can't imagine having to insert several seasons of data everytime I have an issue with the database (luckily this is the first season of using this module).

Again, thank you and great job walking me through this.

Posted: Wed Oct 18, 2006 1:11 pm

Yes, everything was restored perfectly. Thank you very much!!
I'll use Export from now on. I hope this was just a fluke, I can't imagine having to insert several seasons of data everytime I have an issue with the database (luckily this is the first season of using this module).

Again, thank you and great job walking me through this.

Posted: Wed Oct 18, 2006 1:12 pm

no problem. I am just happy that you are set now.