Uploading Joomla Database

Hello,

I would like to upload my Joomla database, but i always get an error:

SQL ERROR: You have an error in 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", 'contact' varch.

I did both Import and Exports.

I am new to HEIDISQL, I need your assistance.

Sounds like an error

Please post a 50-line-chunk of the SQL-file here that contains this problematic line.

Hi,

Below are sample codes taken from the exproted sql using phpMyadmin.

Please help me solve my problem

-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 07, 2007 at 11:38 PM
-- Server version: 5.0.27
-- PHP Version: 4.4.4
--
-- Database: `mharj`
--

-- --------------------------------------------------------

--
-- Table structure for table `jos_banner`
--

CREATE TABLE `jos_banner` (
`bid` int(11) NOT NULL auto_increment,
`cid` int(11) NOT NULL default '0',
`type` varchar(10) collate utf8_unicode_ci NOT NULL default 'banner',
`name` varchar(50) collate utf8_unicode_ci NOT NULL default '',
`imptotal` int(11) NOT NULL default '0',
`impmade` int(11) NOT NULL default '0',
`clicks` int(11) NOT NULL default '0',
`imageurl` varchar(100) collate utf8_unicode_ci NOT NULL default '',
`clickurl` varchar(200) collate utf8_unicode_ci NOT NULL default '',
`date` datetime default NULL,
`showBanner` tinyint(1) NOT NULL default '0',
`checked_out` tinyint(1) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar(50) collate utf8_unicode_ci default NULL,
`custombannercode` text collate utf8_unicode_ci,
PRIMARY KEY (`bid`),
KEY `viewbanner` (`showBanner`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `jos_banner`
--

INSERT INTO `jos_banner` VALUES (1, 1, 'banner', 'OSM 1', 0, 43, 0, 'osmbanner1.png', 'http://www.opensourcematters.org', '2004-07-07 15:31:29', 1, 0, '0000-00-00 00:00:00', NULL, NULL);
INSERT INTO `jos_banner` VALUES (2, 1, 'banner', 'OSM 2', 0, 51, 0, 'osmbanner2.png', 'http://www.opensourcematters.org', '2004-07-07 15:31:29', 1, 0, '0000-00-00 00:00:00', NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `jos_bannerclient`
--

CREATE TABLE `jos_bannerclient` (
`cid` int(11) NOT NULL auto_increment,
`name` varchar(60) collate utf8_unicode_ci NOT NULL default '',
`contact` varchar(60) collate utf8_unicode_ci NOT NULL default '',
`email` varchar(60) collate utf8_unicode_ci NOT NULL default '',
`extrainfo` text collate utf8_unicode_ci NOT NULL,
`checked_out` tinyint(1) NOT NULL default '0',
`checked_out_time` time default NULL,
`editor` varchar(50) collate utf8_unicode_ci default NULL,
PRIMARY KEY (`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

--
-- Dumping data for table `jos_bannerclient`
--

INSERT INTO `jos_bannerclient` VALUES (1, 'Open Source Matters', 'Administrator', 'admin@opensourcematters.org', '', 0, '00:00:00', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `jos_bannerfinish`
--

CREATE TABLE `jos_bannerfinish` (
`bid` int(11) NOT NULL auto_increment,
`cid` int(11) NOT NULL default '0',
`type` varchar(10) collate utf8_unicode_ci NOT NULL default '',
`name` varchar(50) collate utf8_unicode_ci NOT NULL default '',
`impressions` int(11) NOT NULL default '0',
`clicks` int(11) NOT NULL default '0',
`imageurl` varchar(50) collate utf8_unicode_ci NOT NULL default '',
`datestart` datetime default NULL,
`dateend` datetime default NULL,
PRIMARY KEY (`bid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `jos_bannerfinish`
--


-- --------------------------------------------------------

--
-- Table structure for table `jos_categories`
--

CREATE TABLE `jos_categories` (
`id` int(11) NOT NULL auto_increment,
`parent_id` int(11) NOT NULL default '0',
`title` varchar(50) collate utf8_unicode_ci NOT NULL default '',
`name` varchar(255) collate utf8_unicode_ci NOT NULL default '',
`image` varchar(100) collate utf8_unicode_ci NOT NULL default '',
`section` varchar(50) collate utf8_unicode_ci NOT NULL default '',
`image_position` varchar(10) collate utf8_unicode_ci NOT NULL default '',
`description` text collate utf8_unicode_ci NOT NULL,
`published` tinyint(1) NOT NULL default '0',
`checked_out` int(11) unsigned NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar(50) collate utf8_unicode_ci default NULL,
`ordering` int(11) NOT NULL default '0',
`access` tinyint(3) unsigned NOT NULL default '0',
`count` int(11) NOT NULL default '0',
`params` text collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `cat_idx` (`section`,`published`,`access`),
KEY `idx_section` (`section`),
KEY `idx_access` (`access`),
KEY `idx_checkout` (`checked_out`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=13 ;

--
-- Dumping data for table `jos_categories`
--

INSERT INTO `jos_categories` VALUES (1, 0, 'Latest', 'Latest News', 'taking_notes.jpg', '1', 'left', 'The latest news from the Joomla! Team', 1, 0, '0000-00-00 00:00:00', '', 0, 0, 1, '');
INSERT INTO `jos_categories` VALUES (2, 0, 'Joomla!', 'Joomla!', 'clock.jpg', 'com_weblinks', 'left', 'A selection of links that are all related to the Joomla! Project.', 1, 0, '0000-00-00 00:00:00', NULL, 0, 0, 0, '');
INSERT INTO `jos_categories` VALUES (3, 0, 'Newsflash', 'Newsflash', '', '2', 'left', '', 1, 0, '0000-00-00 00:00:00', '', 0, 0, 0, '');
INSERT INTO `jos_categories` VALUES (4, 0, 'Joomla!', 'Joomla!', '', 'com_newsfeeds', 'left', '', 1, 0, '0000-00-00 00:00:00', NULL, 2, 0, 0, '');
INSERT INTO `jos_categories` VALUES (5, 0, 'Business: general', 'Business: general', '', 'com_newsfeeds', 'left', '', 1, 0, '0000-00-00 00:00:00', NULL, 1, 0, 0, '');
INSERT INTO `jos_categories` VALUES (7, 0, 'Examples', 'Example FAQs', 'key.jpg', '3', 'left', 'Here you will find an example set of FAQs.', 1, 0, '0000-00-00 00:00:00', NULL, 0, 0, 2, '');
INSERT INTO `jos_categories` VALUES (9, 0, 'Finance', 'Finance', '', 'com_newsfeeds', 'left', '', 1, 0, '0000-00-00 00:00:00', NULL, 5, 0, 0, '');
INSERT INTO `jos_categories` VALUES (10, 0, 'Linux', 'Linux', '', 'com_newsfeeds', 'left', '<br />\r\n', 1, 0, '0000-00-00 00:00:00', NULL, 6, 0, 0, '');
INSERT INTO `jos_categories` VALUES (11, 0, 'Internet', 'Internet', '', 'com_newsfeeds', 'left', '', 1, 0, '0000-00-00 00:00:00', NULL, 7, 0, 0, '');
INSERT INTO `jos_categories` VALUES (12, 0, 'Contacts', 'Contacts', '', 'com_contact_details', 'left', 'Contact Details for this website', 1, 0, '0000-00-00 00:00:00', NULL, 0, 0, 0, '');

--

Below are sample codes taken from the exproted sql using phpMyadmin.

.....

The above works fine for me in HeidiSQL.
Try again, perhaps you have a copy/paste error somewhere.

I guess you're trying to import that file into a server with a version below 4.1 . The mysql docs say:

As of MySQL 4.1, character data types (CHAR, VARCHAR, TEXT) can include CHARACTER SET and COLLATE attributes ...

Found here: http://dev.mysql.com/doc/refman/4.1/en/create-table.html

Would mean that you have to either
- make the exported file 4.0-compatible
- upgrade the target server to 4.1 or above
- downgrade the source server to 4.0 or below

If you do the export with HeidiSQL you *should be able* to get a 4.0 compatible export by selecting the correct option in the pulldown "target-compatibility". I tested that by selecting "MySQL 3.x" but the collate-attribute still gets exported. So this should be fixed for HeidiSQL. In addition we should split the options "MySQL 4.0 - 5.0" into 2 options: "MySQL 4.0" and "MySQL 4.1 - 5.0".

Just posted as an issue in our bugtracker:

http://sourceforge.net/tracker/index.php?func=detail&aid=1685835&group_id=164593&atid=832347

This has been fixed for HeidiSQL 3.0 (final) in this way:

If you select "MySQL 4.0" or below as target-compatibility in the dialog "Export-SQL", HeidiSQL will automatically remove COLLATE- and CHARSET-attributes for all column-specifications (when in file-mode).

The only thing you have to take care of is to know the target-version where the file will be imported. Because: in file-mode HeidiSQL is not able to predict on which server-version you will import that file, that's basically the reason why there is the target-compatibility-dropdown. If you select the correct version you shouldn't run into problems at the time you re-import that file.