Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 16

Displaying posts with tag: sql_mode (reset)

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...]
    Good Practice / Bad Practice: CREATE TABLE and the Storage Engine
    +0 Vote Up -0Vote Down

    When you write your create table statements, always make sure that you make them non-ambiguous. That way even though other servers might have different configurations, you make sure your table will be created in the same way.
    Imagine for instance you are developing an application on a development server, nicely storing all the scripts you need to create the same database on your production server. If the same script creates a table differently on both servers, that might cause you a lot of headache later on. At Open Query, we strive to minimise (or preferrably eliminate) headaches.

    One of the parts of the create table statement that has the largest impact is the storage engine specification. When you omit the storage engine from the create table statement, your table is automatically created with the default storage engine type configured for the server.

      [Read more...]
    MySQL security: data integrity issues
    +0 Vote Up -0Vote Down

    MySQL’s security model is not as elaborate as other popular databases. It’s missing quite a lot.

    I wish to point out what I think are some very disturbing security holes, which may affect the database integrity.

    This post is not about Roles, Kerberos, IPs and such. It’s about simple MySQL features, which allow common, unprivileged users, to break data integrity by using unprotected session variables.

    I will consider three such issues.

    We will assume a database with two tables, and two users.

    GRANT SELECT, INSERT, UPDATE, DELETE ON `w2`.* TO 'w2user'@'%';

    We have one ‘root’ user, and one very simple ‘w2user’, which can’t be accused of having too many privileges. The schema, with some sample

      [Read more...]
    MySQL's sql_mode: My Suggestions
    +0 Vote Up -0Vote Down
    Just the previous week, Anders mentioned the sql_mode with regard to Joomla. Past week, Shlomi's started a nice discussion pertaining to MySQL (http://www.mysql.com/" target="_mysql)'s sql_mode. He offers the suggestion to modify the installation procedure so MySQL is by default installed using a so-called "strict" sql_mode. In addition, he  [Read more...]
    sql_mode: a suggestion
    +0 Vote Up -0Vote Down

    I thought this deserves more than a comment on my previous post on the subject, in which I expressed the opinion that sql_mode is undesired.

    Back to reality: sql_mode is here right now. What else can be done?


    Is there anything to do about sql_mode? I believe so: make it strict by default.

    I wish to stress the following:

    • sql_mode is empty by default (which means it is very permissive).
    • It is not documented enough. Many DBAs don’t really know anything about it.
    • As a MySQL instructor, I have repeatedly witnessed the look of surprise on a student’s face when he learns that in the default configuration, an overflowed value is silently truncated to match the data type. My personal observation: people don’t like the default
      [Read more...]
    Do we need sql_mode?
    +0 Vote Up -0Vote Down

    sql_mode must be one of the most elusive issues in setting up a MySQL database. It is also responsible for difficulties in migration, upgrading and securing of databases.

    MySQL usually has little issues with migrating database from one machine to another, one version to another, one OS to another, one architecture to another. If we’re not sure, we can always migrate using mysqldump’s logical dump, right?

    Not entirely right. The elusive sql_mode (empty by default) may affect out backup+restore, may affect our application behavior, may affect our data integrity.

    • Did we remember to set NO_AUTO_CREATE_USER? Oh dear, we have some users without passwords.
    • Did we remember to set NO_AUTO_VALUE_ON_ZERO? Oh dear, we’ve dumped our database for backup, restored, but AUTO_INCREMENT values
      [Read more...]
    Mind the SQL_MODE when running ALTER TABLE
    +0 Vote Up -0Vote Down

    The other day, a client mentioned they were getting strange results when running ALTER TABLE. The episode involved modifying an existing primary key to add an auto_increment primary key: it was “shifting” values. Say what?!

    As it turns out, it was a very special value getting changed: zero. Some fiddling revealed the underlying reason. Care to join me?

    To understand what’s going on, follow the example below as we start with an empty database, create a table and insert a few rows:

    mysql> use test;
    Database changed
    mysql> show tables;
    Empty set (0.00 sec)
    mysql> create table test_table (id int not null primary key) engine=innodb;
    Query OK, 0 rows affected (0.01 sec)
    mysql> desc test_table;
    | Field |
      [Read more...]
    SQL commands for a fresh install
    +0 Vote Up -0Vote Down

    As a rule I always execute the following commands on a fresh database installation. Then for each user that is granted privileges, of course they are given a password and the host permissions are locked down as much as possible for their needs, alternately table and column privs as well. I’m not going to get into the parts the manual covers, but rather mention a couple of things on my mind.

    First the initial commands:

    mysql> use mysql
    mysql> delete from user where User='';
    mysql> delete from db where User='';
    mysql> update user set Password=password('password_here') where User=’root’;
    mysql> flush privileges;

    However, one thing I’ve noticed is that when you hand over a server to someone that doesn’t necessarily follow your same understanding or regard to user privilege security, bad things can

      [Read more...]
    Showing entries 1 to 16

    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.