X Plugin

Fun with Bugs #63 – On Bugs Detected by ASan

Among other things Geir Hoydalsvik stated in his nice post yesterday: “We’ve fixed a number of bugs detected by UBsan and Asan.”
This is indeed true, I already noted many related bugs fixed in recent MySQL 8.0.4. But I think that a couple of details are missing in the blog post. First of all, there still a notable number of bugs detected by ASan or noted in builds with ASan that remain “Verified”. Second, who actually found and reported these bugs?I decided to do a quick search and present my summary to clarify these details. Let me start with the list of “Verified” or “Open” bugs in public MySQL bugs database, starting from the oldest one:

Bug #69715 – “UBSAN: Item_func_mul::int_op() mishandles 9223372036854775809*-1”. The oldest related “Verified” bug I found was reported back in 2013 by Arthur O’Dwyer. Shane Bester from Oracle kindly keeps checking it with recent and upcoming releases, so we know that even ‘9.0.0-dmr-ubsan’ (built on 20 October 2017) was still affected.

Bug #80309 – “some innodb tests fail with address sanitizer (WITH_ASAN)”. It was reported by Richard Prohaska and remains “Verified” for more than two years already.

Bug #80581 – “rpl_semi_sync_[non_]group_commit_deadlock crash on ASan, debug”. This bug reported by Laurynas Biveinis from Percona two years ago is still “Verified”.

Bug #81674 – “LeakSanitizer-enabled build fails to bootstrap server for MTR”. This bug reported by  Laurynas Biveinis affects only MySQL 5.6, but still, why not to backport the fix from 5.7?

Bug #82026 – “Stack buffer overflow with –ssl-cipher=<more than 4K characters>”. Bug detected by ASan was noted by Yura Sorokin from Percona and reported by Laurynas Biveinis.

Bug #82915 – “SIGKILL myself when using innodb_limit_optimistic_insert_debug=2 and drop table”. ASan debug builds are affected. This bug was reported by Roel Van de Paar from Percona.

Bug #85995 – “Server error exit due to empty datadir causes LeakSanitizer errors”. This bug in MySQL 8.0.1 (that had to affect anyone who runs tests on ASan debug builds on a regular basis) was reported by Laurynas Biveinis and stay “Verified” for almost a year.

Bug #87129 – “Unstable test main.basedir”. This test problem reported by Laurynas Biveinis affects ASan builds, among others. See also his Bug #87190 – “Test main.group_by is unstable”.

Bug #87201 – “XCode 8.3.3+ -DWITH_UBSAN=ON bundled protobuf build error”. Yet another (this time macOS-specific) bug found by Laurynas Biveinis.

Bug #87295 – “Test group_replication.gr_single_primary_majority_loss_1 produces warnings”. Potential bug in group replication noted by Laurynas Biveinis in ASan builds.

Bug #87923 – “ASan reporting a memory leak on merge_large_tests-t”. This bug by Laurynas Biveinis is still “Verified”, while Tor Didriksen’s comment states that it it resolved with the fix for Bug #87922 (that is closed as fixed in MySQL 8.0.4). Why not to close this one also?

Bug #89438 – “LeakSanitizer errors on xplugin unit tests”. As Laurynas Biveinis found, X Plugin unit tests report errors with LeakSanitizer.

Bug #89439 – “LeakSanitizer errors on GCS unit tests”. yet another bug report for MySQL 8.0.4 by Laurynas Biveinis.

Bug #89961 – “add support for clang ubsan”. This request was made by Tor Didriksen from Oracle. It is marked as “fixed in 8.0.12”. It means we may get MySQL 8.0.11 released soon. That’s why I decided to mention the bug here.
There were also few other test failures noted on ASan debug builds. I skipped them to make this post shorter.Personally I do not run builds or tests with ASan on a regular basis. I appreciate Oracle’s efforts to make code warning-free, UBSan- and ASan-clean, and fix bugs found with ASan. But I’d also want them to process all/most of related bugs in public database properly before making announcements of new related achievement, and clearly admit and appreciate a lot of help and contribution from specific community members (mostly Laurynas Biveinis in this case).Percona engineers seem to test ASan builds of MySQL 5.7 and 8.0 (or Percona’s closely related versions) regularly, for years, and contribute back public bug reports. I suspect they found way more related bugs than internal Oracle’s QA. I think we should explicitly thank them for this contribution that made MySQL better!

How to combine BATS, PyTest and MySQL X Plugin for tests

Hi,
In this post I am going to show some tricks on using BATS framework + PyTest + Python X Plugin things, to have combined, simple test solutions.
Let’s describe MySQL X Plugin side, here is the full class:

# Connecting to MySQL and working with a Session
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, reuse=True)
# 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)
cursor = sql.execute()
cursor.fetch_all()
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)
cursor = sql.execute()
cursor.fetch_all()
except Exception as e:
raise mysqlx.errors.OperationalError(“The JSON binary value contains invalid data”)
else:
return 0

And for testing purposes I have added some PyTest tests as:

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
assert return_plugin_obj.select_from_view(“my_collection_view”) == 0

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

Previously I have described the full pytest run but now, I am going to call each test method from bats file:

#!/usr/bin/env bats

# Created by Shahriyar Rzayev from Percona

DIRNAME=$BATS_TEST_DIRNAME

@test “Running test_check_if_collection_exists” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_if_collection_exists
echo $output
[ $status -eq 0 ]
}

@test “Running test_check_collection_count” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_collection_count
echo $output
[ $status -eq 0 ]
}

@test “Running test_alter_table_engine_raises” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_alter_table_engine_raises
echo $output
[ $status -eq 0 ]
}

@test “Running test_alter_table_drop_column” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_alter_table_drop_column
echo $output
[ $status -eq 0 ]
}

@test “Running test_alter_table_engine” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_alter_table_engine
echo $output
[ $status -eq 0 ]
}

@test “Running test_check_if_table_exists” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_if_table_exists
echo $output
[ $status -eq 0 ]
}

@test “Running test_check_table_count” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_table_count
echo $output
[ $status -eq 0 ]
}

@test “Running test_check_table_name” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_table_name
echo $output
[ $status -eq 0 ]
}

@test “Running test_check_schema_name” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_schema_name
echo $output
[ $status -eq 0 ]
}

@test “Running test_check_if_table_is_view” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_check_if_table_is_view
echo $output
[ $status -eq 0 ]
}

@test “Running test_create_view_from_collection” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_create_view_from_collection
echo $output
[ $status -eq 0 ]
}

@test “Running test_select_from_table [Should not raise an OperationalError after MYR-151/152]” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_select_from_table
echo $output
[ $status -eq 0 ]
}

@test “Running test_select_from_view [Should not raise an OperationalError asfter MYR-151/152]” {
run python -m pytest -vv ${DIRNAME}/myrocks_mysqlx_plugin_test/test_module01.py::TestXPlugin::test_select_from_view
echo $output
[ $status -eq 0 ]
}

The idea behind bats framework is quite simple and handy.
So just pick one test and run it inside bats.
The final sample output is something like this which is very well structured.

#Running X Plugin tests#
✓ Running test_check_if_collection_exists
✓ Running test_check_collection_count
✓ Running test_alter_table_engine_raises
✓ Running test_alter_table_drop_column
✓ Running test_alter_table_engine
✓ Running test_check_if_table_exists
✓ Running test_check_table_count
✓ Running test_check_table_name
✓ Running test_check_schema_name
✓ Running test_check_if_table_is_view
✓ Running test_create_view_from_collection
✓ Running test_select_from_table
✓ Running test_select_from_view

13 tests, 0 failures

For full code please see:
https://github.com/Percona-QA/percona-qa/tree/master/myrocks-tests

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=87472https://bugs.mysql.com/bug.php?id=87475https://bugs.mysql.com/bug.php?id=87492https://bugs.mysql.com/bug.php?id=87493

Pipelining versus Parallel Query Execution with MySQL 5.7 X Plugin

In this blog post, we’ll look at pipelining versus parallel query execution when using X Plugin for MySQL 5.7.
In my previous blog post, I showed how to use X Plugin for MySQL 5.7 for parallel query execution. The tricks I used to make it work:

Partitioning by hash
Open N connections to MySQL, where N = number of CPU cores

I had to do it manually (as well as to sort the result at the end) as X Plugin only supports “pipelining” (which only saves the round trip time) and does not “multiplex” connections to MySQL (MySQL does not use multiple CPU cores for a single query).
TL:DR; version
In this (long) post I’m playing with MySQL 5.7 X Plugin / X Protocol and document store. Here is the summary:

X Plugin does not “multiplex” connections/sessions to MySQL. Similar to the original protocol, one connection to X Plugin will result in one session open to MySQL
An X Plugin query (if the library supports it) returns immediately and does not wait until the query is finished (async call). MySQL works like a queue.
X Plugin does not have any additional server-level durability settings. Unless you check or wait for the acknowledgement (which is asynchronous) from the server, the data might or might not be written into MySQL (“fire and forget”).

At the same time, X Protocol can be helpful if:

We want to implement an asynchronous client (i.e., we do not want to block the network communication such as downloading or API calls) when the MySQL table is locked.
We want to use MySQL as a queue and save the round-trip time.

Benchmark results: “pipelining” versus “parallelizing” versus a single query
I’ve done a couple of tests comparing the results between “pipelining” versus “parallelizing” versus a single query. Here are the results:

Parallel queries with NodeJS:
$ time node async_wikistats.js

All done! Total: 17753

real 0m30.668s
user 0m0.256s
sys 0m0.028s

Pipeline with NojeJS:
$ time node async_wikistats_pipeline.js

All done! Total: 17753

real 5m39.666s
user 0m0.212s
sys 0m0.024s
In the pipeline with NojeJS, I’m reusing the same connection (and do not open a new one for each thread).

Direct query – partitioned table:
mysql> select sum(tot_visits) from wikistats.wikistats_by_day_spark_part where url like ‘%postgresql%’;
+—————–+
| sum(tot_visits) |
+—————–+
| 17753 |
+—————–+
1 row in set (5 min 31.44 sec)

Direct query – non-partitioned table.
mysql> select sum(tot_visits) from wikistats.wikistats_by_day_spark where url like ‘%postgresql%’;
+—————–+
| sum(tot_visits) |
+—————–+
| 17753 |
+—————–+
1 row in set (4 min 38.16 sec)

Advantages of pipelines with X Plugin 
Although pipelining with X Plugin does not significantly increase query response time (it can reduce the total latency), it might be helpful in some cases. For example, let’s say we are downloading something from the Internet and need to save the progress of the download as well as the metadata for the document. In this example, I use youtube-dl to search and download the metadata about YouTube videos, then save the metadata JSON into MySQL 5.7 Document Store. Here is the code:var mysqlx = require(‘mysqlx’);
# This is the same as running $ youtube-dl -j -i ytsearch100:”mysql 5.7″
const spawn = require(‘child_process’).spawn;
const yt = spawn(‘youtube-dl’, [‘-j’, ‘-i’, ‘ytsearch100:”mysql 5.7″‘], {maxBuffer: 1024 * 1024 * 128});
var mySession =
mysqlx.getSession({
host: ‘localhost’,
port: 33060,
dbUser: ‘root’,
dbPassword: ‘<your password>’
});
yt.stdout.on(‘data’, (data) => {
try {
dataObj = JSON.parse(data);
console.log(dataObj.fulltitle);
mySession.then(session => {
session.getSchema(“yt”).getCollection(“youtube”).add( dataObj )
.execute(function (row) {
}).catch(err => {
console.log(err);
})
.then( function (notices) { console.log(“Wrote to MySQL: ” + JSON.stringify(notices)) });
}).catch(function (err) {
console.log(err);
process.exit();
});
} catch (e) {
console.log(” — Can’t parse json” + e );
}
});
yt.stderr.on(‘data’, (data) => {
console.log(“Error receiving data”);
});
yt.on(‘close’, (code) => {
console.log(`child process exited with code ${code}`);
mySession.then(session => {session.close() } );
});In the above example, I execute the youtube-dl binary (you need to have it installed first) to search for “MySQL 5.7” videos. Instead of downloading the videos, I only grab the video’s metadata in JSON format  (“-j” flag). Because it is JSON, I can save it into MySQL document store. The table has the following structure:CREATE TABLE `youtube` (
`doc` json DEFAULT NULL,
`_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,’$._id’))) STORED NOT NULL,
UNIQUE KEY `_id` (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4Here is the execution example:$ node yt.js
What’s New in MySQL 5.7
Wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“3f312c3b-b2f3-55e8-0ee9-b706eddf”]}}
MySQL 5.7: MySQL JSON data type example
Wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“88223742-9875-59f1-f535-f1cfb936”]}}
MySQL Performance Tuning: Part 1. Configuration (Covers MySQL 5.7)
Wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“c377e051-37e6-8a63-bec7-1b81c6d6”]}}
Dave Stokes — MySQL 5.7 – New Features and Things That Will Break — php[world] 2014
Wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“96ae0dd8-9f7d-c08a-bbef-1a256b11”]}}
MySQL 5.7 & JSON: New Opportunities for Developers – Thomas Ulin – Forum PHP 2015
Wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“ccb5c53e-561c-2ed5-6deb-1b325739”]}}
Cara Instal MySQL 5.7.10 NoInstaller pada Windows Manual Part3
Wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“95efbd79-8d79-e7b6-a535-271640c8”]}}
MySQL 5.7 Install and Configuration on Ubuntu 14.04
Wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“b8cfe132-aca4-1eba-c2ae-69e48db8”]}}Now, here is what make this example interesting: as NodeJS + X Plugin = Asynchronous + Pipelining, the program execution will not stop if the table is locked. I’ve opened two sessions:

session 1: $ node yt.js > test_lock_table.log
session 2:
mysql> lock table youtube read; select sleep(10); unlock tables;
Query OK, 0 rows affected (0.00 sec)
+———–+
| sleep(10) |
+———–+
| 0 |
+———–+
1 row in set (10.01 sec)
Query OK, 0 rows affected (0.00 sec)

Results:…
Upgrade MySQL Server from 5.5 to 5.7
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“d4d62a8a-fbfa-05ab-2110-2fd5cf6d”]}}
OSC15 – Georgi Kodinov – Secure Deployment Changes Coming in MySQL 5.7
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“8ac1cdb9-1499-544c-da2a-5db1ccf5”]}}
MySQL 5.7: Create JSON string using mysql
FreeBSD 10.3 – Instalación de MySQL 5.7 desde Código Fuente – Source Code
Webinar replay: How To Upgrade to MySQL 5.7 – The Best Practices – part 1
How to install MySQL Server on Mac OS X Yosemite – ltamTube
Webinar replay: How To Upgrade to MySQL 5.7 – The Best Practices – part 4
COMO INSTALAR MYSQL VERSION 5.7.13
MySQL and JSON
MySQL 5.7: Merge JSON data using MySQL
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“a11ff369-6f23-11e9-187b-e3713e6e”]}}
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“06143a61-4add-79da-0e1d-c2b52cf6”]}}
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“1eb94ef4-db63-cb75-767e-e1555549”]}}
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“e25f15b5-8c19-9531-ed69-7b46807a”]}}
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“02b5a4c9-6a21-f263-90d5-cd761906”]}}
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“e0bef958-10af-b181-81cd-5debaaa0”]}}
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“f48fa635-fa63-7481-0668-addabbac”]}}
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“557fa5c5-3c8a-fe01-c17c-549c557e”]}}
MySQL 5.7 Install and Configuration on Ubuntu 14.04
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“456b11d8-ba03-0aec-8e06-9517c6e1”]}}
MySQL WorkBench 6.3 installation on Ubuntu 14.04
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“0b651987-9b23-b5e0-f8f7-49b8ba5c”]}}
Going through era of IoT with MySQL 5.7 – FOSSASIA 2016
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“e133746c-836c-a7e0-3893-292a7429”]}}
MySQL 5.7: MySQL JSON operator example
… => wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“4d13830d-7b30-5b31-d068-c7305e0a”]}}As we can see, the first two writes were immediate. Then I’ve locked the table, and no MySQL queries went through. At the same time the download process (which is the slowest part here) proceeded and was not blocked (we can see the titles above, which are not followed by lines “… => wrote to MySQL:”). When the table was unlocked, a pile of waiting queries succeeded.
This can be very helpful when running a “download” process, and the network is a bottleneck. In a traditional synchronous query execution, when we lock a table the application gets blocked (including the network communication). With NodeJS and X Plugin, the download part will proceed with MySQL acting as a queue.
Pipeline Durability
How “durable” this pipeline, you might ask. In other words, what will happen if I will kill the connection? To test it out, I have (once again) locked the table (but now before starting the nodejs), killed the connection and finally unlocked the table. Here are the results:Session 1:
———-
mysql> truncate table youtube_new;
Query OK, 0 rows affected (0.25 sec)
mysql> lock table youtube_new read;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from youtube_new;
+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (0.00 sec)
Session 2:
———-
(when table is locked)
$ node yt1.js
11 03 MyISAM
Switching to InnoDB from MyISAM
tablas InnoDB a MyISAM
MongoDB vs MyISAM (MariaDB/MySQL)
MySQL Tutorial 35 – Foreign Key Constraints for the InnoDB Storage Engine
phpmyadmin foreign keys myisam innodb
Convert or change database manual from Myisam to Innodb
… >100 other results omited …
^C
Session 1:
———-
mysql> select count(*) from youtube_new;
+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (0.00 sec)
Id: 4916
User: root
Host: localhost:33221
db: NULL
Command: Query
Time: 28
State: Waiting for table metadata lock
Info: PLUGIN: INSERT INTO `iot`.`youtube_new` (doc) VALUES (‘{“upload_date”:”20140319″,”protocol”:”
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from youtube_new;
+———-+
| count(*) |
+———-+
| 2 |
+———-+
1 row in set (0.00 sec)
mysql> select json_unquote(doc->’$.title’) from youtube_new;
+———————————+
| json_unquote(doc->’$.title’) |
+———————————+
| 11 03 MyISAM |
| Switching to InnoDB from MyISAM |
+———————————+
2 rows in set (0.00 sec)Please note: in the above, there isn’t a single acknowledgement from the MySQL server. When code receives a response from MySQL it prints “Wrote to MySQL: {“_state”:{“rows_affected”:1,”doc_ids”:[“…”]}}“. Also, note that when the connection was killed the MySQL process is still there, waiting on the table lock.
What is interesting here is is that only two rows have been inserted into the document store. Is there a “history length” here or some other buffer that we can increase? I’ve asked Jan Kneschke, one of the authors of the X Protocol, and the answers were:

Q: Is there any history length or any buffer and can we tune it?

A: There is no “history” or “buffer” at all, it is all at the connector level.

Q: Then why is 2 rows were finally inserted?

To answer this question I’ve collected tcpdump to port 33060 (X Protocol), see below

This is very important information! Keep in mind that the asynchronous pipeline has no durability settings: if the application fails and there are some pending writes, those writes can be lost (or could be written).
To fully understand how the protocol works, I’ve captured tcpdump (Jan Kneschke helped me to analyze it):tcpdump -i lo -s0 -w tests/node-js-pipelining.pcap “tcp port 33060″(see update below for the tcpdump visualization)
This is what is happening:

When I hit CTRL+C, nodejs closes the connection. As the table is still locked, MySQL can’t write to it and will not send the result of the insert back.
When the table is unlocked, it starts the first statement despite the fact that the connection has been closed. It then acknowledges the first insert and starts the second one.
However, at this point the script (client) has already closed the connection and the final packet (write done, here is the id) gets denied. The X Plugin then finds out that the client closed the connection and stops executing the pipeline.

Actually, this is very similar to how the original MySQL protocol worked. If we kill the script/application, it doesn’t automatically kill the MySQL connection (unless you hit CTRL+C in the MySQL client, sends the kill signal) and the connection waits for the table to get unlocked. When the table is unlocked, it inserts the first statement from a file.Session 1
———
mysql> select * from t_sql;
Empty set (0.00 sec)
mysql> lock table t_sql read;
Query OK, 0 rows affected (0.00 sec)
Session 2:
———-
$ mysql iot < t.sql
$ kill -9 …
[3] Killed mysql iot < t.sql
Session 1:
———-
mysql> show processlist;
+——+——+—————–+——+———+———+———————————+———————————————–+
| Id | User | Host | db | Command | Time | State | Info |
+——+——+—————–+——+———+———+———————————+———————————————–+
| 4913 | root | localhost | iot | Query | 41 | Waiting for table metadata lock | insert into t_sql values(‘{“test_field”:0}’) |
+——+——+—————–+——+———+———+———————————+———————————————–+
4 rows in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_sql;
+——————-+
| doc |
+——————-+
| {“test_field”: 0} |
+——————-+
1 row in set (0.00 sec)
Enforcing unique checks
If I restart my script, it finds the same videos again. We will probably need to enforce the consistency of our data. By default the plugin generates the unique key (_id) for the document, so it prevents inserting the duplicates.
Another way to enforce the unique checks is to create a unique key for youtube id. Here is the updated table structure:CREATE TABLE `youtube` (
`doc` json DEFAULT NULL,
`youtube_id` varchar(11) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,’$.id’))) STORED NOT NULL,
UNIQUE KEY `youtube_id` (`youtube_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4I’ve changed the default “_id” column to the YouTube’s unique ID. Now when I restart the script it shows:MySQL 5.7: Merge JSON data using MySQL
{ [Error: Document contains a field value that is not unique but required to be]
info:
{ severity: 0,
code: 5116,
msg: ‘Document contains a field value that is not unique but required to be’,
sql_state: ‘HY000′ } }
… => wrote to MySQL: undefined…as this document has already been loaded.
Conclusion
Although X Plugin pipelining does not necessarily significantly increase query response (it might save the roundtrip time) it can be helpful for some applications.We might not want to block the network communication (i.e., downloading or API calls) when the MySQL table is locked, for example. At the same time, unless you check/wait for the acknowledgement from the server, the data might or might not be written into MySQL.
Bonus: data analysis
Now we can see what we have downloaded. There are a number of interesting fields in the result:”is_live”: null,
“license”: “Standard YouTube License”,
“duration”: 2965,
“end_time”: null,
“playlist”: “”mysql 5.7″”,
“protocol”: “https”,
“uploader”: “YUI Library”,
“_filename”: “Douglas Crockford – The JSON Saga–C-JoyNuQJs.mp4”,
“age_limit”: 0,
“alt_title”: null,
“extractor”: “youtube”,
“format_id”: “18”,
“fulltitle”: “Douglas Crockford: The JSON Saga”,
“n_entries”: 571,
“subtitles”: {},
“thumbnail”: “https://i.ytimg.com/vi/-C-JoyNuQJs/hqdefault.jpg”,
“categories”: [“Science & Technology”],
“display_id”: “-C-JoyNuQJs”,
“like_count”: 251,
“player_url”: null,
“resolution”: “640×360”,
“start_time”: null,
“thumbnails”: [{
“id”: “0”,
“url”: “https://i.ytimg.com/vi/-C-JoyNuQJs/hqdefault.jpg”
}],
“view_count”: 36538,
“annotations”: null,
“description”: “Yahoo! JavaScript architect Douglas Crockford tells the story of how JSON was discovered and how it became a major standard for describing data.”,
“format_note”: “medium”,
“playlist_id”: “”mysql 5.7″”,
“upload_date”: “20110828”,
“uploader_id”: “yuilibrary”,
“webpage_url”: “https://www.youtube.com/watch?v=-C-JoyNuQJs”,
“uploader_url”: “http://www.youtube.com/user/yuilibrary”,
“dislike_count”: 5,
“extractor_key”: “Youtube”,
“average_rating”: 4.921875,
“playlist_index”: 223,
“playlist_title”: null,
“automatic_captions”: {},
“requested_subtitles”: null,
“webpage_url_basename”: “-C-JoyNuQJs”We can see the most popular videos. To do that I’ve added one more virtual field on view_count, and created an index on it:CREATE TABLE `youtube` (
`doc` json DEFAULT NULL,
`youtube_id` varchar(11) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,’$.id’))) STORED NOT NULL,
`view_count` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,’$.view_count’))) VIRTUAL,
UNIQUE KEY `youtube_id` (`youtube_id`),
KEY `view_count` (`view_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4We can run the queries like:mysql> select json_unquote(doc->’$.title’),
-> view_count,
-> json_unquote(doc->’$.dislike_count’) as dislikes
-> from youtube
-> order by view_count desc
-> limit 10;
+—————————————————————————————————-+————+———-+
| json_unquote(doc->’$.title’) | view_count | dislikes |
+—————————————————————————————————-+————+———-+
| Beginners MYSQL Database Tutorial 1 # Download , Install MYSQL and first SQL query | 664153 | 106 |
| MySQL Tutorial | 533983 | 108 |
| PHP and MYSQL – Connecting to a Database and Adding Data | 377006 | 50 |
| PHP MySQL Tutorial | 197984 | 41 |
| Installing MySQL (Windows 7) | 196712 | 28 |
| Understanding PHP, MySQL, HTML and CSS and their Roles in Web Development – CodersCult Webinar 001 | 195464 | 24 |
| jQuery Ajax Tutorial #1 – Using AJAX & API’s (jQuery Tutorial #7) | 179198 | 25 |
| How To Root Lenovo A6000 | 165221 | 40 |
| MySQL Tutorial 1 – What is MySQL | 165042 | 45 |
| How to Send Email in Blackboard Learn | 144948 | 28 |
+—————————————————————————————————-+————+———-+
10 rows in set (0.00 sec)Or if we want to find out the most popular resolutions:mysql> select count(*) as cnt,
-> sum(view_count) as sum_views,
-> json_unquote(doc->’$.resolution’) as resolution
-> from youtube
-> group by resolution
-> order by cnt desc, sum_views desc
-> limit 10;
+—–+———–+————+
| cnt | sum_views | resolution |
+—–+———–+————+
| 273 | 3121447 | 1280×720 |
| 80 | 1195865 | 640×360 |
| 18 | 33958 | 1278×720 |
| 15 | 18560 | 1152×720 |
| 11 | 14800 | 960×720 |
| 5 | 6725 | 1276×720 |
| 4 | 18562 | 1280×682 |
| 4 | 1581 | 1280×616 |
| 4 | 348 | 1280×612 |
| 3 | 2024 | 1200×720 |
+—–+———–+————+
10 rows in set (0.02 sec)Special thanks to Jan Kneschke and Morgan Tocker from Oracle for helping with the X Protocol internals.
Update: Jan Kneschke also generated the visualization for the tcpdump I’ve collected (when connection was killed):

Major post-GA features in the 5.7 release!

Interesting developments in the MySQL world – it can now be used as a document store and you can query the database using JavaScript instead of SQL (via the MySQL Shell). There is also a new X Plugin (see: mysql-5.7.12/rapid/) (which now makes use of protocol buffers (see: mysql-5.7.12/extra/protobuf/)). I will agree, this is more than just a maintenance release.
Do get started playing with MySQL Shell. If you’re using the yum repository, remember to ensure you have enabled the mysql-tools-preview in /etc/yum.repos.d/mysql-community.repo. And don’t forget to load the X Plugin in the server! I can’t wait for the rest of the blog posts in the series, and today just took a cursory look at all of this — kudos Team MySQL @ Oracle.
However, I’m concerned that the GA is getting what you would think of as more than just a maintenance release. We saw 5.7.11 get at rest data encryption for InnoDB, and now 5.7.12 getting even more changes. This is going to for example, ship in the next Ubuntu LTS, Xenial Xerus. Today it has 5.7.11, but presumably after release it will be upgrade to 5.7.12. I am not a huge fan of surprises in LTS releases (predictability over 5 years is a nice thing; this probably explains why I still have a 5.0.95 server running), but I guess this small band-aid is what we need to ensure this doesn’t happen going forward?
As for the other question I’ve seen via email from several folk so far: will MariaDB Server support this? I don’t see why not in the future, so why not file a Jira?

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569