distal-attribute
distal-attribute
distal-attribute
distal-attribute

trouble importing - syntax error

User, date Message
Written by helpme
3 years ago
Category: Import/Export
14 posts since Wed, 08 Jul 09
About 2 years ago I exported a DB using Heidi as a SQL file in the "Ansi" format.

I'm now trying to import the SQL ansi format file BACK to a new database using Heidi. But I keep getting this error:

# # Dumping data for table 'adminlog' # LOCK TABLES "adminlog" WRITE;
/* SQL 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 '"adminlog" WRITE' at line 5 */




Any ideas what's wrong?

Here is the actual SQL data that's causing the error:


# HeidiSQL Dump
#
# --------------------------------------------------------
# Host: 72.3.135.18
# Database: viplounge
# Server version: 4.1.22
# Server OS: redhat-linux-gnu
# Target-Compatibility: Standard ANSI SQL
# HeidiSQL version: 3.2 Revision: 1129
# --------------------------------------------------------

/*!40100 SET CHARACTER SET latin1;*/
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI';*/
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;*/


#
# Table structure for table 'adminlog'
#

CREATE TABLE /*!32312 IF NOT EXISTS*/ "adminlog" (
"adminlogid" int(10) unsigned NOT NULL auto_increment,
"userid" int(10) unsigned NOT NULL default '0',
"dateline" int(10) unsigned NOT NULL default '0',
"script" varchar(50) NOT NULL default '',
"action" varchar(20) NOT NULL default '',
"extrainfo" varchar(200) NOT NULL default '',
"ipaddress" varchar(15) NOT NULL default '',
PRIMARY KEY ("adminlogid"),
KEY "script_action" ("script","action")
) AUTO_INCREMENT=6928 /*!40100 DEFAULT CHARSET=latin1*/;



#
# Dumping data for table 'adminlog'
#

LOCK TABLES "adminlog" WRITE;
/*!40000 ALTER TABLE "adminlog" DISABLE KEYS;*/
REPLACE INTO "adminlog" ("adminlogid", "userid", "dateline", "script", "action", "extrainfo", "ipaddress") VALUES
('1','1','1102279454','index.php','','','65.10.155.112');


/*!40000 ALTER TABLE "adminlog" ENABLE KEYS;*/
UNLOCK TABLES;


Written by ansgar
3 years ago
4940 posts since Fri, 07 Apr 06
The double quotes are the problem. You should have selected ANSI SQL as target version as you have exported. However, you could search/replace the double quotes with backticks. Easier and more reliable way could be to activate ANSI quotes in the session before you run the above sql script:

SET @@SESSION.sql_mode='ANSI_QUOTES';

Written by ansgar
3 years ago
4940 posts since Fri, 07 Apr 06
Hm, I just see this line in your script which should do already the same plus some more:

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI';*/


Could also be the server complains about the conditional comments. If so you should set ANSI_QUOTES, and remove ANSI sql mode.
Written by helpme
3 years ago
14 posts since Wed, 08 Jul 09
I replaced the double quotes with back ticks and that seemed to do the trick. No more error. Thanks smile

Something else is causing an issue though. It doesn't want to create the table.

I ran the create table command alone all by itself, and it doesn't do anything. No error, but no table created either.

Any ideas what's wrong?

CREATE TABLE /*!32312 IF NOT EXISTS*/ `adminlog` (
`adminlogid` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL default '0',
`dateline` int(10) unsigned NOT NULL default '0',
`script` varchar(50) NOT NULL default '',
`action` varchar(20) NOT NULL default '',
`extrainfo` varchar(200) NOT NULL default '',
`ipaddress` varchar(15) NOT NULL default '',
PRIMARY KEY (`adminlogid`),
KEY `script_action` (`script`,`action`)
) AUTO_INCREMENT=6928 /*!40100 DEFAULT CHARSET=latin1*/;

Written by helpme
3 years ago
14 posts since Wed, 08 Jul 09
hold on, it may be working...
Written by ansgar
3 years ago
4940 posts since Fri, 07 Apr 06
Of course you can break your data if you rely on search/replace. That's so obvious so I didn't even mention it. Use the sql_mode query and your data will be ok.

Regarding the conditionals - not sure why but they look wrong in a way that the semicolons need to be right besides the closing comment, not within:
Wrong:

/*!40101 bla bla;*/


Correct:

/*!40101 bla bla*/;


Written by helpme
3 years ago
14 posts since Wed, 08 Jul 09
Thanks anse. Between (1) correcting the semicolons on the conditionals and (2) adding

SET @@SESSION.sql_mode='ANSI_QUOTES';


at the top, my initial tests look like it's going to work. No need to search and replace the double quotes happy
Written by malavikakswamy
3 years ago
9 posts since Mon, 18 Jul 11
I am having the same problem and tried correcting the semicolons as well as adding the above line as it is in my code which is as below but I still get an error which says I have an error in my SQL syntax check the manual that corresponds to your MySql server version for the right syntax to use near "album" ("album_id", "contrib_id" .... at line1

PLEASE HELP!!

# HeidiSQL Dump
#
# --------------------------------------------------------
# Host: cleardev.org
# Database: cleardev2_mea
# Server version: 5.0.22
# Server OS: unknown-freebsd6.0
# Target-Compatibility: Standard ANSI SQL
# HeidiSQL version: 3.2 Revision: 1129
# --------------------------------------------------------

/*!40100 SET CHARACTER SET latin1*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI_QUOTES'*/;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0*/;
SET @@SESSION.sql_mode='ANSI_QUOTES';


#
# Database structure for database 'cleardev2_mea'
#

CREATE DATABASE /*!32312 IF NOT EXISTS*/ "cleardev2_mea" /*!40100 DEFAULT CHARACTER SET latin1 */;

USE "cleardev2_mea";


#
# Table structure for table 'album'
#

CREATE TABLE /*!32312 IF NOT EXISTS*/ "album" (
"album_id" int(10) unsigned NOT NULL auto_increment,
"contrib_id" int(10) unsigned NOT NULL,
"v_id" int(10) unsigned default NULL,
"album_name" varchar(255) default NULL,
"album_description" text,
"album_image" int(10) unsigned default NULL,
"album_position" int(3) unsigned default NULL,
"album_count" int(5) unsigned default '0',
"status" tinyint(3) unsigned default '0',
"album_date" date default NULL,
"agreement_type" tinyint(2) unsigned default '1',
"allow_voices" tinyint(1) unsigned default '0',
"comments" text,
PRIMARY KEY ("album_id"),
UNIQUE KEY "album_id" ("album_id"),
KEY "album_id_2" ("album_id")
) /*!40100 DEFAULT CHARSET=latin1*/;
Written by ansgar
3 years ago
4940 posts since Fri, 07 Apr 06

malavikakswamy wrote: HeidiSQL version: 3.2 Revision: 1129


Man, this is a 4 years old release - 3.2 is from november 2007. And the dump looks like you had selected ANSI style quoting. You can try to replace the double quotes with backticks to make it work.
Written by malavikakswamy
3 years ago
9 posts since Mon, 18 Jul 11
Is that the only option I am left with???Search and replace with back ticks??? I know its an old release. But I have the dump which had the following statement:

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI'*/;

I replaced it with ANSI_QUOTES after looking at this post..
Written by malavikakswamy
3 years ago
9 posts since Mon, 18 Jul 11
Also... I was wondering how come it works until the statement to create a database and then gives me an error with the create table statement....
Written by helpme
3 years ago
14 posts since Wed, 08 Jul 09
I got my situation fixed, but now don't remember what I did. Check on all of my threads, I had multiple threads related to this issue.

I think I had to delete some of the commented statements like /*!40100 SET CHARACTER SET latin1*/; but I don't remember now.

Also apostrophes in image files were causing problems. I replaced all ' characters with a ]. I never could get images working, only to stop causing errors.
 

Please login to leave a reply, or register at first.