trouble importing - syntax error

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;

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';

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.

I replaced the double quotes with back ticks and that seemed to do the trick. No more error. Thanks

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*/;

hold on, it may be working...

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*/;

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

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*/;

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.

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..

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....

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.