X
FEATURED POST

Migration

I am leaving Oracle. My last day will be tomorrow. Since Oracle blogsare for Oracle employees only I would not be able to post here. I will write new posts to...

Recent Posts

MySQL

JSON UDF functions version 0.4.0 have been released

New version of JSON UDF functions has been just released. This version introduces two new features. Escape characters in results of JSON_SEARCH function. In previous version if you had document @doc = '{"foo:bar": "baz"}' or '{"foo": {"bar": "baz"}}' return value of JSON_SEARCH(@doc, '"baz"'); was 'foo:bar::' for both. There was no way to distinguish two search paths. Now for the first document JSON_SEARCH returns 'foo\:bar::' Second feature is much bigger. Now JSON functions accessing elements by a key (JSON_CONTAINS_KEY, JSON_EXTRACT, JSON_REPLACE, JSON_REMOVE, JSON_APPEND, JSON_SET, JSON_COUNT) can use alternate keypath syntax: $.key1.key2[arr_index] where $ is root element, .key is key name, [arr_index] is array index. JSON_SEARCH can also return path in this format with escaped $, . and [] symbols. Let's see how it works. mysql> set @doc='{"JSON UDFs": {"version": "0.4.0", "functions": ["json_extract", "json_contains_key", "json_search"]}}';Query OK, 0 rows affected (0.00 sec)mysql> select JSON_EXTRACT(@doc, '$.JSON UDFs.version');+-------------------------------------------+| JSON_EXTRACT(@doc, '$.JSON UDFs.version') |+-------------------------------------------+| 0.4.0                                     |+-------------------------------------------+1 row in set (0.00 sec) Accessing array element: mysql> select JSON_EXTRACT(@doc, '$.JSON UDFs.functions[1]');+------------------------------------------------+| JSON_EXTRACT(@doc, '$.JSON UDFs.functions[1]') |+------------------------------------------------+| json_contains_key                              |+------------------------------------------------+1 row in set (0.00 sec) Searching: mysql> select JSON_SEARCH(@doc, '"json_search"');+------------------------------------+| JSON_SEARCH(@doc, '"json_search"') |+------------------------------------+| $.JSON UDFs.functions[2]           |+------------------------------------+1 row in set (0.01 sec) Now you can use result of JSON_SEARCH as an argument for other functions: mysql> select JSON_EXTRACT(@doc, JSON_SEARCH(@doc, '"json%"'));+--------------------------------------------------+| JSON_EXTRACT(@doc, JSON_SEARCH(@doc, '"json%"')) |+--------------------------------------------------+| json_extract                                     |+--------------------------------------------------+1 row in set (0.00 sec) mysql> select JSON_EXTRACT(@doc, JSON_SEARCH(@doc, '"json%"', 2));+-----------------------------------------------------+| JSON_EXTRACT(@doc, JSON_SEARCH(@doc, '"json%"', 2)) |+-----------------------------------------------------+| json_contains_key                                   |+-----------------------------------------------------+1 row in set (0.00 sec) Old style of keypath is also supported, but two styles cannot work simultaneously. To use old path style you need to install libmy_json_udf library and to use new path style - libmy_json_udf_path. There is also install_jsonudf_path.sql script installing functions which support new path style automatically. As usual functions are available at MySQL Labs.

New version of JSON UDF functions has been just released. This version introduces two new features. Escape characters in results of JSON_SEARCH function. In previous version if you had document @doc...

MySQL

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')): 11 row in set (0.54 sec)Ormysql> 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')): 11 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_externalPlugin: 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.

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 mysqldumpdoes not work for...

MySQL

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 90041. The machine comes with 3G RAM which is not enough. Better to increase up to 8::sudo vboxmanage modifyvm myhost-vbox04 --memory 81922. 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 vdisudo vboxmanage modifyhd myhost-vbox04.vdi --resize 1024003. Then convert it back to vmdk and replace old disk with new one:sudo vboxmanage clonehd myhost-vbox04.vdi myhost-vbox04-01.vmdk --format vmdksudo vboxmanage storageattach myhost-vbox04 --storagectl "SATA" --device 0  \ --port 0 --type hdd --medium /data0/vbox/myhost-vbox04/myhost-vbox04-01.vmdk4. 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 emptydrive6. 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 -lDisk /dev/sda: 107.3 GB, 107374182400 bytes255 heads, 63 sectors/track, 13054 cylindersUnits = 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 LVMDisk /dev/dm-0: 36.5 GB, 36574330880 bytes255 heads, 63 sectors/track, 4446 cylindersUnits = cylinders of 16065 * 512 = 8225280 bytesDisk /dev/dm-0 doesn't contain a valid partition tableDisk /dev/dm-1: 7314 MB, 7314866176 bytes255 heads, 63 sectors/track, 889 cylindersUnits = cylinders of 16065 * 512 = 8225280 bytesDisk /dev/dm-1 doesn't contain a valid partition table$ df -hFilesystem            Size  Used Avail Use% Mounted on/dev/mapper/VolGroup00-LogVol00                       33G   32G     0 100% //dev/sda1              99M   23M   71M  25% /boottmpfs                 4.0G     0  4.0G   0% /dev/shmThis happens, because OEM is on LVM partition and we need to fix it too:$ /sbin/lvmlvm> pvresize /dev/sda2lvm> lvextend -L+55G /dev/mapper/VolGroup00-LogVol00  Extending logical volume LogVol00 to 89.06 GB  Logical volume LogVol00 successfully resizedlvm> quit  Exiting. $ /sbin/resize2fs /dev/mapper/VolGroup00-LogVol00resize2fs 1.39 (29-May-2006)Filesystem at /dev/mapper/VolGroup00-LogVol00 is mounted on /; on-line resizing requiredPerforming 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 -hFilesystem            Size  Used Avail Use% Mounted on/dev/mapper/VolGroup00-LogVol00                       87G   32G   51G  39% //dev/sda1              99M   23M   71M  25% /boottmpfs                 4.0G     0  4.0G   0% /dev/shmVoi-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.shAnd, 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:

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...

MySQL

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 schemaAnd 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.

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...

MySQL

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 -P13001Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 10Server version: 5.7.6-m16-debug-log Source distributionCopyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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: 0mysql> create temporary table t2 select * from t1;Query OK, 3 rows affected (0.03 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> create temporary table t3 select * from t1;Query OK, 3 rows affected (0.03 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> create temporary table t4 select * from t1;Query OK, 3 rows affected (0.03 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> create temporary table t5 select * from t1;Query OK, 3 rows affected (0.03 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> create temporary table t6 select * from t1;Query OK, 3 rows affected (0.02 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> create temporary table t7 select * from t1;Query OK, 3 rows affected (0.03 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> create temporary table t8 select * from t1;Query OK, 3 rows affected (0.03 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> create temporary table t9 select * from t1;Query OK, 3 rows affected (0.02 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> create temporary table t0 select * from t1;Query OK, 3 rows affected (0.02 sec)Records: 3  Duplicates: 0  Warnings: 0Nothing 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.1current_count_used: 303 current_allocated: 235.34 KiB current_avg_alloc: 795 bytes current_max_alloc: 45.70 KiB   total_allocated: 2.33 MiB1 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.1current_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.1current_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.

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...

MySQL

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: 1first element count: 21 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 events2014-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 distributionParse 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 CORRECT72962/18960413 `JSON_COUNT` CRASHES SERVER73486/19383795 `JSON_SET`, `JSON_APPEND` DO NOT APPEND TO EMPTY DOCUMENT73588/19447238 JSON FUNCTIONS CRASHES IN MEMMOVE CALLED FROM `MAKE_JEARGS`73587/19447278 `JSON_SET`: UNINTIALIZED VALUE IN JPARSER::`PARSE_NUM` WITH MALFORMED NUMBER73585/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

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...

MySQL

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 theplatform (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 arrays71530/18160842 `JSON_EXTRACT` RETURNS WRONG RESULT IN VERSION 0.371949/18355404 `JSON_VALID` DOES NOT REJECT INVALID DOCUMENTS70606/17596818 Implement SAFE versions for all JSON functions And, of course, this release contains all changes made in version 0.2.2

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...

MySQL

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.

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.co...

MySQL

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.

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...

MySQL

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 ReleaseThis 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 testAnd, 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.

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...

MySQL

Last element for JSON array: what do you think?

After I released maintenance release of JSON UDFs last week it is time to think about which features I should implement in upcoming major version.Many users asked me about the possibility to explicitly specify if they want to append last element to a JSON array. This feature can be made for two functions: json_append and json_set.I have four ideas of how to implement this. All have pros and contras. Create new function called json_append_last which will work exactly like json_append, but it will add the element to the end of array. I.e., for JSON document {"colors": ["red", "green", "blue"]} json_append_last(doc, 'colors', 'orange') returns {"colors": ["red", "green", "blue", "orange"]} Pros: Such a code is easy to understand Contras:  There is no sense to implement analogous function json_set_last, therefore this possibility is for json_append only You should explicitly branch your code if you want to add an element to known position of the array and if you want to add last element Use string keyword. For example, 'LAST'. I.e., for JSON document {"colors": ["red", "green", "blue"]} json_append(doc, 'colors', 'LAST', 'orange') returns {"colors": ["red", "green", "blue", "orange"]}  Pros: It is easy to implement same thing for json_set You should not branch code if you need both to add an element to known position and to the end of array Contras: It is easy to misuse. For example, if you mistakenly passed JSON document {"colors": {"LAST": "transparent"}} to json_append(doc, 'colors', 'LAST', 'orange') it returns {"colors": {"LAST": "orange"}} instead of error Use NULL instead of string keyword. I.e., for JSON document {"colors": ["red", "green", "blue"]} json_append(doc, 'colors', NULL, 'orange') returns {"colors": ["red", "green", "blue", "orange"]}  Pros: It is easy to implement same thing for json_set You should not branch code if you need both to add an element to known position and to the end of array No way to misuse: JSON functions don't take NULL as an argument anywhere else. Contras: Looks like a hack: NULL is not the type of last element It is hard to guess what this function is doing without looking into user manual Use negative index. So, -1 means last element, -2 last before last and so on. I.e., for JSON document {"colors": ["red", "green", "blue"]} json_append(doc, 'colors', -1, 'orange') returns {"colors": ["red", "green", "blue", "orange"]} and json_set(doc, 'colors', -2, 'orange') returns {"colors": ["red", "green", "orange"]}. json_append returns original document in case of -2. Pros: Works for both json_append and json_set You should not branch code if you need both to add an element to known position and to the end of array No way to misuse: JSON functions don't take negative number as an argument anywhere else. Contras: Confusing: while users of programming languages which use negative array indexes can guess what these functions do it is still needed to look in the manual to find out how -1, -2, etc. are interpreted Error-prone: it is easy to mix up such numbers As a conclusion I can say that I think solutions 1 and 3 are less error-prone and solution 4 introduces useful side-effects. I think that it is better to implement either 1 or 3, but certainly not both.But what do you think about it? Please either comment here or at bugs.mysql.com

After I released maintenance release of JSON UDFs last week it is time to think about which features I should implement in upcoming major version.Many users asked me about the possibility to...

MySQL

JSON UDF functions version 0.2.1 have been released.

Today new version of JSON UDF functions: 0.2.1 was released. This is maintenance release which added no new functionality and only contains bug fixes. However, it also includes improvements for build ans test procedures. As usual, you can download source and binary packages at MySQL Labs. Binary packages were build for MySQL server 5.6.14. If you want to use other version of the server, you need to recompile functions.What was changed? Let me quote the ChangeLog.Functionality added or changed:Added cmake option WITH_PCRE which alolows to specify if existent or bundled version of PCRE  should be used. Bundled is default on Windows. To compile with bundled version, run: "cmake . -DMYSQL_DIR=/path/to/mysql/dir -DWITH_PCRE=bundled", to turn bundled version off on Windows, run: "cmake . -DMYSQL_DIR=/path/to/mysql/dir -DWITH_PCRE=system"This means you don't need to type additional commands and move files from one directory to another to build JSON UDFs on Windows.Added possibility to run tests, using command make test. By default tests are running without valgrind. If you need to run tests under valgrind, add option VALGRIND=1 to cmake command: "cmake . -DMYSQL_DIR=/path/to/mysql/dir -DVALGRIND=1" You can automatically test on both UNIX and Windows. To test on UNIX simply run make test. On Windows: open solution my_json_udf.sln in Visual Studio, then choose target ALL_TESTS and run it. If you know how to run custom project of a solution on command line please let me know too =)Added cmake target build_source_dist which creates source tarball. Usage: "make build_source_dist".This addition is mostly for developers and needed to create packages for MySQL Labs.Minimum supported cmake version changed to 2.8. It is necessary to run tests and build PCRE.Bugs Fixed:70392/17491678 MySQL JSON UDFs binary is called libmy_json_udf.so but DDL uses libmy_json.so70573/17583505 Typo in README for JSON_MERGE70605/17596660 Include all references to documentaiton to README file70569/17583335 JSON_VALID allows mixed case in the keyword names70568/17583310 JSON_VALID treats invalid values as valid70570/17583367 JSON_VALID allows \u two-hex-digit while standard allows only \u four-hex-digit70574/17583525 JSON_MERGE treats document without opening bracket as valid70486/17548362 When using json_replace(), '}' of the end disappear.70839/17751860 JSON_VALID allows to have two elements with the same keyI hope this version will be more stable than the first one and I hope you enjoy it.

Today new version of JSON UDF functions: 0.2.1 was released. This is maintenance release which added no new functionality and only contains bug fixes. However, it also includes improvements for build...

MySQL

To be safe or to be fast?

When I designed first version of JSON UDFs which was reviewed only internally, I let all functions to validate input and output JSON. But my colleagues told me to remove this functionality, because it makes such functions, as json_search, json_replace or json_contains_key deadly slow if they find the occurrence in the beginning of the long document. And first published version of JSON UDFs: 0.2.0 has not this functionality. What we expected is that users would call json_valid if they want to be 100% sure the document is valid.But I was not surprised that some users expect JSON functions to work as it was in the first version: validate first, then process. For example, Ammon Sutherland writes: "json_set - according to the documentation a sort of 'INSERT... ON DUPLICATE KEY UPDATE' function which checks and parses the JSON (but not the whole document in this version for some reason)." Ulf Wendel also writes: "Taken from the README. For most functions manipulating or generating JSON documents is true: Warning! This version does not check whole document for validity. Hey, it is a labs.mysql.com pre-production release."So this is certain what at least some of users want to have the behavior which was rejected. But since I believe others still can want better speed, I decided to put in my plans implementation of second set of the functions: safe JSON functions.First, and more logical, candidate for this implementation is json_safe_merge, because currently json_merge is mostly not usable: if you checked already that documents are valid, you can easily split them. Therefore I created a separate feature request about this function: http://bugs.mysql.com/bug.php?id=70575But I am not sure regarding other functions. This is why I created one more public feature request: "Implement SAFE versions for all JSON functions". Will I implement this or not would depend from your opinions. Therefore, if you want me to do so, please vote in the bug report by clicking "Affects Me!" button and comment if you want this feature will be implemented only for a few functions (or sooner for a particular function).

When I designed first version of JSON UDFs which was reviewed only internally, I let all functions to validate input and output JSON. But my colleagues told me to remove this functionality, because...

MySQL

Late feedback

MySQL Community team asked me to write about Devconf 2013 few months ago. Conference was in June, 2013, but I remembered about this my promise only now: month later after my participating in MySQL Connect and Expert Troubleshooting seminar (change country to United Kingdom if you see blank page). I think it is too late for the feedback, but I still have few thoughts which I want to record.DevConf (former PHPConf) always was a place where I tried new topics. At first, because I know audience there very well and they will be bored if I repeat a story which I was telling last year, but also because it is much easier to get feedback in your own native language. But last years my habit seems started to change and I presented improved version of my 2012 MySQL Connect talk about MySQL backups. Of course, I also had a seminar with unique topic, made for this conference first time: Troubleshooting MySQL Performance with EXPLAIN and Using Performance Schema to Troubleshoot MySQL. And these topics, improved, were presented at the expert seminar. It is interesting how my habit changes and one public speaking activity interferes next one.What is good about DevConf is it forces you to create new ideas and do it really well, because audience is not forgiving at all, so they catch everything you miss or prepared not good enough. This can be bad if you want to make a marketing-style topic for free, but allows to present technical features in really good details: all these sudden discussions really help.In year 2013 Oracle had a booth at the conference and was presented by a bunch of people. Dmitry Lenev presented topic "New features of replication in MySQL 5.6" and Victoria Reznichenko worked on the booth. What was new at the conference this year is greater interest in NoSQL, scale and fast development solutions. This, unfortunately, means not so huge interest in MySQL as it was earlier. However, at the same time, "Common" track was really MySQL track: not only Oracle, but people from other companies presented about it.

MySQL Community team asked me to write about Devconf 2013 few months ago. Conference was in June, 2013, but I remembered about this my promise only now: month later after my participating in MySQL...

MySQL

JSON UDFs first feedback

Yesterday Ulf Wendel created great blog post with deep analysis of JSON UDF functions which I presented at MySQL Connect at September, 21.Ulf found few bugs in these functions, I reported them all at bugs.mysql.com. You can find numbers in my comment to his blog post. But he also raised concerns, which can not be considered pure bugs, rather feature requests, or even design especiallities.* First concern, of course, is the documentation. Ulf writes: "Here’s what the README, the only documentation available apart from the *.c[omment]/*.h[elp] files".I agree single README file is not enough, but this is still Labs project for which I can not abuse MySQL documentation team for making proper documentation for me. But you still can find more information, than single README file. And these are slides from MySQL Connect, which are available on the conference website (they published them today!) at https://oracleus.activeevents.com/2013/connect/fileDownload/session/470E8AA1C46CAA3A9ACFBDB7653FC313/CON3130_Smirnova.pdf as well as on my own website at http://www.microbecal.com/ftp_load/JSON_UDFs.odp or http://www.microbecal.com/ftp_load/JSON_UDFs.pdf I strongly recommend you to download these slides, because this is the best user manual for JSON functions which exists so far.But I also want to hear your suggestions about where I can put the complete user manual. I was going to post updates to my blog and update README file. Is this sufficient? Or do you think would be better if I put the documentation at my own website? Or pack it as a separate *-doc package at labs.mysql.com? Any other ideas? If you also miss the documentation, please comment.* Another request which I have for you is about parameter syntax. Ulf writes: "A function that works “by key” on a subset of a JSON document, usually has the prototype: JSON_SOMETHING(string json_document, string key [, string key...], other_parameter). The first argument passed to the functions is always the JSON document to work on. It follows a variable number of arguments that describe on which subpart of the entire document the function shall work. Having this string key [, string key...] arguments in the middle of the function signature is a bit odd. Some programming languages may forbid this for style considerations. However, depending on the function, further arguments may follow, such as the value to search for or to add."We discussed this syntax internally before I started implementing the functions and get to this style. But I understand what some people could prefer different syntax. If you such a person, please, file a feature request. At this stage it is easy to rewrite functions, so they support better style of user arguments. I will think some time about it and will post a poll to my blog with proposals which I think can be better than current syntax. Also it is possible to have different set of functions with the same functionality, but which support different style of arguments. Only requirement for this feature is users suggestions and votes. Therefore, please suggest and vote!* Another feature for which I need your suggestions is "speed vs validity checks". I again will cite Ulf: "Taken from the README. For most functions manipulating or generating JSON documents is true: Warning! This version does not check whole document for validity. Hey, it is a labs.mysql.com pre-production release :-)."But this design was done not because these functions are at the Labs and not because this is alpha version. We discussed this functionality internally, but decided to don't implement validity checks to speed up functions. If you think we did wrong, please, open a feature request at bugs.mysql.com about *safe* version of the functions. I also think that having two sets of "safe" and "fast" functions can also work. But I need to hear your opinions before implementing this feature.* Another feature for which I want to count user votes is search abilities. Currently, function JSON_SEARCH uses only exact match. There is no support for wildcards, case insensitive search or full-text search. I got complains about such a limitation not only from Ulf, but from users at MySQL Connect too. Therefore I created two feature requests: http://bugs.mysql.com/bug.php?id=70571 about LIKE and http://bugs.mysql.com/bug.php?id=70572  about FULLTEXT. If you need these features, please go and vote for them using "Affects Me!" Button.Current behavior of JSON_SEARCH is tricky and Ulf, expectedly, did mistake when tried to use it. Correct syntax should be select json_search('{"key":"value"}', '"value"' ); and select json_search('{"a":{"b":"c"}}', '"c"' ) instead of select json_search('{"key":"value"}', "value" ); and select json_search('{"a":{"b":"c"}}', "c" ) which he used:mysql> select json_search('{"key":"value"}', '"value"' )\G*************************** 1. row ***************************json_search('{"key":"value"}', '"value"' ): key::1 row in set (0.00 sec)mysql> select json_search('{"a":{"b":"c"}}', '"c"' );+----------------------------------------+| json_search('{"a":{"b":"c"}}', '"c"' ) |+----------------------------------------+| b:a::                                  |+----------------------------------------+1 row in set (0.00 sec)This syntax is used to let you search for objects: mysql> select json_search('{"a":{"b":"c"}}', '{"b":"c"}' );+----------------------------------------------+| json_search('{"a":{"b":"c"}}', '{"b":"c"}' ) |+----------------------------------------------+| a::                                          |+----------------------------------------------+1 row in set (0.00 sec)You can also notice "::" in the end of the key path. This is the root element and done by design.And last Ulf's concern is about syntactic sugar:"With a bit of syntactic sugar, say “SELECT document.member[offset]” this could look much more appealing. On the contrary, I am talking syntaxtic sugar only! Syntactic sugar is really hard to add with todays MySQL."I so agree here! I wish I could implement the functions in such a way! But it is not possible to change MySQL syntax with UDFs!And while we can not do much for syntactic sugar, you still can help to make better other features. Please comment and vote for the user manual format and location; parameter syntax; safe versions of the functions and extended search capabilites.Thank you! :)

Yesterday Ulf Wendel created great blog post with deep analysis of JSON UDF functions which I presented at MySQL Connect at September, 21.Ulf found few bugs in these functions, I reported them all...

MySQL

Vote for bugs which impact you!

Matt Lord already announced this change, but I am so happy, so want to repeat. MySQL Community Bugs Database Team introduced new button "Affects Me". After you click this button, counter, assigned to each of bug reports, will increase by one. This means we: MySQL Support and Engineering, - will see how many users are affected by the bug. Why is this important? We have always considered community input as we prioritize bug fixes, and this is one more point of reference for us. Before this change we only had a counter for support customers which increased when they opened a support request, complaining they are affected by a bug. But our customers are smart and not always open support request when hit a bug: sometimes they simply implement workaround. Or there could be other circumstances when they don't create a ticket. Or this could be just released version, which big shops frighten to use in production. Therefore, sometimes, when discussing which bug to prioritize and which not we can not rely only on "Affects paying customers" number, rather need to make guess if one or another bug can affect large group of our users. We used number of bug report subscribers, most recent comments, searched forums, but all these methods gave only approximation. Therefore I want to ask you. If you hit a bug which already was reported, but not fixed yet, please click "Affects Me" button! It will take just a few seconds, but your voice will be heard.

Matt Lord already announced this change, but I am so happy, so want to repeat. MySQL Community Bugs Database Team introduced new button "Affects Me". After you click this button, counter, assigned to...

MySQL

Yet another UDF tutorial

Some time ago I wrote a blog post describing a way I use to verify MySQL Server bugs. But my job consists not only of bugs which can be verified just by passing SQL queries to the server.One of such examples is UDF bugs.MySQL User Reference Manual is good source of information for those who want to write UDF functions, as well as book "MySQL 5.1 Plugin Development" by  Sergei Golubchik and Andrew Hutchings. But while the book describes in details how to write UDFs it was created in time when current MySQL version was 5.1 and does not contain information about how to build UDF nowadays. User Reference Manual has this information, of course. But it missed details, such as how to build UDF with custom library. And, last but not least, I need to create layout which allows me to test my UDF quickly with any server version I need to.So here is brief overview of how I do it.Lets took a MySQL bug report which I just created as example: "Bug #68946     UDF *_init and *_deinit functions called only once for multiple-row select".All code, necessary to repeat the issue, is attached to the bug report. So you can download it and try. Or simply read this description.After unpacking the archive we can see following layout: -rwxrwx--- 1 sveta sveta  1070 Apr 13 11:48 CMakeLists.txt -rwxrwx--- 1 sveta sveta   180 Apr 13 12:16 initid_bug.cc -rwxrwx--- 1 sveta sveta   146 Apr 13 12:16 initid_bug.h drwxrwx--- 1 sveta sveta     0 Apr 13 11:22 initid_bug_udf -rwxrwx--- 1 sveta sveta   715 Apr 13 12:18 initid_bug_udf.cc -rwxrwx--- 1 sveta sveta    76 Apr 13 11:48 initid_bug_udf.def -rwxrwx--- 1 sveta sveta   484 Apr 13 12:08 initid_bug_udf.h -rwxrwx--- 1 sveta sveta  6281 Apr 13 13:07 Makefile -rwxrwx--- 1 sveta sveta   299 Apr 13 12:28 Makefile.unix Lets start from code. $ cat initid_bug_udf.def LIBRARY         initid_bug_udf VERSION         0.1 EXPORTS   initid_bugThis is common *.def file, which contains version of UDF and function names. I am going to use single function, showing issue with initid->ptr pointer.initid_bug.h and initid_bug.cc contain declaration and definition of a helper function, necessary to demonstrate the problem. For this particular bug report I didn't need to create this function, but I had in mind future blog post when created it, so I have an example of external library which should be linked with the UDF: $ cat initid_bug.h /* MySQL */ #pragma once #ifndef _INITID_BUG #define _INITID_BUG #define MAXRES 10 char* multiply_by_ten(int value); #endif /* UNUTUD_BUG */ $ cat initid_bug.cc /* MySQL */ #include <stdio.h> #include "initid_bug.h" char* multiply_by_ten(int value) {     char* result= new char[MAXRES];     sprintf(result, "%d", value * 10);     return result; }Files initid_bug_udf.h and initid_bug_udf.cc contain code for the UDF itself.initid_bug_udf.h is simple header file: $ cat initid_bug_udf.h /* MySQL */ #include <my_global.h> #include <my_sys.h> #include <mysql.h> #include <string.h> #include "initid_bug.h" #ifdef __WIN__ #define WINEXPORT __declspec(dllexport) //#define strcpy strcpy_s #else #define WINEXPORT #endif extern "C" { WINEXPORT long long initid_bug(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error); WINEXPORT my_bool initid_bug_init(UDF_INIT *initid, UDF_ARGS *args, char *message); WINEXPORT void initid_bug_deinit(UDF_INIT *initid); }And initid_bug_udf.cc contains code, demonstrating the issue: $ cat initid_bug_udf.cc /* MySQL */ #include "initid_bug_udf.h" long long initid_bug(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) {     int result= atoi(initid->ptr);     char* bug= multiply_by_ten(result);     memcpy(initid->ptr, bug, strlen(bug));     delete[] bug;     return result; } my_bool initid_bug_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {   if (!(initid->ptr= (char*)malloc(MAXRES)))   {     strcpy(message,"Couldn't allocate memory for result buffer");     return 1;   }   memset(initid->ptr, '\0', MAXRES);   memcpy(initid->ptr, "1", strlen("1"));   initid->maybe_null= 1;   initid->const_item= 0;   return 0; } void initid_bug_deinit(UDF_INIT *initid) {   if (initid->ptr)     free(initid->ptr); }So far so good. However there is nothing interesting yet.And now is the part I am writing this tutorial for: how to build and test it.User manual contains build instructions for version 5.5 and up at http://dev.mysql.com/doc/refman/5.6/en/udf-compiling.htmlBut since I want to test my UDF with several MySQL installations I need an easy way to pass value of MySQL's basedir to my build and test scripts. For this purpose I introduced variable MYSQL_DIR in CMakeLists.txt $ cat CMakeLists.txt CMAKE_MINIMUM_REQUIRED(VERSION 2.6) # Avoid warnings in higher versions if("${CMAKE_MAJOR_VERSION}.${CMAKE_MINOR_VERSION}" GREATER 2.6)  CMAKE_POLICY(VERSION 2.8) endif() PROJECT(initid_bug_udf) # The version number. set (initid_bug_udf_VERSION_MAJOR 0) set (initid_bug_udf_VERSION_MINOR 1) # Path for MySQL include directory SET(MYSQL_DIR_NAME_DOCSTRING "Path to MySQL directory") IF(DEFINED MYSQL_DIR)   SET(MYSQL_DIR ${MYSQL_DIR} CACHE STRING ${MYSQL_DIR_NAME_DOCSTRING} FORCE) ELSE()   MESSAGE(WARNING "${MYSQL_DIR_NAME_DOCSTRING} was not specified. If something goes wrong re-reun with option -DMYSQL_DIR")  ENDIF() INCLUDE_DIRECTORIES("${MYSQL_DIR}/include") I also added the library here ADD_LIBRARY(initid_bug initid_bug.cc) ADD_DEFINITIONS("-DMYSQL_DYNAMIC_PLUGIN") ADD_DEFINITIONS("-fPIC") ADD_DEFINITIONS("-g") ADD_LIBRARY(initid_bug_udf MODULE initid_bug_udf.cc initid_bug_udf.def) And linked it IF(${CMAKE_SYSTEM_NAME} MATCHES "Windows") TARGET_LINK_LIBRARIES(initid_bug_udf initid_bug wsock32) ELSE() TARGET_LINK_LIBRARIES(initid_bug_udf initid_bug) ENDIF() In other aspects this CMakeLists.txt is the same as described in the user manual.Now it is easy to build UDF for any server, installed on the same machine as UDF sources.On Linux/Solaris/Mac:cmake . -DMYSQL_DIR=/home/sveta/src/mysql-5.6makeOn some Mac machines it is failed to create 64-bit binaries. You can build universal binaries instead providing option -DCMAKE_OSX_ARCHITECTURES="x86_64;i386;ppc"On Windows:You need Visual Studio (I did not test with Express, but I hope it works) and cmake (cmake.org) If you want to run automatic tests you need Perl.To create makefiles run: "C:\Program Files (x86)\CMake 2.8\bin\cmake.exe" -G "Visual Studio 11 Win64" . -DMYSQL_DIR="D:/build/mysql-5.5" As you can see I have Visual Studio 11. If you have another version, change accordingly, for example: "Visual Studio 10 Win64" Then compile: devenv my_json_udf.sln /build Release In all cases change MYSQL_DIR value so it points to basedir of your MySQL installation. It does not matter if you compiled MySQL server yourself or have pre-compiled binaries.After compiling the library, install it or test. Since I care about tests mostly I did not create install script, but created tests and makefile to run them.Tests are located in the initid_bug_udf directory: $ ls -l initid_bug_udf total 4 drwxrwx--- 1 sveta sveta 4096 Apr 13 13:08 include drwxrwx--- 1 sveta sveta    0 Apr 13 11:31 plugin drwxrwx--- 1 sveta sveta    0 Apr 13 11:59 r drwxrwx--- 1 sveta sveta    0 Apr 13 11:21 t They are in MTR format. I put all installation-related and commonly used scripts into include directory and copy UDF binary into plugin directory.Test case itself is easy: $ cat initid_bug_udf/t/initid_bug.test --source suite/initid_bug_udf/include/initid_bug_udf_install.inc --source suite/initid_bug_udf/include/initid_bug_udf_testdata.inc select initid_bug() from t1; --source suite/initid_bug_udf/include/initid_bug_udf_cleanup.inc As well as initialization and cleanup files: $ cat initid_bug_udf/include/initid_bug_udf_install.inc --disable_query_log let $win=`select @@version_compile_os like 'win%'`; if($win==1) { --source suite/initid_bug_udf/include/initid_bug_udf_install_win.inc } if($win==0) { --source suite/initid_bug_udf/include/initid_bug_udf_install_unix.inc } --enable_result_log --enable_query_log $ cat initid_bug_udf/include/initid_bug_udf_install_unix.inc --disable_query_log --disable_warnings drop function if exists initid_bug; --enable_warnings create function initid_bug returns integer soname 'libinitid_bug_udf.so'; --enable_query_log $ cat initid_bug_udf/include/initid_bug_udf_install_win.inc --disable_query_log --disable_warnings drop function if exists initid_bug; --enable_warnings create function initid_bug returns integer soname 'initid_bug_udf.dll'; --enable_query_log However you can use *install* scripts as templates for installation automation if you care more about usage than testing. Note, I did not test this particular UDF on Windows, so Windows-related code can be buggy.Also, if you use versions prior to 5.5, you could not use initid_bug_udf/include/initid_bug_udf_install.inc script, but have to distinguish Windows and UNIX installation scripts in different way. $ cat initid_bug_udf/include/initid_bug_udf_testdata.inc --disable_warnings drop table if exists t1; --enable_warnings create table t1(f1 int); insert into t1 values(1); insert into t1 select f1 from t1; insert into t1 select f1 from t1; insert into t1 select f1 from t1; And, finally, cleanup: $ cat initid_bug_udf/include/initid_bug_udf_cleanup.inc --disable_query_log --disable_warnings drop table if exists t1; drop function if exists initid_bug; --enable_warnings --enable_query_log To run tests easily I created Makefile.unix file. I leave a task to create similar file for Windows up to you, because this depends from Windows Perl installation: scripts for Cygwin, ActiveState Perl or whichever Perl you have can vary in slight details. $ cat Makefile.unix #MYSQL_DIR - path to source dir test_initid_bug:                cp libinitid_bug_udf.so initid_bug_udf/plugin             cp -R initid_bug_udf  $(MYSQL_DIR)/mysql-test/suite             cd $(MYSQL_DIR)/mysql-test; \                 perl mtr  --suite=initid_bug_udf --mysqld=--plugin-dir=$(MYSQL_DIR)/mysql-test/suite/initid_bug_udf/plugin Finally, to run tests you simply need to call following command: $ MYSQL_DIR=/home/sveta/src/mysql-5.6 make -f Makefile.unix test_initid_bug It will copy test directory under mysql-test/suite directory of the MySQL installation, pointed by MYSQL_DIR variable, then run the test.If you did not create result file you will see test failure and result of queries. You can create result file after that and put it into r directory. ZiP archive, attached to the bug report, contains result file.

Some time ago I wrote a blog post describing a way I use to verify MySQL Server bugs. But my job consists not only of bugs which can be verified just by passing SQL queries to the server.One of such...

MySQL

Troubleshooting Performance Diagrams

Last year, when I was speaking about MySQL performance at Devconf in Moscow, I expected my audience will be very experienced as this always happen at all PHPClub conferences. So I had to choose: either make full-day seminar and explain people every basic of performance, or rely on their knowledge and make some one and half hours seminar. I prefer short speeches, so I considered latter. But even with such a mature audience you don't always know if they knew some or another basic thing. Like somebody can be good analyzing EXPLAIN output and other is in reading InnoDB Monitor printout. Also, native language of the audience is not English and it would be always good to have short reference to simple things, described in their native language. In this case Russian. This is why I created these slides first time. I was in doubt if I need to translate them to English, because information there repeats the official MySQL user reference manual in many aspects. Although diagrams still can be useful for English-speaking audience. So I did not translate those slides in first turn. Time passed and in few days I am speaking about MySQL Troubleshooting at Fosdem. This is 30-minustes talk this time! And I just created slides and content for 8-hours Oracle University training! Can I zip this 8-hours training into 30-minutes talk? Of course, not. But I also want to give users as much in these 30 minutes as possible. So idea with add-ons came back. You can download them in PDF format either from my own website or from slideshare.

Last year, when I was speaking about MySQL performance at Devconf in Moscow, I expected my audience will be very experienced as this always happen at all PHPClub conferences. So I had to choose:...

MySQL

My way to verify MySQL bug reports

I promised to write this blog post long time ago at one of conferences in Russia. Don't know why I delayed this, but finally I did.We, members of MySQL bugs verification group, have to verify bugs in all currently supported versions. We use not only version reported, but test in development source tree for each of supported major versions and identify recent regressions.You can imagine that even if I would do so for simple bug report about wrong results with perfect test case, which requires me simply run few queries I would have to start 4 or more MySQL servers: one for each of currently supported versions 5.0, 5.1, 5.5 plus one for current development. And unknown number of servers if I could not repeat or if I want to check if this is regression.Even if I have all these basic 4 servers running I still should type all these queries at least 4 times. How much time it would take to verify single bug report if I did so?I know some members of my group preferred this way, because typing queries manually is same action which our customers do. Again, some bugs are repeatable only if you type queries manually.But I prefer to test manually erroneous exceptions only and don't make it my routine job. So how do I test bug reports?Every version of MySQL server comes with regression test suite: a program, called mtr (mysql-test-run.pl), its libraries, mysqltest program (you should not call it directly, though) and set of tests. Good thing with MySQL test suite is that you can create your own test cases. So do I.I write my tests in MTR format, then run MTR with record option and examine result. Actually this is kind of hack, because users expected to create result file first, then compare output of running test with that result file. But my purpose is to repeat bug report, not to create proper test case for it, so I can be lazy.But simply running MTR manually still takes time. And I found a way to automate this process as well.I created a BASH script, called do_test.sh, which run through all my MySQL trees and runs tests for me automatically, then prints result.Let me explain it a little bit. $ cat ~/scripts/do_test.sh #!/bin/bash # runs MySQL tests in all source directories # prints usage information usage () {     echo "$VERSION"     echo " do_test copies MySQL test files from any place to each of source directory, then runs them Usage: `basename $0` [option]... [testfile ...]     or `basename $0` [option]... -d dirname [test ...]     or `basename $0` [option]... [-b build [build option]... ]... Options:     -d --testdir    directory, contains test files I have a directory, there I store test files. It has subdirectory t where tests to be run are stored, subdirectory r, where results, sorted by MySQL server version number, are stored, and directory named archive, there test are stored for archiving purpose.     -s --srcdir     directory, contains sources directories This is path to the directory where MySQL package is located. I called it srcdir, but this is actually not so strict: program will work with binary packages as well.     -b --build      mysql source directory Name of MySQL source directory. You can specify any package name. For example, to run tests in 5.6.9 package in my current dir I call the program as do_test -s . -b mysql-5.6.9-rc-linux-glibc2.5-x86_64     -c --clean      remove tests from src directory after execution     -t --suite      suite where to put test MTR can have test suites with their own rules of how to run test case. If you want to run your tests in specific suite, specify this option. You can also have directory for your own suite, but in this case you need to create directories your_suite, your_suite/t and your_suite/r in mysql-test/suite directory of your MySQL installation prior doing this.As I told I am lazy, so I run tests in main test suite mostly. This can be not good idea if you use MySQL installation not only for tests of its own bugs, but for some other tests.Rest of the code speaks for itself, so I would not explain it. What you need to do to run this program is simply call it: do_test.sh and pass paths to your test, src dir and MySQL installation.     -v --version    print version number, then exit     -h --help       print this help, then exit You can also pass any option to mysqltest program.     " } # error exit error() {     printf "$@" >&2     exit $E_CDERROR } # creates defaults values initialize() {This probably not very obvious. These are my default paths and, most importantly, default set of servers I test     TESTDIR=/home/sveta/src/tests     SRCDIR=/home/sveta/src     BUILDS="mysql-5.0 mysql-5.1 mysql-5.5 mysql-trunk"     CLEAN=0 #false     MYSQLTEST_OPTIONS="--record --force"     TESTS_TO_PASS=""     TESTS=""     SUITE=""     SUITEDIR=""     OLD_PWD=`pwd`     VERSION="do_test v0.2 (May 28 2010)" } # parses arguments/sets values to defaults parse() {     TEMP_BUILDS=""         while getopts "cvhd:s:b:t:" Option     do         case $Option in             c) CLEAN=1;;             v) echo "$VERSION";;             h) usage; exit 0;;             d) TESTDIR="$OPTARG";;             s) SRCDIR="$OPTARG";;             b) TEMP_BUILDS="$TEMP_BUILDS $OPTARG";;             t) SUITE="$OPTARG"; SUITEDIR="/suite/$SUITE"; MYSQLTEST_OPTIONS="$MYSQLTEST_OPTIONS --suite=$SUITE";;             *) usage; exit 0; ;;         esac     done     if [[ $TEMP_BUILDS ]]     then         BUILDS="$TEMP_BUILDS"     fi } # copies test to source directories copy() {     cd "$TESTDIR/t"     TESTS_TO_PASS=`ls *.test 2>/dev/null | sed s/.test$//`     cd $OLD_PWD     for build in $BUILDS     do         #cp -i for reject silent overload         cp "$TESTDIR"/t/*.{test,opt,init,sql,cnf} "$SRCDIR/$build/mysql-test$SUITEDIR/t" 2>/dev/null     done } # runs tests run() {     for build in $BUILDS     do         cd "$SRCDIR/$build/mysql-test"         ./mysql-test-run.pl $MYSQLTEST_OPTIONS $TESTS_TO_PASS     done     cd $OLD_PWD } # copies result and log files to the main directory get_result() {     for build in $BUILDS     do         ls "$TESTDIR/r/$build" 2>/dev/null         if [[ 0 -ne $? ]]         then             mkdir "$TESTDIR/r/$build"         fi         for test in $TESTS_TO_PASS         do             cp "$SRCDIR/$build/mysql-test$SUITEDIR/r/$test".{log,result} "$TESTDIR/r/$build" 2>/dev/null         done     done } # removes tests and results from MySQL sources directories cleanup() {     if [[ 1 -eq $CLEAN ]]     then         for build in $BUILDS         do             for test in $TESTS_TO_PASS             do                 rm "$SRCDIR/$build/mysql-test$SUITEDIR/r/$test".{log,result} 2>/dev/null                 rm "$SRCDIR/$build/mysql-test$SUITEDIR/t/$test.test"             done         done     fi } # shows results show() {     for build in $BUILDS     do         echo "=====$build====="         for test in $TESTS_TO_PASS         do             echo "=====$test====="             cat "$TESTDIR/r/$build/$test".{log,result} 2>/dev/null             echo         done         echo     done } E_CDERROR=65 #usage initialize parse $@ copy run get_result cleanup show exit 0After I finished with test I copy it to archive directory, again, with a script, named ar_test.sh: $ cat ~/scripts/ar_test.sh #!/bin/bash # moves MySQL tests from t to archive directory and clean ups r directories # prints usage information usage () {     echo "$VERSION"     echo " ar_test copies MySQL test files from t to archive folder Usage: `basename $0` [-v] [-d dirname] [test ...] Options:     -d    directory, contains test files     -v    print version     -h    print this help     " } # error exit error() {     printf "$@" >&2     exit $E_CDERROR } # creates defaults values initialize() {     TESTDIR=/home/sveta/src/tests     TESTS_TO_MOVE=""     OLD_PWD=`pwd`     VERSION="ar_test v0.2 (Dec 01 2011)" } # parses arguments/sets values to defaults parse() {        while getopts "vhd:" Option     do         case $Option in             v) echo "$VERSION"; shift;;             h) usage; exit 0;;             d) TESTDIR="$OPTARG"; shift;;             *) usage; exit 0;;         esac     done         TESTS_TO_MOVE="$@" } # copies test to source directories copy() {     if [[ "xx" = x"$TESTS_TO_MOVE"x ]]     then         cp "$TESTDIR"/t/* "$TESTDIR"/archive 2>/dev/null     else         for test in $TESTS_TO_MOVE         do             cp "$TESTDIR/t/$test".{test,opt,init,sql} "$TESTDIR"/archive 2>/dev/null         done     fi } # removes tests and results from r directories cleanup() {     if [[ "xx" = x"$TESTS_TO_MOVE"x ]]     then         rm "$TESTDIR"/t/* 2>/dev/null         rm "$TESTDIR"/r/*/* 2>/dev/null     else         for test in $TESTS_TO_MOVE         do             rm "$TESTDIR/t/$test".{test,opt,init,sql} 2>/dev/null             rm "$TESTDIR/r/"*"/$test".{test,opt,init,sql} 2>/dev/null         done     fi } E_CDERROR=65 initialize parse $@ copy cleanup exit 0But most important part: what to do if I want to test on some specific machine which is not available at home? Fortunately, we have shared machines to run tests on, so I can simply move them to my network homedir, then choose appropriate machine and run. Since this is BASH script and test cases in MTR format this would work on any operating system. $ cat ~/scripts/scp_test.sh #!/bin/bash # copies MySQL tests to remote box # prints usage information usage () {     echo "$VERSION"     echo " scp_test copies MySQL test files from t directory on local box to MySQL's XXX     Usage: `basename $0` [-v] [-d dirname] [-r user@host:path] [test ...] Options:     -d    directory, contains test files     -r    path to test directory on remote server, default: USERNAME@MACHINE_ADDRESS:~/PATH/src/tests/t     -v    print version     -h    print this help     " } # error exit error() {     printf "$@" >&2     exit $E_CDERROR } # creates defaults values initialize() {     TESTDIR=/home/sveta/src/tests     MOVETO='USERNAME@MACHINE_ADDRESS:~/PATH/src/tests/t'     TESTS_TO_MOVE=""     OLD_PWD=`pwd`     VERSION="scp_test v0.2 (Dec 1 2011)" } # parses arguments/sets values to defaults parse() {        while getopts "vhd:" Option     do         case $Option in             v) echo "$VERSION"; shift;;             h) usage; exit 0;;             d) TESTDIR="$OPTARG"; shift;;             r) MOVETO="$OPTARG"; shift;;             *) usage; exit 0;;         esac     done         TESTS_TO_MOVE="$@" } # copies test to source directories copy() {     if [[ "xx" = x"$TESTS_TO_MOVE"x ]]     then         scp "$TESTDIR"/t/* "$MOVETO"     else         for test in $TESTS_TO_MOVE         do             scp "$TESTDIR/t/$test".{test,opt,init,sql} "$MOVETO"         done     fi } E_CDERROR=65 initialize parse $@ copy exit 0Wanted to put them to Launchpad, but stack with name for this package. Does anybody have ideas?

I promised to write this blog post long time ago at one of conferences in Russia. Don't know why I delayed this, but finally I did.We, members of MySQL bugs verification group, have to verify bugs in...

MySQL

My eighteen MySQL 5.6 favorite troubleshooting improvements

MySQL 5.6 is in RC state now which means it is going to be GA sooner or later.This release contains a lot of improvements. However, since I am a support engineer, I most amazed by those which make troubleshooting easier.So here is the list of my favorite troubleshooting improvements.1. EXPLAIN for UPDATE/INSERT/DELETE.This is extremely useful feature.Although prior version 5.6 we, theoretically, could have some kind of explain for them too, for example, if convert DML queries to their SELECT equivalents, optimizer can optimize them differently.We still could execute DELETE or UPDATE, then query Hadler_% status variables, but who wants to execute update just for testing on live database? And anyway, querying Handler_% variables we could only know if some index was used or not, but can not identify which one.2. INFORMATION SCHEMA.OPTIMIZER_TRACE table.This table contains trace of last few queries, number of which is specified by configuration option optimizer_trace_limit.Actually we could have similar information prior version 5.6: just use debug server and start it with option --debug. In this case MySQL server creates trace file where it writes information about functions and methods executed during server run. Problem with such files they are extremely verbose and large. It is hard to find necessary information there if you are not MySQL developer. As support engineer I use them when need to test a bug, but I use as follow: create very short script which executes as less queries as it can, then start MySQL server with debug option and run the test. Then stop debugging. Even with such short single-threaded tests size of resulting file is thousands of rows. It is almost not possible to use such files in production.With optimizer trace we can have similar information just for optimizer, but in much compact way.Hmm... I want similar feature for all parts of the server!3. EXPLAIN output in JSON format.This is actually simply syntax sugar around normal EXPLAIN output. It prints exactly same information like normal, table-view EXPLAIN, but can be used for some automations.4. New InnoDB tables in Information Schema.Table INNODB_METRICS contains a lot of information about InnoDB performance, starting from InnoDB buffer usage and up to number of transactions and records.Tables INNODB_SYS_* contains information about InnoDB internal dictionary and its objects. Particularly about tables, fields, keys, etc.Table INNODB_SYS_TABLESTATS contains information about InnoDB performance statistics.Tables INNODB_BUFFER_POOL_* store information about InnoDB Buffer Pool usage.5. Option to log all InnoDB deadlocks into error log file: innodb_print_all_deadlocks.Currently we can use InnoDB Monitor output for the same purpose, but it prints latest deadlock only, not everyone since server startup. This feature should be very handy to troubleshoot issues, repeatable on production only.6. Persistent statistics for InnoDB tables.It is also OFF by default and can be turned ON with help of option innodb_analyze_is_persistent.It was originally OFF. Now this option has name innodb_stats_persistent and is ON by default.But this is performance improvement and how does it relate to troubleshooting?In version 5.5 by default when you run query ANALYZE TABLE and after each MySQL server restart InnoDB renews table statistics which optimizer uses to generate better execution plans. Unfortunately such often statistics renewal does not fit for all data and tables. Sometimes fresh statistics can lead to choice of not the best plan. Now, if innodb_stats_persistent is ON, statistics get renewed only after ANALYZE TABLE and stays same after server restart.In past we sometimes get complains from people who initially had fast perfectly running query, but performance decreased after inserting few millions of rows. As you can imagine rebuilding the table does not help in this case. For such cases we offered FORCE/IGNORE INDEX as a workaround which usually means customers had to rewrite dozens of queries.Now we can start recommending to load data for which optimizer chooses best plan, then ANALYZE TABLE and never update statistics again.7. InnoDB read-only transactions.Every query on InnoDB table is part of transaction. This does not depend if you SELECT or modify the table. For example, for TRANSACTION ISOLATION levels REPEATABLE-READ and SERIALIZABLE, InnoDB creates a snapshot for data which was received at the first time. This is necessary for multiversion concurrency control. At the same time such snapshots slow down those transactions which are used only for reads. For example, many users complained about catastrophic performance decrease after InnoDB reached 256 connections. And if for transactions, which modify data, such slowdown is necessary for better stability, for read-only transactions this is not fair.To solve this issue in version 5.6 new access modificator for transactions was introduced: READ ONLY or READ WRITE. Default is READ WRITE. We can modify it with help of START TRANSACTION READ ONLY, SET TRANSACTION READ ONLY queries or variables. InnoDB run tests which showed that usage of READ ONLY transaction completely solves 256 threads issue.If InnoDB is running in autocommit mode it considers data snapshot for SELECT queries is not needed and does not create it. This means SELECT performance in autocommit mode is same as if READ ONLY transactions are used. InnoDB Team published in their blog nice graphs with results of benchmarks of this new feature. Blog post is available here.8. Support of backup/restore of InnoDB Buffer Pool during restart and on demand.This feature can noticably speed up first few running hours of installations which use large InnoDB Buffer Pool. Earlier, when application with large InnoDB Buffer Pool started, some time, until queries filled the buffer, performance could be not so fast as it should. Now, we should not wait until the application execute all queries and fills up the pool: just backup it at shutdown, then restore at startup or in any time while MySQL server is running.9. Multithreaded slave.Prior version 5.6 slave could be catching up the master very slowly not because it runs on slow hardware, but due to its single-threaded nature. Now it can use up to 1024 parallel threads and catch up the master more easily.10. Possibility to execute events from binary log with N-seconds delay.This feature is opposite to previous one. Why did I put it in my troubleshooting improvements favorites list than? Because in addition to its main purposes, such as possibility to rollback DROP, DELETE (and other) operations we can also use this technique when master sends updates which slow down parallel reads executed by slave. For example, we can delay expensive ALTER TABLE until time when peak load on a web site passed. Because this option is set up on a slave, we can spread such expensive queries among them, so the application can use another slave for reads while one is busy.11. Partial images in RBR.This feature can increase replication performance dramatically.Today one of most popular row-based replication performance issues is long transferring time of large events. For example, if you have a row with LONGBLOB column, any modification of this row, even of an integer field, will require to send whole row. In other words, to modify 4 bytes master will send to slave up to 4G. Since version 5.6 it is possible to setup how to send rows in case of row-based replication.binlog_row_image=full will mimic 5.5 and earlier behavior: full row will be stored in the binary log file.binlog_row_image=minimal will store only modified dataandbinlog_row_image=noblob will store full row for all types except blobs 12. GET DIAGNOSTICS queries and access to Diagnostic Area.Diagnostic Area is a structure which filled after each query execution. It contains two kinds of information: query results, such as number of affected rows, and information about warnings and notes.Now we can access Diagnostic Area through GET DIAGNOSTICS queries.13.  HANDLERs processing is more like SQL standard now.I wrote about this two times already: here and hereSo I would not repeat my second post, just mention again I am finally happy with how HANDLERs work now.PERFORMANCE_SCHEMA improvementsThese are huge improvements!In version 5.5, when performance schema was introduced, I'd say it was mostly designed for MySQL developers themselves or at least for people who know MySQL source code very well. Now performance schema is for everybody.14. New instruments to watch IO operations.We can even watch IO operations for specific table. There are also new aggregation tables by these operations.15. New tables events_statements_* instrument statements.Now we can know what happens during query execution.Things like SQL text of the query, event name, corresponding to particular code and statistics of the query execution.16. Instruments for operation stages: events_stages_* tables.EVENT_NAME field of these tables contains information similar to content of SHOW PROCESSLIST, but field SOURCE has exact source code row there operations executed. This is very useful when you want to know what is going on. I strongly recommend you to study events_statements_* and events_stages_* tables17. New digests: for operators by account, user, host and so on.These are very useful for work with historical data.Of course, there are history tables, which we know since version 5.5, exist for both events_stages and events_statements.We can also filter information by users, sessions and tables.18. HOST_CACHE table.This table contains information about client host and IP address which are stored in the host cache, so MySQL server should not query DNS server second time.I am not sure why it is in Performance Schema and not in Information Schema, but maybe this is only me. I expect to get use of it when diagnosing connection failures.

MySQL 5.6 is in RC state now which means it is going to be GA sooner or later.This release contains a lot of improvements. However, since I am a support engineer, I most amazed by those which make...

MySQL

Performance Schema in version 5.6 helps to prevent SQL Injection attacks

There were few prominent SQL Injection attacks lately. Such breakages could be possible when an attacker finds that user data, sent to a SQL server, not properly sanitized.Usually successful attack is preceded by queries which cause parse errors. mysql> select thread_id, sql_text from events_statements_history_long where event_name='statement/sql/er'ror;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ror' at line 1 Easiest test to find out if website can be attacked is to send, using web form or address bar, a value which contains special symbol, say "'", like in the word "D'Artagnan", then analyse if it was properly handled. There are practices which web developer should use to avoid such situations. They include 100% sanitizing of all incoming data, allowing only expected values and rejecting any other or limitation of database accounts which web site uses. In latter case even if SQL injection happens, damage, which it can make, would be minimal.But all these means work at the design stage. What to do if you are responsible for large legacy project, so you can not be 100% sure everything is handled properly?In old time we could only suggest to log queries into general query log, then search for those which look suspicious. This hard job can be hardly automated: general query log does not contain information about query return code, therefore you have to guess which query could cause parse error yourself and not much automation can be applied.In version 5.6 MySQL introduced new tables in Performance Schema database.Performance schema in 5.6 became really user friendly and I am falling in love with it.A bunch of them, events_statements_*, can be useful for debugging possible SQL injection attacks.event_statements_* tables keep queries, which were executed in past, together with information about how they were proceeded. It includes: time, spent waiting for table locks how many temporary tables were created, splitting disk and memory-based tables which optimizer techniques were chosen statistics based on them. However, for SQL injection discussion, we are interested in diagnostic information, such as return SQLSTATE code, warning and error information.These fields can give the list of queries which were used by attackers to predict their chances to break the website.Performance schema has two special error-handling instruments: statement/com/Error for statements which were parsed successfully, but rejected thereafter and statement/sql/error for statements which were rejected at parsing state.In case of SQL Injection we should examine those statements, which were rejected at parsing state: statement/sql/error. They can easily found with help of this query: mysql> select thread_id, sql_text from events_statements_history_long where event_name='statement/sql/error'; +-----------+-------------------------------------------------------------------------------------+ | thread_id | sql_text                                                                            | +-----------+-------------------------------------------------------------------------------------+ |        16 | select * from events_statements_history_long wher event_name='statement/sql/error'  | |        16 | select * from events_statements_history_long here event_name='statement/sql/error'  | |        16 | select * from events_statements_history_long where event_name='statement/sql/er'ror | +-----------+-------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) In this example I specially made errors in queries I typed, but the idea can be derived. Usually application does not produce such queries at all: they all should be fixed at the development stage, therefore periodically checking for such queries can be good practice for determining possibility of SQL injection attacks.And to finish this topic I should mention another tool which can help to prevent SQL injection attacks even before they reach real database. This is Oracle Database Firewall. I'll copy part of its white paper here:Oracle Database Firewall monitors data access, enforces access policies, highlights anomalies and helps protect against network based attacks originating from outside or inside the organization. Attacks based on SQL injection can be blocked by comparing SQL against the approved white list of application SQL. Oracle Database Firewall is unique and offers organizations a first line of defense, protecting databases from threats and helping meet regulatory compliance requirement.With its unique feature of blocking access by analyzing all incoming SQL statements your database can be really protected. And amazingly it supports MySQL out of the box.References:MySQL user manual - http://dev.mysql.com/doc/refman/5.6/en/performance-schema-statements-tables.htmlMark Leith's blog - http://www.markleith.co.uk/ps_helper/#statements_with_errors_or_warningsOracle Database Firewall - http://www.oracle.com/technetwork/products/database-firewall/overview/index.html

There were few prominent SQL Injection attacks lately. Such breakages could be possible when an attacker finds that user data, sent to a SQL server, not properly sanitized.Usually successful attack is...

MySQL

Warning and error information in stored procedures revisited

Originally way to handle warnings and errors in MySQL stored routine was designed as follows: if warning was generated during stored routine execution which has a handler for such a warning/error, MySQL remembered the handler, ignored the warning and continued execution after routine is executed MySQL checked if there is a remembered handler and activated if any This logic was not ideal and causes several problems, particularly: it was not possible to choose right handler for an instruction which generated several warnings or errors, because only first one was chosen handling conditions in current scope messed with conditions in different there were no generated warning/errors in Diagnostic Area that is against SQL Standard. First try to fix this was done in version 5.5. Patch left Diagnostic Area intact after stored routine execution, but cleared it in the beginning of each statement which can generate warnings or to work with tables. Diagnostic Area checked after stored routine execution.This patch solved issue with order of condition handlers, but lead to new issues. Most popular was that outer stored routine could see warnings which should be already handled by handler inside inner stored routine, although latest has handler. I even had to wrote a blog post about it.And now I am happy to announce this behaviour changed third time.Since version 5.6 Diagnostic Area cleared after instruction leaves its handler.This lead to that only one handler will see condition it is supposed to proceed and in proper order. All past problems are solved.I am happy that my old blog post describing weird behaviour in version 5.5 is not true any more.

Originally way to handle warnings and errors in MySQL stored routine was designed as follows:if warning was generated during stored routine execution which has a handler for such a warning/error,...

MySQL

Story of success: MySQL Enterprise Backup (MEB) was successfully integrated with IBM Tivoli Storage Manager (TSM) via System Backup to Tape (SBT) interface.

Since version 3.6 MEB supports backups to tape through the SBT interface.The officially supported tool for such backups to tape is Oracle Secure Backup (OSB).But there are a lot of other Storage Managers. MEB allows to use them through the SBT interface. Since version 3.7 it also has option --sbt-environment which allows to pass environment variables, not needed by OSB, to third-party managers. At the same time MEB can not guarantee it would work with all of them.This month we were contacted by a customer who wanted to use IBM Tivoli Storage Manager (TSM) with MEB. We could only say them same thing I wrote in previous paragraph: this solution is supposed to work, but you have to be pioneers of this technology. And they agreed. They agreed to be the pioneers and so the story begins.MEB requires following options to be specified by those who want to connect it to SBT interface:--sbt-database-name: a name which should be handed over to SBT interface. This can be any name. Default, MySQL, works for most cases, so user is not required to specify this option.--sbt-lib-path: path to SBT library. For TSM this library comes with "Data Protection for Oracle", which, in its turn, interfaces with Oracle Recovery Manager (RMAN), which uses SBT interface. So you need to install it even if you don't use Oracle.--sbt-environment: environment for third-party manager. This option is not needed when you use OSB, but almost always necessary for third-party SBT managers. TSM requires variable TDPO_OPTFILE to be set and point to the TSM configuration file.--backup-image=sbt:: path to the image. Prefix "sbt:" indicates that image should be sent through SBT interfaceSo full command in our case would look like: ./mysqlbackup --port=3307 --protocol=tcp --user=backup_user --password=foobar \--backup-image=sbt:my-first-backup --sbt-lib-path=/usr/lib/libobk.so \--sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt" --backup-dir=/path/to/my/dir backup-to-imageAnd this command results in the following output log: MySQL Enterprise Backup version 3.7.1 [2012/02/16]Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved. INFO: Starting with following command line ...  ./mysqlbackup --port=3307 --protocol=tcp --user=backup_user        --password=foobar --backup-image=sbt:my-first-backup        --sbt-lib-path=/usr/lib/libobk.so        --sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt"        --backup-dir=/path/to/my/dir backup-to-image sbt-environment: 'TDPO_OPTFILE=/path/to/my/tdpo.opt'INFO: Got some server configuration information from running server. IMPORTANT: Please check that mysqlbackup run completes successfully.            At the end of a successful 'backup-to-image' run mysqlbackup            prints "mysqlbackup completed OK!". --------------------------------------------------------------------                       Server Repository Options:--------------------------------------------------------------------  datadir                          =  /path/to/data  innodb_data_home_dir             =  /path/to/data  innodb_data_file_path            =  ibdata1:2048M;ibdata2:2048M;ibdata3:64M:autoextend:max:2048M  innodb_log_group_home_dir        =  /path/to/data  innodb_log_files_in_group        =  2  innodb_log_file_size             =  268435456 --------------------------------------------------------------------                       Backup Config Options:--------------------------------------------------------------------  datadir                          =  /path/to/my/dir/datadir  innodb_data_home_dir             =  /path/to/my/dir/datadir  innodb_data_file_path            =  ibdata1:2048M;ibdata2:2048M;ibdata3:64M:autoextend:max:2048M  innodb_log_group_home_dir        =  /path/to/my/dir/datadir  innodb_log_files_in_group        =  2  innodb_log_file_size             =  268435456 Backup Image Path= sbt:my-first-backup mysqlbackup: INFO: Unique generated backup id for this is 13297406400663200120220 08:54:00 mysqlbackup: INFO: meb_sbt_session_open: MMS is 'Data Protection for Oracle: version 5.5.1.0'120220 08:54:00 mysqlbackup: INFO: meb_sbt_session_open: MMS version '5.5.1.0' mysqlbackup: INFO: Uses posix_fadvise() for performance optimization. mysqlbackup: INFO: System tablespace file format is Antelope. mysqlbackup: INFO: Found checkpoint at lsn 31668381. mysqlbackup: INFO: Starting log scan from lsn 31668224.120220  8:54:00 mysqlbackup: INFO: Copying log...120220  8:54:00 mysqlbackup: INFO: Log copied, lsn 31668381.          We wait 1 second before starting copying the data files...120220  8:54:01 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata1 (Antelope file format).mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000120220  8:55:30 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata2 (Antelope file format).mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000120220  8:57:18 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata3 (Antelope file format). mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.120220 08:57:22 mysqlbackup: INFO: Starting to lock all the tables....120220 08:57:22 mysqlbackup: INFO: All tables are locked and flushed to disk mysqlbackup: INFO: Opening backup source directory '/path/to/data/'120220 08:57:22 mysqlbackup: INFO: Starting to backup all files in subdirectories of '/path/to/data/' mysqlbackup: INFO: Backing up the database directory 'mysql' mysqlbackup: INFO: Backing up the database directory 'test' mysqlbackup: INFO: Copying innodb data and logs during final stage ... mysqlbackup: INFO: A copied database page was modified at 31668381.          (This is the highest lsn found on page)          Scanned log up to lsn 31670396.          Was able to parse the log up to lsn 31670396.          Maximum page number for a log record 328120220 08:57:23 mysqlbackup: INFO: All tables unlocked mysqlbackup: INFO: All MySQL tables were locked for 0.000 seconds120220 08:59:01 mysqlbackup: INFO: meb_sbt_backup_close: blocks: 4162  size: 1048576  bytes: 4363985063120220  8:59:01 mysqlbackup: INFO: Full backup completed! mysqlbackup: INFO: MySQL binlog position: filename bin_mysql.001453, position 2105 mysqlbackup: WARNING: backup-image already closed mysqlbackup: INFO: Backup image created successfully.:           Image Path: 'sbt:my-first-backup' -------------------------------------------------------------   Parameters Summary-------------------------------------------------------------   Start LSN                  : 31668224   End LSN                    : 31670396------------------------------------------------------------- mysqlbackup completed OK!Backup successfully completed.To restore it you should use same commands like you do for any other MEB image, but need to provide sbt* options as well: $./mysqlbackup --backup-image=sbt:my-first-backup --sbt-lib-path=/usr/lib/libobk.so \--sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt" --backup-dir=/path/to/my/dir image-to-backup-dirThen apply log as usual: $./mysqlbackup --backup-dir=/path/to/my/dir apply-logThen stop mysqld and finally copy-back: $./mysqlbackup --defaults-file=path/to/my.cnf --backup-dir=/path/to/my/dir copy-backDisclaimer. This is only story of one success which can be useful for someone else. MEB is not regularly tested and not guaranteed to work with IBM TSM or any other third-party storage manager.

Since version 3.6 MEB supports backups to tape through the SBT interface. The officially supported tool for such backups to tape is Oracle Secure Backup (OSB).But there are a lot of other Storage...

MySQL

Warning and error information in stored procedures

UPD: Information in this blog entry is not true since version 5.6. Please read revisited blog about warning and error information in stored procedures. I got this question second time this week, so I think it is worth writing a blog post about this topic.Question was "Why I get error in EVENT which calls a STORED PROCEDURE which already has HANDLER?"This changed in version 5.5 after fix of Bug #36185, Bug #5889, Bug #9857 and Bug #23032. Explanation is in this commit: The problem was in the way warnings/errors during stored routine execution were handled. Prior to this patch the logic was as follows: - when a warning/an error happens: if we're executing a stored routine, and there is a handler for that warning/error, remember the handler, ignore the warning/error and continue execution. - after a stored routine instruction is executed: check for a remembered handler and activate one (if any). This logic caused several problems: - if one instruction generates several warnings (errors) it's impossible to choose the right handler -- a handler for the first generated condition was chosen and remembered for activation. - mess with handling conditions in scopes different from the current one. - not putting generated warnings/errors into Warning Info (Diagnostic Area) is against The Standard. The patch changes the logic as follows: - Diagnostic Area is cleared on the beginning of each statement that either is able to generate warnings, or is able to work with tables. - at the end of a stored routine instruction, Diagnostic Area is left intact. - Diagnostic Area is checked after each stored routine instruction. If an instruction generates several condition, it's now possible to take a look at all of them and determine an appropriate handler. This means you will notice error which was last handled in stored routine after its execution.In case of EVENT error would be written to the error log file. Assume you have a table and two stored procedures: create table t1(f1 int);insert into t1 values (1), (2), (3), (4), (5);\\d |create procedure search_t1()begindeclare done int default 0;declare val int;declare cur cursor for select f1 from t1;declare continue handler for not found set done=1;open cur;repeatfetch cur into val;if not done thenselect val;end if;until done end repeat;select done;end|create procedure call_search()begincall search_t1();end| If you call call_search you will get a warning from search_t1: mysql> \\WShow warnings enabled.mysql> call call_search()|+------+| val  |+------+|    1 |+------+1 row in set (0.33 sec)...Query OK, 0 rows affected, 1 warning (0.33 sec)Error (Code 1329): No data - zero rows fetched, selected, or processed This happens because Diagnostic Area now is not cleared after stored procedure execution. One of advantages of this fix, in addition to what it is compatible with SQL Standard now, is you can handle errors from stored routines called inside another routine.Imagine you have same 2 routines, but defined as follow: create procedure search_t1()begin    declare val int;    declare cur cursor for select f1 from t1;        open cur;    repeat        fetch cur into val;        select val;    until 0=1 end repeat;end|create procedure call_search()begin    declare nf int default 0;    declare continue handler for not found set nf=1;    call search_t1();    select nf;end| Before version 5.5 call of call_search would fail: mysql> call call_search()|+------+| val |+------+| 1 |+------+1 row in set (0.01 sec)...+------+| val |+------+| 5 |+------+1 row in set (0.01 sec)ERROR 1329 (02000): No data - zero rows fetched, selected, or processed Pay attention no query after call of search_t1 was executed. Since version 5.5 same call would not fail: mysql> call call_search();+------+| val  |+------+|    1 |+------+1 row in set (0.12 sec)...+------+| val  |+------+|    5 |+------+1 row in set (0.12 sec)+------+| nf   |+------+|    1 |+------+1 row in set (0.12 sec)Query OK, 0 rows affected, 1 warning (0.12 sec) Of course this is just generic example and actually shows bad code practice, but real life can provide us not so generic errors

UPD: Information in this blog entry is not true since version 5.6. Please read revisited blog about warning and error information in stored procedures. I got this question second time this week, so I...

MySQL

Translation of Summary of Part 1 of "Methods for searching errors in SQL application" just published

There is not much new content this week. Just summary of what was written before. Summary. In the summary of this part I'd want to recommend following: Try to find a query which causes inconsistent behavior Use log files: General query log Log files in your application MySQL Proxy or any other suitable proxy Others Analyze what is wrong, then solve the problem using your findings Below I place a list of methods which we studied in the first part. Method #1: use output operator to output query in exactly same way in which RDBMS gets it. Method #2: use general query log if you need to find which exact query causes wrong behavior of your application. Method #3: after you found a query which causes the problem run it using command line client and analyze result. Method #4: try to modify SQL in such a way what you get correct result. Use search engines to find a workaround. Method #5: use EXPLAIN EXTENDED for finding how optimized (and executing) SQL query. Method #6: convert DML queries to corresponding SELECT to examine which rows will be modified. Method #7: repeat your scenario backward step-by-step until you found the problem query. Method #8: always check result of the query! Use means of your connector or interface of interactive client. Method #9: tune your application in such a way so it will write queries to log files itself. Method #10: use MySQL Proxy or any other proxy. Full text (which now is same as posted above) is here

There is not much new content this week. Just summary of what was written before. Summary.In the summary of this part I'd want to recommend following: Try to find a query which causes inconsistent...

MySQL

"Alternate methods for finding problem query.": chapter 5 of "Methods for searching errors in SQL application" just published

Translation of chapter 5 which describes alternate methods for searching problem query just published. It starts like: Chapter 5. Alternate methods for finding problem query. I already wrote about using of general query log requires resources.Part of the problem can be solved if use new feature of MySQL 5.1:online logging which allows to turn general query log to on or to offwithout stopping the MySQL server. Unfortunately this doesn't always work: you can have oldversion of MySQL server which has not such a feature, general query logcan contain very large amount of information, so you can just miss theproblem in thousands of correct queries, you can have some other ownreason. But what to do if you can not use general query log? One of the methods is write log files using your application.Add a code which will write queries which your application sends toMySQL to the log file. With this method you can tune how and what to write to the logfile. Would be good if you will write return value and error messagesin the same log file. Method #9: tune your application in such a way so it will write queries to log files itself. Yet another method is using proxy which will get queries and write them to the file. One of most preffered variants is MySQL Proxy, because this isscriptable proxy made for working with MySQL server. It uses MySQLclient-server protocol. You can write programs for MySQL Proxy usingLua programming language. Below you can see example how to write general query log using MySQL Proxy: ...  Rest of the chapter is here

Translation of chapter 5 which describes alternate methods for searching problem query just published. It starts like: Chapter 5. Alternate methods for finding problem query.I already wrote about...

MySQL

Chapter 3 of "Methods for searching errors in SQL application" has been published

Translation of "Chapter 3. Wrong data in database or what to do with problem DML query." of  "Methods for searching errors in SQL application" just published.  This is short chapter which discuss single method of dealing with mysterious DML query. Chapter 3. Wrong data in database or what to do with problem DML query. Problems with wrong data happens not only with SELECT queries likecases we discussed in chapter 2, but in cases of querires which modifydata: DML queries. Lets discuss example below mysql> create table t1(f1 int); Query OK, 0 rows affected (0.01 sec) mysql> create table t2(f2 int); Query OK, 0 rows affected (0.08 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.01 sec) mysql> select \* from t1; +------+ | f1   | +------+ |    1 |  +------+ 1 row in set (0.00 sec) mysql> delete from t1, t2 using t1, t2; Query OK, 0 rows affected (0.00 sec) User can expect what DELETE query will remove all rows from tablest1 and t2: "using t1, t2;" used and there is no WHERE clause. But asyou see this is not true. Please pay attentsion for a row which says "0 rows affected". This means what 0 rows were removed! But why? ....  Rest of the chapter is here Comments and translation corrections are always welcome.

Translation of "Chapter 3. Wrong data in database or what to do with problem DML query." of  "Methods for searching errors in SQL application" just published.  This is short chapter which discuss...

MySQL

How to raise error in your MySQL application

Recently I got this question twice. Although SIGNAL was implemented in version 6.0 (which is partially mysql-trunk now) this version is not stable yet, so users still need to use workaround.Here it is. Create 2 procedures as following: DROP PROCEDURE IF EXISTS raise_application_error;DROP PROCEDURE IF EXISTS get_last_custom_error;DROP TABLE IF EXISTS RAISE_ERROR;DELIMITER $$CREATE PROCEDURE raise_application_error(IN CODE INTEGER, IN MESSAGE VARCHAR(255)) SQL SECURITY INVOKER DETERMINISTICBEGIN  CREATE TEMPORARY TABLE IF NOT EXISTS RAISE_ERROR(F1 INT NOT NULL);  SELECT CODE, MESSAGE INTO @error_code, @error_message;  INSERT INTO RAISE_ERROR VALUES(NULL);END;$$CREATE PROCEDURE get_last_custom_error() SQL SECURITY INVOKER DETERMINISTICBEGIN  SELECT @error_code, @error_message;END;$$DELIMITER ; You can use them as:CALL raise_application_error(1234, 'Custom message');CALL get_last_custom_error(); Example: table which stores only odd numbers.DROP TABLE IF EXISTS ex1;DROP TRIGGER IF EXISTS ex1_bi;DROP TRIGGER IF EXISTS ex1_bu;CREATE TABLE ex1(only_odd_numbers INT UNSIGNED);DELIMITER $$CREATE TRIGGER ex1_bi BEFORE INSERT ON ex1 FOR EACH ROWBEGIN   IF NEW.only_odd_numbers%2 != 0 THEN    CALL raise_application_error(3001, 'Not odd number!');  END IF;END$$CREATE TRIGGER ex1_bu BEFORE UPDATE ON ex1 FOR EACH ROWBEGIN   IF NEW.only_odd_numbers%2 != 0 THEN    CALL raise_application_error(3001, 'Not odd number!');  END IF;END$$DELIMITER ; Usage: mysql> INSERT INTO ex1 VALUES(2);Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO ex1 VALUES(3);ERROR 1048 (23000): Column 'F1' cannot be nullmysql> CALL get_last_custom_error();+-------------+-----------------+| @error_code | @error_message  |+-------------+-----------------+| 3001        | Not odd number! | +-------------+-----------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> SELECT \* FROM ex1;+------------------+| only_odd_numbers |+------------------+|                2 | +------------------+1 row in set (0.00 sec)

Recently I got this question twice. Although SIGNAL was implemented in version 6.0 (which is partially mysql-trunk now) this version is not stable yet, so users still need to use workaround.Here it...

MySQL

Why EXPLAIN runs forever

Sometimes you need to run EXPLAIN on long running queries. Most time EXPLAIN takes few seconds, but sometimes it looks like it executes query itself instead of using statistic. Like in the example following: mysql> explain select \* from (select sleep(10) as foo) bar;+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra          |+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |                | |  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used | +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+2 rows in set (10.02 sec) Look at the time: 10.02 sec for such easy query. This happens because query in the subquery executes first, then executes whole statement. If rewrite query in a way what it does not use subquery EXPLAIN would work fast as usual: mysql> explain select sleep(10) as foo;+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+1 row in set (0.01 sec) For not generic this will mean you should replace subquery with JOIN.

Sometimes you need to run EXPLAIN on long running queries. Most time EXPLAIN takes few seconds, but sometimes it looks like it executes query itself instead of using statistic. Like in the example...

MySQL

Why you can not connect to your fresh installed MySQL database.

I read in Russian the talk which I presented at MySQL Conference & Expo this year. Really it was not exactly same, but some parts existed in both. And there was one of my friends who heard the talk in Russian at the conference. He didn't attend English version, but when it finished he came and asked: "Did you say the most important?" "The most important what?" "Why people can not connect to MySQL" So here is most important part of my talk :) And if go through our bugs database you will see enormous quantity of bug reports with similar synopsis: "I can not connect to my fresh installed MySQL". Why does it happen? Major reason is localhost has special meaning. This problem can look different, but has same route. Favorite problem of Windows users, especcially ones who love GUI Tools is default host. Default host in MySQL is %, but not localhost, neither 127.0.0.1. So if you CREATE USER `abc` IDENTIFIED BY 'xyz'; you really create user `abc`@`%`. Af first glance it looks like you are safe for "Access denied" errors, but MySQL sorts its privilege tables in such a way what more exact address is first and less exact is last and if you have default anonymous user (and you have unless you had run script mysql_secure_installation or manually edited privilege tables) this user would be chosen if you connect as abc from localhost Favorite problem of UNIX users, especcially ones who use Windows as developer's machine and UNIX on production, is mix of localhost and 127.0.0.1. Assuming you have user test@'127.0.0.1' identified by 'password'; and trying to connect from the same machine MySQL is installed on. Again you will get "Access denied" error, because localhost on UNIX has special meaning and used by default even if you specify --port option. Workaround here is force mysql to use protocol TCP/IP with option --protocol=TCP/IP or --host=127.0.0.1 And last but not least popular reason for "Access denied" errors is mixing name-based and IP-based hostnames for the same username, then specifying different privileges for these users. Which privilege MySQL chooses depends from sort order of mysql.\* tables, but in such cases this can be unpredictable and can create bad headaches, especcially in case of upgrade. Better just don't mix them.

I read in Russian the talk which I presented at MySQL Conference & Expo this year. Really it was not exactly same, but some parts existed in both. And there was one of my friends who heard the talk in...

Oracle

Integrated Cloud Applications & Platform Services