[peterlaursen]

I think I got it!

You will need to use an ESCAPED BY too, because this

Quote:

AARHAUS, PATRICK A

contains a comma. And you also use comma as field delimiter.

Or you could create a new fille with another delimiter and enclose-character that is not in the dataset. Could be ; (semicolon) or | (pipe) for instance.

When the MySQL parser has made the first escape-parsing error everything becomes unpredictable. The delimiter-character (probably!) is 'higher' in the evaluation hierachy than the enclose-character.

I copied the first 5 fields of your two first row and imported from SQLyog GUI. I executed

Code:

load data local infile 'C:/Documents and Settings/Peter/Skrivebord/ttt.csv' into table `test`.`ttt` fields escaped by '\' terminated by ',' enclosed by '”' lines terminated by 'rn' ( `id`, `t1`, `t2`, `t3`, `t4` )

next I exported the table to .csv (using defaults) and the file reads:

Quote:

“831544256”,”AARHAUS, PATRICK A”,”9026 133RD AVE SE”,”NEWCASTLE”,”WA”

“532780374”,”ABBOTT, JAMES (MIKE)”,”13733 50th Dr. SE”,”EVERETT”,”WA”

However a SELECT * returns

Code:

       id  t1                    t2                 t3         t4   
———  ——————–  —————–  ———  ——
831544256  AARHAUS, PATRICK A    9026 133RD AVE SE  NEWCASTLE  WA   
532780374  ABBOTT, JAMES (MIKE)  13733 50th Dr. SE  EVERETT    WA   

It is a matter of escaping! Or to be perfectly sure that the data don't contain any character having a special meaning with MySQL or that you use for another purpose!

I too get weird results when not escaping – even with that reduced dataset!

[peterlaursen]

you'd better explain

Quote:

FIRST ROWS: (second row start with “832780374” and ends up truncated)

There is no “832780374” in the data! Do you mean that “532780374” becomes “8327803” ?

How did you print out the data/result? Where did you get those \N 's from?

Please use ONLY SQLyog and no other program when posting here.

But this is interesting: “8327803”

the ” -character has been escaped!

You will need to tell THE WHOLE STORY. Where do the data come from? How did you generate the txt/csv file. How EXACTLY did you try to import (which program which functionality) ? How do you list the output? What are the program versions. We can't GUESS all these things!!!

Did you consider using a better method than LOAD DATA …

Also try execute this SQL

Code:

select @@sql_mode;

. I do suspect that the server is running in ANSI-mode of some variant of it.

[peterlaursen]

Please paste in the 'create statement for the table' too!

And inform about MySQL and otehr program versions.

Do you use SQLyog? How? csv-import functionality or just executing an external script?

You are sure that lines must not be terminated by 'rn' (depends on the origins of the file – most windows' program use 'rn' or in ASCII/ANSI

http://www.webyog.com/faq/28_73_en.html

[peterlaursen]

I saw your screenshots, but I am in doubt how I should understand all this!

What is the situation like now?

Do you still have questions/problems ?

[peterlaursen]

Well …

I don't think phpMyAdmin works with php5 at all ?

Correct me if I am wrong! But I have a Windows XP as well as a SUSE 10 Linux machine. The Yast installer on SUSE insists to uninstall myphpadmin if I upgrade from php4 to php5.

It is noteworthy that practically ALL ISPs stay at php4 (not php5) php_mysql extension (not php_mysqli) and MySQL 4.0 or earlier. The main reason is I believe that they depend on phpmyadmin and thats why they don't upgrade the server and preprocessor software.

I am not quite sure that I understand you completely, but if you did not allrady, then start from scratch and do EVERYTHING with SQLyog. If phpmyadmin has destroyed the table metadata everything could happen.

Didn't you consider using SQLyog at work too?

[peterlaursen]

Is that the ONLY thing that you cannot ?

Can you simply create ANY table using INNODB (without FK's)?

I ask this because some ISP (silently) disable InnoDB.

But the MySQL and PHP version tells me that it is probably is your own server. Right ? Is the server running on your local WinXP?

And it is SQLyog that you are using? What do you use PHP for then?

But if you use PHP you must use a webserver then too. Which one?

And also please explain (screenshots are good!) EXACTLY where you are when you get this error message?

We can't guess all those things. You must PIN IT OUT! 🙂

[peterlaursen]

Quote:

BTW, are you sure that the upgrade email is not marked as SPAM by your anti-spam software?

Actually I experienced about 2 weeks ago that the spam-filer at my ISP quarantined 2 mails with download links from Webyog.

So .. maybe the confimations mail(s) could be rewritten somewhat

[peterlaursen]

Actually I think that the next major release (with Unicode support etc.) should have an additional column in ALTER TABLE and CREATE TABLE pane where to set charset and collation for character-data.

As I said before: in an international environment you might need to have various language translations for the same thing in more columns such as catalogs, stock-lists etc. And there also could be good reasons to choose a binary collation for certain columns.

[peterlaursen]

so ..

I guess you simply have some columns of data where the collation differs from your defaults. How could that happen?

1)

if the data were imported from another server or database using other defaults, the data don't use your defaults. The charset and collation stay with the data!

2)

Also changing server/database/table defaults does not change data. To do that you must issue an ALTER TABLE statement. So if the data were created when you had other defaults, the charset and collation still stay with the data!

[peterlaursen]

Well yes there is a “latin1_general_ci” collation ! Sorry!

http://dev.mysql.com/doc/refman/5.0/en/charset-mysql.html

here

http://dev.mysql.com/doc/refman/5.0/en/charset-defaults.html

…. it is explained that there are server, database, table and column defaults!

The OBJECTS tab show (or should show!) the collation for each column.

Advanced table properties show the table default (only), BUT IT MAY NOT BE USED by the columns actually!

[peterlaursen]

I have some columns using latin1_swedish_ci collation and some using latin1_danish_ci. The collation latin1_swedish_ci is the server default.

the columns using latin1_danish_ci show that all three places

the columns using latin1_swedish_ci show this both places in SQLyog. In MySQL Administrator they show up as using “default” collation.

Is there such thing as a “latin1_general_ci” collation at all ?

What is your server default ?

[peterlaursen]

.. bvadr !!! 😀

Just to point out how to use the tool: you can run the sync job from a Windows machine with SQLyog Enterprise installed or from a Linux machine with EITHER the SJA for Linux OR SQLyog enterprise (for Windows) installed on the WINE windows emulator.

The Windows version offers you a GUI to build the jobfiles (on Windows and Linux/WINE).

You'll find more information is our FAQ as well. Look in categories “SJA” and “data synchronization”.

And my article is not quite up to date, because with the SQLyog Enterprise version 5 you can even sync tables without a Primary Key (though it is recommended to have one). As soon as Ritesh has given me the necessary information about how this works I'll write-up the article.

[peterlaursen]

I got the point too now!

[peterlaursen]

I just tested with the free version. It works perfectly here.

Could you possible post a screenshot?

Do you create a conncection with the connections manager.

Start clicking on “New”

See attached picture.

[peterlaursen]

to pin it out:

execute this SQL with the above table

Code:

set sql_mode = '';
insert into `test`.`tt` (`t` ) values ('nn');

and a row is inserted. The MySQL server 'unstrictly' replaces the undefined numerical value with a zero. This applies when coloumn is defined as 'NOT NULL'. If column is defined as 'NULL' the value of n-column stays NULL with this statement.

However

Code:

set sql_mode = 'strict_all_tables';
insert into `test`.`tt` (`t` ) values ('nn');

raises the error

Quote:

Field 'n' doesn't have a default value

So what we have here is that traditional mySQL syntax is very 'forgiving' and 'relaxed' and 'non-strict'. That is true for versions up to 4.0. From 4.1 and 5.0 in particular a more 'strict' approach is used by MySQL defaults. And SQLyog was written for 3.x and 4.0 originally.

A parsing for NULL, type, (predefined) variables and functions in the grids is becoming even more important for each new MySQL release. It is for the same reason that we have the problem with CURRENT_TIMESTAMP for instance.

But with MySQL up to 4.0 it worked perfectly fine just to get the grid value and send it to the server as a string value.

[Author]

Posts