?
Solved

MySQL weird import problem

Posted on 2007-08-09
11
Medium Priority
?
276 Views
Last Modified: 2013-12-13
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
Comment
Question by:jtcy
  • 6
  • 5
11 Comments
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19661832
It sould say:
---
DEFAULT CHARACTER SET
---
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19661840
No `=` either:
---
DEFAULT CHARACTER SET latin1
---
0
 

Author Comment

by:jtcy
ID: 19662224
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19662303
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
 

Author Comment

by:jtcy
ID: 19666571
so u mean i need to add default before charset?
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19668220
I mean you do not need to add it.
0
 

Author Comment

by:jtcy
ID: 19670017
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
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19670128
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
 

Author Comment

by:jtcy
ID: 19670409
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
 

Author Comment

by:jtcy
ID: 19670488
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
 
LVL 17

Accepted Solution

by:
Aleksandar Bradarić earned 2000 total points
ID: 19671162
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question