ERROR importing SQL table

Hangin' Around Joined: Apr 28, 2006 Posts: 31

Well guys, I am at the end of my rope here, and hoping someone can help me with getting my arcade working once again.

I recently exported the "nuke_bbarcade_comments" table to change a few comments. I changed nothing else except the comments themselves that were wrapped inside quotes.

Once I was done, I then turned right back around and tried to reimport the table I had JUST exported, only to have phpMyAdmin return the following error:

Quote:

Error
SQL query:

-- Table structure for table `nuke_bbarcade_comments`
--
CREATE TABLE `nuke_bbarcade_comments` (

`game_id` mediumint( 8 ) NOT NULL default '0',
`comments_value` varchar( 255 ) NOT NULL default ''
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

MySQL said:

#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_bbarcade_comments` (
`game_id` mediumint( Cool

NOT NULL d' at line 1

Well, I am a bit dumbfounded, as I even tried cubstituting a comments table from a previous backup that had not been touched, and that gave the same problem. I also changed a few things around in the "nuke_bbscores" table and it re-inported just fine.

Here is exactly what the header of my comments table looks like:

Quote:

-- Table structure for table `nuke_bbarcade_comments`
--

CREATE TABLE `nuke_bbarcade_comments` (
`game_id` mediumint( Cool

NOT NULL default '0',
`comments_value` varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--

WTF is going on here guys? Any help is much appreciated. Thanks.

Posted: Sat Nov 11, 2006 6:02 am

That is probably indicating that when you tried to re-import the data, the table already had data in it.
For example, the table already holds data in record 1 so trying to re-import data with the same record id it will fail.

Posted: Sat Nov 11, 2006 7:50 am

First do this: within phpMyAdmin, create a copy of this table, in case something gets messed up. You could also take another export of the structure and data and save it off, but I suggest doing the copy in this case.

It is not liking your table CREATE statement, but it may be because the table already exists and/or the ENGINE or CHARSET settings. Do you have the entire dump of the data with your few changes in it? If so, change your CREATE statement to have this in front of it:

DROP TABLE IF EXISTS `nuke_bbarcade_comments`;

Then try it again, BUT, I cannot emphasize this strong enough, this will REMOVE your table completely from the database, which is why I asked you above to copy it in case we need to copy it back.

If the CREATE does not work, then make your CREATE look like this instead:

Code:




-- Table structure for table `nuke_bbarcade_comments` 


-- 





DROP TABLE IF EXISTS `nuke_bbarcade_comments`;


CREATE TABLE `nuke_bbarcade_comments` ( 


`game_id` mediumint( NOT NULL default '0', 


`comments_value` varchar(255) NOT NULL default '' 


);

Do NOT just copy and paste this in as there is data that was chopped off when you posted the original code (it turned into a smiley). The thing to note, is that I only removed the ENGINE and DEFAULT CHARSET parameters.

I have had issues with this when I have tried to take an export from one mySQL version to my local database of a different version. I've even had differences in phpMyAdmin cause this. The settings end up mot matching and I have to remove/change certain things to get them to work.