phpMyAdmin - Error #1064 -- Create Table problem

[quantass]

I am using CPanel 11 and the built in phpMyAdmin. I'm trying to import my SQL statements in but am getting the following 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 '`0`, `ip_address` varchar(16) NOT NULL DEFAULT `0`, `user_agent` varchar(5' at line 2
CREATE TABLE IF NOT EXISTS `ci_sessions` (
`session_id` varchar( 40 ) NOT NULL DEFAULT `0` ,
`ip_address` varchar( 16 ) NOT NULL DEFAULT `0` ,
`user_agent` varchar( 50 ) NOT NULL ,
`last_activity` int( 10 ) unsigned NOT NULL DEFAULT `0` ,
`user_data` text NOT NULL ,
PRIMARY KEY ( `session_id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1;

Any clue as to the issue? My local mySQL is version 5.1.41 and was used to export the statement.

 

[cPanelTristan]

Just to clarify, you used the same server to export the statement that you are using to import the statement with both running the same MySQL version?

Next, what is the exact statement you are using to import? It almost seems as if you aren't separating the ENGINE and DEFAULT CHARSET statements in any way.

 

[quantass]

Actually my local MySQL is version 5.1.41 and the remote web server with Cpanel 11 reports it is using MySQL 5.0.92-community. I have no idea from the SQL statement if anything is illegal.

The SQL statement shown is exactly what was exported from mysql GUI. I just copy / pasted it into phpMyAdmin. I havent yet checked the proper syntax on MySQL but i did try removing the ENGINE and DEFAULT CHARSET as in:

CREATE TABLE IF NOT EXISTS `ci_sessions` (
  `session_id` varchar(40) NOT NULL DEFAULT `0`,
  `ip_address` varchar(16) NOT NULL DEFAULT `0`,
  `user_agent` varchar(50) NOT NULL,
  `last_activity` int(10) unsigned NOT NULL DEFAULT `0`,
  `user_data` text NOT NULL,
  PRIMARY KEY (`session_id`)
);

But no go.

 

[cPanelTristan]

Do you have access to MySQL command line in root SSH? If so, could you try logging into that to try to import the statement instead?

mysql
mysql > \u databasename
mysql > CREATE TABLE IF NOT EXISTS `ci_sessions` (
  `session_id` varchar(40) NOT NULL DEFAULT `0`,
  `ip_address` varchar(16) NOT NULL DEFAULT `0`,
  `user_agent` varchar(50) NOT NULL,
  `last_activity` int(10) unsigned NOT NULL DEFAULT `0`,
  `user_data` text NOT NULL,
  PRIMARY KEY (`session_id`)
);

You would basically run the "mysql" command in root SSH to get to the command line for MySQL, then select the database there using the "\u databasename" command where databasename is the full name of the database (usually cPanelusername_databasename), then paste the command into the command line after selecting the database. You might want to show the tables in the database prior to running the command:

mysql > show tables;

 

[quantass]

All is ok now. I went back to my local mysql server and told it to export using UTF8 instead of latin1.

Thanks so much for the patient assistance. I'm going to keep that SSH approach in mind for the future.