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 GROUP_CONCAT more and more, recently, to solve otherwise difficult problems. And in most cases, 1024 was just too low, resulting in silent (Argh!) truncating of the result, thus returning incorrect results. It is interesting to learn that the maximum value for this parameter is limited by max_packet_size. I would suggest, then, that this parameter should be altogether removed, and have the max_packet_size limitation as the only limitation. Otherwise, I’d like it to have a very large default value, in the order of a few MB.
• wait_timeout: Here’s a parameter whose default value is over permissive. wait_timeout enjoys an 8 hour default. I usually go for 5-10 minutes. I don’t see a point in letting idle connections waste resources for 8 hours. Applications which hold up such connections should be aware that they’re doing something wrong, in the form of a forced disconnection. Connection pools work beautifully with low settings, and can themselves do keepalives, if they choose to.
• sql_mode: I’ve discussed this in length before. My opinion unchanged.
• open_files_limit: What with the fact connections, threads, table descriptors, table file descriptors (depending on how you use InnoDB), temporary file tables — all are files on unix-like systems, and considering this is an inexpensive payment, I think open_files_limit should default to a few thousands. Why risk the crash of “too many open files”?

No setting will ever be perfect for everyone, I know. But there are those parameters which you automatically set values for when you do a new install. These should be at focus and their defaults change.

• 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 relaxed sql_mode? Give me 255, I’ll silently drop the remaining 45. I owe you.

Warnings and errors

It would be nice to:

• Have an auto_propagate_warning_to_error server variable (global/session/both) which, well, does what it says.
• Have an i_am_really_not_a_dummy server variable which implies stricter checks for all the above and prevents you from doing with anything that may be problematic (or rolls back your transactions on your invalid actions).

Connectors may be nice enough to propagate warnings to errors – that’s good. But not enough: since data is already committed in MySQL.

If I understand correctly, and maybe it’s just a myth, it all relates to the times where MySQL had interest in a widespread adoption across the internet, in such way that it does not interfere too much with the users (hence leading to the common myth that “MySQL just works out of the box and does not require me to configure or understand anything”).

MySQL is a database system, and is now widespread, and is used by serious companies and products. It is time to stop play nice to everyone and provide with strict integrity — or, be nice to everyone, just allow me to specify what “nice” means for me.

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 ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
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 data, follows.

DROP DATABASE IF EXISTS w2;
CREATE DATABASE w2;
USE w2;

DROP TABLE IF EXISTS city;
DROP TABLE IF EXISTS country;

CREATE TABLE country (
  country_id int(11) not null auto_increment,
  name varchar(32) NOT NULL,
  PRIMARY KEY  (country_id)
)ENGINE=INNODB;

CREATE TABLE city (
  city_id int(11) NOT NULL auto_increment,
  name varchar(32) NOT NULL,
  country_id int(11) not null ,
  PRIMARY KEY  (city_id),
  INDEX country_id (country_id),
  FOREIGN KEY (country_id) REFERENCES country(country_id)
                      ON DELETE CASCADE

)ENGINE=INNODB;

INSERT INTO country (country_id, name) values (1, 'gbr');
INSERT INTO country (country_id, name) values (2, 'usa');

INSERT INTO city (name, country_id) values ('london',1);
INSERT INTO city (name, country_id) values ('liverpool',1);
INSERT INTO city (name, country_id) values ('birmingham',1);
INSERT INTO city (name, country_id) values ('ny',2);
INSERT INTO city (name, country_id) values ('boston',2);

Both tables are InnoDB, to support transactions and foreign keys.

Obviously, ‘root’ is allowed to do anything. But what harm can our unprivileged ‘w2user’ do?

FOREIGN_KEY_CHECKS

The following INSERT should fail:

INSERT INTO city (name, country_id) values ('no_city',1234567);

But look at the following:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| w2user@%       |
+----------------+
1 row in set (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO city (name, country_id) values ('no_city',1234567);
Query OK, 1 row affected (0.01 sec)

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

What was that? w2user was allowed to temporarily disable foreign key checks, insert an otherwise invalid row, then re-enable checks, and no error was thrown? Wait, did the row really get inserted?

mysql> SELECT * FROM city;
+---------+------------+------------+
| city_id | name       | country_id |
+---------+------------+------------+
|       1 | london     |          1 |
|       2 | liverpool  |          1 |
|       3 | birmingham |          1 |
|       4 | ny         |          2 |
|       5 | boston     |          2 |
|       6 | no_city    |    1234567 |
+---------+------------+------------+
6 rows in set (0.01 sec)

Yes, it did.

Disabling FK checks is handy when importing large data from dump, or from CSV, when it is known to be valid. For example, when restoring a backup created with mysqldump, FK checks can be safely disabled since dumped data must have been valid. Disabling checks helps in reducing import time.

But I don’t think normal users should be allowed to set the FOREIGN_KEY_CHECKS variable. This should be restricted to users with the SUPER privilege.

tx_isolation

When using InnoDB, we can choose one of four isolation levels:

• READ-UNCOMMITTED
• READ COMMITTED
• REPEATABLE-READ (default):
• SERIALIZABLE

In READ-UNCOMMITTED, a transaction can read other open transactions uncommitted data. It’s usually not a good idea to use this isolation level when working with transactional engines, since it undermines the very foundation of using transactions.

But MySQL, and through it, InnoDB, allow a strange thing: the transaction isolation level can be modified on the run. I consider this to be peculiar and undesired. An isolation level imposes an application logic, which should not be changed. But MySQL also allows different isolation level on a per-connection basis.

Every session can work on a different isolation level. This may be a good idea, when a session wishes to be stricter than the rest of the code, by using the SERIALIZABLE isolation, for example.

But our w2user may decide to lower her session’s isolation level below the global one. That is, MySQL may be configured to work at REPEATABLE-READ, but w2user is allowed to:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| w2user@%       |
+----------------+
1 row in set (0.00 sec)

mysql> SET tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

Our ‘root’ user does the following:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO country (name) VALUES ('nowhere');
Query OK, 1 row affected (0.00 sec)

While the transaction is still open, w2user can:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| w2user@%       |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM country;
+------------+---------+
| country_id | name    |
+------------+---------+
|          1 | gbr     |
|          2 | usa     |
|          3 | nowhere |
+------------+---------+
3 rows in set (0.00 sec)

w2user used the READ-UNCOMMITED, hence was allowed to see the (soon to be rolled back?) ‘nowhere’ country. But that country was inserted by a session using the REPEATABLE-READ level.

Each session confirms to its isolation level rules, and the complaint is not about that. The complaint is with the fact that there’s a mess in our database.

Working with the REPEATABLE-READ isolation level should guarantee me some privacy in my transaction. My transaction may choose to delete all rows from a table, only to fill them back again, and none (a small white lie here, since locking is also involved) is the wiser. The privacy notion is so inherent, that it’s shocking to learn that any other connection can knowingly choose to ignore my privacy and see any changes I make. This is why I consider this as a security breach, and not just some isolation nuance.

In my opinion, the isolation level should not be dynamic at all. It must not be changed while the database is running. Perhaps I’m missing some interesting scenario where it would be desired, but the majority of applications would not find this feature beneficial.

sql_mode

I’ve written about sql_mode before, and here’s an example for a data integrity issue caused by weak security:

In our example, sql_mode is set to ‘TRADITONAL‘, which maps to:

STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER

Let’s add a TIMESTAMP column to the country table:

ALTER TABLE country ADD COLUMN ts TIMESTAMP NOT NULL;

We now try to set a ‘0’ value for the time stamps (as user root):

mysql> UPDATE country SET ts=NOW();
Query OK, 2 rows affected (0.42 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE country SET ts=0;
ERROR 1292 (22007): Incorrect datetime value: '0' for column 'ts' at row 1

We got the error becuase of the NO_ZERO_DATE part of our sql_mode.

But, again, look at what w2user can do:

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE country SET ts=0;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM country;
+------------+------+---------------------+
| country_id | name | ts                  |
+------------+------+---------------------+
|          1 | gbr  | 0000-00-00 00:00:00 |
|          2 | usa  | 0000-00-00 00:00:00 |
+------------+------+---------------------+
2 rows in set (0.00 sec)

So, are ‘0’ values allowed for timestamps in our database or not? Turns out any simple user may decide differently.

See my earlier posts here and here. Roland Bouman also offers suggestions for fixing this issue.

Conclusion

The above three examples show how simple users can break data integrity due to very permissive MySQL logic. Even when the database is carfully tuned and secured, there’s no way to prevent non privileged users from damaging its integrity.

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

Suggestion

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 configuration.
• Stricter is better, if you code your application this way right from the start (naturally, trying to make changes after 3 years of development invites trouble).

The fact that sql_mode is empty by default, and has been like that for the past years, means that many applications have been built which are not strict. The developers and DBAs never changed the sql_mode (remember they didn’t know about sql_mode). Many of the applications would not work under stricter sql_mode.

Please imagine the following: assume we keep the sql_mode feature as it is, but make it ‘TRADITIONAL’ by default. Now imagine we did that 5 years ago.

Most, if not all of today’s applications were strict. Those who would disable STRICT_TRANS_TABLES or ERROR_FOR_DIVISION_BY_ZERO would be the exception, and they would have to do it knowingly, understanding the impact of their actions.

May I offer my suggestion, then, that future MySQL installations come with a strict sql_mode. At the very least allow the user (in the Windows installer, for example, or via ncurses, dpkg-reconfigure, other) to choose her level of strictess. The windows installer already asks about encodings and engines.

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 have changed!
• Did we set STRICT_ALL_TABLES? How do we know if the 255 value in our TINYINT really stands for 255 or if it was a truncated 299?
• Do we allow selecting non aggregated columns in GROUP BY? Did we set ONLY_FULL_GROUP_BY? Will our application crash now?
• Our old database has zero for empty date values (columns are NOT NULL). But what settings do we have for NO_ZERO_IN_DATE on our new installation? Will import fail?
• And how did the NULL get in? Was it because we divided by zero, and forgot to set ERROR_FOR_DIVISION_BY_ZERO? How can we tell?

The fact is: two mysql instances, same version, same OS and architecture, with different sql modes – can be incompatible!

As said, the sql_mode is empty by default. This is very non-strict. But more than that: it can be changed even while the database is running; even on a per connection basis.

Setting sql_mode should be one of the first things to do after installation. The usual manuals talk about setting the innodb_buffer_pool_size and the query_cache_size, when sql_mode will dictate the nature of your database and application on an entirely grander scale.

I think it would be best if MySQL chooses a desired set of sql modes (like ‘TRADITIONAL’), then make it the default. I further believe it would be best if MySQL would not allow changes to sql_mode. Not globally and certainly not per session. Choosing the stricter mode is better, I believe: errors such as overflow values should be reported to the application, not just swiped under the carpet.

Backward compatibility? Indeed a problem (inherent to the very existence of sql_mode). Perhaps allow one setting per installation. Perhaps just go for it.