As a junior once I asked a seasoned MySQL DBA (Abuelo) “How do you stay so calm in critical situations?”Abuelo DBA then uttered golden words: “Son, I keep my dirty…

The post InnoDB Flushing is simple – explained first appeared on Change Is Inevitable.

In the previous article we researched MySQL Client / Server Protocol using WireShark. Now lets start to write our codes in python to simulate MySQL native client. Ready codes are here: Github repo

First of all we have to create MYSQL_PACKAGE class. MYSQL_PACKAGE class is the parent of all other package classes (HANDSHAKE_PACKAGE, LOGIN_PACKAGE, OK_PACKAGE and etc.)

class MYSQL_PACKAGE: “””Data between client and server is exchanged in packages of max 16MByte size.”””    def __init__(self, resp= b”):       self.resp = resp       self.start =0       …

MySQL Client / Server protocol is used in many areas. For example:

• MySQL Connectors like ConnectorC, ConnectorJ and etc.
• MySQL proxy
• Between master and slave

What is MySQL Client / Server protocol?

MySQL Client / Server protocol is accepted conventions (rules). Through these rules client and server “talks” and understand each other. Client connects to server through TCP connection with special socket, sends to server special packets and accepts them from server. There are two phases of this connection:

• Connection phase
• Command phase

Next illustration describes phases:

STRUCTURE OF PACKETS

Each packet consists of valuable data types. Maximum length of each packet can be 16MB. If the length of packet is more than 16MB, then it is separated into several chunks (16MB). First of all let’s see the protocol data types. …

Some days ago we found very interesting bug report with my friend: http://bugs.mysql.com/bug.php?id=71879

Problem is that, if a client moves binlog files to the other folder, stops MySQL server, update in the config file bin_log and bin_log_index values for a new path and starts server again, then the result of SHOW BINARY LOG command will be wrong:

mysql> show binary logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| cluster-repo-bin.000001 |       120 |
| cluster-repo-bin.000002 |         0 |
| cluster-repo-bin.000003 |         0 |
| cluster-repo-bin.000004 |         0 |
| cluster-repo-bin.000005 |         0 |
| cluster-repo-bin.000006 |       120 |
+-------------------------+-----------+

As you see, the size of some binlog files are zero. So why?

If you …

According to this bug report http://bugs.mysql.com/bug.php?id=79151

when client tries to create a new table with not existing (or write permission denied) datadir, then he gets an error message like this: ERROR 1030 (HY000): Got error -1 from storage engine

As you see it is not informative error message, we don’t know what’s wrong at the backend. But in the version 5.7 it seems this problem was resolved already.

I discovered that at the low level of innodb storage engine, program tries to create subfolders, when occurs a problem it checks system error partly in the file storage/innobase/fil/fil0fil.cc:

success = os_file_create_subdirs_if_needed(path);

os_file_create_subdirs_if_needed function in the storage/innobase/os/os0file.cc file checks system errors but returns only boolean value depending on the …

My first bug fix was about a year ago. You can see bug report here: http://bugs.mysql.com/bug.php?id=72259

If to shortly explain, so problem was that when you make file system read only, and try to start the server, it fails. And there is no exact error message in the log files which explains the problem correctly.

I added to the sql/mysqld.cc file a simple function whitch checks the given path’s permission and returns the state code:

// checks if file system is read-only

int is_filesystem_read_only(char const* name) {

    if (access(name, W_OK) == -1) {

        if (access(name, R_OK) == 0) {

            return R_OK; // read only

        } else if (access(name, F_OK) == 0) {

      …

The more I go through others SQL, there are some common mistakes that I see developers making over and over again, so I thought why not start a series of tips that can help developers optimize their queries and avoid common pitfalls. So this post is a part of that series of tips, and this is the first tip "Avoid using a wild card character at the start of a LIKE pattern".

We’ve been getting lots of questions about how MySQL 5.1 optimises queries on partitioned tables. Recently, we added some new information about this to our Internals Manual provided by one of our developers, Sergey Petrunia. (I hope that my edits and formatting haven’t messed up anything — if there are any problems with what’s been published in the Internals Manual, then the fault’s probably mine and not his.)

The new section can be accessed here, and includes coverage of the following topics:

• An overview of how MySQL performs partition pruning
• What partitioning and subpartitioning intervals are, and why they’re important
• Converting WHERE and ON clauses into intervals
• Partition selection
• Where to find partition pruning and selection code in …