MySQL syntax error


#1

I am attempting to move a site from a different host to DH and am unable to import my database dump.

I get the following error:




– Table structure for table pn_admin_category

CREATE TABLE pn_admin_category (
pn_cid INT( 10 ) NOT NULL AUTO_INCREMENT ,
pn_name VARCHAR( 32 ) NOT NULL DEFAULT ‘’,
pn_description VARCHAR( 254 ) NOT NULL DEFAULT ‘’,
PRIMARY KEY ( pn_cid )
) TYPE = MYISAM ;

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 ‘pn_cidint(10) NOT NULL auto_increment,pn_name` varchar(32) NOT NULL defaul’ at line 1


I assume it has something to do with a mismatch between the phpMyAdmin settings at the two sites or the MySQL versions.

Any suggestions where I start looking?

Thanks.

Peter


#2

I have an idea about your problem, but it involves an inconsistency in your post, so I don’t know.

The error message you pasted has " use near 'pn_cidint(10) NOT NULL" whilst the original SQL you provided has "pn_cid` INT(10) "

The inconsistency is between the ’ (apostrophe) and the ` (tick).

My guess on your problem is that you may have accidentally altered the SQL statement and changed a tick to an apostrophe, which SQL does not like.

I really hope that does it, because otherwise I’m stumped.


#3

I noticed that as well, but when I look at the Query itself, it is fine:

CREATE TABLE pn_admin_category (
pn_cid int(10) NOT NULL auto_increment,
pn_name varchar(32) NOT NULL default ‘’,
pn_description varchar(254) NOT NULL default ‘’,
PRIMARY KEY (pn_cid)
) TYPE=MyISAM AUTO_INCREMENT=6 ;

However, when I use my text editor and show invisibles, there is an upside-down red question mark present before many of the variables, including pn-cid. They appear to be functioning as a form of line return.

I suspect that this is an encoding issue and that if I could make those hidden characters vanish, or find the correct encoding, it would be ok.

Hopefully someone will have a clue how I do this.

Peter

The chief cause of problems is solutions.


#4

If it’s just the one table, or a small handful of tables, you could just write out the queries by hand. I know that phpMyAdmin throws in a lot of unnecessary stuff into the queries, so you could probably just trim it all up and it should work fine. Hell, you could actually probably just copy and paste from this posting and use that instead.


#5

[quote]I am attempting to move a site from a different host to DH and am unable to import my database dump.

I get the following error:
[/quote]
I copied the query from here and ran it in Query Browser against my DreamHost hosted MySQL server. It ran just fine with no complaints. The table now exists in my database.

I suggest copying it back as another user suggested. If that still doesn’t work, and you are using phpAdmin, it may be causing a problem.

I really prefer using MySQL Query Browser. You can find information in the wiki about setting up your server so you can have access to it through normal tools.

Be sure to user strong password protection and lock down the access to your specific IP, or at least your ISP.


find / offer local services free
http://www.TheHelpfulNeighbor.com


#6

I spent some time unsuccessfully trying to figure out what was making phpMyAdmin add some invisible control characters that caused the syntax errors. I tried lots of combinations of character encodings at both the db_dump end and the import end, to no avail. Though, as is always the case, I now know much more about encodings than I did - and that’s a plus.

Then I spent time trying to remove the control characters, manually or automatically (I’m using a Mac and BBedit which found and zapped the gremlins for me) but still no joy.

So I gave up and followed the good advice above and copied and pasted.

Fascinating.

I opened the datadump.sql file in BBedit and then copied and pasted the SQL queries in. The file was 6.8MB (168 tables) and I had to do it in multiple chunks. No sweat. That’s what music is for…

I do not understand why using copy and paste into SQL query rather than importing the whole dump avoided the syntax error. I would have understood if it just refused because of the size - but that wouldn’t have given me a syntax error.

Anyway, bottom line:

  1. Copy and paste as smaller SQL queries works.
  2. Communities like this are a positive force for good. Thanks, all.

Peter

The chief cause of problems is solutions.