[peterlaursen]

Yes I can reproduce now …

It seems that you will have to enter at least 2 characteres in “line terminated by” -field.

It you only enter 1 character you will miss one character at the end

and if don't enter any actually you will miss 2 characters …

I misunderstood you from the start – I thougt you meant a missing character in each row but thats not it. I now see that it is only at the very end

My data again

Lines terminated with $$:

John McLaughlin|The Heart of Things|Jazz$$Danu|The Road Less Traveled|Folk$$Bud Powell|A Portrait of Thelonious|Jazz$$Mississippi John Hurt|Revisited|Blues$$Andy Statman Klezmer Orchestra|Klezmer Suite|Klezmer$$Herbie Mann|Memphis Underground|Jazz/Soul/Funk$$Kek Lang|Chants Room|Folk/Trad.$$Astor Piazzolla|Concierto de Nascar|Tango$$Grady Tate|All Love/Grady Tate Sings|Jazz$$The Mills Brothers|Chronological, Vol. 3|Jazz$$Cubanismo|Reencarnacion|Latin$$Torben Westergård|Brazilian Heart|Latin$$Duke Ellington|The Private Collection vol. 7|Jazz$$Kassé-Mady Diabaté|Kassi Kasse|Afrikansk/Vest$$Arne Forchhammer Trio|Assimilation|Jazz$$Vieja Trova Santiaguera|Hotel Asturias|Latin$$Sergio Mendes|Brasileiro|Latin$$Fernando Tarres|The Outsider|Jazz/Fusion$$Jan Johansson|Folkvisor|Jazz$$Sonny Boy Williamson|The Bluebird Blues|Blues

Lines terminated with $:

John McLaughlin|The Heart of Things|Jazz$Danu|The Road Less Traveled|Folk$Bud Powell|A Portrait of Thelonious|Jazz$Mississippi John Hurt|Revisited|Blues$Andy Statman Klezmer Orchestra|Klezmer Suite|Klezmer$Herbie Mann|Memphis Underground|Jazz/Soul/Funk$Kek Lang|Chants Room|Folk/Trad.$Astor Piazzolla|Concierto de Nascar|Tango$Grady Tate|All Love/Grady Tate Sings|Jazz$The Mills Brothers|Chronological, Vol. 3|Jazz$Cubanismo|Reencarnacion|Latin$Torben Westergård|Brazilian Heart|Latin$Duke Ellington|The Private Collection vol. 7|Jazz$Kassé-Mady Diabaté|Kassi Kasse|Afrikansk/Vest$Arne Forchhammer Trio|Assimilation|Jazz$Vieja Trova Santiaguera|Hotel Asturias|Latin$Sergio Mendes|Brasileiro|Latin$Fernando Tarres|The Outsider|Jazz/Fusion$Jan Johansson|Folkvisor|Jazz$Sonny Boy Williamson|The Bluebird Blues|Blue

Lines terminated with

John McLaughlin|The Heart of Things|JazzDanu|The Road Less Traveled|FolkBud Powell|A Portrait of Thelonious|JazzMississippi John Hurt|Revisited|BluesAndy Statman Klezmer Orchestra|Klezmer Suite|KlezmerHerbie Mann|Memphis Underground|Jazz/Soul/FunkKek Lang|Chants Room|Folk/Trad.Astor Piazzolla|Concierto de Nascar|TangoGrady Tate|All Love/Grady Tate Sings|JazzThe Mills Brothers|Chronological, Vol. 3|JazzCubanismo|Reencarnacion|LatinTorben Westergård|Brazilian Heart|LatinDuke Ellington|The Private Collection vol. 7|JazzKassé-Mady Diabaté|Kassi Kasse|Afrikansk/VestArne Forchhammer Trio|Assimilation|JazzVieja Trova Santiaguera|Hotel Asturias|LatinSergio Mendes|Brasileiro|LatinFernando Tarres|The Outsider|Jazz/FusionJan Johansson|Folkvisor|JazzSonny Boy Williamson|The Bluebird Blues|Blu

[peterlaursen]

I tested once more with a copy of the DB where charset was set as utf8.

Still can't reproduce the error.

But although charset is now uft8, characters in the base are still latin-based characters, so that's not necessisarily all there is to it …

[peterlaursen]

I have tested with MySQL for windows ver. 4.1.11 and 5.0.4 and Sqlyog 4.05 and 4.06 beta4 and I can't reproduce the error!

See for yourself: Here are sample data form my music database (first 20 records – columns artist, albumtitle & genre). And as you see genres “Jazz”, “Blues” etc. are not abbreviated to “Jaz”, “Blue” etc.

John McLaughlin|The Heart of Things|Jazz||Danu|The Road Less Traveled|Folk||Bud Powell|A Portrait of Thelonious|Jazz||Mississippi John Hurt|Revisited|Blues||Andy Statman Klezmer Orchestra|Klezmer Suite|Klezmer||Herbie Mann|Memphis Underground|Jazz/Soul/Funk||Kek Lang|Chants Room|Folk/Trad.||Astor Piazzolla|Concierto de Nascar|Tango||Grady Tate|All Love/Grady Tate Sings|Jazz||The Mills Brothers|Chronological, Vol. 3|Jazz||Cubanismo|Reencarnacion|Latin||Torben Westergård|Brazilian Heart|Latin||Duke Ellington|The Private Collection vol. 7|Jazz||Kassé-Mady Diabaté|Kassi Kasse|Afrikansk/Vest||Arne Forchhammer Trio|Assimilation|Jazz||Vieja Trova Santiaguera|Hotel Asturias|Latin||Sergio Mendes|Brasileiro|Latin||Fernando Tarres|The Outsider|Jazz/Fusion||Jan Johansson|Folkvisor|Jazz||Sonny Boy Williamson|The Bluebird Blues|Blues

Here I've chosen fields terminated by “|” and lines terminated by “||” (for visual apperance). But it also does not matter what termination and escape characters I use. I also tested with some other data and still can't reproduce your problem.

It could however be some sort of charset problem. There are unfixed issues with sqlyog and charsets control introduced with Mysql 4.1.1 and later (should be fixed in a couple of months I've been told!)

What is your charset setting (see create statement for the table at the bottom of Objects pane!) ?

since I'm European my charset is “latin1”. Could be a problem with double-byte charsets (Chinese, Korean etc)…

Or you don't happen use only the first “halfpart” of a “two-stroke character” such as “~” (maybe this is just a silly idea!).

[peterlaursen]

what I meant …

First TIMESTAMP in table definition could run either

CREATE TABLE `tablename1` (

`ID` bigint(20) NOT NULL auto_increment,

`mydate1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`mydate2` timestamp NOT NULL default '0000-00-00 00:00:00',

`mydate3` timestamp NULL default NULL,

`mystring` varchar(20) default NULL,

PRIMARY KEY (`ID`)

CREATE TABLE `tablename3` (

`ID` bigint(20) NOT NULL auto_increment,

`mydate1` timestamp NOT NULL default CURRENT_TIMESTAMP,

`mydate2` timestamp NOT NULL default '0000-00-00 00:00:00',

`mydate3` timestamp NULL default NULL,

`mystring` varchar(20) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

The point was that mydate2 should have been defined with NOT NULL, mydate3 with NULL

BTW:

did you change something with the code in Beta4. I don't think I can reproduce the exact behaviour of beta3!

Or maybe it's just my poor head that needs to rest 😮

But problems with INSERTs and UPDATEs are unchanged with Beta4.

[peterlaursen]

You're right again …

create_view and show_view privileges can be 'global privileges' or 'schema privileges'.

At least that's what “MySQL Administrator” lets you do with them.

[peterlaursen]

I have to settings of max_allowed_packet i my my.ini

– one in the main server section [mysqld] and one in the [mysqldump] section

i.e.:

[mysqld]

..

..

max_allowed_packet=100M

[mysqldump]

quick

max_allowed_packet = 16M

I have changed the first one from 2 to 100 M. But besides that and besides that I changed the datadir location I just use one of the templates coming with MySQL.

I've not touched the latter. Is this the one you are talking about Shadow ?

[peterlaursen]

OK Shadow …

but probably you'll need to have CREATE_VIEW and SELECT priviliges to the underlying TABLE to CREATE the VIEW.

'twas probably what I mismatched.

Ritesh told me recently that they will need a few months to fix some other issues before they can seriouslys start implementing support for the new features of MySQL ver5. So there is time for a debate, and more ideas!

Wasn't it an idea that you wrote something similar concerning how to integrate SP's and Triggers with the Sqlyog GUI ??

Or you might happen to know someone who has used SP with MSSQL, Oracle or DB2 and who is migrating til MySQL.

Traditional users of MySQL probably won't have many good ideas, since they haven't used it before.

[peterlaursen]

Oh .. you were there allready!

Good thing that problems are fixed! 🙂

[peterlaursen]

GGRRRR .. I really have prob's with my mouse. I'd better get a new one!

With Sqlyog 4.06 an latin1 charset I can't reproduce it.

[peterlaursen]

do you know if it is an issue realted to sqlyog ?

Try

[peterlaursen]

More considerations

*******************

First TIMESTAMP in table definition could run either

CREATE TABLE `tablename1` (

`ID` bigint(20) NOT NULL auto_increment,

`mydate1` timestamp NOT NULL default CURRENT_TIMESTAMP,

`mydate2` timestamp NULL default '0000-00-00 00:00:00',

`mydate3` timestamp NULL default NULL,

`mystring` varchar(20) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

(with sqlyog 4.06 you achieve this by setting the default value to CURRENT_TIMESTAMP)

or

CREATE TABLE `tablename1` (

`ID` bigint(20) NOT NULL auto_increment,

`mydate1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`mydate2` timestamp NULL default '0000-00-00 00:00:00',

`mydate3` timestamp NULL default NULL,

`mystring` varchar(20) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

(with sqlyog 4.06 you achieve this by omitting the default value AND specifying NOT NULL)

Please note:

************

I have added more TIMESTAMP columns – and they should behave different with INSERT and UPDATE statements.

mydate1 is FIRST TIMESTAMP and should behave in a “standard MySQL manner” as far as UPDATING 'first TIMESTAMP field' goes.

mydate1 and mydate2 are NOT FIRST TIMESTAMP so the “standard MySQL manner for first TIMESTAMPs” doesn't apply here so they just should behave as the definition goes (whether they are defined with NULL or NOT NULL).

Actually I think tests with TIMESTAMPs should run against SCHEMAs like (both of) these two ones.

The sql generated by sqlyog is now

update `test`.`tablename1` set `ID`='6', `mydate1`='2005-05-03 09:22:35', `mydate2`='2005-05-03 10:55:54', `mydate3`=NULL, `mystring`='thisstring' where `ID`='6'

I still think it should be

update `test`.`tablename1` set `ID`='6', `mystring`='thatstring' where `ID`='6'

IN CONCLUSION

*************

the UPDATE statement should not list TIMESTAMP fields unless the user explicitly wants to update a TIMESTAMP with a value that's entered from the keyboard.

BTW: when will pic upload work again ???

[peterlaursen]

That's right … 4.06 fixes the problem with the “ALTER TABLE” statement. Sqlyog now generates SQL like

alter table `test`.`tablename1` ,change `v` `ID` bigint (20) NULL , change `b` `mydate` timestamp NULL , change `n` `mystring` varchar (20) NULL

And that runs withourt errors.

But I believe there is still a problem/undesired behaviour with the INSERT- and UPDATE-statements

Table definition in this example runs

CREATE TABLE `tablename1` (

`ID` bigint(20) NOT NULL auto_increment,

`mydate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`mystring` varchar(20) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

INSERTING

********

When INSERTING rows by adding values for column 'mystring' the value of 'mydate' generated is 0000-00-00 00:00:00 .

the SQL (from history pane) is

insert into `test`.`tablename1` ( `ID`, `mydate`, `mystring` ) values ( 'NULL', 'CURRENT_TIMESTAMP', 'newstring' )

should be

insert into `test`.`tablename1` ( `ID`, `mydate`, `mystring` ) values ( NULL, CURRENT_TIMESTAMP, 'thisstring' )

the first example generates TIMESTAMP value '0000-00-00 00:00:00'

the second example generates correct value 2005-05-03 09:22:56

UPDATING

********

Changing a string value in the example generates this sql

update `test`.`tablename1` set `ID`='10', `mydate`='2005-05-03 09:22:56', `mystring`='thatstring' where `ID`='10'

The value '2005-05-03 09:22:56' is current content of the field and thus the value is NOT changed. But that is what i call undesired behaviour, since it overrides the “normal” behaviour of the MySQL server. I think the sql should be (only)

update `test`.`tablename1` set `ID`='10', `mystring`='thatstring' where `ID`='10'

… because that wil allow the server to do as is normal. No overriding takes place with this example.

SUMMARY

*******

1) Problem with ALTER TABLE is fixed

2) There is still a regular BUG with INSERT

3) There is undesired behaviour with UPDATE

[peterlaursen]

If you want to use MySQL command-line tools you should read and understand these

http://dev.mysql.com/doc/mysql/en/mysql.html

http://dev.mysql.com/doc/mysql/en/server-options.html

http://dev.mysql.com/doc/mysql/en/mysqladmin.html

If you just doubleclik the mysqld.exe file or if you try to run “mysqld” from a DOS command prompt … then it just shows up for a second and goes away! From a DOS-promt first navigate to the mysqlbin directory and then enter the appropiate command (mysql, mysqld or mysqladmin) with the correct parameteres.

But what is the “code” you want to write ?

Can't you do that from the SQL-pane in Sqlyog ?

[peterlaursen]

I just tested on my system (Athlon xp+ 1700, 512 MB RAM)

With a dump of about 12 MB, it takses 20-25 seconds to import with “create bulk insert statements” unchecked. With “create bulk insert statements” checked it's less than 5 seconds.

Also I can't reproduce that Capital Letters are “decapitalized” ( that was what you meant ? ). But I have only Windows' machines, so if it is some charset mismatch with MySQL across platforms I don't have any chance to find out …

[peterlaursen]

confirmed!

[Author]

Posts