[peterlaursen]

I tried too and get the same error.

But there is one thing that you do wrong. OPTINALLY shoul not be checked. Optionally is when only strings are enclosed. Here numbers are enclosed too.

I have said it sometimes before. The CSV import tool is written by a C-prgrammer who does not understand that not all people are C-programmers. It is unusable for most people in my opinion! 😛 But who needs CSV when there are so many other import options ?!

EDIT: Sorry I forgot the smiley! But I always thought that the SQLyog CSV-functionality is the most non-intuitive and un-user-friendly I've ever seen. Should not surprise anyone that I think so – it is more than a year I wrote it for the first time here.

But no matter what I do I cant get those simple data to import! So I think something went wrong when “Excel” fill-in was added to the code.

[peterlaursen]

@ddaily

To attach a -csv you must zip it. For security only some filetypeds are allowed. Common graphics and ZIP.

[peterlaursen]

Confirmed. I tested on Linux too.

There also are problems with datasynch

I created these two tables on my Webhost

Quote:

CREATE TABLE `Base1`.`tablename1` (               

              `ID` bigint(20) NOT NULL auto_increment, 

              `t` bigint(20) NOT NULL default '0',     

              `u` varchar(20) default NULL,           

              PRIMARY KEY  (`ID`,`t`)                 

            ) ;

CREATE TABLE `Base2`.`TableName1` (               

              `ID` bigint(20) NOT NULL auto_increment, 

              `t` bigint(20) NOT NULL default '0',     

              `u` varchar(20) default NULL,           

              PRIMARY KEY  (`ID`,`t`)                 

            ) ;

and ran the datasync. I offfered me to sync the two tables (which it should not on Linux!). Howewer it failed. See picture.

Next I crated tablename1 and TableName1 in both databases. SQLyog displayed an error “Table allready exists” when the second was created. Nevertheless they were all created. Also see picture.

Next I created tablename1 like

Quote:

CREATE TABLE `tablename1` (               

              `ID` bigint(20) NOT NULL auto_increment, 

              `t` bigint(20) NOT NULL default '0',     

              `u` varchar(20) default NULL,           

              PRIMARY KEY  (`ID`,`t`)                 

            ) ;

in one database and

Quote:

CREATE TABLE `tablename1` (               

              `ID` bigint(20) NOT NULL auto_increment, 

              `T` bigint(20) NOT NULL default '0',     

              `U` varchar(20) default NULL,           

              PRIMARY KEY  (`ID`,`t`)                 

            ) ;

in another and ran datasync. The columns t and T were synced as were U and u. The should not!

Either case-sensivity should be handled automatically by the program, or it should be an option to choose by user. It is a little tricky when sync'ing two DB's on WIN and NIX respectively … For instance: What to do if there is tablename1 on WIN and both TableName1 and tablename1 on NIX ??

Maybe those issues would solve themselves more or less with SQLyog 4.3 that would compile with a Unicode codebase?? As long as SQLyog uses ANSI internally there is no chance to fix it I guess! At least not worth spending time with in the 4.2 tree! But should have attention with 4.3.

[peterlaursen]

the more I think about it the more I don't find it provoking or wrong.

Others connections and applications are not affected.

Actually that must be the very idea in making sql-modes connections settable and even swith-able from within a connection.

Actually I have been using it for 2 years without problems. The application that serves my photography database uses ANSI-mode and needs to run in ANSI-mode. This is specified in the parameters of the ODBC-setup (the 'Initial Statement' of the Connect Option -tab as of MyODBC 3.51.12. This is in this case “set sql_mode = 'ANSI' “). And no problems. I can have that application scanning my harddisk for graphics files (and writing paths and other files system data and generating thumbnails to the database) while at the same time working with SQLyog or any other application with traditional MySQL 'mode'.

the sql_mode is a CONNECTIONS setting (that specifies formal rules for the SQL syntax only), not a setting for DATABASES or databaseOBJECTS. When the data havegot into the MySQL database they are MySQL data no mater which syntax was used to get them there.

What does the MySQL GUI-tools do? I bet they start setting the sql-mode that they are coded with! It is OpenSource – you can check it!

That's why sql_mode is settable (yeah, I love to repeat myself!)

[peterlaursen]

BTW:

What prevents that SQLyog simply disables 'strict mode' and other incompatible modes when connecting? Or simply (if version() >= 4.1) then specify sql_mode = '' for the connection. You will then have to do a 'pre-connect' to test for version() and @@sql_mode and use that info to decide whether the 'real-connect” should be with sql_mode = '' . You could then display a splash screen like

Quote:

Default for connections to this server is ORACLE SQL (button>>details) .  SQLyog will use traditional MySQL SQL syntax for this connection.  Continue ?

The SQL-modes were created primarly to make it possible to execute scripts written for other databases with MySQL and easily migrate applications to MySQL. This is not relevant with SQLyog that was writtten for MySQL. If people want to execute scripts written for Oracle for instance with MySQL version 5 they can change the SQL-mode.

I think it is worth considering. The connections setting only affects the one connection. 'strict mode' is not 'safe mode' and 'non-strict mode' is not unsafe. It is safe with SQLyog! Better keep our heads cool now!

Unicode and localizations first – that is what I think right now!

What do other users think?

… well those that think, I mean 😛

But still the NULL issue still should be solved correctly!

[peterlaursen]

Fine with me! I don't need it right now!

Better do it right first time! And maybe have a look at some other 'sql-modes' too! Though the only important one besides 'strict mode' is 'ansi mode' and I don't think SQLyog is afffected by that. For instance SQLyog never uses the ” character – only ` and ' characters in SQL.

If there are no problems with ANSI and if STRICT can be handled automatically, then other mode-manipulations can be done with SQL. They won't need a GUI (though it is always nice!)

But I still am in favour of a 'hack' of the “create user” and “edit user” functionality. Because it can't wait till user management is totally rewritten. And I think it is safe to disable 'strict mode' for the session, do the change and restore old 'mode'.

[peterlaursen]

Heikii Tuuri from MySQL responded

this

Code:

insert into `test`.`tablename1` ( `id`, `t` ) values (  '',  'a' );

must be

Code:

insert into `test`.`tablename1` ( `id`, `t` ) values (  NULL,  'a' );

I thought we got those empty-string/NULL mismatches out of SQLyog but not quite yet it seems! :huh:

In 'non-strict-mode' the server sustitutes an empty string with with NULL with a non-string-type variable. But not in 'strict-mode'

So we have two issues:

1st issue:

A general mismatch of empty-string and NULL with SQLyog when it INSERTS a row. It should allways be NULL unless otherwise specified.

This is obviously a problem with autoincremented values in 'strict mode'. But more than so! It is a general DATA-bug inherited from a very early SQLyog version. Due to two facts:

a> empty strings are converted to NULL with numerical by the server with earlier MySQL versions and with mySQL 5 in 'non-strict mode'

b> it rarely matters whether string variables are empty or NULL.

…this has not be critical before. Not even discovered. But it is a DATA-bug and all DATA-bugs are critical in my opinion!

And that relates to (what we discussed elsewhere) that the DATA and RESULT pane of SQLyog does not distinguish properly between NULL and empty strings. We miss the “set NULL” button that is available for BLOBS and TEXTs. As long as nothing has been entered everything should be NULL! It is a little tricky to fix with a grid-type GUI. Just as trailing blanks are.

2nd issue:

In 'strict mode' all colums that do not have an explicit or implicit default must be written, when UPDATING. This is a minor coding issue I believe (by 'minor' I have not decided upon how much work there is in it – just that it is in principle no problem to understand and implement (only nice work if you can get it!) ).

[peterlaursen]

Quote:

I have attached a screen shot of the data on the left and the mysql results.

I don't think you have! 😀

BTW: you want to import to MySQL or some other Database (Access for instance?)

[peterlaursen]

Quote:

One crash is even reproducible: just press F2 (“edit”) on a Text field

If I understand you right I am still not able to reproduce! What do you mean by “that has an extra edit button” ? Are you inside the BLOB-viewer? And you really mean TEXT not CHAR/VARCHAR ?

However I experience something funny. First I press on a TEXT column BLOB viewer opens. If I close it then next I press SQLyog displays 'true' for the column. ???

BTW: is quite undocumented, isn't it? I don't find it anywhere! Not in help .. keyboard shortcuts.

[peterlaursen]

I created an entry in the MySQL bugs repository.

http://bugs.mysql.com/bug.php?id=14083&thanks=4

To me it is a bug that autoincrement is out-of-function in 'strict mode'.

[peterlaursen]

Quote:

Same issue as all other “strict mode” issues!

Basically yes! Plus one thing more here: SQLyog is not “up-to-date” with the most recent user privileges. Problem is that there are columns in USER-table that SQLyog does not know about and that don't have a default.

I favour this 'hack' of mine as far as user creation goes, if a permanent solution to all 'strict mode' issues is not available.

If vygi's experience that most recent MySQL-5 configres itself to 'strict mode' is the general thing than something will have to be done soon. Sooner than User Management can be rewritten!

[peterlaursen]

Well if it must be hard-coded then be so. User must then select valid engines.

As of MySQL ver. 5 you get MyISAM tables when trying to create a GEMINI or ISAM (unless set session|global sql_mode = 'NO_ENGINE_SUBSTITUTION'). If ARCHIVE was added of course same error would occur with earlier MySQL versions. But the ARCHIVE engine is great for big DB's that are not 'in production' anymore. To support it the only thing SQLyog needs to do is to put the keyword ARCHIVE into an ALTER TABLE statement.

The reall tricky stuff is the FEDERATED engine …

[peterlaursen]

Quote:

… the TO-DO list

Be careful that it does not fall over you one day! 😀

For efficiency I think that when NOT in 'strict mode' no changed is needed, but IF 'strict mode' all columns must be explicitly written. That means the the C-code must “branch” when INSERTing or UPDATing tables.

In the famous PPL (Peters Programming Language):

Code:

If not strict_mode write-changed-griditems-only else write-all

[peterlaursen]

of course it must be studied!

But I think you should add support for the ARCHIVE storage engine soon. It is all handled by MySQL server. Probably only a few lines in the SQLyog code!

[peterlaursen]

You use SQL for this!

Code:

Update mytable set newcolomn = oldcolumn;

or if you want to copy and add the suffix in one operation

Code:

Update mytable set newcolomn = concat(oldcolumn,'.suffix');

NOTE: there is no WHERE in the statement.

In SQL NO WHERE means EVERYWHERE. 😀

[Author]

Posts