Using MySQL Connector/Python X (mysqlx module) MyRocks episode

This post is about, how I have tried to make simple Python script using mysqlx module work with MyRocks.
This is also related to pytest, as I have implemented simple pytest tests to call them from bash file.

So let’s discuss problem description:
The base problem is, by default when you create collection using Python X Plugin, the collection will have, 1 json type column called `doc` and 1 generated column from this `doc` column called `_id`.
So basically, you can not alter table engine to MyRocks because it will give an error something like:

ERROR 3106 (HY000): 'Specified storage engine' is not supported for generated columns.

The result:

Well, it can be solved by dropping generated `_id` column. Here we are encountering another issue that, if you have table with json data, please do NOT alter it to MyRocks, otherwise, you will get some weird results as described here:

https://jira.percona.com/browse/MYR-152

So basically, when I select from this collection it will raise an OperationalError:

mysqlx.errors.OperationalError("The JSON binary value contains invalid data")

Now the sample code portions:

The main worker class code:


import mysqlx

class MyXPlugin:

    def __init__(self, schema_name, collection_name):
        # Connect to a dedicated MySQL server
        self.session = mysqlx.get_session({
            'host': 'localhost',
            'port': 33060,
            'user': 'bakux',
            'password': 'Baku12345',
            'ssl-mode': mysqlx.SSLMode.DISABLED
        })

        self.schema_name = schema_name
        self.collection_name = collection_name

        # Getting schema object
        self.schema = self.session.get_schema(self.schema_name)
        # Creating collection
        self.schema.create_collection(self.collection_name)
        # Getting collection object
        self.collection_obj = self.schema.get_collection(self.collection_name)


    def insert_into_collection(self):
        # You can also add multiple documents at once
        print "Inserting 3 rows into collection"
        self.collection_obj.add({'_id': '2', 'name': 'Sakila', 'age': 15},
                    {'_id': '3', 'name': 'Jack', 'age': 15},
                    {'_id': '4', 'name': 'Clare', 'age': 37}).execute()

    def remove_from_collection(self):
        # Removing non-existing _id
        self.collection_obj.remove('_id = 1').execute()


    def alter_table_engine(self):
        # Altering table engine to rocksdb; Should raise an error
        try:
            command = "alter table {}.{} engine=rocksdb".format(self.schema_name, self.collection_name)
            sql = self.session.sql(command)
            sql.execute()
        except Exception as e:
            raise mysqlx.errors.OperationalError("Could not alter engine of table here!")
        else:
            return 0

    def alter_table_drop_column(self):
        # Dropping generated column
        print "Altering default collection to drop generated column"
        try:
            command = "alter table {}.{} drop column `_id`".format(self.schema_name, self.collection_name)
            sql = self.session.sql(command)
            sql.execute()
        except Exception as e:
            raise
        else:
            return 0

    def return_table_obj(self):
        # Returning Table object
        table = mysqlx.Table(self.schema, self.collection_name)
        return table

    def create_view_from_collection(self, view_name):
        # Creating view from collection
        print "Trying to create view based on MyRocks collection"
        try:
            command = "create view {}.{} as select * from {}.{}".format(self.schema_name, view_name, self.schema_name, self.collection_name)
            sql = self.session.sql(command)
            sql.execute()
        except Exception as e:
            raise
        else:
            return 0

    def select_from_view(self, view_name):
        # Running select; Should raise an error
        print "Trying to select from view [Should raise an OperationalError]"
        try:
            command = "select * from {}.{}".format(self.schema_name, view_name)
            sql = self.session.sql(command)
            sql.execute()
        except Exception as e:
            raise mysqlx.errors.OperationalError("The JSON binary value contains invalid data")
        else:
            return 0

    def select_from_table(self):
        # Running select; Should raise an error
        print "Trying to select from view [Should raise an OperationalError]"
        try:
            command = "select * from {}.{}".format(self.schema_name, self.collection_name)
            sql = self.session.sql(command)
            sql.execute()
        except Exception as e:
            raise mysqlx.errors.OperationalError("The JSON binary value contains invalid data")
        else:
            return 0

The PyTest module:

import pytest
from mysqlx.errors import OperationalError

@pytest.mark.usefixtures("return_plugin_obj")
class TestXPlugin:
    """
    Tests for XPlugin + MyRocks
    """

    def test_check_if_collection_exists(self, return_plugin_obj):
        assert return_plugin_obj.collection_obj.exists_in_database() == True

    def test_check_collection_count(self, return_plugin_obj):
        # Inserting data
        return_plugin_obj.insert_into_collection()
        # Removing data
        return_plugin_obj.remove_from_collection()
        # Checking count
        assert return_plugin_obj.collection_obj.count() == 3

    def test_alter_table_engine_raises(self, return_plugin_obj):
        # Should raise error here
        print "Altering default collection engine from InnoDB to MyRocks [Should raise an OperationalError]"
        with pytest.raises(OperationalError) as er:
            return_plugin_obj.alter_table_engine()
        print er

    def test_alter_table_drop_column(self, return_plugin_obj):
        return_value = return_plugin_obj.alter_table_drop_column()
        assert return_value == 0

    def test_alter_table_engine(self, return_plugin_obj):
        print "Altering default collection engine from InnoDB to MyRocks [Should NOT raise an OperationalError]"
        return_value = return_plugin_obj.alter_table_engine()
        assert return_value == 0

    def helper_function(self, return_plugin_obj):
        table_obj = return_plugin_obj.return_table_obj()
        return table_obj

    def test_check_if_table_exists(self, return_plugin_obj):
        assert self.helper_function(return_plugin_obj).exists_in_database() == True

    def test_check_table_count(self, return_plugin_obj):
        assert self.helper_function(return_plugin_obj).count() == 3

    def test_check_table_name(self, return_plugin_obj):
        assert self.helper_function(return_plugin_obj).get_name() == "my_collection"

    def test_check_schema_name(self, return_plugin_obj):
        assert self.helper_function(return_plugin_obj).get_schema().get_name() == "generated_columns_test"

    def test_check_if_table_is_view(self, return_plugin_obj):
        assert self.helper_function(return_plugin_obj).is_view() == False

    def test_create_view_from_collection(self, return_plugin_obj):
        return_value = return_plugin_obj.create_view_from_collection("my_collection_view")

    def test_select_from_view(self, return_plugin_obj):
        with pytest.raises(OperationalError) as er:
             return_plugin_obj.select_from_view("my_collection_view")
        print er

    def test_select_from_table(self, return_plugin_obj):
        with pytest.raises(OperationalError) as er:
            return_plugin_obj.select_from_table()
        print er

The PyTest fixture code:

from myrocks_mysqlx_plugin.myrocks_mysqlx_plugin import MyXPlugin
import pytest
# schema_name = "generated_columns_test"
# collection_name = "my_collection"
plugin_obj = MyXPlugin("generated_columns_test", "my_collection")

@pytest.fixture()
def return_plugin_obj():
    return plugin_obj

The final bash runner test output:

#Running X Plugin tests#
========================================================================== test session starts ==========================================================================
platform linux2 -- Python 2.7.5, pytest-3.2.1, py-1.4.34, pluggy-0.4.0 -- /usr/bin/python
cachedir: ../../.cache
rootdir: /home/shahriyar.rzaev, inifile:
collected 13 items                                                                                                                                                       

../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_if_collection_exists PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_collection_count PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_alter_table_engine_raises PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_alter_table_drop_column PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_alter_table_engine PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_if_table_exists PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_table_count PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_table_name PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_schema_name PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_if_table_is_view PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_create_view_from_collection PASSED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_select_from_view FAILED
../../percona-qa/myrocks-tests/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_select_from_table PASSED

Also reported interesting thing here:
https://bugs.mysql.com/bug.php?id=87531

Using MySQL Connector/Python X (mysqlx module)

This post is about simple usage of mysqlx module i.e X Plugin with latest Connector/Python DMR.
The version of Python Connector is 8.0.4.

Installing:

wget https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-8.0.4-0.1.dmr.el7.x86_64.rpm
sudo yum install mysql-connector-python-8.0.4-0.1.dmr.el7.x86_64.rpm

Sample Python code:

# Connecting to MySQL and working with a Session
import mysqlx

# Connect to a dedicated MySQL server
session = mysqlx.get_session({
    'host': 'localhost',
    'port': 33060,
    'user': 'bakux',
    'password': 'Baku12345',
    'ssl-mode': mysqlx.SSLMode.DISABLED
})

schema = session.get_schema('generated_columns_test')

# Create 'my_collection' in schema
schema.create_collection('my_collection')

# Get 'my_collection' from schema
collection = schema.get_collection('my_collection')

assert(True == collection.exists_in_database())

# You can also add multiple documents at once
collection.add({'_id': '2', 'name': 'Sakila', 'age': 15},
            {'_id': '3', 'name': 'Jack', 'age': 15},
            {'_id': '4', 'name': 'Clare', 'age': 37}).execute()

collection.remove('_id = 1').execute()

assert(3 == collection.count())

The code is quite clean so basically we have created a collection and inserted some data.

From MySQL client side the structure of this “table”==collection:

CREATE TABLE `my_collection` (
  `doc` json DEFAULT NULL,
  `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$._id'))) STORED NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The collection has “stored generated column” _id which is extracted from doc column.

select * from my_collection;
+-------------------------------------------+-----+
| doc                                       | _id |
+-------------------------------------------+-----+
| {"_id": "2", "age": 15, "name": "Sakila"} | 2   |
| {"_id": "3", "age": 15, "name": "Jack"}   | 3   |
| {"_id": "4", "age": 37, "name": "Clare"}  | 4   |
+-------------------------------------------+-----+
3 rows in set (0.00 sec)

This default behavior restricted my tests on other storage engines. Because it is impossible to alter the engine of this default “collection”.

ERROR 3106 (HY000): 'Specified storage engine' is not supported for generated columns.

BTW, while testing, found some bugs and reported:

https://bugs.mysql.com/bug.php?id=87472
https://bugs.mysql.com/bug.php?id=87475
https://bugs.mysql.com/bug.php?id=87492
https://bugs.mysql.com/bug.php?id=87493

Testing MySQL 8.0 – let me create a ton of undo files

This is the late blog post about 2 recent bug reports 🙂
#85969
#85971

The basic idea came after reading -> http://mysqlserverteam.com/the-mysql-8-0-1-milestone-release-is-available/

So the result of test ->
After each restart of MySQL the new undo log files are going to be created + keeping old files.

shahriyar.rzaev@qaserver-06:~/sandboxes/msb_8_0_1/data$ du -hs
6.4G	
# The count of undo files
shahriyar.rzaev@qaserver-06:~/sandboxes/msb_8_0_1/data$ ls | grep undo | wc -l
539

After new restart:

# New count
shahriyar.rzaev@qaserver-06:~/sandboxes/msb_8_0_1/data$ ls | grep undo | wc -l
616

So how to reproduce?

1. Started server with following settings

early-plugin-load=keyring_file.so
keyring_file_data=/home/shahriyar.rzaev/sandboxes/msb_8_0_1/tmp/keyring

innodb_redo_log_encrypt=ON
innodb_undo_tablespaces=77
innodb_undo_logs=77
innodb_undo_log_encrypt=ON

2. Then killed it with -9 and got the error:

2017-05-08T08:54:07.174124Z 1 [ERROR] InnoDB: Expected to open 77 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0
2017-05-08T08:54:07.174147Z 1 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-05-08T08:54:07.674794Z 1 [ERROR] Failed to initialize DD Storage Engine
2017-05-08T08:54:07.674922Z 0 [ERROR] Data Dictionary initialization failed.
2017-05-08T08:54:07.674932Z 0 [ERROR] Aborting

3. To overcome this issue I have changed config and restarted MySQL:

innodb_redo_log_encrypt=ON
innodb_undo_tablespaces=0
innodb_undo_logs=77
innodb_undo_log_encrypt=ON

And it is started.

4. Then I have changed back the config again and restarted MySQL:

innodb_redo_log_encrypt=ON
innodb_undo_tablespaces=77
innodb_undo_logs=77
innodb_undo_log_encrypt=ON

From now after each restart it will create new undo files.
See below the full test result:
https://bugs.mysql.com/file.php?id=25383&bug_id=85971

My first patch for MySQL world; fixing BUG #76852

Hi dear MySQL Community.
I have reported a bunch of bugs and wrote some articles, but today is a special for me 🙂
Because today I send my first PR for MySQL source code.

So I have logged a bug -> #76852 and now fixed it after year.
Okey, I know I am lazy.

Let’s reproduce the bug.
Simulating full disk condition is easy as:

mkdir /filesystems
dd if=/dev/zero of=/filesystems/tmp_fs bs=1024 count=0 seek=$[1024*10]
mkfs.ext4 /filesystems/tmp_fs
mkdir small_mounted_dir
mount -o loop /filesystems/tmp_fs /home/sh/small_mounted_dir/

Then obtain MySQL source and compile it with DEBUG. If you don’t know how to do it just refer to -> installing-mysql-from-source-cmake-issues

From now you can use MySQL Sandbox to install 1 master and 2 slaves:

make_sandbox_from_source /home/sh/MySQLPakcages/mysql-5.6.32 replication

Inside main path there will be a script for enabling GTID:

/home/sh/sandboxes/rsandbox_mysql-5_6_32/enable_gtid

Run it.

Then stop master using ./stop script inside master folder:

/home/sh/sandboxes/rsandbox_mysql-5_6_32/master/stop

Move binary mysql-bin.* files from datadir to /home/sh/small_mounted_dir/.
Update my.sandbox.cnf and add new path for log-bin variable. Then start MySQL using ./start script.
You can run sysbench to populate data:

 sysbench --test=/home/sh/REPOS/sysbench/sysbench/tests/db/oltp.lua --oltp-test-mode=complex \
 --num-threads=100 --mysql-db=dbtest --mysql-user=root --mysql-password=msandbox --db-driver=mysql \
 --max-requests=0 --innodb-table-size=1000000 --mysql-socket=/tmp/mysql_sandbox23097.sock prepare
  
sysbench --test=/home/sh/REPOS/sysbench/sysbench/tests/db/insert.lua --oltp-test-mode=complex \
--num-threads=100 --mysql-db=dbtest --mysql-user=root --mysql-password=msandbox --db-driver=mysql  \
--max-requests=0 --innodb-table-size=1000000 --mysql-socket=/tmp/mysql_sandbox23097.sock run

Next you will likely see something:

2016-11-08 00:56:32 13417 [Warning] Disk is full writing '/home/sh/small_mounted_dir/mysql-bin.000003' (Errcode: 28 - No space left on device). Waiting for someone to free space...
2016-11-08 00:56:32 13417 [Warning] Retry in 60 secs. Message reprinted in 600 secs

If you try to stop MySQL, you will likely hit -> #83705

And if you try to start you will likely hit -> #76852

According to coredump:

(gdb) bt
#0  0x0000000000000000 in ?? ()
#1  0x0000000000ae6242 in my_printf_warning (format=0x1026f08 "Disk is full writing '%s' (Errcode: %d - %s). Waiting for someone to free space...")
    at /root/mysql-5.6.24/mysys/my_error.c:260
#2  0x0000000000ac9052 in wait_for_free_space (filename=0x1fe7ee0 "/opt/mysql/datadir/mysql-bin.~rec~", errors=0) at /root/mysql-5.6.24/mysys/errors.c:115
#3  0x0000000000af1ac7 in my_write (Filedes=19, Buffer=0x1964d00 "/opt/mysql/datadir/mysql-bin.000003\n", Count=36, MyFlags=52)
    at /root/mysql-5.6.24/mysys/my_write.c:89
#4  0x0000000000acd5ae in inline_mysql_file_write (src_file=0x1027708 "/root/mysql-5.6.24/mysys/mf_iocache.c", src_line=1788, file=19, 
    buffer=0x1964d00 "/opt/mysql/datadir/mysql-bin.000003\n", count=36, flags=52) at /root/mysql-5.6.24/include/mysql/psi/mysql_file.h:1141
#5  0x0000000000ad078c in my_b_flush_io_cache (info=0x183c1a8, need_append_buffer_lock=0) at /root/mysql-5.6.24/mysys/mf_iocache.c:1787
#6  0x0000000000a7132b in MYSQL_BIN_LOG::sync_purge_index_file (this=0x183b400) at /root/mysql-5.6.24/sql/binlog.cc:4420
#7  0x0000000000a6e206 in MYSQL_BIN_LOG::open_binlog (this=0x183b400, log_name=0x190d130 "/opt/mysql/datadir/mysql-bin", new_name=0x0, 
    io_cache_type_arg=WRITE_CACHE, max_size_arg=1073741824, null_created_arg=false, need_lock_index=true, need_sid_lock=true, extra_description_event=0x0)
    at /root/mysql-5.6.24/sql/binlog.cc:3146
#8  0x000000000063ad8e in init_server_components () at /root/mysql-5.6.24/sql/mysqld.cc:5012
#9  0x000000000063b6e7 in mysqld_main (argc=19, argv=0x186de68) at /root/mysql-5.6.24/sql/mysqld.cc:5455
#10 0x000000000062fc74 in main (argc=10, argv=0x7fffffffe3d8) at /root/mysql-5.6.24/sql/main.cc:25

The problem is somewhere at line 260 in /mysys/my_error.c:

void(*sql_print_warning_hook)(const char *format,...);
void my_printf_warning(const char *format, ...)
{
  va_list args;
  char wbuff[ERRMSGSIZE];
  DBUG_ENTER("my_printf_warning");
  DBUG_PRINT("my", ("Format: %s", format));
  va_start(args,format);
  (void) my_vsnprintf (wbuff, sizeof(wbuff), format, args);
  va_end(args);
  (*sql_print_warning_hook)(wbuff);
  DBUG_VOID_RETURN;
}

It gives SEGFAULT exactly on (*sql_print_warning_hook)(wbuff) which makes clear that, sql_print_warning_hook is not initialized at that point.

So as a suggestion by Sergei Glushchenko the solution can be achieved by checking sql_print_warning_hook prior calling inside my_error.c or initialize earlier sql_print_warning_hook inside sql/mysqld.cc. The best to have both:

I decide to initialize sql_print_warning_hook inside init_server_components():
Took this one:

+  /*
    The subsequent calls may take a long time : e.g. innodb log read.
    Thus set the long running service control manager timeout
   */
@@ -5596,7 +5598,7 @@ int mysqld_main(int argc, char **argv)
   */
   error_handler_hook= my_message_sql;
   start_signal_handler();       // Creates pidfile
-  sql_print_warning_hook = sql_print_warning;
+
[/cpp]

and added to:


@@ -4663,6 +4663,8 @@ static int init_server_components()
     We need to call each of these following functions to ensure that
     all things are initialized so that unireg_abort() doesn't fail
   */
+  sql_print_warning_hook = sql_print_warning;
+
   mdl_init();
   if (table_def_init() | hostname_cache_init(host_cache_size))
     unireg_abort(1);
@@ -5486,7 +5488,7 @@ int mysqld_main(int argc, char **argv)
 #endif
   }

Second thing is to add simple check inside /mysys/my_error.c:

diff --git a/mysys/my_error.c b/mysys/my_error.c
index b1f57ba..4441228 100644
--- a/mysys/my_error.c
+++ b/mysys/my_error.c
@@ -257,7 +257,14 @@ void my_printf_warning(const char *format, ...)
   va_start(args,format);
   (void) my_vsnprintf (wbuff, sizeof(wbuff), format, args);
   va_end(args);
-  (*sql_print_warning_hook)(wbuff);
+  if(sql_print_warning_hook)
+  {
+    (*sql_print_warning_hook)(wbuff);
+  }
+  else
+  {
+    fprintf(stderr, "%s\n", wbuff);
+  }
   DBUG_VOID_RETURN;
 }

After recompiling and retesting, there will be no SEGFAULT anymore.
So it can be considered as a fix, except that, my IDE added/removed some spaces from source code 🙂

The PR link -> PR #109

Playing with MySQL Source code; Adding “STOP ALL SLAVES” command

This blog post is a result of reading book.
To be clear -> “Expert MySQL” by Charles Bell -> http://www.apress.com/gp/book/9781430246596
This book explain internals of MySQL based on 5.6.x version. So relatively new and really good resource to use.

So the task is:
Chapter 8, “Extending MySQL High Availability” -> page 310, “Extending Replication”.
Where we should add new command -> “SQLCOM_STOP_SLAVES” executed on master and causing to STOP IO_THREAD on all slaves.
The basic idea is to run “STOP ALL SLAVES” on master, which in turn will appear on binary log and then it will applied on slaves.

Great 🙂
MySQL version is 5.6.32.

So, as book advice go to sql/lex.h. “We are adding the new symbol for the command. This file contains the symbol array stored in alphabetical order.”

Added:

{ "SLAVE",            SYM(SLAVE)},
  /* BEGIN CAB MODIFICATION */
  /* Reason for Modification */
  /* Add SLAVES command */
  { "SLAVES",           SYM(SLAVES)},
  /* END CAB MODIFICATION*/
  { "SLOW",             SYM(SLOW)},

Then go to sql/sql_cmd.h to add a new enumeration for the big switch. Locate enum enum_sql_command definition near the top of the file.
Added:

SQLCOM_COMMIT, SQLCOM_SAVEPOINT, SQLCOM_RELEASE_SAVEPOINT,
  /* BEGIN CAB MODIFICATION */
  /* Reason for Modification */
  /*Add SQLCOM_STOP_SLAVES enum */
  SQLCOM_SLAVE_START, SQLCOM_SLAVE_STOP, SQLCOM_STOP_SLAVES,
  /*END CAB MODIFICATION*/
  SQLCOM_BEGIN, SQLCOM_CHANGE_MASTER,

“Next we need to add a new token to be used in the new rule. Once again, the list of tokens is arranged in alphabetical order. Open the sql/sql_yacc.yy and locate the section where new tokens are defined. In this case, we need to add a definition for a token for new command. We will name it SLAVES.”
Added near line 1539/1540:

%token  SLAVE
/* BEGIN CAB MODIFICATION */
/* Reason for Modification */
/*Add SLAVES token */
%token SLAVES
/*END CAB MODIFICATION*/
%token  SLOW

“Next modify the section where the “%type” defintion resides. We need to add new token to this definition.”
Added near line 1855/1856:

slave master_def master_defs master_file_def slave_until_opts
        /* BEGIN CAB MODIFICATION */
        /* Reason for Modification */
        /* Add stop to list of NONE types */
        repair analyze check start stop checksum
        /*END CAB MODIFICATION*/
        field_list field_list_item field_spec kill column_def key_def

“Next, we add a new command definition to the list of commands so that the parser can direct control to the new rule. Notice we add a new ‘or’ condition mapping to a new rule to be evaluated.”
Added near line 2079/2080:

       | start
  /* BEGIN CAB MODIFICATION */
  /* Reason for Modification */
  /* Add stop to list of statement targets */
        | stop
  /*END CAB MODIFICATION*/
        | truncate

“Last, we will add the new rule to process the STOP ALL SLAVES command. The rule simply saves the new enumeration to the lex->sql_command attribute. This is how the code maps the result of the rule(and the processing of the command) to the big switch to a case equal to the enumeration value.”

Added near line 8114/8115:

/* BEGIN CAB MODIFICATION */
/* Reason for Modification */
/* Add rule for stop ALL SLAVES command*/
stop:
          STOP_SYM ALL SLAVES
          {
            LEX *lex= Lex;
            lex->sql_command= SQLCOM_STOP_SLAVES;
          }
        ;
/*END CAB MODIFICATION*/

“With changes to the YACC file complete, we can add a new case for the big switch to ensure the command, once captured by the parser, is directed to code to written the event to the binary log. Normally, the STOP SLAVE command is not replicated. Our code would also override this restriction. Let us add that case statement. Open the sql/sql_parse.cc

Added near line 3159/3160:

    mysql_mutex_unlock(&LOCK_active_mi);
    break;
  }

/* BEGIN CAB MODIFICATION */
/* Reason for Modification */
/* Add case statement for STOP ALL SLAVES command */
  case SQLCOM_STOP_SLAVES:
  {
    if (!lex->no_write_to_binlog)
    {
        res = write_bin_log(thd, TRUE, "STOP SLAVE IO_THREAD", 20);
    }

    break;
  }
/*END CAB MODIFICATION*/


#endif /* HAVE_REPLICATION */

  case SQLCOM_RENAME_TABLE:

“The first statement is designed to check to see if the server is able to write to the binary log. If so, we add a new log event, passing it the STOP SLAVE SQL command. Notice that we use a specific version of the STOP SLAVE command. In this case, we are stopping only IO_THREAD.”

I assume that you are familiar with compiling MySQL from source code. If not then read -> installing-mysql-from-source-cmake-issues

Go ahead and run “make” again. It will likely fail with:

Scanning dependencies of target sql
[ 54%] Building CXX object sql/CMakeFiles/sql.dir/sql_parse.cc.o
[ 54%] Building CXX object sql/CMakeFiles/sql.dir/mysqld.cc.o
In file included from /home/sh/MySQLPakcages/mysql-5.6.32/include/my_compiler.h:151:0,
                 from /home/sh/MySQLPakcages/mysql-5.6.32/include/my_global.h:473,
                 from /home/sh/MySQLPakcages/mysql-5.6.32/sql/mysqld.cc:17:
/home/sh/MySQLPakcages/mysql-5.6.32/sql/mysqld.cc: In function ‘int init_common_variables()’:
/home/sh/MySQLPakcages/mysql-5.6.32/include/my_attribute.h:38:42: error: size of array ‘compile_time_assert’ is negative
 #  define MY_ATTRIBUTE(A) __attribute__(A)
                                          ^
/home/sh/MySQLPakcages/mysql-5.6.32/include/my_global.h:377:52: note: in expansion of macro ‘MY_ATTRIBUTE’
     typedef char compile_time_assert[(X) ? 1 : -1] MY_ATTRIBUTE((unused)); \
                                                    ^
/home/sh/MySQLPakcages/mysql-5.6.32/sql/mysqld.cc:3871:3: note: in expansion of macro ‘compile_time_assert’
   compile_time_assert(sizeof(com_status_vars)/sizeof(com_status_vars[0]) - 1 ==
   ^
sql/CMakeFiles/sql.dir/build.make:3581: recipe for target 'sql/CMakeFiles/sql.dir/mysqld.cc.o' failed
make[2]: *** [sql/CMakeFiles/sql.dir/mysqld.cc.o] Error 1
CMakeFiles/Makefile2:5192: recipe for target 'sql/CMakeFiles/sql.dir/all' failed
make[1]: *** [sql/CMakeFiles/sql.dir/all] Error 2
Makefile:160: recipe for target 'all' failed
make: *** [all] Error 2

As a beginner in source code world, I suddenly found a BUG in book 🙂
After reading comments I have found something inside sql/sql_cmd.h:

/*
  When a command is added here, be sure it's also added in mysqld.cc
  in "struct show_var_st status_vars[]= {" ...
*/

So the new command should also be added inside sql/mysqld.cc. But there is no such struct show_var_st status_vars[] there. It took me some time to figure out that instead of status_vars[], command should be added into SHOW_VAR com_status_vars[]
See related report -> #83613

So edited mysqld.cc file near line 3571/3572:

{"slave_stop",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SLAVE_STOP]), SHOW_LONG_STATUS},
        /* BEGIN CAB MODIFICATION */
        /* Reason for Modification */
  {"stop_slaves",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_STOP_SLAVES]), SHOW_LONG_STATUS},
        /*END CAB MODIFICATION*/

Save and recompile it will succeed. Next is to look at easy way to setup replication and test the new added command. In the book, author used tools from “MySQL Utilities”. But I have used “MySQL Sandbox” as:

make_sandbox_from_source /home/sh/MySQLPakcages/mysql-5.6.32 replication

That’s it, 1 master and 2 slaves are ready to test.

For my bad, ” stop all slaves” command failed with following assertion on master:

Version: '5.6.32-debug-log'  socket: '/tmp/mysql_sandbox23097.sock'  port: 23097  Shahriyar Rzayev's Ubuntu 16.04 MySQL-5.6.32
mysqld: /home/sh/MySQLPakcages/mysql-5.6.32/sql/protocol.cc:515: void Protocol::end_statement(): Assertion `0' failed.
14:49:07 UTC - mysqld got signal 6 ;

So ideally my statement, should return something like “Query OK”. After asking Weixiang Zhai issue fixed by adding my_ok(thd)

So the final code for sql/sql_parse.cc:

    mysql_mutex_unlock(&LOCK_active_mi);
    break;
  }

/* BEGIN CAB MODIFICATION */
/* Reason for Modification */
/* Add case statement for STOP ALL SLAVES command */
  case SQLCOM_STOP_SLAVES:
  {
    if (!lex->no_write_to_binlog)
    {
        res = write_bin_log(thd, TRUE, "STOP SLAVE IO_THREAD", 20);
    }

    if(!res)
    {
        my_ok(thd);
    }

    break;
  }
/*END CAB MODIFICATION*/


#endif /* HAVE_REPLICATION */

  case SQLCOM_RENAME_TABLE:

Recompiled and reinstalled with sandbox, the result of running new command on master:

master [localhost] {msandbox} ((none)) > stop all slaves; 
Query OK, 0 rows affected (0.00 sec)

Result on slaves:

Slave_IO_Running: No 
Slave_SQL_Running: Yes

So it works like a charm 🙂

MySQL Group Replication plugin issues with 5.7.8-rc2

Well the new things are cool to test and learn. But it is not possible every time, to have smooth testing process. if you can’t get right software on your hand.

So there are great articles about this new plugin here you can follow all of them: Group Replication Topics

I must say that, plugin available for download as source code and as for Oracle Linux 6 x86_64 from -> labs.mysql.com

So if you want to try it with MySQL 5.7.8-rc2 on Ubuntu you should compile it with MySQL. But you will not be able to compile due to well known ‘boost’ dependency -> see BUG #78600

UPDATE to BUG 78600:

Pedro Gomes clarified the reason of failed compiling process. There must not be any “spaces” between cmake options specified with -DMYSQL_SERVER_CMAKE_ARGS=””. So we have working cmake command as follows:

    cmake ..  -DMYSQL_SERVER_SRC_DIR="/home/sh/Sandboxes/mysql-5.7.8-rc" -DMYSQL_SERVER_CMAKE_ARGS="-DMYSQL_DATADIR=/opt/mysql-5.7.8-grouprepl/datadir;-DSYSCONFDIR=/opt/mysql-5.7.8-grouprepl;-DWITH_SSL=system;-DMYSQL_TCP_PORT=3307;-DMYSQL_UNIX_ADDR=/opt/mysql-5.7.8-grouprepl/mysqld.sock;-DDEFAULT_CHARSET=utf8;-DDEFAULT_COLLATION=utf8_general_ci;-DWITH_DEBUG=1;-DCOMPILATION_COMMENT='Group Replication enabled MySQL-5.7.8';-DOPTIMIZER_TRACE=1;-DWITH_ZLIB=system;-DWITH_VALGRIND=1;-DCMAKE_C_FLAGS=-DHAVE_purify;-DCMAKE_CXX_FLAGS=-DHAVE_purify;-DDOWNLOAD_BOOST=1;-DWITH_BOOST=/home/sh/Sandboxes"

But even you have Oracle Linux 6 x86_64 and even you follow topics: 1. Corosync Guide 2. Getting Started with MySQL Group Replication

You will likely get “Segmentation Fault” after running START GROUP_REPLICATION:

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x7fffc8ef2700 (LWP 3294)]
0x00007ffff625d532 in _int_malloc () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install corosynclib-1.4.7-2.el6.x86_64
(gdb) bt
#0  0x00007ffff625d532 in _int_malloc () from /lib64/libc.so.6
#1  0x00007ffff625e991 in malloc () from /lib64/libc.so.6
#2  0x00007ffff6acf0bd in operator new(unsigned long) () from /usr/lib64/libstdc++.so.6
#3  0x00007fffc8f5b2ab in Certifier::Certifier (this=0x7fffbc051d20)
    at /export/home/pb2/build/sb_0-16452762-1441960761.59/release-0.5.0-export-8447366/BIN/BUILD/mysql-server/plugin/group_replication/src/certifier.cc:209
#4  0x00007fffc8f6bb36 in Certification_handler::initialize (this=0x7fffbc0507e0)
    at /export/home/pb2/build/sb_0-16452762-1441960761.59/release-0.5.0-export-8447366/BIN/BUILD/mysql-server/plugin/group_replication/src/handlers/certification_handler.cc:32
#5  0x00007fffc8f634d7 in configure_pipeline (pipeline=0x7fffbc051a80, handler_list=0x7fffbc0506f0, num_handlers=3)
    at /export/home/pb2/build/sb_0-16452762-1441960761.59/release-0.5.0-export-8447366/BIN/BUILD/mysql-server/plugin/group_replication/src/pipeline_factory.cc:143
#6  0x00007fffc8f6364d in get_pipeline (pipeline_type=<value optimized out>, pipeline=0x7fffbc051a80)
    at /export/home/pb2/build/sb_0-16452762-1441960761.59/release-0.5.0-export-8447366/BIN/BUILD/mysql-server/plugin/group_replication/src/pipeline_factory.cc:30
#7  0x00007fffc8f554b0 in Applier_module::setup_applier_module (this=0x7fffbc051920, pipeline_type=STANDARD_GROUP_REPLICATION_PIPELINE, reset_logs=false, 
    stop_timeout=31536000, group_sidno=2)
    at /export/home/pb2/build/sb_0-16452762-1441960761.59/release-0.5.0-export-8447366/BIN/BUILD/mysql-server/plugin/group_replication/src/applier.cc:99
#8  0x00007fffc8f63eca in configure_and_start_applier_module ()
    at /export/home/pb2/build/sb_0-16452762-1441960761.59/release-0.5.0-export-8447366/BIN/BUILD/mysql-server/plugin/group_replication/src/plugin.cc:761
#9  0x00007fffc8f657df in plugin_group_replication_start ()
    at /export/home/pb2/build/sb_0-16452762-1441960761.59/release-0.5.0-export-8447366/BIN/BUILD/mysql-server/plugin/group_replication/src/plugin.cc:289
#10 0x00000000016927c9 in Group_replication_handler::start (this=0x378f2d0) at /home/full_datadir/mysql-5.7.8-rc/sql/rpl_group_replication.cc:56
#11 0x0000000001692b81 in group_replication_start () at /home/full_datadir/mysql-5.7.8-rc/sql/rpl_group_replication.cc:192
#12 0x000000000149739c in mysql_execute_command (thd=0x7fffbc007180) at /home/full_datadir/mysql-5.7.8-rc/sql/sql_parse.cc:3029
#13 0x000000000149e15b in mysql_parse (thd=0x7fffbc007180, parser_state=0x7fffc8ef14a0) at /home/full_datadir/mysql-5.7.8-rc/sql/sql_parse.cc:5255
#14 0x0000000001492dec in dispatch_command (thd=0x7fffbc007180, com_data=0x7fffc8ef1cd0, command=COM_QUERY)
    at /home/full_datadir/mysql-5.7.8-rc/sql/sql_parse.cc:1272
#15 0x00000000014917f3 in do_command (thd=0x7fffbc007180) at /home/full_datadir/mysql-5.7.8-rc/sql/sql_parse.cc:852
#16 0x00000000015c9042 in handle_connection (arg=0x36c1a50) at /home/full_datadir/mysql-5.7.8-rc/sql/conn_handler/connection_handler_per_thread.cc:300
#17 0x0000000001c56c41 in pfs_spawn_thread (arg=0x37d1990) at /home/full_datadir/mysql-5.7.8-rc/storage/perfschema/pfs.cc:2178
#18 0x00007ffff7bc79d1 in start_thread () from /lib64/libpthread.so.0
#19 0x00007ffff62ccb6d in clone () from /lib64/libc.so.6

For further reading refer to related fresh BUG report -> #78627

It is really cool to help community to improve Open Source projects. So go on and test by your own, report interesting things and ofcourse contact with community members. They are all open to discussion 🙂

Getting SIGABRT -> Assertion `is_binlog_empty()’ failed with MySQL 5.7.7-rc

What about inserting data to MySQL 5.7.7-rc-debug with GTID/binary log enabled. It is OK until you hit disk full condition while inserting data. It will crash on the way and likely you will lose MySQL. Error log indicates related problem:

2015-07-29T07:20:05.865708Z 2 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./sls2/sales.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2015-07-29T07:20:05.895529Z 2 [Warning] InnoDB: 1048576 bytes should have been written. Only 557056 bytes written. Retrying again to write the remaining bytes.
2015-07-29T07:20:05.905149Z 2 [Warning] InnoDB: 491520 bytes should have been written. Only 106496 bytes written. Retrying again to write the remaining bytes.
2015-07-29T07:20:05.906292Z 2 [Warning] InnoDB: 385024 bytes should have been written. Only 4096 bytes written. Retrying again to write the remaining bytes.
2015-07-29T07:20:05.907309Z 2 [Warning] InnoDB: 380928 bytes should have been written. Only 4096 bytes written. Retrying again to write the remaining bytes.
2015-07-29T07:20:05.908075Z 2 [Warning] InnoDB: 376832 bytes should have been written. Only 4096 bytes written. Retrying again to write the remaining bytes.
2015-07-29T07:20:05.908727Z 2 [Warning] InnoDB: 372736 bytes should have been written. Only 4096 bytes written. Retrying again to write the remaining bytes.
2015-07-29T07:20:05.909397Z 2 [Warning] InnoDB: 368640 bytes should have been written. Only 4096 bytes written. Retrying again to write the remaining bytes.
2015-07-29T07:20:05.910056Z 2 [ERROR] InnoDB: Error in system call pwrite(). The operating system error number is 28.
2015-07-29T07:20:05.910135Z 2 [ERROR] InnoDB: Error number 28 means 'No space left on device'.
2015-07-29T07:20:05.910156Z 2 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2015-07-29T07:20:05.910172Z 2 [Warning] InnoDB: Retry attempts for writing partial data failed.
2015-07-29T07:20:05.910190Z 2 [ERROR] InnoDB: Write to file ./sls2/sales.ibd failed at offset 49283072. 1048576 bytes should have been written, only 684032 were written. Check that your OS and file system support files of  this size. Check also that the disk is not full or a disk quota  exceeded.
2015-07-29T07:20:05.910220Z 2 [Warning] InnoDB: Error while writing 4194304 zeroes to ./sls2/sales.ibd starting at offset 46137344
2015-07-29T07:20:06.001777Z 2 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full
mysqld: /root/mysql-5.7.7-rc/sql/binlog.cc:389: virtual void binlog_cache_data::reset(): Assertion `is_binlog_empty()' failed.

Getting: mysqld: /root/mysql-5.7.7-rc/sql/binlog.cc:389: virtual void binlog_cache_data::reset(): Assertion `is_binlog_empty()’ failed.

From GDB:

Program received signal SIGABRT, Aborted.
[Switching to Thread 0x7fffde31f700 (LWP 2816)]
0x00007ffff61675d7 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
56    return INLINE_SYSCALL (tgkill, 3, pid, selftid, sig);

The next step is to examine backtrace:

(gdb) bt
#0  0x00007ffff61675d7 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
#1  0x00007ffff6168cc8 in __GI_abort () at abort.c:90
#2  0x00007ffff6160546 in __assert_fail_base (fmt=0x7ffff62b0128 "%s%s%s:%u: %s%sAssertion `%s' failed.n%n", 
    assertion=assertion@entry=0x20fdb01 "is_binlog_empty()", file=file@entry=0x20fd990 "/root/mysql-5.7.7-rc/sql/binlog.cc", line=line@entry=389, 
    function=function@entry=0x21030c0 <binlog_cache_data::reset()::__PRETTY_FUNCTION__> "virtual void binlog_cache_data::reset()") at assert.c:92
#3  0x00007ffff61605f2 in __GI___assert_fail (assertion=0x20fdb01 "is_binlog_empty()", file=0x20fd990 "/root/mysql-5.7.7-rc/sql/binlog.cc", line=389, 
    function=0x21030c0 <binlog_cache_data::reset()::__PRETTY_FUNCTION__> "virtual void binlog_cache_data::reset()") at assert.c:101
#4  0x000000000181ed71 in binlog_cache_data::reset (this=0x7fffb400da68) at /root/mysql-5.7.7-rc/sql/binlog.cc:389
#5  0x000000000181f091 in binlog_trx_cache_data::reset (this=0x7fffb400da68) at /root/mysql-5.7.7-rc/sql/binlog.cc:599
#6  0x000000000180776a in binlog_trx_cache_data::truncate (this=0x7fffb400da68, thd=0x7fffb4000c80, all=false) at /root/mysql-5.7.7-rc/sql/binlog.cc:1457
#7  0x00000000018089fa in MYSQL_BIN_LOG::rollback (this=0x2bd78c0 <mysql_bin_log>, thd=0x7fffb4000c80, all=false) at /root/mysql-5.7.7-rc/sql/binlog.cc:1994
#8  0x0000000000f84dfa in ha_rollback_trans (thd=0x7fffb4000c80, all=false) at /root/mysql-5.7.7-rc/sql/handler.cc:1854
#9  0x0000000001682449 in trans_rollback_stmt (thd=0x7fffb4000c80) at /root/mysql-5.7.7-rc/sql/transaction.cc:442
#10 0x000000000158e778 in mysql_execute_command (thd=0x7fffb4000c80) at /root/mysql-5.7.7-rc/sql/sql_parse.cc:4586
#11 0x000000000158ff62 in mysql_parse (thd=0x7fffb4000c80, parser_state=0x7fffde31e5a0) at /root/mysql-5.7.7-rc/sql/sql_parse.cc:5159
#12 0x0000000001585cf1 in dispatch_command (command=COM_QUERY, thd=0x7fffb4000c80, 
    packet=0x7fffb49fd901 "INSERT INTO `sales` VALUES (408505,'1155','40','2001-06-30 14:28:05','253','6399','34','597','0','A','','0'),(408506,'827','5','2005-05-31 14:28:05','378','1169','46','707','0','A','','0'),(408507,'16"..., packet_length=1034768) at /root/mysql-5.7.7-rc/sql/sql_parse.cc:1249
#13 0x0000000001584b21 in do_command (thd=0x7fffb4000c80) at /root/mysql-5.7.7-rc/sql/sql_parse.cc:835
#14 0x00000000016adcff in handle_connection (arg=0x3cbd6b0) at /root/mysql-5.7.7-rc/sql/conn_handler/connection_handler_per_thread.cc:298
#15 0x0000000001cd9905 in pfs_spawn_thread (arg=0x3bfb1f0) at /root/mysql-5.7.7-rc/storage/perfschema/pfs.cc:2147
#16 0x00007ffff7bc6df5 in start_thread (arg=0x7fffde31f700) at pthread_create.c:308
#17 0x00007ffff62281ad in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113

For further reading and finding bt full output refer to related BUG report -> #76825