[peterlaursen]

@ukwiz

Could you please explain

Quote:

As SQLyog 5 doesn't seem to fully support MySQL 5 …

????

What are you missing? There are a few minor issues with the structure sync tool and the SJA to be solved with SQLyog 4.1. Full support for MySQL 4.1-5.x charset control will be with 5.2. User management will then be the last thing to follow shortly after. Read http://www.webyog.com/faq/5_20_en.html

However SQLyog is a tool for Data Management primarly, not for Server Management. You shall probably never expect SQLyog to handle Server Configuration like 'MySQl Administrator' does.

Once more: what missing feature are you thinking about ?

[peterlaursen]

@vygi

I can inform you that at the Webyog Office in Bangalore everything is practically closed down from now till after New Year.

So even they have (hopefully) a happy holiday!

[peterlaursen]

Sorry about the missing 'show warnings' in 4.0. I should have checked it.

Actually I get 150 warnings when importing your data to MySQL 5. See attached. It is the same no matter if I use your (tab-delimited) txt.file, the .csv or whether I produce my own csv. or text from your .xls-file.

However I think there is a small issue with SQLyog here. It does not show all 150 warnings but only about 50. Some buffer running full? Or is a buffer-setting with the server?

But I cannot reproduce any sort of import problem with the data. You wrote you have had a lot of trouble and have had to try various formats. I can import the data with no problem in the first try no matter which text-format I use.

I also can open the .xls in Access and export using ODBC. Actually that was what I did in the first place to be sure to get the columns definitions right. Next I can empty the table and import any .csv/.txt format to that table.

[peterlaursen]

What next …

If you execute the SQL simply without using SQLyog GUI, but simply by typing/pasting the SQL. Do you get the same number of warnings? Can you now 'show warnings' ?

Next type (or copy from SQLyog SQL-pane and paste) into MySQL command-line client. What happens here?

[peterlaursen]

My point was that you build some SQL using OPTIONALLY ENCLOSED BY , but you don't use ENCLOSE in your data!

At least this should correspond! It might also very well remove the warnings, I think.

But still I don't understand all your problems with other CSV-formats/styles.

Is it possible to zip the .xls-file as well as the .csv-file and attach it here ? I'll look into in tomorrow then (it is late night here!)

Also not the error 1064, unless the show warnings statement is 'mixed with some garbage' from a non-empty buffer of some kind.

Also: is there any special server setting like ANSI-mode? What does 'Select sql_mode;' return ?

[peterlaursen]

“Optionally enclosed by” according to MySQL docs:

Code:

FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For output (SELECT … INTO OUTFILE), if you omit the word OPTIONALLY, all fields are enclosed by the ENCLOSED BY character. An example of such output (using a comma as the field delimiter) is shown here:

“1”,”a string”,”100.20″
“2”,”a string containing a , comma”,”102.20″
“3”,”a string containing a ” quote”,”102.20″
“4”,”a string containing a “, quote and comma”,”102.20″

If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose values in columns that have a string data type (such as CHAR, BINARY, TEXT, or ENUM):

1,”a string”,100.20
2,”a string containing a , comma”,102.20
3,”a string containing a ” quote”,102.20
4,”a string containing a “, quote and comma”,102.20

I think first you should ensure that your data and your use of ENCLOSED correspond!

[peterlaursen]

BTW: what happened to the QUOTE tag ?

[peterlaursen]

OK …

did you try to execute simply “show warnings”

I did not get this point before:

Quote:

The SQL error is not reported in response to the import process – but only on clicking the “Show warnings” button.

When you click the show warning button SQLyog sends simply “show warnings”. you can see that from history tab. This should not trigger an error 1064 … hmmm

however 'optionally enclosed by '”' ' means that strings and only strings in the data are enclosed with ” (doublequote). That is not as your data are, I guess.

Now have a look at

Quote:

Local news Local news and notices

is this one, two three four five or six variables ?! You should at least enclosed string-data, I think like

Quote:

“Local” “news” “Local” “news” “and” “notices”

or

“Local news” “Local news and notices”[/QUOTE].

But still 'show warnings' should not trigger error 1064!

[peterlaursen]

Quote:

Error No. 1064. You have an error in your SQL syntax….

and

Quote:

The issue is not being able to view the warnings!

With an error 1064 you don't get any warnings! You get an error! Warnings occur with MySQL if the statement executes, but not a 'clean' way. Here nothing executes and thus no warnings! The MySQL error 1064 is a 'garbage error' that occurs when no other and better error message is available. It is a MySQL server error – not a SQLyog error. Read http://www.webyog.com/faq/5_8_en.html

Quote:

As this is my first attempt at import with SQLyog, there may be configuration issues?

There are lots of configuration issues with CSV! Read about SQLyog and CSV here: http://www.webyog.com/faq/28_73_en.html

On the HISTORY tab you can see what SQL was actually did send to MySQL. Could you paste that statement in with a few lines of data?

More Q's:

Also are there some 'header-lines' in the file ?

Is comma or semicolon used as seperator ?

If you press the 'Change' button in CSV-dialogue what does it look like?

Did you create the table structure in advance and do filed definitions match the data?

It is much simpler in my opinion to open the .xls file from Access and use ODBC. But that probably is a matter of taste too.

[peterlaursen]

Just an update:

MySQL 5.1.4 was released this morning. Get it from the same FTP-mirror for instance. It is still alpha and there is still only a win-version without installer.

[peterlaursen]

Looks like then that pressing only does a graphics refresh, but does not refresh the memory stack/structure that holds information about available databases !?

[peterlaursen]

Confirmed fixed here!

[peterlaursen]

Isn't there two identical nested SELECT .. WHERE .. IN ?

One must be redundant, I think!?

Suggestion: try each SELECT for itself and see which one uses the memory!

[peterlaursen]

it might take some time … 😀

… but you can export your data, disable InnoDB, delete the InnoDB files, enable InnoDB with the per_table option and import again! I think it is the only way to be considered safe.

You might also simply wait to do this until you upgrade the server to a newer version (4.x>5.0 or 5.0>5.1. Then enable innodb_pr_table before importing data!

[peterlaursen]

No matter what …

bug confirmed fixed with 5.02 Beta. 😀

[Author]

Posts