Import help

Hi, i have exported DB using ansi sql option (dunno if it is by default i later noticed), and i cant import it now. When i do Import it hangs and partialy import DB (btw heidi is only thing that accepts that file, everything else gives me the wrong syntax error). Is there any converter so i can use to convert to standard mysql db.
Yeah i didnt noticed DB is ~60MB, and i cant use source command since it doesnt recognize formating ...
pls help

ANSI basically means for HeidiSQL that
1. identifiers are quoted using doublequotes (instead of backticks `)
2. string values are escaped slightly differently wrt the backslash.

If I were you I would replace all occurences of [^\\]" with ` using regular expressions. And then try to reimport.

Or, even better, redo the export using the target version "same as source server"

I think ill try with replace, cause after i exported i deleted that DB, so only copy is the one in ansi :)
Can you just confirm , should i replace all characters [, ], //, " with ' ?
Or is there some DB converter that you know of (or could someone do that for me) ?

Can you just confirm , should i replace all characters [, ], //, " with ' ?

No, totally wrong. You need to replace all doublequotes " with backticks ` which is not the single quote ' . Be very careful here, this rule only applies for identifieres, not for the data of some column. In addition you need to replace all not yet escaped single quotes in the data ' with \' . Tricky.. Better do a new export ...

Or is there some DB converter that you know of (or could someone do that for me) ?

As ANSI SQL is not completely supported by HeidiSQL I would keep my fingers from any automatic converter - you can easily corrupt your data with such tools. However, I don't know any.

hi

I made the same backup "Standart Ansi SQL" i thoad is standart for HeidiSQL.
I can`t do a new backup of my database, so i must restore anyway this backup, to mysql 4.1
The file is 11MB, HeidiSQL is the only way to restore on mysql, and when i Execute SQL the first 10 sec is ewerything ok, after HeidiSQL freezing, and nothing heppend, there is no error print, or log.
The only thing that give me HOPE, is that i see the first 5 tables are filled from backup, all the data is there, but why heidi stops responding and can`t finish restore?
Is that compatibility error ?

Thank you for any help

Regards

Well, noone can tell you unless you post the relevant SQL code here.

Btw, "Standard ANSI SQL" is not by default selected in Heidi and should be used with caution. MySQL is not ANSI, although there is an ANSI-mode in MySQL. If you don't plan to exchange data between different DBMS "Same as source server" is always the better choice.

As first thanks for fast reply

Ok, here a part of sql:

# HeidiSQL Dump
#
# --------------------------------------------------------
# Host: 127.0.0.1
# Database: cao
# Server version: 4.1.22-community-nt
# Server OS: Win32
# 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;*/


#
# Dumping data for table 'adressen'
#

LOCK TABLES "adressen" WRITE;
/*!40000 ALTER TABLE "adressen" DISABLE KEYS;*/
INSERT IGNORE INTO "adressen" ("REC_ID", "MATCHCODE", "KUNDENGRUPPE", "SPRACH_ID", "GESCHLECHT", "KUNNUM1", "KUNNUM2", "NAME1", "PLZ", "ORT", "LAND", "NAME2", "NAME3", "ABTEILUNG", "ANREDE", "STRASSE", "POSTFACH", "PF_PLZ", "DEFAULT_LIEFANSCHRIFT_ID", "GRUPPE", "TELE1", "TELE2", "FAX", "FUNK", "EMAIL", "EMAIL2", "INTERNET", "DIVERSES", "BRIEFANREDE", "BLZ", "KTO", "BANK", "IBAN", "SWIFT", "KTO_INHABER", "DEB_NUM", "KRD_NUM", "STATUS", "NET_SKONTO", "NET_TAGE", "BRT_TAGE", "WAEHRUNG", "UST_NUM", "VERTRETER_ID", "PROVIS_PROZ", "INFO", "GRABATT", "KUN_KRDLIMIT", "KUN_LIEFART", "KUN_ZAHLART", "KUN_PRLISTE", "KUN_LIEFSPERRE", "LIEF_LIEFART", "LIEF_ZAHLART", "LIEF_PRLISTE", "LIEF_TKOSTEN", "LIEF_MBWERT", "PR_EBENE", "BRUTTO_FLAG", "MWST_FREI_FLAG", "KUNPREIS_AUTO", "KUN_SEIT", "KUN_GEBDATUM", "ENTFERNUNG", "ERSTELLT", "ERST_NAME", "GEAEND", "GEAEND_NAME", "SHOP_KUNDE", "SHOP_ID", "SHOP_NEWSLETTER", "SHOP_KUNDE_ID", "SHOP_CHANGE_FLAG", "SHOP_DEL_FLAG", "SHOP_PASSWORD", "USERFELD_01", "USERFELD_02", "USERFELD_03", "USERFELD_04", "USERFELD_05", "USERFELD_06", "USERFELD_07", "USERFELD_08", "USERFELD_09", "USERFELD_10") VALUES
the values of this table are filled out

another tables for exp. artikels comes down on the dump and i see 30% of artikels is ok, but not in the right please.
if i had this dump from another database there should be some way to restore on mysql ? i can read ewerywere that to migrate data from one to another diferent database Standart Ansi Sql is the best way :shock:

Any chance you zip the complete SQL dump and upload it to our ftp server?

ftp.heidisql.com
User: upload
Pass: upload
Folder: /upload/

You have write-only access, so you cannot see your data once you have uploaded it.

Thank you wery much anse for your support & help

all the night i worked on it, i have back my database, not 100% but all important things are back.
I installed mysql 5.0.17 on linux server, and started from begin.
On this mysql version Heidi printed all logs and errors, and didn`t freez like on win32 mysql. After ewery error i delete/modify the INSERT, save the file and started agein.
I had about 12 table errors, some of them i fixed some deleted.
Then heidisql did the job, restored ewerything
After i made a dump whith heidisql from mysql 5.0.17 target to 4.1, and at last i retored this on my win32 produktive mysql4.1 server whithout any error.

Regards

If I were you I would replace all occurences of [^\\]" with ` using regular expressions. And then try to reimport.

That's pretty bad advice.

Just import the file with HeidiSQL. The file contents are fine, and there's even a switch in the SQL file that will tell the server to expect ANSI quotes, so it should import just fine.