• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

MySQL weird import problem

I am trying to run this:

CREATE TABLE `wp_eblex_captcha` (
  `id` varchar(32) NOT NULL default '',
  `text` tinytext NOT NULL,
  `time` bigint(20) unsigned NOT NULL default '0',
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT;

but the error says:

/* SQL Error: 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 'DEFAULT CHARSET=latin1' at line 6 */

I dont know what is wrong. It's PHP Version 5.1.6 and MySQL 5.0.27 .
0
jtcy
Asked:
jtcy
  • 6
  • 5
1 Solution
 
Aleksandar BradarićSoftware DeveloperCommented:
It sould say:
---
DEFAULT CHARACTER SET
---
0
 
Aleksandar BradarićSoftware DeveloperCommented:
No `=` either:
---
DEFAULT CHARACTER SET latin1
---
0
 
jtcyAuthor Commented:
oH, SORRY...actually..this is my code:

CREATE TABLE `wp_eblex_captcha` (
  `id` varchar(32) NOT NULL default '',
  `text` tinytext NOT NULL,
  `time` bigint(20) unsigned NOT NULL default '0',
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and it gave that error /* SQL Error: 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 'DEFAULT CHARSET=latin1' at line 6 */
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Aleksandar BradarićSoftware DeveloperCommented:
Try with:
---
CREATE TABLE `wp_eblex_captcha` (
  `id` varchar(32) NOT NULL default '',
  `text` tinytext NOT NULL,
  `time` bigint(20) unsigned NOT NULL default '0',
  KEY `id` (`id`)
) ENGINE=MyISAM CHARSET latin1;
---

It works on 4.1.22 and 5.0.45. I'll see if I can get my hands on a 5.0.27 server to test it...
0
 
jtcyAuthor Commented:
so u mean i need to add default before charset?
0
 
Aleksandar BradarićSoftware DeveloperCommented:
I mean you do not need to add it.
0
 
jtcyAuthor Commented:
This is another one that has problem:

CREATE TABLE `wp_eblex_categories` (  `id` varchar(32) collate utf8_unicode_ci NOT NULL default '',  `parent` varchar(32) collate utf8_unicode_ci NOT NULL default '',  `title` mediumtext collate utf8_unicode_ci NOT NULL,  `description` mediumtext collate utf8_unicode_ci NOT NULL,  `keywords` mediumtext collate utf8_unicode_ci NOT NULL,  `nicename` mediumtext collate utf8_unicode_ci NOT NULL,  `time` bigint(20) unsigned NOT NULL default '0',  `visible` tinyint(1) NOT NULL default '0',  `zindex` bigint(20) NOT NULL default '0',  KEY `id` (`id`) ) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_unicode_ci

/* SQL Error: 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 'collate utf8_unicode_ci NOT NULL default '',  `parent` varcha */
0
 
Aleksandar BradarićSoftware DeveloperCommented:
You already have the COLLATE set for the whole table, so it should be OK to remove it from the field:
---
CREATE TABLE `wp_eblex_categories` (  
  `id` varchar(32) NOT NULL default '',  
  `parent` varchar(32) NOT NULL default '',  
  `title` mediumtext NOT NULL,  
  `description` mediumtext NOT NULL,  
  `keywords` mediumtext NOT NULL,  
  `nicename` mediumtext NOT NULL,  
  `time` bigint(20) unsigned NOT NULL default '0',  
  `visible` tinyint(1) NOT NULL default '0',  
  `zindex` bigint(20) NOT NULL default '0',  
  KEY `id` (`id`)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_unicode_ci
---
0
 
jtcyAuthor Commented:
CREATE TABLE `wp_eblex_categories` (   `id` varchar(32) NOT NULL default '',   `parent` varchar(32) NOT NULL default '',   `title` mediumtext NOT NULL,   `description` mediumtext NOT NULL,   `keywords` mediumtext NOT NULL,   `nicename` mediumtext NOT NULL,   `time` bigint(20) unsigned NOT NULL default '0',   `visible` tinyint(1) NOT NULL default '0',   `zindex` bigint(20) NOT NULL default '0',   KEY `id` (`id`) ) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_unicode_ci                                                                                            CREATE TABLE `wp_eblex_links` (  `title` mediumtext collate utf8_unicode_ci NOT NULL,  `active` tinyint(4) NOT NULL default '0',  `nonreciprocal` tinyint(4) NOT NULL default '0',  `url` mediumtext collate utf8_unicode_ci NOT NULL,  `category` varchar(32) collate utf8_unicode_ci NOT NULL default '',  `description` mediumtext collate utf8_unicode_ci NOT NULL,  `email` mediumtext collate utf8_unicode_ci NOT NULL,  `reciprocalurl` mediumtext collate utf8_unicode_ci NOT NULL,  `status` tinytext collate utf8_unicode_ci NOT NULL,  `time` bigint(20) unsigned NOT NULL default '0',  `administratorcomment` mediumtext collate utf8_unicode_ci NOT NULL,  `zindex` bigint(20) NOT NULL default '0',  `id` varchar(32) collate utf8_unicode_ci NOT NULL default '',  KEY `id` (`id`) ) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_unicode_ci

/* SQL Error: 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 'COLLATE=utf8_unicode_ci */
0
 
jtcyAuthor Commented:
CREATE TABLE `wp_eblex_categories` (   `id` varchar(32) NOT NULL default '',   `parent` varchar(32) NOT NULL default '',   `title` mediumtext NOT NULL,   `description` mediumtext NOT NULL,   `keywords` mediumtext NOT NULL,   `nicename` mediumtext NOT NULL,   `time` bigint(20) unsigned NOT NULL default '0',   `visible` tinyint(1) NOT NULL default '0',   `zindex` bigint(20) NOT NULL default '0',   KEY `id` (`id`) ) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_unicode_ci                                                                                            

/* SQL Error: 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 'COLLATE=utf8_unicode_ci */
0
 
Aleksandar BradarićSoftware DeveloperCommented:
It looks like you have to remove all COLLATE declarations from your queries - even for the tables:
---
CREATE TABLE `wp_eblex_categories` (  
  `id` varchar(32) NOT NULL default '',  
  `parent` varchar(32) NOT NULL default '',  
  `title` mediumtext NOT NULL,  
  `description` mediumtext NOT NULL,  
  `keywords` mediumtext NOT NULL,  
  `nicename` mediumtext NOT NULL,  
  `time` bigint(20) unsigned NOT NULL default '0',  
  `visible` tinyint(1) NOT NULL default '0',  
  `zindex` bigint(20) NOT NULL default '0',  
  KEY `id` (`id`)
) ENGINE=MyISAM CHARSET=utf8
---
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now