Monday Feb 09, 2015

BACKUP SERVER command for MySQL with Query Rewrite Pre-Parse Plugin

Few times I heard from MySQL users, customers and Sales consultants about their wish to have SQL command, allowing to backup MySQL server. I asked why calling mysqbackup or mysqldump does not work for them and these people had reasons. One of them is security: this way you don't need to give shell access to MySQL server for the person who is supposed to do backup.

MySQL does not have such a command, except for MySQL Cluster. There are instead multiple programs which can make backup if run externally, such as mysqlbackup of mysqldump.

That time I wrote a UDF which runs any external command (https://github.com/svetasmirnova/run_external). So customers could run something similar to:

mysql> SELECT run_external(concat('$HOME/build/mysql-trunk/bin/mysqldump  --socket=', @@socket, ' --all-databases > /Users/sveta/src/BACKUPDIR/plugin_test/backup_test.sql'))\G
*************************** 1. row ***************************
run_external(concat('$HOME/build/mysql-trunk/bin/mysqldump  --socket=', @@socket, ' --all-databases > /Users/sveta/src/BACKUPDIR/plugin_test/backup_test.sql')): 1
1 row in set (0.54 sec)


Or

mysql> SELECT run_external(concat('mysqlbackup --backup-dir="$HOME/src/BACKUPDIR/plugin_test/meb" --socket=', @@socket, ' backup'))\G
*************************** 1. row ***************************

run_external(concat('mysqlbackup --backup-dir="$HOME/src/BACKUPDIR/plugin_test/meb" --socket=', @@socket, ' backup')): 1
1 row in set (2.14 sec)


But this is not so nice looking like BACKUP SERVER or BACKUP DATABASE statements. Unfortunately few years ago I could do nothing with it: MySQL does not have pluggable parsers and does not allow to modify built-in parser unless you want to maintain one more MySQL fork forever. Of course, users could use MySQL Proxy, but this is yet again one external tool they are trying to get rid off.

In year 2014 things changed. MySQL Optimizer Team introduced  Query Rewrite Pre-Parse and Post-Parse Plugins. So I decided to make a try.

Since I am crafting completely new SQL command I use Pre-Parse plugin.

Writing Pre-parse plugins is pretty easy. Just take as example rewrite_example plugin and its only source file rewrite_example.cc: change mysql_declare_plugin, so it is not rewrite_example, but your plugin, and re-define rewrite_lower function:

mysql_declare_plugin(mysqlbackup_plugin)
{
  MYSQL_REWRITE_PRE_PARSE_PLUGIN,
  &mysqlbackup_plugin_descriptor,
  "mysqlbackup_plugin",
  "Sveta Smirnova",
  "Plugin which provides SQL interface for MySQL Enterprise Backup and mysqldump",
  PLUGIN_LICENSE_GPL,
  mysqlbackup_plugin_init,
  NULL,
  0x0001,                                       /* version 0.0.1      */
  NULL,                                         /* status variables   */
  mysqlbackup_plugin_vars,                      /* system variables   */
  NULL,                                         /* config options     */
  0,                                            /* flqgs              */
}
mysql_declare_plugin_end;


In my case I called function perform_backup (because I don't rewrite query to lowercase, but perform backup), so I need to change mysqlbackup_plugin_descriptor as well:

static st_mysql_rewrite_pre_parse mysqlbackup_plugin_descriptor= {
  MYSQL_REWRITE_PRE_PARSE_INTERFACE_VERSION,    /* interface version          */
  perform_backup,                               /* performs backup            */
  free_resources,                               /* frees allocated resources  */
};

static int perform_backup(Mysql_rewrite_pre_parse_param *param)
{
  if (0 == strcasecmp("backup server", param->query))
  {
    if (!backup_dir_value)
    {
      const char* newq= "SELECT 'You must set global variable mysqlbackup_plugin_backup_dir before running this command!'";
      param->rewritten_query= new char[strlen(newq) + 1];
      param->rewritten_query_length=strlen(newq);
      strncpy(param->rewritten_query, newq, param->rewritten_query_length + 1);
    }
    else
    {
      ostringstream oss;
      switch(backup_tool_name) {
      case MYSQLBACKUP:
        oss << "SELECT '" << supported_tools[backup_tool_name] << " not supported yet.'";
        param->rewritten_query_length= strlen(oss.str().c_str());
        param->rewritten_query= new char[param->rewritten_query_length + 1];
        strncpy(param->rewritten_query, oss.str().c_str(), param->rewritten_query_length + 1);
        break;
      case MYSQLDUMP:
        cerr << "Processing mysqldump" << endl;
        if (backup_tool_basedir_value && 0 < strlen(backup_tool_basedir_value))
          oss << "SELECT run_external(concat('" << backup_tool_basedir_value << "/" << supported_tools[backup_tool_name] << " " << backup_tool_options_value << " --socket=', @@socket, ' --all-databases > " << backup_dir_value << "/backup_', date_format(now(), '%Y-%m-%e_%H:%i:%s'), '.sql'))";
        else
          oss << "SELECT run_external(concat('" << supported_tools[backup_tool_name] << " " << backup_tool_options_value << " --socket=', @@socket, ' --all-databases > " << backup_dir_value << "/backup_', date_format(now(), '%Y-%m-%e_%H:%i:%s'), '.sql'))";
cerr << oss.str() << endl;
        param->rewritten_query_length= strlen(oss.str().c_str());
        param->rewritten_query= new char[param->rewritten_query_length + 1];
        strncpy(param->rewritten_query, oss.str().c_str(), param->rewritten_query_length + 1);
        break;
        }
      }
      param->flags|= FLAG_REWRITE_PLUGIN_QUERY_REWRITTEN;
    }
  return 0;
}


Disclaimer: Although this tool claims to support both mysqldump and mysqlbackup commands, currently only mysqldump is supported, because mysqlbackup cannot backup 5.7 server yet. And query rewrite plugin exists in version 5.7 only.

I also added 4 variables, allowing to specify mysqlbackup_plugin_backup_tool (currently only mysqldump is supported), mysqlbackup_plugin_backup_dir: required variable, pointing to the place where backup should be stored; mysqlbackup_plugin_backup_tool_basedir - path to directory where backup tool located and mysqlbackup_plugin_backup_tool_options which is used to specify non-default options:

static MYSQL_SYSVAR_STR(backup_dir, backup_dir_value, PLUGIN_VAR_MEMALLOC, "Default directory where to store backup", NULL, NULL, NULL);
static MYSQL_SYSVAR_ENUM(backup_tool, backup_tool_name, PLUGIN_VAR_RQCMDARG, "Backup tool. Possible values: mysqldump|mysqlbackup", NULL, NULL, MYSQLDUMP, &supported_tools_typelib);
static MYSQL_SYSVAR_STR(backup_tool_basedir, backup_tool_basedir_value, PLUGIN_VAR_MEMALLOC, "Base dir for backup tool. Default: \"\"", NULL, NULL, "");
static MYSQL_SYSVAR_STR(backup_tool_options, backup_tool_options_value, PLUGIN_VAR_MEMALLOC, "Options for backup tool", NULL, NULL, "");

static struct st_mysql_sys_var *mysqlbackup_plugin_vars[] = {
    MYSQL_SYSVAR(backup_dir),
    MYSQL_SYSVAR(backup_tool),
    MYSQL_SYSVAR(backup_tool_basedir),
    MYSQL_SYSVAR(backup_tool_options),
    NULL
};


And now we can compile and run the tool: change directory to the top directory of MySQL source, run cmake, then make.

You still need to install plugin: INSTALL PLUGIN mysqlbackup_plugin SONAME 'mysqlbackup_plugin.so';

Then you only need to set backup_dir location, provide path to backup tool if it  is not in your PATH, add user, password and other options you want mysqldump to use either under [client]/[mysqldump] section of your configuration file or as mysqlbackup_plugin_backup_tool_options value, then run BACKUP SERVER command:

mysql> set global mysqlbackup_plugin_backup_dir='$HOME/src/BACKUPDIR/plugin_test';
Query OK, 0 rows affected (0.00 sec)

mysql> backup server;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| run_external(concat('mysqldump  --socket=', @@socket, ' --all-databases > $HOME/src/BACKUPDIR/plugin_test/backup_', date_format(now(), '%Y-%m-%e_%H:%i:%s'), '.sql')) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                     1 |     
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.60 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                          |       
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'backup server' rewritten to 'SELECT run_external(concat('mysqldump  --socket=', @@socket, ' --all-databases > $HOME/src/BACKUPDIR/plugin_test/backup_', date_format(now(), '%Y-%m-%e_%H:%i:%s'), '.sql'))' by plugin: mysqlbackup_plugin. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


Voi-la! You just created backup file in the directory specified without accessing any command line shell!

All code is available at GitHub.

UDF: https://github.com/svetasmirnova/run_external
Plugin: https://github.com/svetasmirnova/mysqlbackup_plugin

One more disclaimer: This code is not for production use, but for illustrating idea of how to use Query Rewrite plugins for crafting your own SQL. It has serious security issues! For example, although only user with SUPER privilege can modify global variables there is still potential security risk if an attacker has access to these variables and can modify in way which the program does not expect. There is no checks for, say, if value of mysqlbackup_plugin_backup_dir or mysqlbackup_tool_basedir_value are really directories and not paths to a tool which does not perform backup. Also value of option mysqlbackup_plugin_backup_tool_options can be crafted in risky way too. Be really careful if you decide to use it not for educational purpose only.

Thursday Jan 15, 2015

JSON UDF functions 0.3.3 have been released

New version of JSON UDF functions has been just released. This is maintenance release which only contains fix for 4 bugs:

74090/19821398 `INSTALL_JSONUDFS` ISSUES A WARNING DUE TO TRUNCATION OF `VERSION_COMPILE_OS`
74091/19692634 `JSON_DEEP_MERGE`: DEEP CRASH IN `MERGE_TREE`
74092/19692628 CRASH WITH SECOND-HAND NULL IN PARAMETERS
74097/19693198 `CREATE_NEW_VALUE`: STACK BUFFER OVERFLOW, %LLD CAN'T FIT IN CHAR[20]

Also 0.3 branch becomes stable branch and no new features will be pushed to this branch in future. Previous stable branch (0.2) removed from MySQL Labs website.

As usual you can download functions from http://labs.mysql.com/.

Wednesday Dec 31, 2014

MySQL Plugin for Oracle Enterprise Manager on VirtualBox: installation gotchas

At the last OOW MySQL Plugin for Oracle Enterprise Manager (OEM) was recognized as most popular MySQL product. If you don't have OEM installed, but want to test the plugin you can download OEM virtual box template. But, althought this is the easiest way to get started, you still need to make few additions. At least I had to do them when deployed such installation for MySQL Support Team.

Here they are. I prefer to use command line when possible.

0. Import virtual machine image and change network adapter to working one, then allow to connections via rdesktop:

sudo vboxmanage import VBox_EM12cR4.ova --vsys 0 --vmname  myhost-vbox04 \
--vsys 0 --unit 17 --disk  /data0/vbox/myhost-vbox04/myhost-vbox04.vmdk
sudo vboxmanage modifyvm myhost-vbox04 --bridgeadapter1 'eth0'
sudo vboxmanage modifyvm myhost-vbox04 --vrdeaddress myhost --vrde on --vrdeport 9004


1. The machine comes with 3G RAM which is not enough. Better to increase up to 8::

sudo vboxmanage modifyvm myhost-vbox04 --memory 8192

2. Also 40G of disk space will be used completely right after you start OEM. Therefore it is better to don't follow me and don't reach this point, but increase it right after installing the machine. I increased mine up to 100G.

The machines comes with vmdk disk format which does not support dynamic size change. Therefore you need to convert it to vdi format first, then resize.

sudo vboxmanage clonehd myhost-vbox04.vmdk myhost-vbox04.vdi --format vdi
sudo vboxmanage modifyhd myhost-vbox04.vdi --resize 102400

3. Then convert it back to vmdk and replace old disk with new one:

sudo vboxmanage clonehd myhost-vbox04.vdi myhost-vbox04-01.vmdk --format vmdk
sudo vboxmanage storageattach myhost-vbox04 --storagectl "SATA" --device 0  \

--port 0 --type hdd --medium /data0/vbox/myhost-vbox04/myhost-vbox04-01.vmdk

4. We are not done yet: 60G will stay invisible unless we change disk partitions. I did it with help of utility GParted, but you can use any, including system's.

5. Turn machine off and eject GParted CD:

sudo vboxmanage storageattach myhost-vbox04 --storagectl "IDE" --port 0 \

--device 0 --medium emptydrive

6. Boot the machine, login as root user (password is welcome1) and confirm that although OS ecognizes extended disk, but partition which holds OEM installation still full:

$ /sbin/fdisk -l

Disk /dev/sda: 107.3 GB, 107374182400 bytes
255 heads, 63 sectors/track, 13054 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2              14       13055   104753177+  8e  Linux LVM

Disk /dev/dm-0: 36.5 GB, 36574330880 bytes
255 heads, 63 sectors/track, 4446 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/dm-0 doesn't contain a valid partition table

Disk /dev/dm-1: 7314 MB, 7314866176 bytes
255 heads, 63 sectors/track, 889 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/dm-1 doesn't contain a valid partition table

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       33G   32G     0 100% /
/dev/sda1              99M   23M   71M  25% /boot
tmpfs                 4.0G     0  4.0G   0% /dev/shm


This happens, because OEM is on LVM partition and we need to fix it too:

$ /sbin/lvm
lvm> pvresize /dev/sda2
lvm> lvextend -L+55G /dev/mapper/VolGroup00-LogVol00
  Extending logical volume LogVol00 to 89.06 GB
  Logical volume LogVol00 successfully resized
lvm> quit
  Exiting.

$ /sbin/resize2fs /dev/mapper/VolGroup00-LogVol00
resize2fs 1.39 (29-May-2006)
Filesystem at /dev/mapper/VolGroup00-LogVol00 is mounted on /; on-line resizing required
Performing an on-line resize of /dev/mapper/VolGroup00-LogVol00 to 23347200 (4k) blocks.
The filesystem on /dev/mapper/VolGroup00-LogVol00 is now 23347200 blocks long.

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       87G   32G   51G  39% /
/dev/sda1              99M   23M   71M  25% /boot
tmpfs                 4.0G     0  4.0G   0% /dev/shm


Voi-la! Now we can logout from root account and start OEM.

7. But not yet if you need to connect to this virtual machine via ssh and cannot tune your network in such a way that emcc.example.com will be resolved to the virtual machine. In my case I had to connect to this machine via ssh and I cannot tune my network, only virtual machine's options. So I set network options as usual (GUI can be used) and changed ORACLE_HOSTNAME from emcc.example.com to appropriate value in .bashrc and ./start_agent.sh files.

Unfortunately this change does not instruct WebLogic to listen remote port even after I disabled machine's firewall, so I still have to use rdesktop to be able to see OEM GUI.

8. Finally we can start OEM as described in the README file, provided at edelivery.oracle.com for this virtual machine: do not use ./start_all.sh, but start each part separately:

./start_db.sh
/u01/OracleHomes/Middleware/oms/bin/emctl start oms
./start_agent.sh


And, last but not least, it is better to perform OEM setup wearing such a t-shirt:



I would not describe how to setup MySQL Plugin, because MySQL User Reference Manual contains detailed and clear instructions. I'd better show few screenshots how you can monitor both remote and local servers.

Just connected with a server on remote machine:



And on local one:



After test load with help of mysqlslap utility:



And this strange chart show how MySQL works on virtual machine:


Wednesday Dec 17, 2014

Memory summary tables in Performance Schema in MySQL 5.7

One of great innovation in MySQL 5.7 is memory summary tables в Performance Schema and corresponding views in sys schema

And as troubleshooting freak I have huge reason to greet this feature.

Before version 5.7 we had very limited abilities to diagnose memory issues in MySQL. We could use operating system tools, such as vmstat, top, free, but they only showed what MySQL server uses memory, but do not show how. In version 5.7 things changed.

Lets examine what can we study about memory usage by MySQL Server.

At first, this is total amount of memory, used by all internal MySQL structures:

mysql> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 458.44 MiB      |
+-----------------+


There is also similar statistics by hosts, users and threads, including those which are created for user connections:

mysql> select host, current_count_used ccu, current_allocated, current_avg_alloc, current_max_alloc, total_allocated from sys.memory_by_host_by_current_bytes WHERE host IS NOT NULL;
+------------+------+-------------------+-------------------+-------------------+-----------------+
| host       | ccu  | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------------+------+-------------------+-------------------+-------------------+-----------------+
| background | 2773 | 10.84 MiB         | 4.00 KiB          | 8.00 MiB          | 30.69 MiB       |
| localhost  | 1509 | 809.30 KiB        | 549 bytes         | 176.38 KiB        | 83.59 MiB       |
+------------+------+-------------------+-------------------+-------------------+-----------------+

mysql> select
host, current_count_used ccu, current_allocated, current_avg_alloc, current_max_alloc, total_allocated from sys.memory_by_user_by_current_bytes;
+------+------+-------------------+-------------------+-------------------+-----------------+
| user |  ccu | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------+------+-------------------+-------------------+-------------------+-----------------+
| root | 1401 | 1.09 MiB          | 815 bytes         | 334.97 KiB        | 42.73 MiB       |
| mark |  201 | 496.08 KiB        | 2.47 KiB          | 334.97 KiB        | 5.50 MiB        |
+------+------+-------------------+-------------------+-------------------+-----------------+

mysql> select thread_id tid, user, 
current_count_used ccu, current_allocated ca, current_avg_alloc caa, current_max_alloc cma, total_allocated from sys.memory_by_thread_by_current_bytes;
+-----+-------------------------+--------+-------------+-----------+-----------+-----------------+
| tid | user                    |    ccu |          ca |       caa |       cma | total_allocated |
+-----+-------------------------+--------+-------------+-----------+-----------+-----------------+
|   1 | sql/main                | 660327 | 2.53 GiB    | 4.01 KiB  | 2.10 GiB  | 2.69 GiB        |
| 150 | root@127.0.0.1          |    620 | 4.06 MiB    | 6.71 KiB  | 2.00 MiB  | 32.17 MiB       |
| 146 | sql/slave_sql           |     38 | 1.31 MiB    | 35.37 KiB | 1.00 MiB  | 1.44 MiB        |
| 145 | sql/slave_io            |    102 | 1.08 MiB    | 10.84 KiB | 1.00 MiB  | 2.79 MiB        |
...
|  92 | innodb/io_write_thread  |      0 | 0 bytes     | 0 bytes   | 0 bytes   | 384 bytes       |
| 124 | innodb/io_write_thread  |      0 | 0 bytes     | 0 bytes   | 0 bytes   | 384 bytes       |
|  28 | innodb/io_read_thread   |      0 | 0 bytes     | 0 bytes   | 0 bytes   | 2.25 KiB        |
|  60 | innodb/io_read_thread   |      0 | 0 bytes     | 0 bytes   | 0 bytes   | 384 bytes       |
| 139 | innodb/srv_purge_thread |    -24 | -328 bytes  | 14 bytes  | 272 bytes | 754.21 KiB      |
|  69 | innodb/io_write_thread  |    -14 | -1008 bytes | 72 bytes  | 0 bytes   | 34.28 KiB       |
|  68 | innodb/io_write_thread  |    -20 | -1440 bytes | 72 bytes  | 0 bytes   | 298.05 KiB      |
|  74 | innodb/io_write_thread  |    -23 | -1656 bytes | 72 bytes  | 0 bytes   | 103.55 KiB      |
|   4 | innodb/io_log_thread    |    -40 | -2880 bytes | 72 bytes  | 0 bytes   | 132.38 KiB      |
|  72 | innodb/io_write_thread  |   -106 | -7632 bytes | 72 bytes  | 0 bytes   | 1.10 MiB        |
+-----+-------------------------+--------+-------------+-----------+-----------+-----------------+
145 rows in set (2.65 sec)


This way you can find out, for example, which thread used most memory and kill it, if necessary.

But it is not always good idea to kill a thread even if it is certain that it leaks memory. For example, in case, described in bug #69848, it is not good idea to kill Slave IO and SQL threads. Although one of them, certainly, leaks memory.

You can see on this output how slave IO thread used more and more memory until I stopped my test. But after two cups of tea memory usage was still high and stayed at 1.04 GiB RAM even on idle server:

mysql> select thread_id tid, user, current_count_used ccu, current_allocated ca, current_avg_alloc caa, current_max_alloc cma, total_allocated from sys.memory_by_thread_by_current_bytes where thread_id in (145, 146);
+-----+---------------+--------+------------+-----------+------------+-----------------+
| tid | user          |    ccu |         ca |       caa |        cma | total_allocated |
+-----+---------------+--------+------------+-----------+------------+-----------------+
| 145 | sql/slave_io  | 170357 | 501.41 MiB | 3.01 KiB  | 496.90 MiB | 8.06 GiB        |
| 146 | sql/slave_sql |  10133 | 1.54 MiB   | 159 bytes | 1.00 MiB   | 10.38 GiB       |
+-----+---------------+--------+------------+-----------+------------+-----------------+
2 rows in set (2.76 sec)

...

mysql> select thread_id tid, user, current_count_used ccu, current_allocated ca, current_avg_alloc caa, current_max_alloc cma, total_allocated from sys.memory_by_thread_by_current_bytes where thread_id in (145, 146);
+-----+---------------+--------+------------+-----------+------------+-----------------+
| tid | user          |    ccu |         ca |       caa |        cma | total_allocated |
+-----+---------------+--------+------------+-----------+------------+-----------------+

| 145 | sql/slave_io  | 229012 | 641.95 MiB | 2.87 KiB  | 636.07 MiB | 10.32 GiB       |
| 146 | sql/slave_sql |  14033 | 1.61 MiB   | 120 bytes | 1.00 MiB   | 10.79 GiB       |
+-----+---------------+--------+------------+-----------+------------+-----------------+
2 rows in set (3.04 sec)

...

mysql> select thread_id tid, user, current_count_used ccu, current_allocated ca, current_avg_alloc caa, current_max_alloc cma, total_allocated from sys.memory_by_thread_by_current_bytes where thread_id in (145, 146);
+-----+---------------+--------+------------+-----------+------------+-----------------+
| tid | user          |    ccu |         ca |       caa |        cma | total_allocated |
+-----+---------------+--------+------------+-----------+------------+-----------------+

| 145 | sql/slave_io  | 412396 | 1.04 GiB   | 2.64 KiB  | 1.03 GiB   | 17.10 GiB       |
| 146 | sql/slave_sql |  26083 | 1.79 MiB   | 72 bytes  | 1.00 MiB   | 12.03 GiB       |
+-----+---------------+--------+------------+-----------+------------+-----------------+
2 rows in set (2.79 sec)


In this context we'd rather find out how it is used in order to decide if we can find workaround for it. In version 5.7 we have a tool which gives us this information! This is table performance_schema.memory_summary_by_thread_by_event_name and its column CURRENT_NUMBER_OF_BYTES_USED, which contains amount of currently used memory.

mysql> select THREAD_ID tid,EVENT_NAME,COUNT_ALLOC ca,SUM_NUMBER_OF_BYTES_ALLOC snba,SUM_NUMBER_OF_BYTES_FREE as bf,CURRENT_NUMBER_OF_BYTES_USED as cbu,HIGH_NUMBER_OF_BYTES_USED as hbu from performance_schema.memory_summary_by_thread_by_event_name where SUM_NUMBER_OF_BYTES_ALLOC > 0 and THREAD_ID in (145) and CURRENT_NUMBER_OF_BYTES_USED>0 order by CURRENT_NUMBER_OF_BYTES_USED DESC;
+-----+-------------------------------+----------+------------+-------------+------------+------------+
| tid | EVENT_NAME                    |       ca |       snba | bf          | cbu        | hbu        |
+-----+-------------------------------+----------+------------+-------------+------------+------------+
| 145 | memory/sql/thd::main_mem_root |     1039 | 1104280592 |           0 | 1104280592 | 1104280592 |
| 145 | memory/innodb/std             | 19378997 | 2848628832 |  2838767096 |    9861736 |    9862208 |
| 145 | memory/sql/NET::buff          |        3 |     557077 |       16391 |     540686 |     557077 |
| 145 | memory/innodb/trx0undo        |    11424 |    3929856 |     3720360 |     209496 |     209496 |
| 145 | memory/mysys/array_buffer     |       11 |      25952 |        8320 |      17632 |      25504 |
| 145 | memory/vio/read_buffer        |        1 |      16384 |           0 |      16384 |      16384 |
| 145 | memory/sql/THD::transaction...|        1 |       4112 |           0 |       4112 |       4112 |
| 145 | memory/client/MYSQL           |        4 |       1366 |           0 |       1366 |       1366 |
| 145 | memory/client/mysql_options   |        9 |      545 |           0 |        545 |        545 |
| 145 | memory/vio/vio                |        1 |        496 |           0 |        496 |        496 |
| 145 | memory/sql/Log_event          |       11 |       1510 |        1122 |        388 |        772 |
| 145 | memory/innodb/mem0mem         |  2405744 | 1220964072 | 11220963816 |        256 |      21928 |
| 145 | memory/sql/String::value      |        4 |        232 |          16 |        216 |        216 |
| 145 | memory/sql/THD::Session_tra...|        6 |        209 |           0 |        209 |        209 |
| 145 | memory/sql/Gtid_set::Interv...|        1 |        200 |           0 |        200 |        200 |
| 145 | memory/sql/Mutex_cond_array...|        1 |        168 |           0 |        168 |        168 |
| 145 | memory/mysys/lf_slist         |        5 |        160 |           0 |        160 |        160 |
| 145 | memory/sql/THD::debug_sync_...|        1 |        120 |           0 |        120 |        120 |
| 145 | memory/sql/Owned_gtids::sid...|        1 |         96 |           0 |         96 |         96 |
| 145 | memory/mysys/lf_node          |        2 |         80 |           0 |         80 |         80 |
| 145 | memory/innodb/ha_innodb       |        1 |         72 |           0 |         72 |         72 |
| 145 | memory/sql/Sid_map::Node      |        1 |         20 |           0 |         20 |         20 |
| 145 | memory/sql/plugin_ref         |        2 |         16 |           0 |         16 |         16 |
| 145 | memory/sql/THD::Session_sys...|        1 |          8 |           0 |          8 |          8 |
+-----+-------------------------------+----------+------------+-------------+------------+------------+
24 rows in set (0.17 sec)


I watched this output in quite a time when slave was active, so I can be certain what memory/sql/thd::main_mem_root is the reason:

| 145 | memory/sql/thd::main_mem_root |      707 |    512337872 |         0 |   512337872 |  512337872 |
...
| 145 | memory/sql/thd::main_mem_root |      802 |    658752752 |         0 |   658752752 |  658752752 |
...
| 145 | memory/sql/thd::main_mem_root |     1039 |   1104280592 |         0 |  1104280592 | 1104280592 |


Unfortunately, in this case, this knowledge is hardly useful for users (but not for MySQL developers!), because this member of class THD (./sql/sql_class.h):

is used for two purposes:
    - for conventional queries, to allocate structures stored in main_lex
    during parsing, and allocate runtime data (execution plan, etc.)
    during execution.
    - for prepared queries, only to allocate runtime data. The parsed
    tree itself is reused between executions and thus is stored elsewhere.


Although we can be sure what reason of memory leak is not SQL thread, so can save our time and don't do unnecessary optimizations. In other situations we can be more lucky and observe information which could help to find workaround.

Thursday Dec 11, 2014

Performance Schema memory tables and rightless users

When I talk about troubleshooting I like to repeat: "Don't grant database access to everybody!" This can sound a bit weird having one can give very limited read-only access.

But only if ignore the fact what even minimal privileges in MySQL allows to change session variables, including those which control server resources. My favorite example is "Kill MySQL server with join_buffer_size". But before version 5.7 I could only recommend this, but not demonstrate. Now, with help of memory summary tables in Performance Schema, I can show how unprivileged user can let your server to use great amount of swap.

At first lets create a user account with minimal privileges and login.

$../client/mysql -ulj -h127.0.0.1 -P13001
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.6-m16-debug-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(), current_user();
+--------------+----------------+
| user()       | current_user() |
+--------------+----------------+
| lj@127.0.0.1 | lj@%           |
+--------------+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+--------------------------------+
| Grants for lj@%                |
+--------------------------------+
| GRANT USAGE ON *.* TO 'lj'@'%' |
+--------------------------------+
1 row in set (0.00 sec)


As you see user 'lj'@'%' has single privilege: USAGE. It even does not have read access! Although it still can create temporary tables.

mysql> create temporary table t1(f1 int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t2 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t3 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t4 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t5 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t6 select * from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t7 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t8 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t9 select * from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t0 select * from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0


Nothing wrong with it: current connection uses only 235.34 KiB RAM.

mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in(152)\G
*************************** 1. row ***************************
         thread_id: 152
              user: lj@127.0.0.1
current_count_used: 303
 current_allocated: 235.34 KiB
 current_avg_alloc: 795 bytes
 current_max_alloc: 45.70 KiB
   total_allocated: 2.33 MiB
1 row in set (2.78 sec)


(Note here thread_id is not processlist_id, you should query P_S.threads table to find necessary thread_id)

And even if 'lj'@'%' will run quite complicated JOIN memory usage won't increase much if default options used.

mysql> select sleep(1) from t1,t2,t3,t4,t5,t6,t7,t8,t9,t0;
...


mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in(152)\G
*************************** 1. row ***************************
         thread_id: 152
              user: lj@127.0.0.1
current_count_used: 322
 current_allocated: 18.23 MiB
 current_avg_alloc: 57.97 KiB
 current_max_alloc: 18.00 MiB
   total_allocated: 20.38 MiB

1 row in set (2.62 sec)

However privilege USAGE allows 'lj'@'%' not only create temporary tables, but change session variables as it wants. For example, increase value of join_buffer_size up to maximum.

mysql> set join_buffer_size=4294967295;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select sleep(1) from t1,t2,t3,t4,t5,t6,t7,t8,t9,t0;
...


And with this value we will see completely different picture.

mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in(152)\G
*************************** 1. row ***************************
         thread_id: 152
              user: lj@127.0.0.1
current_count_used: 325
 current_allocated: 36.00 GiB
 current_avg_alloc: 113.43 MiB
 current_max_alloc: 36.00 GiB
   total_allocated: 37.95 GiB

1 row in set (2.70 sec)

36G is huge even in year 2014.

I am happy we finally have instrument which allows to both demonstrate and catch such issues.

Thursday Sep 25, 2014

JSON UDF functions version 0.3.2 have been released

Today new version of JSON UDF functions: 0.3.2 was released. This is development release which contains new functionality. You can download functions from the MySQL Labs website.

What is new?

New function `JSON_COUNT` added. This function returns number of children of the key path specified. If no key path specified, number of children of the root element is returned. Bug #70580/17584692 ADD FUNCTION `JSON_COUNT` TO COUNT SIZE OF JSON DOCUMENT

mysql> select json_count('{"MySQL Central": ["conference", 2014]}') as 'root count',
    ­> json_count('{"MySQL Central": ["conference", 2014]}', 'MySQL Central') as 'first element count'\G     
                                                   
************************ 1. row ************************
         root count: 1
first element count: 2
1 row in set (0.02 sec)

New function `JSON_VERSION`, returning version of JSON UDFs, added. Bug #72197/18499329 `JSON_VERSION` WOULD BE USEFUL

mysql> select json_version();
+­­­­­­­­­­­­­­­­­­­­­­­­­­­­----------------------------+
| json_version()             |
+­­­­­­­­­­­­­­­­­­­­­­­­­­­­----------------------------+
| MySQL JSON UDFs 0.3.2­labs  |
+­­­­­­­­­­­­­­­­­­­­­­­­­­­­----------------------------+
1 row in set (0.00 sec)

`JSON_SEARCH` now accepts wildcards. This also means if you want exact match of '\_' or '%' characters, you need to escape them. Bug #70571/17583417 ADD POSSIBILITY TO SEARCH IN LIKE-MANNER FOR `JSON_SEARCH`

mysql> set @doc='{"people": [{"name1": "Joe"}, {"name2": "John"}, {"name3": "Jon"}]}';
Query OK, 0 rows affected (0.00 sec)
mysql> select json_search(@doc, '"Jo_"', 1);
+-------------------------------+
| json_search(@doc, '"Jo_"', 1) |
+-------------------------------+
| name1:0:people::              |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@doc, '"Jo_"', 2);
+-------------------------------+
| json_search(@doc, '"Jo_"', 2) |
+-------------------------------+
| name3:0:people::              |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@doc, '"Jo_"', 3);
+-------------------------------+
| json_search(@doc, '"Jo_"', 3) |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@doc, '"Jo%"', 1);
+-------------------------------+
| json_search(@doc, '"Jo%"', 1) |
+-------------------------------+
| name1:0:people::              |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@doc, '"Jo%"', 2);
+-------------------------------+
| json_search(@doc, '"Jo%"', 2) |
+-------------------------------+
| name2:0:people::              |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@doc, '"Jo%"', 3);
+-------------------------------+
| json_search(@doc, '"Jo%"', 3) |
+-------------------------------+
| name3:0:people::              |
+-------------------------------+
1 row in set (0.00 sec)

Added logging abilities to JSON functions. Due to limitation of UDF interface it is not possible to return meaningful error message if error happened after initial arguments check. But having information why a function fails for one or another reason can be useful for troubleshooting. Therefore added new CMake option `VERBOSE_LEVEL` which can take values quiet, note, warning and error. Default mode is quiet: no additional error output. If `VERBOSE_LEVEL` set to either note, warning or error, all messages of the level and up will be written into server error log file. For users meaningful value is only error. Values note and warning are used for development only. Bug #71951/18391372 ADD LOGGING POSSIBILITIES TO JSON FUNCTIONS Note, you have to build the functions yourself if want to use functions.

To show example of this functionality lets create invalid document, then call function JSON_VALID and examine log file:

mysql> set @doc='{"people": [{"name1": "Joe"}, {"name2": "John"}, {"name3": "Jon"}}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_valid(@doc);
+------------------+
| json_valid(@doc) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

[sveta@delly mysql-test]$ cat var/log/mysqld.1.err 
...
2014-09-24 20:09:19 10845 [Note] Event Scheduler: Loaded 0 events
2014-09-24 20:09:19 10845 [Note] /home/sveta/src/mysql-5.6/sql/mysqld: ready for connections.
Version: '5.6.21-debug-log'  socket: '/home/sveta/src/mysql-5.6/mysql-test/var/tmp/mysqld.1.sock'  port: 13000  Source distribution
Parse error: document proceeded up to 65'th character }

Error messages are added not to all functions. If you miss error for some example, create a feature request and I will add.

New function `JSON_DEEP_MERGE` added. This function works as `JSON_SAFE_MERGE`, but updates values in case if following document contains duplicate keys Bug #71809/18315925 WHEN USING `JSON_MERGE` IT DUPLICATES KEY'S INSTEAD OF UPDATING THE NEW VALUE Good example of usage is at the bug report page.

Now JSON functions fully support negative keys. So, -1 means last element, -2 last before last and so on. Bug #71230/18315920 IMPLEMENT NEGATIVE INDEXES FOR JSON FUNCTIONS I wrote about supported format at "Last element for JSON array: what do you think?" Bug report contains example of usage too.

Output format for floating point values with exponent changed: Bug #73586/19447246 `JSON_SET` DIES WITH LARGE DECIMAL PARAMETER Earlier numbers, such as 1e2, were converted to decimal format, and not they are printed as 1.000000e+02 if used in functions, such as JSON_REPLACE:

mysql> set @json = '{"a":1,"b":2}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_replace(@json, 'a', 1e2);
+-------------------------------+
| json_replace(@json, 'a', 1e2) |
+-------------------------------+
| {"a":1.000000e+02,"b":2}      |
+-------------------------------+
1 row in set (0.00 sec)

There is also a change in binary packages which we provide. They are for Mac OS X 10.9 and 64-bit only.

Bugs fixed:

72196/18499334 `JSON_APPEND`: ERROR MESSAGE NOT ENTIRELY CORRECT
72962/18960413 `JSON_COUNT` CRASHES SERVER
73486/19383795 `JSON_SET`, `JSON_APPEND` DO NOT APPEND TO EMPTY DOCUMENT
73588/19447238 JSON FUNCTIONS CRASHES IN MEMMOVE CALLED FROM `MAKE_JEARGS`
73587/19447278 `JSON_SET`: UNINTIALIZED VALUE IN JPARSER::`PARSE_NUM` WITH MALFORMED NUMBER
73585/19447265 `JSON_TEST_PARSER` MISBEHAVES...

And yes, I am speaking at OOW about the functions. Attend session "Second Step to the NoSQL Side: MySQL JSON Functions" [CON2494] at Thursday, Oct 2, 12:00 PM

Thursday Mar 27, 2014

JSON UDF functions version 0.3.1 have been released.

Today new version of JSON UDF functions: 0.3.1 was released. This is development release which contains new functionality. You can download functions from the MySQL Labs website.

What is new?

Default value for compile option `WITH_PCRE` now is `bundled` independently from the
platform (Bug #71265/18081332 Library name mismatch for PCRE on Ubuntu)

New values, passed to functions `JSON_APPEND`, `JSON_REPLACE`, `JSON_SET` as numbers, empty strings or `NULL`s are converted to JSON objects. Objects, passed as strings are not converted: you are still responsible to put quotes around string values. Also these functions check if element which needs to be inserted is valid JSON value. (Bug #70394/17491708 MySQL JSON UDFs: json_replace can generate invalid target JSON)

README and ChangeLog files use Markdown format (Bug #71261/18023002 REFERENCES IN README MISSING)

`JSON_SEARCH` now accepts third argument: occurrence number, allowing to specify number of occurrence needed. (Bug #70607/17597479 ADD POSSIBILITY TO SEARCH ALL OCCURRENCES WITH JSON_SEARCH)

Added function `JSON_DEPTH` which returns depth of the JSON document.

Bugs fixed:


71510/18154696 `JSON_EXTRACT` returns wrong value if extracts from object which holds two arrays

71530/18160842 `JSON_EXTRACT` RETURNS WRONG RESULT IN VERSION 0.3

71949/18355404 `JSON_VALID` DOES NOT REJECT INVALID DOCUMENTS

70606/17596818 Implement SAFE versions for all JSON functions

And, of course, this release contains all changes made in version 0.2.2

Wednesday Mar 19, 2014

Why is important to be active at bugs.mysql.com?

When I presented JSON UDF functions at MySQL Connect last year attendees asked me to implement few features. I quickly wrote their wishes in my notepad. I also created feature requests at bugs.mysql.com  when I was home.

During following months I fixed bugs in the functions and implemented feature requests, including those which I got from MySQL Connect. I started from most important and affecting users, such as wrong results bugs of features for which I got more than one request.

But today all such requests finished and I am working on bug #70580  This feature request says: "Add function JSON_COUNT which will go through the JSON document and count number of childs of the root element." Well, I wrote it myself, but I don't exactly remember if the user wanted to know depth of the JSON document, e.g. (Scenario 1) if for document {"a": {"b": "c", "d": "e", "f": "g"}} result should be 3 and for document {"a": "b", "c": "d", "e": "f", "g": "h"} result should be 2.

Or he wanted to know how many element has the whole document? (Scenario 2) In this case for document {"a": {"b": "c", "d": "e", "f": "g"}} result should be 5 and for document {"a": "b", "c": "d", "e": "f", "g": "h"} result should be 5.

Or he wanted exactly what I wrote in the bug report: how many children root element has? (Scenario 3) In this case for document {"a": {"b": "c", "d": "e", "f": "g"}} result should be 1 and for document {"a": "b", "c": "d", "e": "f", "g": "h"} result should be 4.

If he'd file bug report himself, I'd ask. But he did not and left no comment for this bug report.

What will I do next? I will implement function JSON_DEPTH which will work exactly as described in the Scenario 1 and which, if I remember correctly, is what the user originally wanted and leave this feature request open for comments if somebody needs behavior as described in Scenario 2 or Scenario 3.

Saturday Mar 15, 2014

Why MySQL engineers open bugs in public bug database?

Oracle engineers suppose to open new bugs in its internal bug database until they think opening them in public one makes sense.

Example of such a case is Bug #68415 "resolveip and mysqlaccess still use gethostbyaddr"

Reason for making it public is that it describes behavior, which was introduced into the tools resolveip and mysqlaccess without intention and they now still can work with NetBIOS name, different or not existent in DNS while MySQL server cannot.

Interesting fact that such NetBIOS names were not ever officially supported, but they worked until deprecated function gethostbyaddr was replaced with recommended to use getnameinfo. And since NetBIOS never was claimed to be supported there is nothing to document in the officail user manual! While this hardly affects many people, because new Windows API does not support NetBIOS anyway, having such a bug public can help those who use antique versions of Windows and NetBIOS names.

Saturday Mar 01, 2014

Translated slides from my seminar about using Performance Schema for MySQL troubleshooting at Devconf 2013

Few weeks ago I asked my friends who speak both English and Russian if it is worth translating slides about Performance Schema which I prepared for a seminar at Devconf 2013. They said it is. Today I finished translation and uploaded slides to SlideShare.

Strictly speaking simple translation of slides is not enough, because they were created for the seminar where I was going to explain what they mean. I think I need to repeat same seminar, this time in English language. But if you have rough imagination about what Performance Schema is and need hints for practical use you will find such suggestions in the slides. You will also find ready-to-use queries which you can use to troubleshoot most frequent performance issues.

Enjoy!

Wednesday Jan 29, 2014

JSON UDF functions version 0.2.2 have been released.

New version of JSON UDF functions 0.2.2 have been just released. It is last maintenance release of 0.2 series. However it contains two new features:

JSON_VALID now accepts array as a root element of the JSON document (Bug#70567/17583282)

JSON functions can now be installed and uninstalled using a script (Bug#71263/18022788 Easy installation sql script).

This feature is Community contribution. Thank you, Daniel van Eeden!

This release also contains following bug fixes:

71050/17882710 json_extract returning same column twice if key names are not fully distinct. (Fixed for JSON_EXTRACT, JSON_CONTAINS_KEY, JSON_APPEND, JSON_REMOVE, JSON_REPLACE, JSON_SET)

71180/17975709 JSON_CONTAINS_KEY founds not existent key in the array element

71267/18020724 buffer underrun in JParser::parse_pair

71264/18022761 No uninstall information in the README

71261/18023002
References in README missing

Thursday Jan 23, 2014

MySQL and PostgreSQL JSON functions: do they differ much?

As author of MySQL JSON functions I am also interested in how development goes in another parties. JSON functions and operators in PostgreSQL, indeed, have great features. Some of them, such as operators, I can not do using UDF functions only. But lets see how these functions are interchangeable.

Note: all PostgreSQL examples were taken from PostgreSQL documentation.

First topic is syntax sugar.

 PostgreSQL
MySQL

Operator ->

postgres=# select '[1,2,3]'::json->2;
 ?column?
----------
 3
(1 row)

postgres=# select '{"a":1,"b":2}'::json->'b';
 ?column?
----------
 2
(1 row)


JSON_EXTRACT

mysql> select json_extract('[1,2,3]', 2);
+----------------------------+
| json_extract('[1,2,3]', 2) |
+----------------------------+
| 3                          |
+----------------------------+
1 row in set (0.00 sec)

mysql> select json_extract('{"a":1,"b":2}', 'b');
+------------------------------------+
| json_extract('{"a":1,"b":2}', 'b') |
+------------------------------------+
| 2                                  |
+------------------------------------+
1 row in set (0.00 sec)

Operator ->>

postgres=# select '[1,2,3]'::json->>2;
 ?column?
----------
 3
(1 row)

postgres=# select '{"a":1,"b":2}'::json->>'b';
 ?column?
----------
 2
(1 row)


JSON_EXTRACT

mysql> select json_extract('[1,2,3]', 2);
+----------------------------+
| json_extract('[1,2,3]', 2) |
+----------------------------+
| 3                          |
+----------------------------+
1 row in set (0.00 sec)

mysql> select json_extract('{"a":1,"b":2}', 'b');
+------------------------------------+
| json_extract('{"a":1,"b":2}', 'b') |
+------------------------------------+
| 2                                  |
+------------------------------------+
1 row in set (0.00 sec)

Operator #>

postgres=# select '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}';
 ?column?
----------
 3
(1 row)


JSON_EXTRACT

mysql> select json_extract('{"a":[1,2,3],"b":[4,5,6]}', 'a', 2);
+---------------------------------------------------+
| json_extract('{"a":[1,2,3],"b":[4,5,6]}', 'a', 2) |
+---------------------------------------------------+
| 3                                                 |
+---------------------------------------------------+
1 row in set (0.00 sec)

Operator #>>

postgres=# select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
 ?column?
----------
 3
(1 row)

JSON_EXTRACT

mysql> select json_extract('{"a":[1,2,3],"b":[4,5,6]}', 'a', 2);
+---------------------------------------------------+
| json_extract('{"a":[1,2,3],"b":[4,5,6]}', 'a', 2) |
+---------------------------------------------------+
| 3                                                 |
+---------------------------------------------------+
1 row in set (0.00 sec)


Then PostgreSQL JSON functions

 PostgreSQL
 MySQL
array_to_json(anyarray [, pretty_bool])  Not supported
row_to_json(record [, pretty_bool])  Not supported
to_json(anyelement)  Not supported
json_array_length(json)  Not supported, planned as bug #70580
json_each(json)  Not supported, cannot be implemented using UDFs only
json_each_text(from_json json)  Not supported, cannot be implemented using UDFs only

json_extract_path(from_json json, VARIADIC path_elems text[])


postgres=# select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4');
  json_extract_path   
----------------------
 {"f5":99,"f6":"foo"}
(1 row)

JSON_EXTRACT

mysql> select json_extract('{"f2":{"f3":1},\c

"f4":{"f5":99,"f6":"foo"}}','f4');
+---------------------------------+
| json_extract('{"f2":{"f3":1},

"f4":{"f5":99,"f6":"foo"}}','f4') |
+---------------------------------+
| {"f5":99,"f6":"foo"}            |
+---------------------------------+
1 row in set (0.00 sec)

json_extract_path_text(from_json json, VARIADIC path_elems text[])


postgres=# select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6');
 json_extract_path_text
------------------------
 foo
(1 row)

 JSON_EXTRACT

select json_extract('{"f2":{"f3":1},\c

"f4":{"f5":99,"f6":"foo"}}','f4', 'f6');
+---------------------------------------+
| json_extract('{"f2":{"f3":1},

"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') |
+---------------------------------------+
| foo                                   |
+---------------------------------------+
1 row in set (0.01 sec)

json_object_keys(json)  Not supported, cannot be implemented using UDFs only
json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]  Not supported, cannot be implemented using UDFs only
json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]  Not supported, cannot be implemented using UDFs only
json_array_elements(json)  Not supported, cannot be implemented using UDFs only

And, finally, MySQL functions

 MySQL
 PostgreSQL
json_append  Not supported, but user can convert JSON value into various formats, then create new JSON document from it
json_contains_key  Not supported, however has function json_object_keys(json)
json_extract  Supported in numerous ways, see above
json_merge   Not supported, but user can convert JSON value into various formats, then create new JSON document from it
json_remove   Not supported, but user can convert JSON value into various formats, then create new JSON document from it
 json_replace
  Not supported, but user can convert JSON value into various formats, then create new JSON document from it
json_search  Not supported
json_set   Not supported, but user can convert JSON value into various formats, then create new JSON document from it
json_test_parser   Not supported
json_valid  Not supported, however it has JSON type and checks every element for validity


As a conclusion I can say that MySQL and PostgreSQL implementations mostly intersect in extracting elements while solve element validation and manipulation tasks in different ways.

Tuesday Dec 31, 2013

JSON UDF functions version 0.3.0 have been released.

Today new version of JSON UDF functions: 0.3.0 was released. This is major release which contains new functionality. You can download functions from the MySQL Labs website.

What was added?

Functions JSON_EXTRACT, JSON_CONTAINS_KEY, JSON_APPEND, JSON_REMOVE, JSON_REPLACE, JSON_SET now accept both string and integer arguments as array indexes. (Bug #70393/17491709)

Now syntax JSON_EXTRACT(doc, 'key', 1); is accepted.

New function JSON_SAFE_MERGE checks JSON documents for validity before merging them. In addition JSON_MERGE now merges valid documents, having array as root element. (Bug #70575/17583568)

JSON_VALID now accepts array as a root element of the JSON document (Bug #70567/17583282)

Also this release contains following bug fixes:

71050/17882710 json_extract returning same column twice if key names are not fully distinct. (Fixed for JSON_EXTRACT, JSON_CONTAINS_KEY, JSON_APPEND, JSON_REMOVE, JSON_REPLACE, JSON_SET)

71180/17975709 JSON_CONTAINS_KEY founds not existent key in the array element

70579/17584678 Add ability to append last element to array in JSON_APPEND

And, of course, this release contains all changes which version 0.2.1 has

Tuesday Dec 17, 2013

JSON UDFs: is it hard to type keys?

Currently, if you want to search a key using JSON UDFs you have to specify each its part as a separate argument to the function: JSON_CONTAINS_KEY(doc, 'root', 'child', 'child of child', 'child of child of child', etc.....). This way of working with parameters is easy for developer, less error-prone, but can be not very beautiful.

I was suggested by some of users to change it to '/parent/child/child-of-child/...' or to 'parent:child:child-of-child:...' There are, probably, can be other suggestions. What do you like the best? How do you feel about current style with separate argument for each key element? Should we change or extend this syntax?

Wednesday Dec 11, 2013

New cmake options for JSON UDFs.

Two weeks ago I announced new maintenance release of JSON UDFs: 0.2.1. It not only contains bug fixes, how you can expect from a maintenance release, but also contains improvements in build and test scripts.

First improvement is the easier way to build JSON UDFs on Windows. In the first version building on Windows was a pane: you had to build PCRE library, copy four files to directories where Visual Studio can find them and only then build JSON functions themselves. Now you can build this way too, but only if you really wish.

By default cmake will create appropriate makefiles for bundled PCRE sources and for JSON functions. Only command you need to prepare sources is:

"C:\Program Files (x86)\CMake 2.8\bin\cmake.exe" -G "Visual Studio 11 Win64" . -DMYSQL_DIR="/path/to/mysql/installation"   

And then you can build functions:

devenv my_json_udf.sln /build Release

This is done by introducing new cmake option WITH_PCRE which can take two parameters: system and bundled. For Windows default is bundled which means bundled version of PCRE will be used. Default parameter for UNIX is system, because you usually have regular expression library installed and no need to build it statically into JSON functions. But you can overwrite defaults on both systems, specifying option -DWITH_PCRE=system|bundled for the cmake command.

Another improvement is test automation. In the first available version you had to use strange file Makefile.unix and weird instructions to run tests. In 0.2.1 this file is not needed anymore: simply run `make test` on UNIX or open solution my_json_udf.sln in Visual Studio and execute target ALL_TESTS. Here I have a question for mature Windows users: do you know how to run this on command line? But regardless to command line or not this also means you don't need Cygwin to run tests on Windows. Perl, however, is still required and must be in the path, because it is required by the MySQL Test Framework.

Of course, MySQL server directory should contain binaries. In other words: if you use source dir when build JSON functions you need to compile MySQL server to be able to run tests.

On Linux, as well on other OSes which can run Valgrind, you can run tests with valgrind too. Instructions are a bit weird: you need to specify option -DVALGRIND=1 when build functions to run tests under valgrind. Command, which you need to use to run tests under valgrind is still the same: make test

And, finally, last improvement is possibility to create a source package by running command make build_source_dist. This option can look not very useful from the user point of view, but it really helps producing packages for MySQL Labs out of internal repository with sources. If you run make build_source_dist file, named mysql-json-udfs-0.2.1-labs-json-udfs-src.tar.gz, which contains all source code, will be produced. On Windows, again, you need to open my_json_udf.sln solution and build this target.

About

Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group

Search

Categories
Archives
« March 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today