Monday Sep 16, 2013

Inserting NULLs into NOT NULL columns in 5.6: refused by default

MySQL 5.6 ships with a default config file that sets the SQL mode to NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES . Here is what happens if you try to insert NULL values into a table with NOT NULL columns:

mysql> create table safetyfirst(
    -> id int primary key not null auto_increment,
    -> country varchar(60) NOT NULL,
    -> product varchar(60) NOT NULL );
Query OK, 0 rows affected (0.24 sec)

mysql> insert into safetyfirst(country) values('Sweden');
ERROR 1364 (HY000): Field 'product' doesn't have a default value

If someone tells you that MySQL 5.6 by default allows you to do this, ask them to prove it using the default settings we use for new installations and check their claim by asking them for the output of SHOW VARIABLES LIKE 'sql%'; .

We would like to use NO_ZERO_DATE, NO_ZERO_IN_DATE, NO_AUTO_VALUE_ON_ZERO, ERROR_FOR_DIVISION_BY_ZERO as well but we know that many web applications use some of these things, so we did not do that for 5.6. If you are doing new application development or provide a development framework please strongly consider using these as well.

(2013-09-18 rewrote unclear last paragraph)

Thursday Aug 22, 2013

Remove on sight: thread_concurrency, innodb_additional_mem_pool_size, innodb_use_sys_malloc

If you have thread_concurrency, innodb_additional_mem_pool_size or innodb_use_sys_malloc in one of your my.cnf or my.ini files please remove it at your next opportunity unless one of the unlikely exceptions applies.

Thread_concurrency is a setting for only Solaris that has no effect in recent Solaris versions like 11. For Solaris 8 and earlier it gave the thread system a hint about how many threads to use for MySQL. Solaris 8 was last a supported platform for MySQL 5.1. We have deprecated this setting from 5.6.1 and removed it from 5.7.

Innodb_additional_mem_pool_size is used in any operating system to specify the initial size of a buffer used for InnoDB data dictionary information when the old InnoDB internal memory allocator is being used. InnoDB automatically increases this size on demand. Until late versions of MySQL 4.0, InnoDB would crash when trying to increase the size, so you needed this option. We have deprecated this setting from 5.6.3 and will remove it later.

The InnoDB internal memory allocator was only needed because when the InnoDB project was started the allocators in many operating systems did a bad job with many memory allocations happening concurrently. That situation persisted until sometime around the release of MySQL 4.1 through 5.1, with operating system allocators gradually getting better. The InnoDB internal allocator is no longer needed and we no longer use it by default in the InnoDB plugin in 5.1 or in 5.5 or 5.6, with innodb_use_sys_malloc set to 1. It is deprecated from 5.6.3 and we'll remove it in a future release because there is now no reason to have it turned off. If you use 5.1 with the InnoDB plugin, 5.5 or 5.6 it's best to remove this setting now and just use the default enabled setting.

Why remove them? If you remove them now you won't be inconvenienced when they go away and it's better to have only things that matter in your configuration files, not things that don't.

Thursday Nov 15, 2012

MySQL Server 5.6 default my.cnf and my.ini

We've introduced a default my.cnf / my.ini file for MySQL Server that you can now see in the 5.6.8 release candidate:

# For advice on how to change settings please see

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M


# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin


# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# socket = ..... 
# server_id = .....


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 




There is also a template file called my-default.cnf or my-default.ini that has these lines near the start:

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.


On Linux systems, the mysql_install_db command will copy the template file to the final location, where the server will read and use the file, removing the extra three lines. On Windows, the installer will create extra settings based on the answers you gave during installation. Neither will overwrite an existing my.cnf or my.ini file.

The only initially active setting here is to change the value of  sql_mode from the server default of NO_ENGINE_SUBSTITUTION to NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES. This strict mode changes warnings for some non-standard behaviour into errors. This can cause applications which rely on the non-standard things, like dates that aren't valid, to lose data. If we had just changed the server default, the new setting would affect all servers that lack an explicit sql_mode setting, including those where strict mode is harmful. So we did it in the default file instead because that will only affect new server installations.

You should expect that in our next version after 5.6, the server default will include STRICT_TRANS_TABLES. Our Windows installer and some of our connectors already use STRICT_TRANS_TABLES by default. Strict has been our preferred setting for many years and it is good to see some development platforms are using it.

If you need the old behaviour, just remove the STRICT_TRANS_TABLES setting. If you do this, please also ask your application provider to make it unnecessary. They can do that by setting the session sql_mode setting in their own connections, so the rest of the applications using the server don't have to have an undesirable default.

We've kept this file as small as possible because we found that our old files were too big and confused people. We've also now removed the old my-huge and related example files.

One key part of this is the link to the documentation, where we will provide an introduction to some key settings. We'd like to hear your feedback on settings that will benefit most users or are most important to call out for existing users. Please do that by commenting here or if you prefer by adding comments to this bug report.

Friday Oct 05, 2012

Act NOW to maximise the chance of your bugs being fixed before 5.6 is declared production ready!

As an RC release, MySQL 5.6 has gone through extensive QA. But what matters most is whether 5.6 works for your environment and application needs - and now's the ideal time to test that, and provide us with any feedback on what needs fixing as we approach GA.

Thursday Oct 04, 2012

MySQL Server 5.6 defaults changes

We're improving the MySQL Server defaults, as announced by Tomas Ulin at MySQL Connect. Here's what we're changing:

 Old  New  Notes
back_log  50  50 + ( max_connections / 5 ) capped at 900
binlog_checksum  NONE  CRC32
 New variable in 5.6. 5.5 doesn't accept the checksums. Use NONE if you want to read binlog with 5.5 or on master if have replication slaves using 5.5.
binlog-row-event-max-size  1k  8k  no binlog_row_event_max_size variable, just the option form.
flush_time  1800  Windows changes from 1800 to 0
 Was already 0 on other platforms
host_cache_size  128  128 + 1 for each of the first 500 max_connections + 1 for every 20 max_connections over 500, capped at 2000
 New variable in 5.6
innodb_autoextend_increment  8  64
 64 is 64 megabytes
 0  8. On 32 bit Windows only, if innodb_buffer_pool_size is greater than 1300M, default is innodb_buffer_pool_size / 128M
innodb_concurrency_tickets  500  5000

innodb_file_per_table  0  1

 5M  48M
 InnoDB will change size to match my.cnf value. Also see innodb_log_compressed_pages and binlog_row_image
innodb_old_blocks_time 0  1000
1 second
innodb_open_files  300  300. If innodb_file_per_table is ON, higher of table_open_cache or 300
 20  300
 0  1
innodb_stats_on_metadata  on  off
join_buffer_size 128k  256k

max_allowed_packet  1M  4M

max_connect_errors  10  100

open_files_limit  0  5000
 See Note 1
query_cache_size  0  1M

query_cache_type  ON/1  OFF/0

sort_buffer_size  2M  256k

 See later post about default my.cnf for STRICT_TRANS_TABLES
sync_master_info  0  10000
 Recommend: master_info_repository=table
sync_relay_log  0  10000

sync_relay_log_info  0  10000
 Recommend: relay_log_info_repository=table. Also see Replication Relay and Status Logs
table_definition_cache  400  400 + table_open_cache / 2, capped at 2000
table_open_cache  400  2000   Also see table_open_cache_instances
 0  8 + max_connections/100, capped at 100

Note 1: In 5.5 there were already rules to ask the OS for the highest of (10 + max_connections + table_cache_size * 2) or (max_connections * 5) or the specified/default open_files_limit. The default is now calculated but the other rules are still used. If the OS refuses to allow as many as requested, max_connections and table_cache_size are reduced and you will see a "Changed limits" warning in the error log, as before.

We are also adding a new default my.cnf file and guided instructions on the key settings to adjust. More on this in a later post. We're providing a page with suggestions for settings to improve backwards compatibility. The old example files like my-huge.cnf are obsolete.

Some of the improvements are present from 5.6.6 and the rest are coming. These are ideas, and until they are in an official GA release, they are subject to change. As part of this work I reviewed every old server setting plus many hundreds of emails of feedback and testing results from inside and outside Oracle's MySQL Support team and the many excellent blog entries and comments from others over the years, including from many MySQL Gurus out there, like Baron, Sheeri, Ronald, Schlomi, Giuseppe and Mark Callaghan.

With these changes we're trying to make it easier to set up the server by adjusting only a few settings that will cause others to be set. This happens only at server startup and only applies to variables where you haven't set a value. You'll see a similar approach used for the Performance Schema. The Gurus don't need this but for many newcomers the defaults will be very useful.

Possibly the most unusual change is the way we vary the setting for innodb_buffer_pool_instances for 32-bit Windows. This is because we've found that DLLs with specified load addresses often fragment the limited four gigabyte 32-bit address space and make it impossible to allocate more than about 1300 megabytes of contiguous address space for the InnoDB buffer pool. The smaller requests for many pools are more likely to succeed.

If the value of innodb_log_file_size or innodb_log_files_in_group changes to be different from the files on disk, you will see a message like this in the error log file at the next restart, instead of the old error message:

[Warning] InnoDB: Resizing redo log from 2*64 to 5*128 pages, LSN=5735153

One of the biggest challenges for the defaults is the millions of installations on a huge range of systems, from point of sale terminals and routers though shared hosting or end user systems and on to major servers with lots of CPU cores, hundreds of gigabytes of RAM and terabytes of fast disk space. Our past defaults were for the smaller systems and these change that to larger shared hosting or shared end user systems, still with a bias towards the smaller end. There is a bias in favour of OLTP workloads, so reporting systems may need more changes. Where there is a conflict between the best settings for benchmarks and normal use, we've favoured production, not benchmarks.

We're very interested in your feedback, comments and suggestions.

2012-10-15 Correction: the initial version of this post wrongly said that innodb_autoextend_increment "Now affects *.ibd files". It still does not and I've updated the table.


I'm James Day, one of the more senior support engineers in Oracle's MySQL team. A former developer, I've had the pleasure of being featured at a MySQL User Conference twice to receive awards, once in 2005 on behalf of Wikipedia as Application of the Year and in 2012 as one of the Community Contributors of the Year for my comments on the blog entries of other people.


Top Tags
« April 2014