Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 17 7 Older Entries

Displaying posts with tag: sql_mode (reset)

GROUP BY fixed
+0 Vote Up -0Vote Down

Friend and former colleague Roland Bouwman has written an excellent update on the GROUP BY implementation in MySQL.

MySQL’s implementation of GROUP BY has historically been quirky. Sometimes that quirkiness has been useful, but often it causes grief as SQL authors can make mistakes that are executed but don’t produce the results they want (or expect).

Simple example:

SELECT cat, COUNT(val) as cnt, othercol FROM tbl GROUP BY cat

The ‘cat‘ column is in the GROUP BY clause, the COUNT(val) is an aggregate, but the ‘othercol‘ column is … well… neither. What used to effectively happen is that the server would pick one othercol value from within each group. As I

  [Read more...]
Stored Routines to easily work with the SQL_MODE
+1 Vote Up -0Vote Down

Versione italiana

Working with the SQL_MODE can be tricky, because it’s a command separated list involving long keywords. To be honest, I hate to rewrite the flags, trying to read an unreadable list, etc. Of course some string functions can help (Justin Swanhart suggests to use REPLACE() to make comma-separated lists readable).

I made a small set of Stored Routines wich allow me to easily show SQL_MODE, add a flag, drop a flag and check if a flag is set. These routines work with the GLOBAL SQL_MODE; if you don’t like this, simply replace “@@global.” with “@@session.” in the

  [Read more...]
Working with comma separated list MySQL options
+1 Vote Up -0Vote Down
Over time, some options have crept into the MySQL server which are comma separated lists of options. These include SQL_MODE, optimizer_switch, optimizer_trace and a few other variables.

Optimizer_switch is particularly problematic to work with as it contains many options and is hard to interpret. This is what you usually see when you examine optimizer_switch:


As you can see, seeing which option is on or off is rather difficult. You can use the REPLACE function to make this easier:
mysql> select replace(@@optimizer_switch,

  [Read more...]
SQL_MODE and storage engines
+2 Vote Up -0Vote Down

I was again reminded why setting SQL_MODE is so important in any new MySQL environment. While performing benchmark tests on parallel backup features with a common InnoDB tablespace and per file tablespace, I inadvertently missed an important step in the data migration. The result was the subsequent test that performed data population worked without any issues however there was no data in any InnoDB tables.

These are the steps used in the migration of InnoDB tables from a common tablespace model to a per-table tablespace model.

  • Dump all InnoDB tables
  • Drop all InnoDB tables
  • Shutdown MySQL
  • Change the my.cnf to include innodb-file-per-table
  • Remove the InnoDB ibdata1 tablespace file
  • Remove the InnoDB transactional log files
  • Start MySQL
  • Verify the error log
  • Create and load new InnoDB
  •   [Read more...]
    Do I really want to get rid of SQL_MODE?
    +2 Vote Up -0Vote Down
    I recently wrote about SQL_MODE, and Ronald Bradford was quick to reply with his view on things. Although my post was a bit of a rant, and I think it was a necessary rant, it seems that Ronald and I largely agree on the overall matter of things.

    Now today WebYog implies that I and Ronald want to get rid of it and replace it with more strict, rigid checks on data entering MySQL. Tell you want, however much I would like that, I am not stupid, I realize that is not practical. Rather, I say sure, keep SQL_MODE, but make it possible to enforce it and check it, and do

      [Read more...]
    Why SQL_MODE is essential even when not perfect
    +2 Vote Up -0Vote Down

    In a recent rant on Why I think SQL_MODE is useless…, I wanted to counteract this statement with why we MUST all use SQL_MODE, even with the inherit flaws.

    The fundamental principle of a database is to restore and retrieve data. When I can insert data into the database and then I select this data it is different, this is fundamentally wrong. This is a loss of essential data integrity, something a database should NEVER do.

    SQL_MODE solves the problem of “silent truncation” in most instances, and produces an all important error. As pointed out, the SQL_MODE has several limitations, however the benefits do out way the risks. Quality control on source code can reduce the limitations, but no amount of coding can stop the CRUD that

      [Read more...]
    MySQL’s SQL Deviations and Extensions
    +3 Vote Up -1Vote Down

    Today at Kaleidoscope I will be doing a 90-minute session comparing MySQL’s SQL syntax to the ANSI/ISO SQL:2003 standard, entitled What Do You Mean, “SQL Syntax Error”?

    You can download the PDF slides now.

    For those that may be following along the presentation later today (4 pm Eastern time), here are some links that I may throw out during the session:

    • SQL 2003 standard – actually it is “Information taken from the Final Committee Draft (FCD) of ISO/IEC 9075-2:2003″ but it’s extremely close to the actual standard. The actual standard is a document that costs a non-trivial amount of money to get, and cannot be

      [Read more...]
    MySQL and Quoting
    +4 Vote Up -2Vote Down

    MySQL does not follow the ANSI SQL standard for quoting. MySQL’s default quoting behavior is that either single or double quotes can be used to quote a string (this gets me into trouble when I work with Oracle databases, as double quotes do not indicate a string!).

    mysql> SELECT 'alive';
    | alive |
    | alive |
    1 row in set (0.00 sec)
    mysql> SELECT "alive";
    | alive |
    | alive |
    1 row in set (0.00 sec)

    Bare words are dealt with in context; in this case, a bare word would be parsed as a column name:

    mysql> SELECT alive;
    ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

    Backquotes are the way MySQL escapes table names. So, if you want a reserved word, number or operator to be the name of an object (ie, a table named “1″ or a column named “date”)

      [Read more...]
    Those oversized, undersized variables defaults
    +2 Vote Up -0Vote Down

    Some mysqld parameters are far from having reasonable defaults. Most notable are the engine-specific values, and in particular the InnoDB parameters.

    Some of these variables have different defaults as of MySQL 5.4. innodb_buffer_pool_size, for example, is 128M on 5.4. innodb_log_file_size, however, has changed back and forth, as far as I understand, and is down to 5M again. These settings are still the same on 5.5.

    I wish to present some not-so-obvious parameters which, in my opinion, have poor defaults, for reasons I will explain.

    • group_concat_max_len: This parameter limits the maximum text length of a GROUP_CONCAT concatenation result. It defaults to 1024. I think this is a very low value. I have been using
      [Read more...]
    But I DO want MySQL to say “ERROR”!
    +4 Vote Up -0Vote Down

    MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data.

    • Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though).
    • Calling CREATE TEMPORARY TABLE? You get silent commit.
    • Issuing a ROLLBACK on non-transactional involved engines? Have a warning; no error.
    • Using LOCK IN SHARE MODE on non transactional tables? Not a problem. Nothing reported.
    • Adding a FOREIGN KEY on a MyISAM table? Good for you; no action actually taken.
    • Inserting 300 to a TINYINT column in a
      [Read more...]
    Showing entries 1 to 10 of 17 7 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.