The weird messages Anthology of MySQL – Episode 1

Hi dear community.
Today I want to share with you some funny and weird error messages which can be extremely annoying 🙂

So let’s begin:

CREATE temporary TABLE `sbtest1_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL,
  `json_test_s` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  `json_test_index` varchar(255) GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `json_test_index` (`json_test_index`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 COMPRESSION='lz4' ENCRYPTION='Y'

ERROR 1478 (HY000): InnoDB: Unsupported encryption option for temporary tables.

Okay, let’s disable it:

CREATE temporary TABLE `sbtest1_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL,
  `json_test_s` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  `json_test_index` varchar(255) GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `json_test_index` (`json_test_index`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 COMPRESSION='lz4' ENCRYPTION='N'

ERROR 1031 (HY000): Table storage engine for 'sbtest1_temp' doesn't have this option

Hmm, table storage engine is InnoDB so it should have this option and in fact which option?
Maybe it is compression? Let’s disable it also:

CREATE temporary TABLE `sbtest1_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL,
  `json_test_s` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  `json_test_index` varchar(255) GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `json_test_index` (`json_test_index`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 COMPRESSION='none' ENCRYPTION='N'

ERROR 1112 (42000): Table 'sbtest1_temp' uses an extension that doesn't exist in this MySQL version

Great, now it is much more clear 🙂 Everything should exist in this MySQL version because it is the latest.
So after testing bunch of combinations it turned out it was due to COMPRESSION=’none’ statement.
If you exclude it from create statement:

CREATE temporary TABLE `sbtest1_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL,
  `json_test_s` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  `json_test_index` varchar(255) GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `json_test_index` (`json_test_index`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 ENCRYPTION='N'

Query OK, 0 rows affected (0,00 sec)

You can puzzle it + can make a list of combinations to find the exact problem as me.
But it seems to be a bug – I can use encryption=’N’ but can not use compression=’none’?
Reported as -> https://bugs.mysql.com/bug.php?id=90040

Thanks for reading.

Doc BUGs – an easy way to contribute Open Source project(part-1)

In this topic I would like to make some notes about questions, how we can contribute or help on Open Source project.
The first thing is, to learn the product itself, how to install, how to remove, how to use etc.
Of course, the first place for this information is an official documentation.
How you can use the DOC? I am taking following approach:
* Read the doc to learn the needed thing.
* Test the info provided in the DOC –
if the info is valid(i.e you have different results)
continue
if it is not valid:
Report a bug – then continue reading.

The issue can be in DOC itself or in the product.
Did you find small typo in DOC? Did you think some info about topic is wrong? Or maybe you found wrong code samples?
Report it immediately and you will be the part of community.

Let’s take some DOC bug reports and examine them to get the idea.
The first is -> #87545:
While reading Connector/Python X Plugin reference did not see any info about Error module(mysqlx.errors).
So this missing info must be there, that is why reported as described. Simple? But it is crucial.

Another one is -> #81036
There is an entry about:
“Installing MySQL Shell on Yum-based Systems”:

> Install MySQL Shell with this command:

sudo apt-get install mysql-shell

Well you have already noticed that, it must be yum command not apt. Funny? But it is needed.

Another one -> #80801

So basically here, I read the doc, run the given SQL and did not get result, then figured out that, the sample SQL was wrong and reported as described in report.

Here -> #88861
There is a typo on ALGORITM word in some places. Silly? But verified 🙂

Let’s examine the last one -> #88464

Here, I saw the error message in 5.7 but read about this only in 8.0 versions DOC. So the same info should be in 5.7s DOC as well, because I see error on 5.7 too.

In conclusion, we should understand the importance of documentation as well as, the importance to report DOC issues.
Many things are happening because of misunderstanding DOC or because of wrong info inside the DOCs.

Think about this twice in the next time, when you find some weird thing in DOC and just ignore it.
It is a real life, the thing which is not crucial for us can be important for others.

Thanks for reading.

MySQL blank users preventing slave I/O connection?

Hi,
Recently I have encountered a weird simple issue with starting Slave server.

So created user as tried to run change master:

2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e 
'select @@port'
2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e 
"CREATE USER 'repl'@'%' IDENTIFIED BY 'Baku12345'"
2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e 
"GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'"
2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/sock0.sock --force test -e 
"CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='repl', MASTER_PASSWORD='Baku12345', MASTER_PORT=10023, MASTER_AUTO_POSITION=1"
2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/sock0.sock --force test -e 
'start slave'

After that got the error:

2017-10-30 13:22:05 1314 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 11, Error_code: 2003
2017-10-30 13:22:48 7182 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 106, Error_code: 2003
2017-10-30 13:22:57 28984 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 1316, Error_code: 2003
2017-10-30 13:23:05 1314 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 12, Error_code: 2003
2017-10-30 13:23:48 7182 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 107, Error_code: 2003
2017-10-30 13:23:57 28984 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 1317, Error_code: 1045
2017-10-30 13:24:05 1314 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 13, Error_code: 1045
2017-10-30 13:24:48 7182 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 108, Error_code: 1045

I got the same result with MySQL 5.5 today and remember previous case with MySQL 5.6

2017-11-24 10:14:15 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231117-5.5.58-38.10-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231117-5.5.58-38.10-linux-x86_64-debug/sock1.sock --force test -e 'show slave status\G'
2017-11-24 10:14:15 DEBUG    <pid.PidFile object at 0x7fbe55751cc8> closing pidfile: /tmp/MySQL-AutoXtraBackup/autoxtrabackup.pid
Traceback (most recent call last):
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/bin/autoxtrabackup", line 11, in <module>
    load_entry_point('mysql-autoxtrabackup==1.5.0', 'console_scripts', 'autoxtrabackup')()
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/autoxtrabackup.py", line 209, in all_procedure
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/prepare_env_test_mode/runner_test_mode.py", line 464, in wipe_backup_prepare_copyback
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/prepare_env_test_mode/runner_test_mode.py", line 378, in run_change_master
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/prepare_env_test_mode/runner_test_mode.py", line 152, in check_slave_status
RuntimeError: Slave_IO_Running is not Yes
2017-11-24 10:14:15 DEBUG    <pid.PidFile object at 0x7fbe55751cc8> closing pidfile: /tmp/MySQL-AutoXtraBackup/autoxtrabackup.pid

So after that, I tried to connect manually via replication user:

$ /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -urepl --password='Baku12345' --port=10023 --socket=/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)

Removed blank users from master:

5.6.37>drop user ''@'localhost';
Query OK, 0 rows affected (0.01 sec)

5.6.37>drop user ''@'qaserver-02.ci.percona.com';
Query OK, 0 rows affected (0.00 sec)

Now reconnecting:

$ /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -urepl --password='Baku12345' --port=10023 --socket=/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 623
Server version: 5.6.37-82.2-debug-log MySQL Community Server (GPL)

So it turned out, the blank users were preventing slave connection to master server 🙂

Also there is a nice bug report Slave can’t reconnect after 2003 error when sha256_password is used

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