Some bugs can have a high impact if it causes data corruption, security issues or simply causes MySQL to crash. But what about small bugs and small usability issues?

This entry from the MySQL 5.6.8 release notes is an interesting one:
InnoDB: On startup, MySQL would not start if there was a mismatch between the value of the innodb_log_file_size configuration option and the actual size of the ib_logfile* files that make up the redo log. This behavior required manually removing the redo log files after changing the value of innodb_log_file_size. The fix causes MySQL to write all …

MySQL lets database administrators define access rights on many levels – from the ability to run global commands down to access to individual columns. Some rights can be applied to many different objects, such as for example SELECT or UPDATE, which can be granted globally or restricted only to certain databases or tables, while others are only meant for one specific purpose. An example of the latter could be FILE privilege, which permits user to interact with the file system from inside a database instance. It only makes sense as the global right and not anywhere else.

As any other activity that produces changes, GRANT statements are replicated to MySQL slaves. Regardless of the binary log format setting, such events are always logged in STATEMENT format. It is likely because the command needs to handle more than just updating the contents of a few system tables, so such design allows each slave to fully execute the changes within their …

I discovered a feature (or bug) of MySQL timestamp fields. Maybe is documented in some place what I not read yet:

When I add a new timestamp field to a table, MySQL magically adds some features to new timestamp field like a “trigger” and a default value to CURRENT_TIMESTAMP.

There is test-case script:

-- CREATING TABLE AND INSERT SOME DUMMY DATA
mysql> CREATE TABLE t(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> val VARCHAR(50)
    -> );
Query OK, 0 ROWS affected (0.15 sec)
 
mysql> INSERT INTO t (val) VALUES ("foo") ,("var");
Query OK, 2 ROWS affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM t;
+----+------+
| id | val  |
+----+------+
|  1 | foo  |
|  2 | var  |
+----+------+
2 ROWS IN SET (0.00 sec)
 
 
-- ADDING NEW TIMESTAMP FIELD AND ADD MORE DUMMY DATA
mysql> ALTER TABLE t ADD ts_field TIMESTAMP;
Query OK, 2 ROWS affected (0.35 sec)
Records: 2  Duplicates: 0 …

I pledged, in my first post last month, that SkySQL will do its part to promote the best of MySQL and its community. Given the recent discovery that Oracle is no longer publishing test cases for bug fixes, and the dialogue surrounding it, it feels like the right time to share my thoughts on what this means to the open source collective, and what we can do – and are doing – about it.

read more

There has been a lot of talk about MySQL 5.6 replication improvements. With few exceptions, what I have seen was either marketing messages or hearsay. This means that few people have really tried out the new features to see whether they meet the users needs.

As usual, I did try the new version in my environment. I like to form my own opinion based on experiments, and so I have been trying out these features since they have appeared in early milestones.

What follows is a list of (potentially) surprising results that you may get when using MySQL 5.6.
All the examples are made using MySQL 5.6.6.

Gotcha #1 : too much noise

I have already mentioned that MySQL 5.6 is too verbose when creating data directory. This also means that your error log may have way more information than you'd like to get. …

This is the third time MySQL has made an entry into the Oracle Critical Patch Update Advisory service. The first time, we at Team MariaDB came up with an analysis: Oracle’s 27 MySQL security fixes and MariaDB.

Security is important to a DBA. Having vague explanations does no one any good. Even Oracle ACE Director Ronald Bradford chooses to ask some tough questions on this issue. Recently we found a bug in MySQL & MariaDB and did some …

There are mistakes that drive you crazy when you try to understand what went wrong.

One of the most annoying and hard to catch was this, apparently harmless line:

tungsten-sandbox -m 5.5.24 --topology all-masters -n 2 -p 7300 -l 12300 -r 10300 –t $HOME/mm -d tsb-mm

The person reporting the error told me that the installation directory (indicated by "-t") was not taken into account.

I usually debug by examples, so I copied the line, and pasted it into one of my servers. Sure enough, the application did not take trat option into account. The installation kept happening in the default directory.

I knew that I had done a good job at making the application configurable, but I checked the code nonetheless. The only place where the default directory is mentioned is when the related variable is initialized. Throughout the code, there are no literal values used for this purpose. And yet, the …

Do you rely on pt-diskstats from Percona Toolkit instead of the standard iostat a lot? There appears to be a nasty bug in pt-diskstats 2.1, which makes it produce bad results.

I noticed some of the numbers I was getting weren’t right, so I tried running iostat and two different releases of pt-diskstat side by side. Here’s what I got:

I can understand the slight differences between the lines in iostat and pt-diskstats 2.0 as they probably weren’t reading /proc/diskstats contents in the same moments, so the values they were seeing could be a bit different. However both lines practically show the same thing.

On the other hand, the line based on the pt-diskstats 2.1 output looks completely different. It seems like maybe the script returns some kind of averages rather than per second deltas. I filed a …

I just finished a database modification, a new foreign key creation  shouldn’t be take more than 5 mins, but I spent 2 hours because MySQL still have some useless error messages.

There is a way to create a new foreign key:

-- Create two tables foo and bar
CREATE TABLE foo (
        id INTEGER NOT NULL PRIMARY KEY,
        bar_id INT NOT NULL    -- foreign key
);
CREATE TABLE bar (
        id INTEGER NOT NULL PRIMARY KEY
);
-- Try to create a foreign key on `foo`
ALTER TABLE foo
        ADD FOREIGN KEY(bar_id) REFERENCES bar(SOME_FIELD) ;

The last sentence returns a generic error message:

Error Code: 1005. Can't create table 'temp.#sql-4bd7_11' (errno: 150)

Everything would have been easier if I had noticed that wrong field name bar(SOME_FIELD), sometimes happens,  but if MySQL would have shown a different message like "field bar.SOME_FIELD don't exists" I would …

When an open source project becomes popular, bug reports start flocking in. This is both good and bad news for the project developers. The good news is that someone is using the product, and they are finding ways of breaking it that we didn't think of. The bad news is that most of the times the reporters assume that the developers have super human powers, and that they will find what's wrong by the simple mentioning that a given feature is not working as expected. Unfortunately, it doesn't work that way. An effective bug report should have enough information that the ones in charge will be able to reproduce it and examine in lab conditions to find the problem. When dealing with databases and database tools, there are several cases, from simple to complex. Let's cover them in order. Installation issuesThis is often a straightforward case of lack of functionality. When a tool does not install what it is supposed to, it is a show stopper, and a solution …