Sql_mode

[pragmatic_lee]

MySQL added a new mode value “NO_ZERO_DATE” in 5.0.2. This mode setting prevents a 0 value from being inserted in to a column of datatype “date”. An insert line such as “insert into demotable set adate = 0” will fail when used in code (as expected and DESIRED). It will also fail when run in MySQL Query Browser (Incorrect date value: '0' for column 'adate' at row 1); however, the same line when run in SQLyog somehow bypasses the “server” setting and will accept and insert such a row.

[peterlaursen]

I think this answers your question:

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

SQL_mode is a per-connection setting. SQLyog always uses the '' (empty) sql_mode for its connection.

You can check the HISTORY tab after connection has been established.

Maybe we should get the GLOBAL sql_mode(s) and use the ones that are not conflicting with GUI functionality. “NO_ZERO_DATE” is a good example.

A workaround could be to save a statement like ” set sql_mode = 'whatever you like' ” as a favorite and execute as first statement of your own.

[pragmatic_lee]

The workaround works; however, NO_ZERO_DATE shouldn't conflict with the GUI and should be “honored” – in my opinion.

set sql_mode=

“STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE”

SELECT @@global.sql_mode

“STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

insert into demotable set adate = 0

Error Code : 1292

Incorrect date value: '0' for column 'adate' at row 1

(15 ms taken)

[peterlaursen]

Quote:

NO_ZERO_DATE shouldn't conflict with the GUI and should be “honored” – in my opinion.

I do not disagree. It is the 'strict modes' that conflict with the GUI. We have not changed this since SQLyog 5.0, but maybe we should consider it somehow now.

[Donna]

Can I please add my request that SQLyog honor the global sql_mode settings? Our developers use SQLyog to create stored procedures. From the MySQL documentation:

“MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force. “

In our my.cnf file we have set the sql_mode to ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE

But since SQLyog sets the sql_mode to '', the developers create these stored procs with the wrong sql_mode. I know they can set it on a session basis, but that's prone to error. Luckily, when I move changes from Development to Test I can set the sql_mode…but I'm waiting for the day that the change in sql_mode causes problems.

I'm also not clear what part of strict mode is not compatible with the GUI. Can you explain, please?

Thank you.

Donna

[peterlaursen]

We do intend to support sql_modes better then today. I doubt whether it is possible to support STRICT_TRANS_TABLES and STRICT_ALL_TABLES. But most of the rest of the common ones should be possible.

The main problem is that an empty cell in the grids (DATA and RESULT tabs etc.) is ambiguous. It is 'nothing' but is 'nothing' the same as NULL or EMPTY?

A workaround for you could be setting the sql_mode from the SQL pane manually after connecting. You can store the SQL as a 'favorite'. But some operations on data might fail (with an error) then. It all depends on what your data are like, if columns are defined NULL or NOT NULL, whether columns have defaults (note that BLOBS cannot have defaults!) and what operations you are actually doing.

[Anonymous]

I agree with Donna. SQLyog is breaking the documented behaviour for MySQL. Unless you've explicitly set @@session.sql_mode a CREATE PROCEDURE *should* create with @@global.sql_mode.

D.

[peterlaursen]

I do not agree with this “SQLyog is breaking the documented behaviour for MySQL”.

MySQL has made it possible for a client/connection to specify its own sql_mode. That is also 'documented behaviour'! Did you check what MySQL Administrator/Query Browser does?

[Anonymous]

peterlaursen wrote on Oct 27 2006, 09:43 AM:

I do not agree with this “SQLyog is breaking the documented behaviour for MySQL”.

MySQL has made it possible for a client/connection to specify its own sql_mode. That is also 'documented behaviour'! Did you check what MySQL Administrator/Query Browser does?

I do not agree with “MySQL has made it possible for a client/connection to specify its own sql_mode”, Mysql has in fact made it possible for a *user* of a client to specify its their own sql_mode, not to have a client overwrite the global value.

using mysql client you'll see that

select @@global.sql_mode;

select @@session.sql_mode;

return the same value.

i.e. the default startup connection state is @@session.sql_mode = @@global.sql_mode not @@session.sql_mode = ''

This holds true for Mysql Query Browser as well.

….although pointing to the way Query Browser “does things” isn't a great sales pitch for your product. I wouldn't have bought a copy of SQLyog if I thought Query Browser worked well!

D.

[peterlaursen]

As far as Query Browser is concerned I just asked because I was not sure about it myself.

If it should not be possible for a client/connection to specify its own SQL_mode why did MySQL then introduce that from 4.1 to 5.0?

As said we have plans for the future to respect the global sql_mode to the most possible extend. But This is not priority at the moment. It will not be before next year that we looke detailed into it.

[Anonymous]

peterlaursen wrote on Oct 30 2006, 07:35 AM:

If it should not be possible for a client/connection to specify its own SQL_mode why did MySQL then introduce that from 4.1 to 5.0?

My point here, is that you are taking the choice away from the user. When you create a connection you are doing an explicit SET @@session.sql_mode='', which is not what a user would expect. Its not the exihibited behaviour of the mysql clients. I'm not sure exactly WHY you explicitly set the sql_mode this way (perhaps you can tell us)?

I know the sql_mode can be set, I just think that it is intended for the USER not for client applications to blanket override the normal behaviour.

from the sqlyog code

Code:

/* Function to set sql_mode, it is used bs a hack to STRICT_ALL_TABLES */
void
MDIWindow::SetDefaultSqlMode()
{
wyString query;
MYSQL_RES *res;

query.Sprintf(“set sql_mode=''”);

res = ExecuteAndGetResult(m_tunnel, &m_mysql, query);

if(!res && m_tunnel->mysql_affected_rows(m_mysql)== -1)
return;

m_tunnel->mysql_free_result(res);

return;
}

Quote:

/* Function to set sql_mode, it is used bs a hack to STRICT_ALL_TABLES */

Hack?? Hmmmmmm!

D.

[Author]

Posts