Syntax error after show create table in mysql

Question

I just want to copy an existing table structure into another database. All queries are executed in phpmyadmin:

I first execute:

SHOW CREATE TABLE keyword_session

Then I execute the output of it in another database:

CREATE TABLE `keyword_session` (  
`id` int(11) NOT NULL AUTO_INCREMENT, 
`session_id` int(11) NOT NULL,
`keyword_id` int(11) NOT NULL,  PRIMARY KEY (`id`))
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

And I got the error:

#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 'id int(11) NOT NULL AUTO_INCREMENT, Â session_id int(11) NOT NULL, Â `keywor' at line 1

Where is the syntax error ? Isn't SHOW CREATE TABLE be a result that you can execute ?

Edit: Some information on the system where I try to create the table:

Database server :
  Server: Localhost via UNIX socket
  Software: MySQL
  Software version: 5.1.63-0ubuntu0.11.10.1 - (Ubuntu)
  Protocol version: 10
  User: ...
  Server charset: UTF-8 Unicode (utf8)
Web server:
  ...
phpMyAdmin:
  Version information: 3.5.1, latest stable version: 3.5.5
  ...

uhmm, your create table statement is working on fiddle, sqlfiddle.com/#!2/ab087 — John Woo
– John Woo, Commented Jan 23, 2013 at 9:57
Â session_id int(11) NOT NULL, Â has unknown symbol Â in it. — hjpotter92
– hjpotter92, Commented Jan 23, 2013 at 9:59

hjpotter92 · Accepted Answer · 2013-01-23 10:01:19Z

2

According to the error, your statement seems to have unknown symbol Â in it.

Probably, your new-line feed is misinterpreted. I say so, because it is noticeable at the line ends only:

id int(11) NOT NULL AUTO_INCREMENT, Â session_id int(11) NOT NULL, Â `keywor

notice how the symbol appears before session_id and keyword_id declaration?

answered Jan 23, 2013 at 10:01

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Benjamin Crouzier Over a year ago

When I copy-pasted, some strange characters smuggled into the string as spaces. Replacing all spaces/newlines fixed the problem.

hjpotter92 Over a year ago

And this probably was because of UTF-8 and other text format conversions. Happens a lot with me when I port stuff b/w linux and Windows.

vidyadhar · Accepted Answer · 2013-01-23 10:14:21Z

2

The command

show create table table_name;

retrieves the information from the database information_schema which has the default character set collation utf8_general_ci

so the data will be stored with that characters specifically

when you type the same manually then you won't get the error

As shown in the Error your statement has unknown symbol Â which belongs to default character set

More information on the character set and collation can be found here which is the best one to refer to understand them to avoid or to solve the future errors

answered Jan 23, 2013 at 10:14

vidyadhar

3,1787 gold badges26 silver badges32 bronze badges

Comments

Benjamin Crouzier · Accepted Answer · 2013-01-23 10:09:35Z

0

Beware of the new versions of phpmyadmin with online editing. Sometimes copying from those text-boxes can give you strange characters.

enter image description here

answered Jan 23, 2013 at 10:09

Benjamin Crouzier

42.2k48 gold badges179 silver badges240 bronze badges

Comments

Kevin · Accepted Answer · 2013-01-26 05:43:11Z

0

Copy and paste it into notepad or a text editor, then try and repaste it. You probably have some garbage characters in there.

answered Jan 26, 2013 at 5:43

Kevin

1,7257 gold badges29 silver badges55 bronze badges

Collectives™ on Stack Overflow

Syntax error after show create table in mysql

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related