UNDERSTANDING MySQL CLIENT / SERVER PROTOCOL USING PYTHON AND WIRESHARK – PART 2

June 25, 2018June 25, 2018 elshadaghazade blog, MySQL, MySQL Internals

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

self.end =0

It accepts resp parameter on initialization. Resp is the binary response received from the server in bytesarray type. One of the important and interesting method of this class is next method.

def next(self, length = None, type=int, byteorder=’little’, signed=False, freeze=False):

if not freeze:

if length:

self.end += length

portion = self.resp[self.start:self.end]

self.start =self.end

else:

portion = self.resp[self.start:]

self.start =self.end =0

else:

if length:

portion = self.resp[self.start:self.start + length]

else:

portion = self.resp[self.start:]

if type is int:

return int.from_bytes(portion, byteorder=byteorder, signed=signed)

elif type is str:

return portion.decode(‘utf-8’)

elif type is hex:

return portion.hex()

else:

return portion

Method next reads portion of bytes from the binary response. When we call this method it reads some portion of bytes and put pointer to the last position where reading was ended (changes a value of self.start and self.end properties). When we call this method again, it starts to read bytes where stayed last time.

Method next accepts five parameters: length, type, byteorder, signed and freeze. If freeze is True it reads some portion of bytes from binary response but does not change pointer position. Otherwise it reads portion of bytes with given length and change the position of pointer. If length is None then method reads bytes until the end of response bytesarray. Parameter type can be int, str and hex datatypes. Method next converts portion of bytes to appropriate datatype according to the value of type parameter.

Parameter byteorder determines conversion of bytes to integer type. It ups to your architecture of computer. If your machine is big-endian then it stores bytes in memory from big address to the little. Or if your machine is little-endian then it stores bytes in memory from little address to the big. Thats why we have to know exact type of our architecture to be able to convert bytes to integer correctly. In my case it is little-endian, thats why i’ve set the default value of byteorder parameter to “little”.

Parameter signed is also used in conversion of bytes to integer. We tell the function to consider each intereg as unsigned or signed.

Second interesting method of this class is encrypt_password. This method encrypts password with given algorithm.

from hashlib import sha1

def encrypt_password(self, salt, password):

bytes1 = sha1(password.encode(“utf-8”)).digest()

concat1 = salt.encode(‘utf-8’)

concat2 = sha1(sha1(password.encode(“utf-8”)).digest()).digest()

bytes2 = bytearray()

bytes2.extend(concat1)

bytes2.extend(concat2)

bytes2 = sha1(bytes2).digest()

hash=bytearray(x ^ y for x, y in zip(bytes1, bytes2))

return hash

This method accepts two parameters: salt and password. Parameter salt is the concatenation of two salt1 and salt2 strings from Greeting Packet received from server. And parameter password is the password string of mysql user.

In the official documentation password encryption algorithm is:

Here “20-bytes random data from server” is concatenation of salt1 and salt2 from Greeting Packet received from server. To remember what is greeting packet look at the previous article

Now I want to explain encrypt_password method line by line.

bytes1 = sha1(password.encode(“utf-8”)).digest()

We are converting password string to bytes, then encrypting it with sha1 function and assigning to bytes1 variable. It is equal to this part of algorithm:

Then we are converting salt string into bytes and assigning to the concat1 variable.

concat1 = salt.encode(‘utf-8’)

Third line of the method is:

concat2 = sha1(sha1(password.encode(“utf-8”)).digest()).digest()

Here we are double-encrypting password string with sha1 function and assign it to the concat2 string.

Now we have two concat1 and concat2 variables. We have to concatenate them into one byte array:

bytes2 = bytearray()

bytes2.extend(concat1)

bytes2.extend(concat2)

Then we have to encrypt concatenated bytes with sha1 function and assign to the bytes2 variable.

bytes2 = sha1(bytes2).digest()

So we have two variables with encrypted bytes: bytes1 and bytes2. Now we have to do bitwise XOR operation between this variables and return the obtained hash.

hash=bytearray(x ^ y for x, y in zip(bytes1, bytes2))

return hash

CLASSES FOR DATATYPES

In the previous article we learned about Int and String data types of MySQL Client / Server protocol. Now we need some classes to be able to read fields from received packets.

INT CLASS

class Int:

“””see documentation: https://dev.mysql.com/doc/internals/en/integer.html”””

def __init__(self, package, length=-1, type=’fix’):

self.package = package

self.length = length

self.type =type

def next(self):

# int<n>

if self.type == ‘fix’ and self.length > 0:

return self.package.next(self.length)

# int<lenenc>

if self.type == ‘lenenc’:

byte = self.package.next(1)

if byte < 0xfb:

return self.package.next(1)

elif byte == 0xfc:

return self.package.next(2)

elif byte == 0xfd:

return self.package.next(3)

elif byte == 0xfe:

return self.package.next(8)

Int class implements INT data type of MySQL Client / Server protocol. It accepts package parameter on initialization. Parameter package should be the instance of any package class inherited from MYSQL_PACKAGE class. Method next detects the type of integer (int<fix> or int<lenenc>. See previous article) and calls the next method of package object to read the byte portion of received response.

STR CLASS

class Str:

“””see documentation: https://dev.mysql.com/doc/internals/en/string.html”””

def __init__(self, package, length=-1, type=”fix”):

self.package = package

self.length = length

self.type =type

def next(self):

# string<fix>

if self.type == ‘fix’ and self.length > 0:

return self.package.next(self.length, str)

# string<lenenc>

elif self.type == ‘lenenc’:

length = self.package.next(1)

if length == 0x00:

return “”

elif length == 0xfb:

return “NULL”

elif length == 0xff:

return “undefined”

return self.package.next(length, str)

# string<var>

elif self.type == ‘var’:

length = Int(self.package, type=’lenenc’).next()

return self.package.next(length, str)

# string<eof>

elif self.type == ‘eof’:

return self.package.next(type=str)

# string<null> – null terminated strings

elif self.type == ‘null’:

strbytes = bytearray()

byte = self.package.next(1)

while True:

if byte == 0x00:

break

else:

strbytes.append(byte)

byte = self.package.next(1)

return strbytes.decode(‘utf-8’)

Str class implements STRING data type of MySQL Client / Server protocol. It accepts package parameter on initialization. Parameter package should be the instance of any package class inherited from MYSQL_PACKAGE class. Method next detects the type of String (String<fix>, String<Var>, String<NULL>, String<EOF> or String<lenenc>. See previous article) and calls the next method of package object to read the byte portion of received response.

HANDSHAKE_PACKAGE CLASS

HANDSHAKE_PACKAGE class is used for parse Greeting Packet received from server. It is inherited from MYSQL_PACKAGE class and accepts resp parameter on initialization. Parameter resp is the Greeting Packet response in bytes type recieved from the server.

class HANDSHAKE_PACKAGE(MYSQL_PACKAGE):

def __init__(self, resp):

super().__init__(resp)

def parse(self):

return {

“package_name”: “HANDSHAKE_PACKAGE”,

“package_length”: Int(self, 3).next(), #self.next(3),

“package_number”: Int(self, 1).next(), #self.next(1),

“protocol”: Int(self, 1).next(), #self.next(1),

“server_version”: Str(self, type=’null’).next(),

“connection_id”: Int(self, 4).next(), #self.next(4),

“salt1”: Str(self, type=’null’).next(),

“server_capabilities”: self.get_server_capabilities(Int(self, 2).next()),

“server_language”: self.get_character_set(Int(self, 1).next()),

“server_status”: self.get_server_status(Int(self, 2).next()),

“server_extended_capabilities”: self.get_server_extended_capabilities(Int(self, 2).next()),

“authentication_plugin_length”: Int(self, 1).next(),

“unused”: Int(self, 10).next(), #self.next(10, hex),

“salt2”: Str(self, type=’null’).next(),

“authentication_plugin”: Str(self, type=’eof’).next()

}

Method parse reading fields from the response using Int and Str classes and puts them into a dictionary and returns.

LOGIN_PACKAGE CLASS

This class is used for create Login Request packet.

class LOGIN_PACKAGE(MYSQL_PACKAGE):

def __init__(self, handshake):

super().__init__()

self.handshake_info = handshake.parse()

def create_package(self, user, password, package_number):

package = bytearray()

# client capabilities

package.extend(self.capabilities_2_bytes(self.client_capabilities))

# extended client capabilities

package.extend(self.capabilities_2_bytes(self.extended_client_capabilities))

# max package -> 16777216

max_package = (16777216).to_bytes(4, byteorder=’little’)

package.extend(max_package)

# charset -> 33 (utf8_general_ci)

package.append(33)

# 23 bytes are reserved

reserved = (0).to_bytes(23, byteorder=’little’)

package.extend(reserved)

# username (null byte end)

package.extend(user.encode(‘utf-8’))

package.append(0)

# password

salt = self.handshake_info[‘salt1’] + self.handshake_info[‘salt2’]

encrypted_password = self.encrypt_password(salt.strip(), password)

length = len(encrypted_password)

package.append(length)

package.extend(encrypted_password)

# authentication plugin

plugin = self.handshake_info[‘authentication_plugin’].encode(‘utf-8’)

package.extend(plugin)

finpack = bytearray()

package_length = len(package)

finpack.append(package_length)

finpack.extend((0).to_bytes(2, byteorder=’little’))

finpack.append(package_number)

finpack.extend(package)

return finpack

This class accepts handshake parameter on initialization. Parameter handshake should be the instance of HANDSHAKE_PACKAGE class. In the __init__ method we call the parse method of handshake object and get all fields of Greeting Packet received from the server.

Method create_package prepares the login request package to be able to send to the server for authentication. Accepts user, password and packet_number parameters.

OK_PACKAGE & ERR_PACKAGE CLASSES

OK package and ERR package are the response package of server after authentication or after sending query to server on command phase.

class OK_PACKAGE(MYSQL_PACKAGE):

def __init__(self, resp):

super().__init__(resp)

def parse(self):

return {

“package_name”: “OK_PACKAGE”,

“package_length”: Int(self, 3).next(), #self.next(3),

“package_number”: Int(self, 1).next(), #self.next(1),

“header”: hex(Int(self, 1).next()),

“affected_rows”: Int(self, 1).next(), #self.next(1),

“last_insert_id”: Int(self, 1).next(), #self.next(1),

“server_status”: self.get_server_status(Int(self, 2).next()),

“warnings”: Int(self, 2).next()

}

class ERR_PACKAGE(MYSQL_PACKAGE):

def__init__(self, resp):

super().__init__(resp)

def parse(self):

return {

“package_name”: “ERR_PACKAGE”,

“package_length”: Int(self, 3).next(), #self.next(3),

“package_number”: Int(self, 1).next(), #self.next(1),

“header”: hex(Int(self, 1).next()), #self.next(1, hex),

“error_code”: Int(self, 2).next(), #self.next(2),

“sql_state”: Str(self, 6).next(),

“error_message”: Str(self, type=’eof’).next()

}

MYSQL CLASS

MYSQL class is the wrapper class which creates TCP connection with server, sends and receives packages from server using above classes.

from socket import AF_INET, SOCK_STREAM, socket, gethostbyname

from modules.packages import *

class MySQL:

def __init__(self, host=””, port=””, user=””, password=””):

self.host = host

self.port = port

self.user = user

self.password = password

def connect(self):

resp = self.client.recv(65536)

return HANDSHAKE_PACKAGE(resp)

def login(self, handshake_package, package_number):

“””Sending Authentication package”””

login_package = LOGIN_PACKAGE(handshake_package)

package = login_package.create_package(user=self.user, password=self.password, package_number=package_number)

self.client.sendall(package)

resp = self.client.recv(65536)

package = self.detect_package(resp)

return package.parse()[‘package_number’]

def __enter__(self):

self.client = socket(AF_INET, SOCK_STREAM)

ip = gethostbyname(self.host)

address=(ip,int(self.port))

self.client.connect(address)

return self

def __exit__(self, exc_type, exc_value, traceback):

print(“Good Bye!”)

self.close()

def close(self):

self.client.close()

I think nothing is not unclear in this class. I’ve defined __enter__ and __exit__ to be able to use this class with “with” statement to automatically close TCP connection with server. In __enter__ method i’m creating TCP connection over socket. And in __exit__ method i’m closing created connection. This class accepts host, port, user and password parameters on initialization.

In the connect method we receive greeting packet from server:

resp = self.client.recv(65536)

return HANDSHAKE_PACKAGE(resp)

In the login method we create Login request package using LOGIN_PACKAGE and HANDSHAKE_PACKAGE classes and sends to the server and gets OK or ERR packages.

That’s all. We’ve implemented connection phase. To avoid be this article too long I will not explain command phase. Because command phase is more easer that connection phase and you can research it yourself with accumulated knowledge from this and previous articles.

Also you can get already implemented command phase codes from this Github repo and research: https://github.com/elshadaghazade/techacademy_mysql_native_client_in_python

Demo Video:

Thank you!

UNDERSTANDING MySQL CLIENT / SERVER PROTOCOL USING PYTHON AND WIRESHARK – PART 1

June 25, 2018July 8, 2018 elshadaghazade blog, MySQL, MySQL Internals, Uncategorized

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. MySQL Client / Server protocol has two data types:

Integer types
String types

(See the official documentation: https://dev.mysql.com/doc/internals/en/basic-types.html)

INTEGER TYPES

Integer types also separates into two section:

Fixed length integer types
Length-encoded integer types

Fixed length integer type consumes 1, 2, 3, 4, 6 or 8 bytes. For example if we want to describe number 2 in int<3> data type then we can write it like this in hex format: 02 00 00. Or if we want to describe number 2 in int<2> then we can write it like this in hex format: 02 00

Length-encoded integer types consumes 1, 3, 4 or 9 bytes. Before length-encoded integer types comes 1 byte. To detect the length of integer we have to check that first byte.

If the first byte is less than 0xfb ( < 251 ) then next one byte is valuable (it is stored as a 1-byte integer)
If the first byte is equal to 0xfc ( == 252 ) then it is stored as a 2-byte integer
If the first byte is equal to 0xfd ( == 253 ) then it is stored as a 3-byte integer
If the first byte is equal to 0xfe ( == 254 ) then it is stored as a 8-byte integer

But if the first byte is equal to 0xfb there is no need to read next bytes, it is equal to the NULL value of MySQL, and if equal to 0xff it means that it is undefined.

For example to convert fd 03 00 00 … into normal integer we have to read first byte and it is 0xfd. According to the above rules we have to read next 3 bytes and convert it into normal integer, and its value is 2 in decimal number system. So value of length-encoded integer data type is 2.

STRING TYPES

String types also separates into several sections.

String – Fixed-length string types. They have a known, hardcoded length
String – Null terminated string types. These strings end with 0x00 byte
String – Variable length string types. Before such strings comes fixed-length integer type. According to that integer we can calculate actual length of string
String – Length-encoded string types. Before such strings comes length-encoded integer type. According to that integer we can calculate actual length of string
String – If a string is the last component of a packet, its length can be calculated from the overall packet length minus the current position

SNIFF WITH WIRESHARK

Let’s start wireshark to sniff the network, filter MySQL packets by ip (in my case server ip is 54.235.111.67). Then let’s try to connect to MySQL server by MySQL native client on our local machine.

>> mysql -u[username] -p[password] -h[host ip] -P3306

As you can see after TCP connection to the server we several MySQL packets from the server. First of them is greeting packet.

Let’s dig into this packet and describe each field.

First 3 bytes are packet length:

Next 1 byte is packet number:

Rest of bytes are payload of Greeting packet of MySQL Client / Server protocol

Let’s describe each field of greeting packet.

Protocol number – Int<1>
Server version – String
Thread id – Int<4>
Salt1 – String
Server capabilities – Int<2>
Server language – Int<1>
Server Status – Int<2>
Extended Server Capabilities – Int<2>
Authentication plugin length – Int<1>
Reserved bytes – 10 bytes
Salt2 – String
Authentication plugin string – String

Server language is integer, next table will help us to pick appropriate language by integer value:

In my case server language is 0x08 (in decimal number system it is 8 also). From above table we can see that equivalent of 8 is latin1_swedish_ci. Now we know that default language of server is latin1_swedish_ci.

Server capabilities and server status are also integers. But reading each BIT of these integers we can know about server capabilities and status. Next illustration describes server capability and status bits:

Using greeting packet client prepares Login Request Packet to send to the server for authentication. Now let’s research login request packet.

First 3 bytes describes payload length
Next 1 byte is packet number
Client capabilities – Int<2> / Same as Server capabilities
Extended client capabilities – Int<2> / Same as Server extended capabilities
Max packet – Int<4> / describes the maximum length of packet
Charset – Int<1> / in my case it is 0x21 (in decimal number system is 33), from the table we can see that it is utf8_general_ci. We set server’s default charset from latin1_swedish_ci to utf8_general_ci
Username – String
Password – String
Client Auth Plugin string – String

As you can see password is encrypted. To encrypt a password we will use sha1, md5 algorithms, also salt1 and salt2 strings from previous Greeting Packet sent from server.

Then we get OK packet from the server if we are authenticated successfully. Otherwise we would get ERR packet.

3 bytes are packet length
1 byte is packet number
Affected rows – Int<1>
Server status – Int<2>
Warnings – Int<2>

That’s all. We have finished researching Connection Phase. Now let’s start to write our python codes.

Second part of this article is here: UNDERSTANDING MySQL CLIENT / SERVER PROTOCOL USING PYTHON AND WIRESHARK – PART 2

Posting to Telegram channel by bot using mysql, python

December 9, 2017June 25, 2018 elshadaghazade blog, MySQL

Idea to create channel in the telegram came me from my friend. I have a lot of useful electronic materials about programming, and he offered me to share them in telegram channel. To create channel is very easy. But share materials manually is very boring and wasting time. That’s why I’ve decided to automate it using mysql and python. First of all I’ve created table in the mysql to store my books there. And then i’ve created my first bot in telegram. Documentation of telegram bots: https://core.telegram.org/bots

Also you can find API documentation of telegram bots here: https://core.telegram.org/bots/api

And than was created channel. You can learn how to create channel from here: https://telegram.wiki/generalfaq/channels

So interesting part is a python code to work with telegram api. You can find many written libraries in internet written in python to work with telegram API. But I wanna show how to write simple script from zero. Table structure:

CREATE TABLE `books` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
`content` longblob,
`author` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`description` text COLLATE utf8mb4_unicode_ci,
`image` blob,
`size` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`year` int(11) unsigned DEFAULT NULL,
`extension` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`content_type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`content_length` int(20) unsigned DEFAULT ‘0’,
`last_modified` int(11) unsigned DEFAULT ‘0’,
`is_shared` tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`id`),
UNIQUE KEY `book_download` (`book_download`),
KEY `year` (`year`),
KEY `is_shared` (`is_shared`),
KEY `last_modified` (`last_modified`),
KEY `content_length` (`content_length`),
KEY `content_type` (`content_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

So let’s assume that our books are stored in the table already and pending to be shared.

So let’s write our poster class step by step. Required python libraries are requests, pymysql and re. We will use python3. Let’s create project folder and then virtualenv and install required libraries

mkdir telegram_bot
> cd telegram_bot
> virtualenv .py3
> source .py3/bin/activate
> pip install requests pymysql
> touch telegram_bot.py

Now, let’s create our first class

import requests as rq
import pymysql as mysql
import re

class poster:
   _token = "****"
   _url = "https://api.telegram.org/bot"
   _channel_id = "@mychannel"
   _conn = None
   def __init__(self):
      self._url += self._token
      self._conn = mysql.connect(host="localhost", user="user", password="password", db="books", charset="utf8")

   def __exit__(self, exc_type, exc_val, exc_tb):
      self._conn.close()

As you see in example we connect to mysql db on initialization, and close on destroying o object. Now let’s add send_book method:

def send_book(self):
   strsql = """select id, 
                      title, 
                      author, 
                      description, 
                      image, 
                      year, 
                      content,
                      extension
                 from books 
                      limit 2
                order by year desc"""

   cur = self._conn.cursor()
   if cur.execute(strsql):
      for id, title, author, description, image, year, content, extension, in cur:
         self.post_text(description)
         self.post_document(title, extension, author, year, content)
         self.post_image(image, title)

In this method we choose 2 books from database, and then call three other methods in the loop: post_text, post_document, post_image

These methods uses there types of bot API of telegram: sendMessage, sendDocument and sendPhoto

Let’s define next methods:

def post_text(self, description):
   method = self._url + "/sendMessage"
   r = rq.post(method, data={
          "chat_id": channel_id,
          "text": description
       })
   
   if r.status_code != 200:
      raise Exception("post_text error")

def post_photo(self, image, title):
   method = self._url + "/sendPhoto"
   r = rq.post(method, data={
          "chat_id": channel_id,
          "photo": image,
          "caption": title
       })

    if r.status_code != 200:
       raise Exception("post_photo error")

def post_document(self, title, extension, author, year, content):
   method = self._url + "/sendDocument"
   filename = title + "-" + author + "-" + year
   filename = re.sub(r'[^a-zA-Z0-9]+', '', filename)
   filename += "." + extension
   r = rq.post(method, data={
          "chat_id": channel_id,
          "caption": title + ", " + author + ", " + year,
       },
       files={
          "document":(filename, content)
       })
   if r.status_code != 200:
      raise Exception("post_document error")

And last step is to create single script file, import our module and connect this script to cron to share books periodically.

cron.py
-----------------
from telegram_bot import poster

p = poster()
p.send_book()
cron.sh
------------------
#!/bin/bash

cd ~/telegram_bot
source .py3/bin/activate
python cron.py

That’s all 🙂

Wrong binlog size reported after moving binlogs on MySQL version 5.6

November 14, 2016November 15, 2016 elshadaghazade MySQL Internals Bug fixes, MySQL, MySQL Internals

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 dive into the mysql internals, you can see these lines of codes in the sql/rpl_master.cc:

/* The file ends with EOF or empty line */

while ((length=my_b_gets(index_file, fname, sizeof(fname))) > 1)

{

int dir_len;

ulonglong file_length= 0; // Length if open fails

fname[–length] = ‘\0’; // remove the newline

….

As you see, my_b_gets function reads index file of binlogs and assigns a real path of binlogs to the fname char array. When we debugged fname we detected that fname contains unreal path to the binlog files (for ex: ./mysql-bin.000001). For this reason the size calculator can’t give us real size of binlog files. To resolve this problem we added a few lines of code to correct the real path of files:

// bug fix for http://bugs.mysql.com/bug.php?id=71879

// if bin file path starts with ./ then we will prepend to it full path of binlog directory

if(fname[0] == ‘.’ && fname[1] == ‘/’)

{

std::string ofname(fname);

// get the new folder path of binlog files and assign to the bin_log_value variable.

std::string bin_log_value(mysql_bin_log.get_name());

// corrects the path of binlog file

std::size_t lpos = bin_log_value.find_last_of(“/”);

std::string dir_path = bin_log_value.substr(0, lpos);

std::string full_path = dir_path + ofname.substr(1, ofname.length());

strcpy(fname, full_path.c_str());

}

Bug fix – Error -1 from storage engine while creating table with non-existing datadir on MySQL version 5.6

November 14, 2016November 15, 2016 elshadaghazade MySQL Internals Bug fixes, MySQL, MySQL Internals

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 result:

/*Creates all missing subdirectories along the given path.

@returnTRUE if call succeeded FALSE otherwise */

UNIV_INTERN

ibool

os_file_create_subdirs_if_needed(

/*=============================*/

const char*path)/*!< in: path name */

{

if (srv_read_only_mode) {

ib_logf(IB_LOG_LEVEL_ERROR,

“read only mode set. Can’t create subdirectories ‘%s'”,

path);

return(FALSE);

}

char*subdir = os_file_dirname(path);

if (strlen(subdir) == 1

&& (*subdir == OS_FILE_PATH_SEPARATOR || *subdir == ‘.’)) {

/* subdir is root or cwd, nothing to do */

mem_free(subdir);

return(TRUE);

}

/* Test if subdir exists */

os_file_type_ttype;

iboolsubdir_exists;

iboolsuccess = os_file_status(subdir, &subdir_exists, &type);

if (success && !subdir_exists) {

/* subdir does not exist, create it */

success = os_file_create_subdirs_if_needed(subdir);

if (!success) {

mem_free(subdir);

return(FALSE);

}

success = os_file_create_directory(subdir, FALSE);

}

mem_free(subdir);

return(success);

}

Depends on the value of boolean variable success in the fil0fil.cc file, program sends to the user unclear message. For this reason I added to the fil0fil.cc file a few lines of codes to resolve this problem:

// checking success variable value if false and global errno variable has a value

if(!success && errno) {

// sends to the client custom message with system error number

my_error(ER_GET_ERRNO, MYF(0), errno);

mem_free(path);

return(DB_ERROR);

}

MySQL doesn’t show informative error message for read-only filesystem in Linux on version 5.6

November 14, 2016November 15, 2016 elshadaghazade MySQL Internals Bug fixes, MySQL, MySQL Internals

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) {

return F_OK; // file exists but have not any access

} else {

return -1; // file does not exist

}

} else {

return W_OK; // read/write

}

}

Then in the mysqld_main function where everythink starts, I got a value of datadir parameter and pass to the is_filesystem_read_only function, then I check a state code. According to this state code I write a custom informative message to the log file. Codes are below:

// Get –datadir value to check if filesystem is read-only

for (int i = 1; i < argc; i++) {

if (strstr(argv[i], “–datadir”)) {

std::string str(argv[i]);

char const* dataDirPath = str.substr(10, str.length()).c_str();

if (is_filesystem_read_only(dataDirPath) == R_OK) {

my_message_local(ERROR_LEVEL, “File system (%s) is read-only”, dataDirPath);

return 1;

}

break;

}

}

Bug fixed 🙂

Elshad Aghazade's Blog

Programming is Lifestyle

UNDERSTANDING MySQL CLIENT / SERVER PROTOCOL USING PYTHON AND WIRESHARK – PART 2

CLASSES FOR DATATYPES

INT CLASS

STR CLASS

HANDSHAKE_PACKAGE CLASS

LOGIN_PACKAGE CLASS

OK_PACKAGE & ERR_PACKAGE CLASSES

MYSQL CLASS

UNDERSTANDING MySQL CLIENT / SERVER PROTOCOL USING PYTHON AND WIRESHARK – PART 1

STRUCTURE OF PACKETS

INTEGER TYPES

STRING TYPES

SNIFF WITH WIRESHARK

Posting to Telegram channel by bot using mysql, python

Wrong binlog size reported after moving binlogs on MySQL version 5.6

Bug fix – Error -1 from storage engine while creating table with non-existing datadir on MySQL version 5.6

MySQL doesn’t show informative error message for read-only filesystem in Linux on version 5.6