Ansi Sql Datenbank Importprobleme

[expired user #5404]'s profile image [expired user #5404] posted 13 years ago in Import/Export Permalink
Hi Folks,

ein Kollege hat unsere Datenbank leider als AnsiSQL exportiert. Über mysql bekomme ich natürlich jetzt den error 1064, da die ` nun " sind etc. Ein Import über Heidi ist nicht möglich, da er quälend langsam wird und allein 7 Stunden für 13000 Queries braucht. Der Import erfordert über 131000 Einträge (125 MB). (Bei Heidi 4.0) Bei Heidi 6 bricht er den Import mit gleicher Fehlermeldung ab. Hier die Infos aus der DB:

# HeidiSQL Dump
#
# --------------------------------------------------------
# Host: 127.0.0.1
# Database: test
# Server version: 4.0.26-debug
# Server OS: Win32
# Target compatibility: ANSI SQL
# HeidiSQL version: 4.0
# Date/time: 2010-12-22 23:06:50
# --------------------------------------------------------

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

Vielleicht weiss jemand, wie ich Mysql dazu bemühen kann die DB zu importieren oder weiss eine andere Lösung! Vielen Dank
ansgar's profile image ansgar posted 13 years ago Permalink
> Bei Heidi 6 bricht er den Import mit gleicher Fehlermeldung ab

Which error message is it?

You could try mysql.exe on the command line to import that file. ANSI style output was quite incomplete in HeidiSQL 4, and removed since 5.0 . But as the file contains SQL_MODE='ANSI,...' that should not be such a big problem.
[expired user #5404]'s profile image [expired user #5404] posted 13 years ago Permalink
Hi, Thanks

in Heidi 6 its error 1064 Sql Syntax error.

I ve tried to import with: Mysql -uroot -ppassword test < c:\test which resulting in the same error. Even when I start Mysqld --ansi (I read somewhere ;)

ansgar's profile image ansgar posted 13 years ago Permalink
Hm, and you did not remove this line from the dump file?
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI,NO_BACKSLASH_ESCAPES';*/

That line should ensure double quotes to work. Or is the target server version below 4.1.1 ?
ansgar's profile image ansgar posted 13 years ago Permalink
If so, a newer server (4.1.1+) should import that file.
[expired user #5404]'s profile image [expired user #5404] posted 13 years ago Permalink
Well, mysql server version is 4.0.26 (is required by CAO Faktura) and I havent removed that file! I am about to install mysql 5.5 and try to import my sql-file. I ll give a report!

Best regards
[expired user #5404]'s profile image [expired user #5404] posted 13 years ago Permalink
Server version is 5.5 now. An import via Mysql -uroot -ppassword test < c:\test.sql gives the same error (Error 1064)

I tried to import the data to heidisql. When I press yes I get an 1064 error from Heidi, when I press no I see my file in the query editor. Well, I start the query and the import works till heidi loses connection because of an out of memory error...

I give the output of my reportfile

date/time : 2010-12-23, 12:50:42, 372ms
computer name : TORSTEN-PC
user name : Torsten <admin>
registered owner : Torsten
operating system : Windows 7 build 7600
system language : German
system up time : 2 hours 2 minutes
program up time : 1 hour 30 minutes
processors : 2x Intel(R) Core(TM)2 Duo CPU T7300 @ 2.00GHz
physical memory : 1065/2038 MB (free/total)
free disk space : (C:) 44,40 GB
display mode : 1280x800, 32 bit
process id : $f74
allocated memory : 608,12 MB
executable : heidisql.exe
exec. date/time : 2010-11-20 18:49
version : 6.0.0.3603
compiled with : Delphi 2010
madExcept version : 3.0k
callstack crc : $2b691daa, $35af4d4d, $35af4d4d
exception number : 1
exception class : EOutOfMemory
exception message : Out of memory.

I know its not very difficult to solve the problem but I just dont know what to do!

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