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.


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

# 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`)

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:

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

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

The basic idea came after reading ->

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
# The count of undo files
shahriyar.rzaev@qaserver-06:~/sandboxes/msb_8_0_1/data$ ls | grep undo | wc -l

After new restart:

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

So how to reproduce?

1. Started server with following settings


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:


And it is started.

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


From now after each restart it will create new undo files.
See below the full test result:

Experimental MySQL UDF code partially written in Python

Hi dear all,
I would like to share our experimental tests about, writing UDF with Python.
So the basic idea was writing a UDF for factorial calculation. The interesting part was to move factorial calculation logic to Python code and then calling this Python file inside UDF code.

Here is the repo link -> UDFs-written-in-Python

The factorial calculation is here ->
UDF code is here ->

Basic idea with UDF code quite simple as it has 3 parts:
1. initialization part -> factorial_init() -> all necessary checks reside here
2. main logic -> factorial() -> calling Python script, reading data back and showing the result.
3. deinitialization -> factorial_deinit()

Compiled as:

gcc $(/home/sh/REPOS/MYSQL_DEV_ACTIONS/mysql-5.7/mysql-server/5.7.16/bin/mysql_config --cflags) -fPIC -shared -o -std=c++11

Copied to plugin_dir:

cp /home/sh/REPOS/MYSQL_DEV_ACTIONS/mysql-5.7/mysql-server/5.7.16/lib/plugin/
cp /home/sh/REPOS/MYSQL_DEV_ACTIONS/mysql-5.7/mysql-server/5.7.16/lib/plugin/

Activated as:
mysql > create function factorial returns integer soname '';


root@test1:~# python --version
Python 2.7.12
mysql> select factorial(5);
| factorial(5) |
| 120 |
1 row in set (0.02 sec)

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:


Run it.

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


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/
#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/
#8  0x000000000063ad8e in init_server_components () at /root/mysql-5.6.24/sql/
#9  0x000000000063b6e7 in mysqld_main (argc=19, argv=0x186de68) at /root/mysql-5.6.24/sql/
#10 0x000000000062fc74 in main (argc=10, argv=0x7fffffffe3d8) at /root/mysql-5.6.24/sql/

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_PRINT("my", ("Format: %s", format));
  (void) my_vsnprintf (wbuff, sizeof(wbuff), format, args);

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/ 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;

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;
   if (table_def_init() | hostname_cache_init(host_cache_size))
@@ -5486,7 +5488,7 @@ int mysqld_main(int argc, char **argv)

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, ...)
   (void) my_vsnprintf (wbuff, sizeof(wbuff), format, args);
-  (*sql_print_warning_hook)(wbuff);
+  if(sql_print_warning_hook)
+  {
+    (*sql_print_warning_hook)(wbuff);
+  }
+  else
+  {
+    fprintf(stderr, "%s\n", wbuff);
+  }

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

The origin of “Assertion Failed” errors in MySQL

Recently, we have discussed BUGs with our local community members. Where the half off people said that, they have seen “Assertion Failure” errors in error log. And the question was what is this at all?

In general “assert” is a code portion put by developer to handle certain things.

Well, i am not a core MySQL developer and my C/C++ knowledge is limited to simple calculator 🙂 But i will try to explain where all these “Assertion Failure” errors come up.
The exact reason of failing Assertion/crash should be known by core developers. I know how to crash, they know how to fix. You help developers to reproduce a crash filling a bug report, by uploading test cases or maybe the core dump, backtrace etc.
Say, for eg, if you try to create a view after getting “full disk” error you will crash MySQL. See related BUG report -> #76827

While testing full disk conditions, if we try to import huge dump, we will see:

2015-04-24 09:07:52 7faf8bcab700 InnoDB: Error: Write to file ./sales/sales.ibd failed at offset 247463936.
InnoDB: 1048576 bytes should have been written, only 299008 were written.
InnoDB: Operating system error number 11.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: Some operating system error numbers are described at
2015-04-24 09:07:52 28213 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full
2015-04-24 09:08:13 28213 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full
2015-04-24 09:08:27 28213 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full

After detecting Full Disk error, if you try to create view:

mysql> create view f as select * from sales;
ERROR 2013 (HY000): Lost connection to MySQL server during query

From error log:

mysqld: /root/mysql-5.6.24/mysys/mf_iocache.c:1799: my_b_flush_io_cache: Assertion `info->end_of_file == inline_mysql_file_tell("/root/mysql-5.6.24/mysys/mf_iocache.c", 1799, info->file, (myf) (0))' failed.
13:13:48 UTC - mysqld got signal 6 ;

So that’s all, from error log it obvious that there is a file named: /root/mysql-5.6.24/mysys/mf_iocache.c and on line 1799 there is an assert inside my_b_flush_io_cache function.
If you go ahead and open up this line you will see something like:

       DBUG_ASSERT(info->end_of_file == mysql_file_tell(info->file, MYF(0)));

For now let’s pause here and introduce new things, such as what is “Optimized”, “Debug” and “Valgrind” builds of MySQL. Please watch this wonderfull video recorded by QA expert Roel Van de Paar after you will learn about newly intoduced topics. -> MySQL QA Episode 11

In general the “Optimized” MySQL is a GA version of MySQL released by vendors. It is production ready and it is working as fast as possible. So there is no “unnecessary” codes inside this build.
The “Debug” build is for debugging purpose and there is a DEBUG instrumentation code portions inside source code.
in “Valgrind” build, there are “Debug” + “Valgrind” instrumentation codes inside source code.

So above we saw DBUG_ASSERT(info->end_of_file == mysql_file_tell(info->file, MYF(0))); -> It means that this “assert” code will be shown only with “Debug” build. You will not see this code in “Optimized” MySQL source code.

Ok, let’s go on. As we have mentioned “assert” code is written by developer to handle several conditions. It might be for eg, developer decides that, if variable named num will be equal to NULL something weird is happened, terminate the program at that point.

Let’s write a simple code with our very own “assert”. Here is our assert_test.c file:

#include <stdio.h>      /* printf */
#include <assert.h>     /* assert */

void check_number(int myInt) {
  assert (myInt!=NULL);
  printf ("%dn",myInt);

int main ()
  int a=10;
  int b = NULL;

  check_number (a);
  check_number (b);

  return 0;

We have put an “assert” ensuring that myInt will never be “NULL”.
Compile source code:

    gcc assert_test.c -o assert_test

And run:

sh@shrzayev:~$ ./assert_test
assert_test: assert_test.c:5: check_number: Assertion `myInt!=((void *)0)' failed.
Aborted (core dumped)

So as you see, the same error comes up with our little code.
We have assert_test.c file and inside check_number function at line 5 there is an “assert” code which is failed.

I Hope have explained this point. Thank you for reading.

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
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
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/ virtual void binlog_cache_data::reset(): Assertion `is_binlog_empty()' failed.

Getting: mysqld: /root/mysql-5.7.7-rc/sql/ 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/", 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/", 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/
#5  0x000000000181f091 in binlog_trx_cache_data::reset (this=0x7fffb400da68) at /root/mysql-5.7.7-rc/sql/
#6  0x000000000180776a in binlog_trx_cache_data::truncate (this=0x7fffb400da68, thd=0x7fffb4000c80, all=false) at /root/mysql-5.7.7-rc/sql/
#7  0x00000000018089fa in MYSQL_BIN_LOG::rollback (this=0x2bd78c0 <mysql_bin_log>, thd=0x7fffb4000c80, all=false) at /root/mysql-5.7.7-rc/sql/
#8  0x0000000000f84dfa in ha_rollback_trans (thd=0x7fffb4000c80, all=false) at /root/mysql-5.7.7-rc/sql/
#9  0x0000000001682449 in trans_rollback_stmt (thd=0x7fffb4000c80) at /root/mysql-5.7.7-rc/sql/
#10 0x000000000158e778 in mysql_execute_command (thd=0x7fffb4000c80) at /root/mysql-5.7.7-rc/sql/
#11 0x000000000158ff62 in mysql_parse (thd=0x7fffb4000c80, parser_state=0x7fffde31e5a0) at /root/mysql-5.7.7-rc/sql/
#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/
#13 0x0000000001584b21 in do_command (thd=0x7fffb4000c80) at /root/mysql-5.7.7-rc/sql/
#14 0x00000000016adcff in handle_connection (arg=0x3cbd6b0) at /root/mysql-5.7.7-rc/sql/conn_handler/
#15 0x0000000001cd9905 in pfs_spawn_thread (arg=0x3bfb1f0) at /root/mysql-5.7.7-rc/storage/perfschema/
#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

Announced – MySQL 5.7 Community Contributor Award Program 2015!

I am glad to share this post with community. It is really great story for a young guy to be among experts!

Today Lenka Kasparova announced results of: MySQL 5.7 Community Contributor Award Program 2015!

Awarded as:

Shahriyar Rzayev, the Azerbaijan MySQL User Group leader for reproducing bugs in MySQL 5.7

See my MySQL BUG database activity -> Reporter: Shahriyar Rzayev

Also there is a gift from MySQL Community team: