Problem creating tables

[Bloodflame]

I'm trying to move a forum to another host, so i dumped up the MySQL database but I can't seem to get it to install on my new host.

 

Here's an example of the code in the SQL file I'm trying to upload

 

CREATE TABLE `bbnboard_auth_access` (
  `group_id` mediumint( NOT NULL default '0',
  `forum_id` smallint(5) unsigned NOT NULL default '0',
  `auth_view` tinyint(1) NOT NULL default '0',
  `auth_read` tinyint(1) NOT NULL default '0',
  `auth_post` tinyint(1) NOT NULL default '0',
  `auth_reply` tinyint(1) NOT NULL default '0',
  `auth_edit` tinyint(1) NOT NULL default '0',
  `auth_delete` tinyint(1) NOT NULL default '0',
  `auth_sticky` tinyint(1) NOT NULL default '0',
  `auth_announce` tinyint(1) NOT NULL default '0',
  `auth_vote` tinyint(1) NOT NULL default '0',
  `auth_pollcreate` tinyint(1) NOT NULL default '0',
  `auth_attachments` tinyint(1) NOT NULL default '0',
  `auth_mod` tinyint(1) NOT NULL default '0',
  KEY `group_id` (`group_id`),
  KEY `forum_id` (`forum_id`)
) ENGINE=MyISAM;

 

I get this error using BigDump (similar error shows up in phpMyAdmin)

 

Error at the line 36: ) ENGINE=MyISAM;

Query: -- phpMyAdmin SQL Dump
CREATE TABLE `bbnboard_auth_access` (
`group_id` mediumint( NOT NULL default '0',
`forum_id` smallint(5) unsigned NOT NULL default '0',
`auth_view` tinyint(1) NOT NULL default '0',
`auth_read` tinyint(1) NOT NULL default '0',
`auth_post` tinyint(1) NOT NULL default '0',
`auth_reply` tinyint(1) NOT NULL default '0',
`auth_edit` tinyint(1) NOT NULL default '0',
`auth_delete` tinyint(1) NOT NULL default '0',
`auth_sticky` tinyint(1) NOT NULL default '0',
`auth_announce` tinyint(1) NOT NULL default '0',
`auth_vote` tinyint(1) NOT NULL default '0',
`auth_pollcreate` tinyint(1) NOT NULL default '0',
`auth_attachments` tinyint(1) NOT NULL default '0',
`auth_mod` tinyint(1) NOT NULL default '0',
KEY `group_id` (`group_id`),
KEY `forum_id` (`forum_id`)
) ENGINE=MyISAM;

MySQL: You have an error in your SQL syntax near '-- phpMyAdmin SQL Dump CREATE TABLE `bbnboard_auth_access` ( `group_id` med' at line 1

 

although when I add this other SQL file for PhpNuke, it works fine. the syntax appears to be the same, so I don't know where the problem is!

 

CREATE TABLE `nuke2_access` (
  `access_id` int(10) NOT NULL auto_increment,
  `access_title` varchar(20) default NULL,
  PRIMARY KEY  (`access_id`)
) TYPE=MyISAM;

 

Could someone please help me!? I can't lose anything from my forum!

[bubblegum.anarchy]

Remove the line that is causing issues since the issue appears to be related to the commenting prefix in a commented line.

[Bloodflame]

the line isnt commented. thats just how the error is displayed.

look closer at the first code in my post.

the error seems to be something to do with this line.

 

) ENGINE=MyISAM;

 

I don't see how thats a problem. I even tried changing ENGINE to TYPE but it gives the same error.

[bubblegum.anarchy]

well.. the error is near the weird characters:

 

MySQL: You have an error in your SQL syntax near '-- phpMyAdmin SQL Dump CREATE TABLE `bbnboard_auth_access` ( `group_id` med' at line 1

[Bloodflame]

thats just in the error. its NOT in the code itself.

 

CREATE TABLE `bbnboard_auth_access` (
  `group_id` mediumint( NOT NULL default '0',
  `forum_id` smallint(5) unsigned NOT NULL default '0',
  `auth_view` tinyint(1) NOT NULL default '0',
  `auth_read` tinyint(1) NOT NULL default '0',
  `auth_post` tinyint(1) NOT NULL default '0',
  `auth_reply` tinyint(1) NOT NULL default '0',
  `auth_edit` tinyint(1) NOT NULL default '0',
  `auth_delete` tinyint(1) NOT NULL default '0',
  `auth_sticky` tinyint(1) NOT NULL default '0',
  `auth_announce` tinyint(1) NOT NULL default '0',
  `auth_vote` tinyint(1) NOT NULL default '0',
  `auth_pollcreate` tinyint(1) NOT NULL default '0',
  `auth_attachments` tinyint(1) NOT NULL default '0',
  `auth_mod` tinyint(1) NOT NULL default '0',
  KEY `group_id` (`group_id`),
  KEY `forum_id` (`forum_id`)
) ENGINE=MyISAM;

 

thats EXACTLY how my SQL file looks, except that its much longer of course.

there's no wierd characters. it only shows up when there's an error. they are caused by the BigDump file.

i appreciate you trying to help me though. maybe you could try to run this code with BigDump on your host to see whats happens? thanks.

 

this is what shows up when trying it with phpMyAdmin

 

You have an error in your SQL syntax near 'CREATE TABLE `bbnboard_auth_access` (
  `group_id` mediumint( NOT NULL defa' at line 1

 

could it be the file encoding? if so, how would i fix it?

[bubblegum.anarchy]

Open the sql dump in vim and see whats what.

[Bloodflame]

OK, so maybe those characters were there when viewed with Vim... stupid notepad and dreamweaver 

 

but the problem is still happening though

 

Error at the line 36: ) ENGINE=MyISAM;

Query: CREATE TABLE `bbnboard_auth_access` (
`group_id` mediumint( NOT NULL default '0',
`forum_id` smallint(5) unsigned NOT NULL default '0',
`auth_view` tinyint(1) NOT NULL default '0',
`auth_read` tinyint(1) NOT NULL default '0',
`auth_post` tinyint(1) NOT NULL default '0',
`auth_reply` tinyint(1) NOT NULL default '0',
`auth_edit` tinyint(1) NOT NULL default '0',
`auth_delete` tinyint(1) NOT NULL default '0',
`auth_sticky` tinyint(1) NOT NULL default '0',
`auth_announce` tinyint(1) NOT NULL default '0',
`auth_vote` tinyint(1) NOT NULL default '0',
`auth_pollcreate` tinyint(1) NOT NULL default '0',
`auth_attachments` tinyint(1) NOT NULL default '0',
`auth_mod` tinyint(1) NOT NULL default '0',
KEY `group_id` (`group_id`),
KEY `forum_id` (`forum_id`)
) ENGINE=MyISAM;

MySQL: You have an error in your SQL syntax near 'ENGINE=MyISAM' at line 18

 

???

[bubblegum.anarchy]

Remove the space between the closing paranthesis and the word ENGINE.

[Bloodflame]

Error at the line 36: )ENGINE=MyISAM;

Query: CREATE TABLE `bbnboard_auth_access` (
`group_id` mediumint( NOT NULL default '0',
`forum_id` smallint(5) unsigned NOT NULL default '0',
`auth_view` tinyint(1) NOT NULL default '0',
`auth_read` tinyint(1) NOT NULL default '0',
`auth_post` tinyint(1) NOT NULL default '0',
`auth_reply` tinyint(1) NOT NULL default '0',
`auth_edit` tinyint(1) NOT NULL default '0',
`auth_delete` tinyint(1) NOT NULL default '0',
`auth_sticky` tinyint(1) NOT NULL default '0',
`auth_announce` tinyint(1) NOT NULL default '0',
`auth_vote` tinyint(1) NOT NULL default '0',
`auth_pollcreate` tinyint(1) NOT NULL default '0',
`auth_attachments` tinyint(1) NOT NULL default '0',
`auth_mod` tinyint(1) NOT NULL default '0',
KEY `group_id` (`group_id`),
KEY `forum_id` (`forum_id`)
)ENGINE=MyISAM;

MySQL: You have an error in your SQL syntax near 'ENGINE=MyISAM' at line 18

 

this is starting to get on my nerves... i just want my forum back

[bubblegum.anarchy]

um.. I don't know, how about replace ENGINE with TYPE.

 

EDIT: FYI - the above create query works perfectly fine via SQLyog

 

and also try removing the backticks from the KEY column names:

KEY `group_id` (group_id),

KEY `forum_id` (forum_id)

[Bloodflame]

ah! it worked just changing ENGINE to TYPE (don't know why I didn't try it again sooner)

 

but now i'm having another problem a little further down the SQL file. that figures

 

Error at the line 2182: ) TYPE=MyISAM AUTO_INCREMENT=4586 ;

Query: CREATE TABLE `bbnboard_search_wordlist` (
`word_text` varchar(50) character set latin1 collate latin1_bin NOT NULL default '',
`word_id` mediumint( unsigned NOT NULL auto_increment,
`word_common` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`word_text`),
KEY `word_id` (`word_id`)
) TYPE=MyISAM AUTO_INCREMENT=4586 ;

MySQL: You have an error in your SQL syntax near 'character set latin1 collate latin1_bin NOT NULL default '', `word_id` mediumi' at line 2

 

this is turning out to be nothing but problems. the last time i moved my forum, the database transferred just fine.

 

thanks a lot for getting me this far!

[Illusion]

as you are trying to impose NOT NULL constraint , it won't accept default '' (which is a NULL value).

[Bloodflame]

previous lines had NOT NULL default, and after fixing the other problems, they worked. i don't see how it would be different on this line.

[Bloodflame]

As far as I know, I think I fixed it by removing character set latin1 collate latin1_bin completely.

it finally went through successfully on my local test server, but I won't know for sure until I try it on my live host.

i'm currently having problems connecting to the FTP because of too many users. I'll post back if there's any more problems.

 

thanks again for helping me out!!