Autoincr Don' Work In 'strict Mode'

[peterlaursen]

I experience that

when this SQL is executed

Code:

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

from SQLyog GUI (by entering a value in t-column and moving cursor) into this table

Code:

CREATE TABLE `tablename1` (                
             `id` bigint(20) NOT NULL auto_increment,  
             `t` varchar(20) default NULL,            
             PRIMARY KEY  (`id`)                      
           ) ENGINE=MyISAM DEFAULT CHARSET=latin1

The error as shown in attachment is raised when server is running in 'strict mode'. The autoincrement does not have effect ?! Is this a bug or a feature ? Expected behaviour ?? Could SQLyog do otherwise ?

in 'non-strict mode' it works as usual/expected: the server inserts the autoincr-values.

[peterlaursen]

Unfortunately this is not only an autoincrement issue.

This table

Code:

CREATE TABLE `tablename1` (            
             `t` varchar(20) default NULL,        
             `n` bigint(20) default NULL          
           ) ENGINE=MyISAM DEFAULT CHARSET=latin1  

makes SQLyog send this SQL

Code:

insert into `test`.`tablename1` ( `t`, `n` ) values (  'c',  '' )

when a value in first row is changed and the samme error occurs.

The MySQL docs says

Quote:

the docs say:

“If you are not running in strict mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that doesn't name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 13.1.5, “CREATE TABLE Syntax”. See also Section 1.8.6.2, “Constraints on Invalid Data”.

If you want an INSERT statement to generate an error unless you explicitly specify values for all columns that do not have a default value, you should use STRICT mode. See Section 5.3.2, “The Server SQL Mode”. “

in 'strict mode' it is necessary

1) either to explicitly write values to all colums

2) or completely omit the colums where there is no change from the statement. That would however create problems with colums defined as “NOT NULL – NO DEFAULT” as we have seen here http://www.webyog.com/forums/index.php?sho…=1724&hl=strict

[Ritesh]

Issue added in the TO-DO list.

[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]

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]

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!) ).

[Ritesh]

I was researching on this strict mode issues yesterday night. It affects various options of SQLyog in varying degree of importance.

Data tab seems to be the most important one. Before taking a shortcut method, we need to decide how to handle NULL and EMPTY STRINGs. We also need to know how different column types behaves in STRICT MODE specially with and without default values.

[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]

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!

[Ritesh]

Thought provoking.

[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!)

[Author]

Posts