Planet MySQL

Restore a table / database from full backup – Yet another way

Restore requests are common and so are the restores of specific entities: a database, or one or more table(s). This has been discussed a lot and we have plenty of tools and solutions already available.
In this blog post we will cover an interesting solution that I came across when I received a restoration request from a client with a specific scenario.

The scenario? Well, the client was on a Windows server with 400GB of mysqldump and wanted to restore a table.

As Linux players we already know of some tools and techniques to export a table or database from mysqldump – for example, using sed command or using the script mysqldumpsplitter (based on sed itself). But on Windows we are powerless by not being able to use sed (we’re sad without sed.) Also, there was no cygwin to ease up the pain.

We had to come-up with a solution that works on Windows as well. During this discussion, my Pythian colleague, Romuller, suggested a cool but simple trick which enlightens us and offers one more way of exporting or recovering a table from a full mysqldump.

So the trick here is as follows:
– Create a user that has very specific grants, limited to one or more table(s) or database(s) that we need to restore.
– Load mysqldump into the database with that user provide with –force. The option –force will ignore all the errors that will occur due to lack of privileges of the new user we created specifically for restore.

Easy right? Database Consultants like to KISS ;).

Let’s give it a try.

I selected a table “stories” & create the “bad” situation by dropping that table.

mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------------+ | Tables_in_test | +------------------------+ ... | stories | ... +------------------------+ mysql> select count(*) from stories; +----------+ | count(*) | +----------+ | 881 | +----------+ 1 row in set (0.02 sec) mysql> drop table stories; Query OK, 0 rows affected (0.29 sec)

Let’s begin the recovery phase now following the grants method.
1. Create the user with limited grants only on test.stories table.

mysql> grant all privileges on test.stories to 'stories'@localhost identified by 'X'; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

Wait, there is a warning. We see this warning in MySQL 5.7.6 onward and it says GRANT commands will be deprecated in favour of CREATE USER statement to create new users. So, we shall have following practice to be ready for MySQL 8 :)

CREATE USER 'stories'@’localhost’ identified with mysql_native_password by ‘X'; grant all privileges on test.stories to 'stories'@'localhost';

2. Load the mysqldump using the same user with –force.

[root@mysql1c ~]# cat fuldump.sql | mysql -ustories -pX test --force mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1044 (42000) at line 22: Access denied for user 'stories'@'localhost' to database 'archive' ... ERROR 1142 (42000) at line 420: ALTER command denied to user 'stories'@'localhost' for table 'emp_new' ... ERROR 1142 (42000) at line 1966: ALTER command denied to user 'stories'@'localhost' for table 'user_address'

3. Verify table is restored:

mysql> show tables; +------------------------+ | Tables_in_test | +------------------------+ ... | stories | ... +------------------------+ mysql> select count(*) from stories; +----------+ | count(*) | +----------+ | 881 | +----------+ 1 row in set (0.00 sec)

Conclusion:
When you compare the table that is being restored to the other one, mysqldump is smaller. This method may take a lot of time just ignoring errors due to –force option. Of course, in most cases you will end up reading the whole file. If our table appears early in the mysqldump, we may monitor the progress and kill the process as well. Otherwise, it may make more sense to try and install Cygwin or move the backup to a Linux Box to extract a database object from the backup file.

Hope this helps.

Percona Toolkit 3.0.9 Is Now Available

Percona announces the release of Percona Toolkit 3.0.9 on April 20, 2018.

Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL®, MongoDB® and system tasks that are too difficult or complex to perform manually. With over 1,000,000 downloads, Percona Toolkit supports Percona Server for MySQL, MySQL, MariaDB®, Percona Server for MongoDB and MongoDB.

Percona Toolkit, like all Percona software, is free and open source. You can download packages from the website or install from official repositories.

This release includes the following changes:

New Tools:
  • PT-1501: pt-secure-collect – new tool to collect and sanitize pt-tools outputs
New Features:
  • PT-1530: Add support for encryption status to pt-mysql-summary
  • PT-1526: Add ndb status to pt-mysql-summary (Thanks Fernando Ipar)
  • PT-1525: Add support for MySQL 8 roles into pt-mysql-summary
  • PT-1509: Make pt-table-sync only set binlog_format when necessary (Thanks Moritz Lenz)
  • PT-1508: Add --read-only-interval and --fail-successive-errors flags to pt-heartbeat (Thanks Shlomi Noach)
  • PT-243: Add --max-hostname-length and --max-line-length flags to pt-query-digest
Bug Fixes:
  • PT-1527: Fixed pt-table-checksum ignores --nocheck-binlog-format
Improvements:
  • PT-1507: pt-summary does not reliably read in the transparent huge pages setting (Thanks Nick Veenhof)
  • PT-1488: pt-show-grants support for MySQL 8.0

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.

The post Percona Toolkit 3.0.9 Is Now Available appeared first on Percona Database Performance Blog.

Percona Monitoring and Management (PMM) 1.10.0 Is Now Available

Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL® and MongoDB® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL® and MongoDB® servers to ensure that your data works as efficiently as possible.

We focused mainly on two features in 1.10.0, but there are also several notable improvements worth highlighting:

  • Annotations – Record and display Application Events as Annotations using pmm-admin annotate
  • Grafana 5.0 – Improved visualization effects
  • Switching between Dashboards – Restored functionality to preserve host when switching dashboards
  • New Percona XtraDB Cluster Overview graphs – Added Galera Replication Latency graphs on Percona XtraDB Cluster Overview dashboard with consistent colors

The issues in the release include four new features & improvements, and eight bugs fixed.

Annotations

Application events are one of the contributors to changes in database performance characteristics, and in this release PMM now supports receiving events and displaying them as Annotations using the new command pmm-admin annotate. A recent Percona survey reveals that Database and DevOps Engineers highly value visibility into the Application layer.  By displaying Application Events on top of your PMM graphs, Engineers can now correlate Application Events (common cases: Application Deploys, Outages, and Upgrades) against Database and System level metric changes.

Usage

For example, you have just completed an Application deployment to version 1.2, which is relevant to UI only, so you want to set tags for the version and interface impacted:

pmm-admin annotate "Application deploy v1.2" --tags "UI, v1.2"

Using the optional --tags allows you to filter which Annotations are displayed on the dashboard via a toggle option.  Read more about Annotations utilization in the Documentation.

Grafana 5.0

We’re extremely pleased to see Grafana ship 5.0 and we were fortunate enough to be at Grafanacon, including Percona’s very own Dimitri Vanoverbeke (Dim0) who presented What we Learned Integrating Grafana and Prometheus!

 

 

Included in Grafana 5.0 are a number of dramatic improvements, which in future Percona Monitoring and Management releases we plan to extend our usage of each feature, and the one we like best is the virtually unlimited way you can size and shape graphs.  No longer are you bound by panel constraints to keep all objects at the same fixed height!  This improvement indirectly addresses the visualization error in PMM Server where some graphs would appear to be on two lines and ended up wasting screen space.

Switching between Dashboards

PMM now allows you to navigate between dashboards while maintaining the same host under observation, so that for example you can start on MySQL Overview looking at host serverA, switch to MySQL InnoDB Advanced dashboard and continue looking at serverA, thus saving you a few clicks in the interface.

New Percona XtraDB Cluster Galera Replication Latency Graphs

We have added new Percona XtraDB Cluster Replication Latency graphs on our Percona XtraDB Cluster Galera Cluster Overview dashboard so that you can compare latency across all members in a cluster in one view.

Issues in this release New Features & Improvements
  • PMM-2330 – Application Annotations DOC Update
  • PMM-2332 – Grafana 5 DOC Update
  • PMM-2293 – Add Galera Replication Latency Graph to Dashboard PXC/Galera Cluster Overview RC Ready
  • PMM-2295 – Improve color selection on Dashboard PXC/Galera Cluster Overview RC Ready
Bugs fixed
  • PMM-2311 – Fix misalignment in Query Analytics Metrics table RC Ready
  • PMM-2341 – Typo in text on password page of OVF RC Ready
  • PMM-2359 – Trim leading and trailing whitespaces for all fields on AWS/OVF Installation wizard RC Ready
  • PMM-2360 – Include a “What’s new?” link for Update widget RC Ready
  • PMM-2346 – Arithmetic on InnoDB AHI Graphs are invalid DOC Update
  • PMM-2364 – QPS are wrong in QAN RC Ready
  • PMM-2388 – Query Analytics does not render fingerprint section in some cases DOC Update
  • PMM-2371 – Pass host when switching between Dashboards
How to get PMM

PMM is available for installation using three methods:

Help us improve our software quality by reporting any Percona Monitoring and Management bugs you encounter using our bug tracking system.

The post Percona Monitoring and Management (PMM) 1.10.0 Is Now Available appeared first on Percona Database Performance Blog.

Fun with caching_sha2_password in MySQL 8.0.11


I want to get benchmark numbers with MySQL 8.0.11. This is my first impression. The default auth method was changed to caching_sha2_password. See this post for more details. There will be some confusion with this change. By confusion I mean the difference between "error" and "OK because cached" below. I am not alone. See the experience that an expert had with replication.

Fun with caching_sha2_password occurs even with clients compiled as part of 8.0.11:

  1. install MySQL 8.0.11, disable SSL but use mostly default my.cnf
  2. bin/mysql -u... -p... -h127.0.0.1 -e ... -> error
  3. bin/mysql -u... -p... -e ... -> OK
  4. bin/mysql -u... -p... -h127.0.0.1 -e ... -> OK because cached

The error in step 2 is: ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

From show global variables I see the default is caching_sha2_password:

default_authentication_plugin   caching_sha2_password Setting this in my.cnf after I created the user doesn't fix the problem. Setting this before creating the user is one fix. I did not test whether changing the value of user.plugin to "mysql_native_password" is another workaround. default_authentication_plugin=mysql_native_passwordThe error when using an old mysql client will also be a source of confusion:
$ ~/b/orig5635/bin/mysql -u... -p.. -h127.0.0.1
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /home/mdcallag/b/orig5635/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory 

MySQL 8.0 official version is released

The MySQL Server Team has announced the General Availability of MySQL 8.0. You can download it here. Quoting their blog post, some key enhancements include: SQL Window functions, Common Table Expressions, NOWAIT and SKIP LOCKED, Descending Indexes, Grouping, Regular Expressions, Character Sets, Cost Model, and Histograms. JSON Extended syntax, new functions, improved sorting, and partial

No orange pants this year

Well here we go another MySQL conference / Percona Live.

Another huge and important event/milestone for the MySQL community. 

But this time I will not be there.

First time in many years I had to decline, drop my speech and say... "No I am so sorry, this time I cannot come".

Was not an easy choice, not only because I am always excited to meet old colleagues, but also because PLSC is a great moment for brainstorming and to identify what could be good to push-on or to investigate better.

Especially this year where we have so many different interesting topics and so many different technologies as well. 

And of course the MySQL 8 GA will make a huge wave, but that was expected. To be honest what I am more interested to see is... what the real adoption of it will means. In Percona, we are working to be able to have it's adoption to happen as smoother as possible.

 

So I will really miss to be there, my hope is to be able to see the videos and get at least the presentations slides, to review the contents offline... but I will miss all the interactions and Q/A.

Anyhow I went through the schedule, and this is the list of speeches I would like to go listen and raise questions, knowing me ... not fix in the stone at all but you know is a start:

 

day 1

Make Your Database Dream of Electric Sheep: Designing for Autonomous Operation

MySQL at Twitter: No More Forkin' - Migrating to MySQL Community version

Containerizing Databases at New Relic: What We Learned

Tuning PostgreSQL for High-Write Workloads

Consistent Reads Using ProxySQL and GTID

Benchmark Noise Reduction: How to Configure Your Machines for Stable Results

Cassandra on RocksDB

ClickHouse in Real Life: Case Studies and Best Practices

 

day 2

Migrating to Vitess at (Slack) Scale

Stateful applications on Mesosphere DC/OS

Aurora PostgreSQL Deep Dive

Data Management in Kubernetes Using Kanister

A Seat At the Blockchain and Cryptocurrency Table for NoSQL Database Technologies

 

I whish to all of you to enjoy the conference, remember ask ask ask, the more you will interact with the speaker during the sessions, the better the presentation will be for all.

Hope to see you all in PL Europe in Germany and in PL 2019 

Have fun!!! And learn! 

SSL Encryption at ProxySQL Part 1

As we all know, MySQL supports using SSL to secure connections by encrypting the data in transit and protecting it from snooping on the wire.

As of now, since version v1.2.0e, ProxySQL supports SSL connections to the backends. Frontend SSL is enabled by default from version 2.0, which is under development.
Even if frontend SSL is not available before 2.0 version , there is a way to secure frontend traffic by deploying ProxySQL with app hosts and use sockets instead of tcp.

This document will cover how to integrate ProxySQL Backend SSL with MySQL to use an encrypted connection.

Enable Encryption on MySQL

Configuring MySQL for Encrypted Connections is out of the scope of this article, used information found in the following link to enable SSL on server.
https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html

First, we must check if MySQL server supports SSL connections.
Log into MySQL…

mysql> show global variables like '%ssl%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | ca.pem | | ssl_capath | | | ssl_cert | server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | server-key.pem | +---------------+-----------------+

Create a user to permit only SSL-encrypted connection on MySQL Server.

GRANT ALL PRIVILEGES ON *.* TO 'sysbench'@'172.16.1.%' IDENTIFIED BY 'sysbench' REQUIRE SSL;

Test a secure connection:

root@proxysql-host:/data/benchmark/v2.0# mysql -usysbench -psysbench -h 172.16.1.102 -P3306 -e '\s' | grep SSL SSL: Cipher in use is DHE-RSA-AES256-SHA

As ProxySQL is forwarding traffic to all backend servers, we need to keep the same *.pem files on all database instances.
You can copy below following files from any DB node to all backends.
Remember that you have to change their ownership from root user to mysql.

root@mysql-host:/data/mysql# ls -lhtr *.pem -rw-r--r-- 1 mysql mysql 1.1K Mar 22 08:07 ca.pem -rw------- 1 mysql mysql 1.7K Mar 22 08:07 ca-key.pem -rw------- 1 mysql mysql 1.7K Mar 22 08:07 server-key.pem -rw-r--r-- 1 mysql mysql 1.1K Mar 22 08:07 server-cert.pem -rw------- 1 mysql mysql 1.7K Mar 22 08:07 client-key.pem -rw-r--r-- 1 mysql mysql 1.1K Mar 22 08:07 client-cert.pem -rw-r--r-- 1 mysql mysql 452 Mar 22 08:07 public_key.pem -rw------- 1 mysql mysql 1.7K Mar 22 08:07 private_key.pem

Once you are done, restart MySQL servers.

Enable Encryption on ProxySQL

At this stage, connection attempts to host 127.0.0.1 and port 6033 will not use SSL because no key and no certificate has been configured. Instead, normal non-SSL connections will be established.
We must now transfer ca.pem, client-cert.pem, and client-key.pem to ProxySQL server under folder /var/lib/proxysql/

mysql> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql%ssl%'; +-------------------------------------+----------------+ | variable_name | variable_value | +-------------------------------------+----------------+ | mysql-have_ssl | false | | mysql-session_idle_show_processlist | true | | mysql-ssl_p2s_ca | (null) | | mysql-ssl_p2s_cert | (null) | | mysql-ssl_p2s_key | (null) | | mysql-ssl_p2s_cipher | (null) | +-------------------------------------+----------------+ 6 rows in set (0.00 sec)

Currently, as seen in the ProxySQL configuration, SSL-related variables are not defined. We will have to change this.

UPDATE mysql_servers SET use_ssl=1 WHERE port=3306; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

First of all, we have to tell ProxySQL that our backend nodes use SSL. Setting ‘use_ssl’ column in mysql_servers will do the trick. Remember that you have to load the changed configuration to runtime and eventually save it to disk.

Let’s see how it looks like now:

mysql> select hostgroup_id,hostname,port,status,max_connections,use_ssl from mysql_servers; +--------------+--------------+------+--------+-----------------+---------+ | hostgroup_id | hostname | port | status | max_connections | use_ssl | +--------------+--------------+------+--------+-----------------+---------+ | 0 | 172.16.1.103 | 3306 | ONLINE | 3000 | 1 | | 0 | 172.16.1.104 | 3306 | ONLINE | 3000 | 1 | | 0 | 172.16.1.105 | 3306 | ONLINE | 3000 | 1 | | 0 | 172.16.1.102 | 3306 | ONLINE | 3000 | 1 | +--------------+--------------+------+--------+-----------------+---------+ 4 rows in set (0.00 sec)

As can be seen above, all looks good. Now it’s time to configure ProxySQL to use keys and certificates that will allow it to connect to the backend using SSL:

SET mysql-ssl_p2s_cert="/var/lib/proxysql/client-cert.pem"; SET mysql-ssl_p2s_key="/var/lib/proxysql/client-key.pem"; SET mysql-ssl_p2s_ca="/var/lib/proxysql/ca.pem"; SET mysql-ssl_p2s_cipher='ECDHE-RSA-AES256-SHA'; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

Again, after setting up those variables remember to load the configuration to runtime and save it to persistent storage. Let’s see how it looks like now:

mysql> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql%ssl%'; +-------------------------------------+-----------------------------------+ | variable_name | variable_value | +-------------------------------------+-----------------------------------+ | mysql-have_ssl | false | | mysql-session_idle_show_processlist | true | | mysql-ssl_p2s_ca | /var/lib/proxysql/ca.pem | | mysql-ssl_p2s_cert | /var/lib/proxysql/client-cert.pem | | mysql-ssl_p2s_key | /var/lib/proxysql/client-key.pem | | mysql-ssl_p2s_cipher | ECDHE-RSA-AES256-SHA | +-------------------------------------+-----------------------------------+ 6 rows in set (0.00 sec)

Everything looks as expected.
Once we are done with above changes, we have to restart ProxySQL service.

At this point, all new connections to host 127.0.0.1 and port 6033 will use SSL.
We can verify this by executing below commands:

root@sysbench-host:/data/benchmark/v2.0# mysql -h127.0.0.1 -P6033 -usysbench -psysbench mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher'; +---------------+----------------------+ | Variable_name | Value | +---------------+----------------------+ | Ssl_cipher | ECDHE-RSA-AES256-SHA | +---------------+----------------------+ 1 row in set (0.00 sec)

Using SSL creates some overhead and to understand how big it is we performed a simple read-only test using sysbench against ProxySQL with backend SSL enabled and disabled. ProxySQL was configured to use 4 internal threads and we are happy to announce that results are quite consistent.

For the reference, we used following sysbench command :

for conn in 1 8 128 ; do for i in $(seq 1 3) ; do echo "${conn}:${i}"; ulimit -n 1048576; ./src/sysbench ./src/lua/oltp_read_only.lua --db-debug=on --report-interval=20 --table-size=70000000 --tables=20 --mysql-db=sbtest_rw --mysql-user=sysbench --mysql-password=sysbench --db-driver=mysql --mysql-host=127.0.0.1 --max-requests=0 --mysql-port=6033 --db-ps-mode=disable --skip-trx=on --threads=${conn} --max-time=60 run ; ulimit -n 1048576; ./src/sysbench ./src/lua/oltp_read_only.lua --db-debug=on --report-interval=20 --table-size=70000000 --tables=20 --mysql-db=sbtest_rw --mysql-user=sysbench --mysql-password=sysbench --db-driver=mysql --mysql-host=127.0.0.1 --max-requests=0 --mysql-port=6033 --db-ps-mode=disable --skip-trx=on --threads=${conn} --max-time=120 run |tee /data/benchmark/v2.0/v2.0_4threads${conn}connections.${i}_line.log ; done ; done

For more details : https://github.com/sysown/proxysql/wiki/SSL-Support

Authored by : Ashwini Ahire

Backup of External Tablespaces in MySQL 8.0

Introduction

We can categorize the InnoDB tablespaces created by users, broadly in following categories.

  1. File-per-table Tablespace - A single, file-per-table tablespace created in the datadir, which contains only one table.
  2. General Tablespace   - A shared tablespace created in the datadir, which may contain multiple tables.
  3. External File-per-table Tablespace - A single, file-per-table tablespace created outside of the datadir, which contains only one table.
  4. External General Tablespace - A shared tablespace created outside of the datadir, which may contain multiple tables.

External tablespaces are sometimes called remote tablespaces, but both refer to a single or shared, file-per-table tablespace outside of the datadir.

Prior to MySQL 8.0, the server creates an InnoDB Symbolic Link (*.isl) file in the datadir to locate the external tablespaces. MEB copies the *.isl files as *.bl files into the backup for those MySQL server versions.  At the time of copy back, MEB copies the *.bl files to the target datadir as the *.isl files. It then copies the corresponding external tablespaces to the location pointed by the *.isl files.

In MySQL 8.0, the use of *.isl files has been removed for InnoDB, as per WL#6416.

This change raises the question: how would MEB find the external tablespaces and copy them back to their respective locations? This article attempts to answer these questions.

tablespaces_tracker file

MEB has introduced a new file in the backup named 'tablespaces_tracker'. It is a JSON file that is created only if at least one external tablespace is included in the backup. The file contains metadata about the backed-up external tablespaces.  The file contains an array named "tablespace_list", which contains a list of the external tablespace objects and the following properties for each external tablespace.

  • "server_file_path" : External tablespace location at the sever
  • "backup_file_path" :  External tablespace location in the backup
  • "space_id”: A tablespace identifier.
  • "Flags”:  As of now these are the possible flag values.
  • GENERAL: If it is a shared tablespace.
  • SINGLE: If it is a file-per-table tablespace.
  • REMOTE: If it is an external tablespace.

The file is present in the datadir of the backup. At the time of copy-back operation, it is copied to the server datadir.

Backup of external tablespaces

In MySQL 8.0, the only source of truth for tablespace information is the transactional data dictionary.  InnoDB keeps the metadata about all tablespaces in the data dictionary tables. Thanks to the new data dictionary, querying of the INFORMATION_SCHEMA has also improved tremendously.

MEB queries the information schema to find out the information about all tablespaces to be backed up.  It then locates all tablespaces on the server and copies them to the backup.

Now, how will MEB be able to copy back the external tablespaces to their respective target locations given that copy-back is an offline operation (except the TTS backups)?  Well, the answer is, with the help of 'tablespaces_tracker' file.

The following is a discussion of a backup of external tablespaces.

  1. Start the MySQL server with an external directory explicitly specified with the --innodb_directories option. Refer to the server manual for information on this option.

 mysqld.exe --no-defaults --datadir=D:\trunk_datadir  --console --innodb_directories='D:\rsisondi\80'

  1. Create the following two external tablespaces:

mysql> CREATE TABLESPACE 80_rem_gen_ddl_ts1  ADD DATAFILE  'D:/rsisondi/80/80_rem_gents1.ibd';

Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE 80_extern_table (c1 INT) ENGINE=InnoDB  DATA DIRECTORY = 'D:/rsisondi/80';

Query OK, 0 rows affected (0.11 sec)

  1. Take a simple directory backup:

> mysqlbackup.exe -ubackup_user --backup-dir=D:\rsisondi\backup backup

MySQL Enterprise Backup version 8.0.12-tr Windows-10.0.14393-AMD64 [2018-04-02  09:01:58]

Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.

A thread created with Id '38700'

Starting with following command line ...

 mysqlbackup.exe –ubackup_user --backup-dir=D:\rsisondi\backup backup

 

<snip>

 

-------------------------------------------------------------

   Parameters Summary        

-------------------------------------------------------------

   Start LSN                  : 19189248

   End LSN                    : 19189656

-------------------------------------------------------------

 

mysqlbackup completed OK!

  1. Navigate to the backup directory and notice the file 'tablespaces_tracker'. It has the metadata about the external tablespaces JSON format as shown below:

{

    "version": 1.0,

    "HowTo": "Update server_file_path if and only if it is needed for each tablespace to restore an external tablespace to a different location; make sure correct database folder is given for each per-table tablespace.",

    "tablespace_list": [

        {

            "server_file_path": "D:/rsisondi/80/80_rem_gents1.ibd",

            "backup_file_path": "D:/rsisondi/backup/datadir/meb#8_80_rem_gents1.ibd",

            "space_id": 8,

            "Flags": [

                "GENERAL",

                "REMOTE"

            ]

        },

        {

            "server_file_path": "D:/rsisondi/80/test_80/80_extern_table.ibd",

            "backup_file_path": "D:/rsisondi/backup/datadir/test_80/80_extern_table.ibd",

            "space_id": 9,

            "Flags": [

                "SINGLE",

                "REMOTE"

            ]

        }

    ]

Notice from above that the file name on the server is 80_remote_gents1.ibd but it has been renamed in the backup. InnoDB allows creation of shared tablespaces with the same name at different locations. This could cause conflicts during directory backups as MEB copies all shared tablespaces into the datadir inside backup. Therefore, a prefix meb#<space_id>_ is appended to the general tablespaces file names in the backup.

  1. Next, drop an existing external tablespace and create a new.

mysql> DROP TABLE 80_extern_table;

Query OK, 0 rows affected (0.17 sec)

 

mysql> CREATE TABLE 80_new_extern_table (c1 INT) ENGINE=InnoDB DATA DIRECTORY = 'D:/rsisondi/80';

Query OK, 0 rows affected (0.07 sec)

 

  1. Take a simple incremental directory backup

>  mysqlbackup.exe -ubackup_user backup --incremental --incremental-backup-dir=D:\rsisondi\incr --incremental-base=history:last_backup

MySQL Enterprise Backup version 8.0.12-tr Windows-10.0.14393-AMD64 [2018-04-02  09:01:58]

Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.

A thread created with Id '36608'

Starting with following command line ...

 mysqlbackup.exe -ubackup  backup --incremental

        --incremental-backup-dir=D:\rsisondi\incr

        --incremental-base=history:last_backup

 

            <snip>

 

-------------------------------------------------------------

   Parameters Summary        

-------------------------------------------------------------

   Start LSN                  : 19189657

   End LSN                    : 19218134

-------------------------------------------------------------

 

           mysqlbackup completed OK! 

  1. Navigate to the datadir in the backup directory and notice the file 'tablespaces_tracker’.  Notice that the information about the dropped table is not present in the tracker file while the information about the newly added file is present.

{

    "version": 1.0,

    "HowTo": "Update server_file_path if and only if it is needed for each tablespace to restore an external tablespace to a different         location; make sure correct database folder is given for each per-table tablespace.",

    "tablespace_list": [

        {

            "server_file_path": "D:/rsisondi/80/80_rem_gents1.ibd",

            "backup_file_path": "D:/rsisondi/incr/datadir/meb#8_80_rem_gents1.ibd",

            "space_id": 8,

            "Flags": [

                "GENERAL",

                "REMOTE"

            ]

        },

        {

            "server_file_path": "D:/rsisondi/80/test_80/80_new_extern_table.ibd",

            "backup_file_path": "D:/rsisondi/incr/datadir/test_80/80_new_extern_table.ibd",

            "space_id": 10,

            "Flags": [

                "SINGLE",

                "REMOTE"

            ]

        }

    ]

}

Copy-back of external tablespaces

The copy-back operation of external tablespaces relies solely on the ‘tablespaces_tracker’ file.  As mentioned before, the ‘tablespaces_tracker’ file is copied into the target datadir as well. It helps MEB to resolve external tablespaces conflicts during the copy-back operation from the incremental backups into the target datadir. For instance, some external tablespaces could have been created/renamed/dropped after the previous backup and before the incremental backup.

Therefore, you should never delete the 'tablesapces_tracker' file from either the backup or datadir after a copy-back operation.

Let us perform a copy-back operation with the backups taken in the previous section.

  1. Stop the server and move/rename the previous external tablespaces directories in case they are still present.
  1. Restore the full backup:

> mysqlbackup.exe -ubackup_user  --backup-dir=D:\rsisondi\backup copy-back-and-apply-log --datadir=D:\rsisondi\datadir

MySQL Enterprise Backup version 8.0.12-tr Windows-10.0.14393-AMD64 [2018-04-02  09:01:58]

Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.

A thread created with Id '27600'

Starting with following command line ...

mysqlbackup.exe –ubackup_user --backup-dir=D:\rsisondi\backup 

        copy-back-and-apply-log --datadir=D:\rsisondi\datadir

 

<snip>

 

180402 20:52:32 MAIN    INFO: Apply-log operation completed successfully.

180402 20:52:32 MAIN    INFO: Full Backup has been restored successfully.

 

mysqlbackup completed OK!

  1. Notice the ‘tablespaces_tracker’ file in the target datadir. It has the same content as it does in the backup.

{

    "version": 1.0,

    "HowTo": "Update server_file_path if and only if it is needed for each tablespace to restore an external tablespace to a different         location; make sure correct database folder is given for each per-table tablespace.",

    "tablespace_list": [

        {

            "server_file_path": "D:/rsisondi/backup/datadir/meb#8_80_rem_gents1.ibd",

            "backup_file_path": "D:/rsisondi/80/80_rem_gents1.ibd",

            "space_id": 8,

            "Flags": [

                "GENERAL",

                "REMOTE"

            ]

        },

        {

            "server_file_path": "D:/rsisondi/backup/datadir/test_80/80_extern_table.ibd",

            "backup_file_path": "D:/rsisondi/80/test_80/80_extern_table.ibd",

            "space_id": 9,

            "Flags": [

                "SINGLE",

                "REMOTE"

            ]

        }

    ]

}

  1. Now, restore the incremental backup that was taken in the above step.

> mysqlbackup.exe --defaults-file=D:\rsisondi\incr\backup-my.cnf -ubackup_user --backup-dir=D:\rsisondi\incr copy-back-and-apply-log --datadir=D:\rsisondi\datadir --incremental

 

MySQL Enterprise Backup version 8.0.12-tr Windows-10.0.14393-AMD64 [2018-04-02  09:01:58]

Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.

 

A thread created with Id '33576'

Starting with following command line ...

 mysqlbackup.exe --defaults-file=D:\rsisondi\incr\backup-my.cnf –ubackup_user

        --backup-dir=D:\rsisondi\incr copy-back-and-apply-log

        --datadir=D:\rsisondi\datadir --incremental

 

<snip>

 

180402 20:52:37 MAIN    INFO: Apply-log operation completed successfully.

180402 20:52:37 MAIN    INFO: Incremental backup applied successfully.

 

mysqlbackup completed OK!

  1. Check the contents of the ‘tablespaces_tracker’ file in the datadir. In this case, it is the same as it was in the incremental backup.  But the contents could be an aggregation of the tracker files that is in the target datadir and tracker file that is in the backup directory. It really depends on what external tablespaces are included in the incremental backup.

{

    "version": 1.0,

    "HowTo": "Update server_file_path if and only if it is needed for each tablespace to restore an external tablespace to a different         location; make sure correct database folder is given for each per-table tablespace.",

    "tablespace_list": [

        {

            "server_file_path": "D:/rsisondi/incr/datadir/meb#8_80_rem_gents1.ibd",

            "backup_file_path": "D:/rsisondi/80/80_rem_gents1.ibd",

            "space_id": 8,

            "Flags": [

                "GENERAL",

                "REMOTE"

            ]

        },

        {

            "server_file_path": "D:/rsisondi/incr/datadir/test_80/80_new_extern_table.ibd",

            "backup_file_path": "D:/rsisondi/80/test_80/80_new_extern_table.ibd",

            "space_id": 10,

            "Flags": [

                "SINGLE",

                "REMOTE"

            ]

        }

    ]

}

Copy-back of external tablespaces to non-default location

There could be a situation in which you want to restore the external tablespace to some other location than its original location. For instance, you are restoring the backup to a new location or preparing a clone. In such cases, you would want to copy the external tablespaces to a different location.

It is possible to achieve the same using the following steps.

  1. Modify the value of "server_file_path" property of the tablespace that you wish to change in the ‘tablespaces_tracker’ file in backup.
  1. Make sure that the proper directory hierarchy exists on the target server for the tablespace to be restored.
  1. Start server with the --innodb-directories option. Specify the directories pertaining to all external tablespaces to this option. For more information refer to the server manual.

Conclusion

With the previous versions of server, the external tablespaces metadata is scattered around the server datadir and also in the backup.  Restoring multiple external tablespaces to the non-default location is cumbersome as you have to modify the .isl files corresponding to the tablespaces.

With 8.0 version, server keeps all tablespaces information in the transactional data dictionary and,

MEB keeps all external tablespaces information in one metadata file. Also, it is easier to modify the restore location of the external tablespaces because it is at one place. We encourage you to give this a try and provide us feedback on how it works for your data. For more details, please see the MySQL Enterprise Backup Guide

Fun with Bugs #67 - On Some Public Bugs Fixed in MySQL 8.0.11 GA

I stopped reviewing MySQL Release Notes for quite a some time, but major GA releases of MySQL do not happen often, so I decided to make an exception and write about some bugs from Community users fixed in MySQL 8.0.11 GA.

I'll start with good news about MySQL 8.0.11 GA! You can get sources at GitHub, and I had no problems to build on Fedora 27 on my good old QuadCore box, using the following cmake command line:
[openxs@fc23 mysql-server]$ cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/home/openxs/boost -DENABLE_DOWNLOADS=1 -DWITH_UNIT_TESTS=OFF -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/8.0
...
[openxs@fc23 mysql-server]$ time make -j 4
...

[100%] Built target mysqld

real    33m52.791s
user    105m47.475s
sys     8m19.018sComparing to previous experience, I had minor problem with unit tests, so just skipped them with -DWITH_UNIT_TESTS=OFF option. There is no problem to run the resulting binaries, unless you try to use data directory from older 8.0.x. Then you'll end up with:
2018-04-19T15:36:35.165841Z 1 [ERROR] [MY-011092] [Server] Upgrading the data dictionary from dictionary version '80004' is not supported.
2018-04-19T15:36:35.166239Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-04-19T15:36:35.166310Z 0 [ERROR] [MY-010119] [Server] AbortingI had to remove data directory and initialize it from scratch (it was testing instance anyway, last time used for real while I worked on this presentation):
[openxs@fc23 8.0]$ rm -rf data/*
[openxs@fc23 8.0]$ bin/mysqld --no-defaults --initialize-insecure --port=3308 --socket=/tmp/mysql.sock --basedir=/home/openxs/dbs/8.0 --datadir=/home/openxs/dbs/8.0/data --skip-log-bin
2018-04-19T15:43:55.324606Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2018-04-19T15:43:55.324726Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2018-04-19T15:43:55.325147Z 0 [System] [MY-013169] [Server] /home/openxs/dbs/8.0/bin/mysqld (mysqld 8.0.11) initializing of server in progress as process 20034
2018-04-19T15:44:14.438776Z 4 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2018-04-19T15:44:29.625227Z 0 [System] [MY-013170] [Server] /home/openxs/dbs/8.0/bin/mysqld (mysqld 8.0.11) initializing of server has completed
[openxs@fc23 8.0]$ bin/mysqld_safe --no-defaults --port=3308 --socket=/tmp/mysql.sock --basedir=/home/openxs/dbs/8.0 --datadir=/home/openxs/dbs/8.0/data --skip-log-bin &
[1] 20080
[openxs@fc23 8.0]$ 2018-04-19T15:44:58.224816Z mysqld_safe Logging to '/home/openxs/dbs/8.0/data/fc23.err'.
2018-04-19T15:44:58.271255Z mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/8.0/data

[openxs@fc23 8.0]$ bin/mysql -uroot --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server (GPL)

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

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

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

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 8.0.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 8.0.11                       |
| version_comment         | MySQL Community Server (GPL) |

| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
| version_compile_zlib    | 1.2.11                       |
+-------------------------+------------------------------+
9 rows in set (0.00 sec) So, you can build MySQL 8.0.11 right now and start using it to make your own conclusions about this release.

I still do not care about NoSQL, JSON, new cool features etc. You'll see megabytes of texts about these by the end of 2018. I am going to concentrate mostly on InnoDB, replication bugs and few others:
  •  I am happy to start with Bugt #89509 - "Valgrind error on innodb.blob_page_reserve, bundled zlib", reported by Laurynas Biveinis. See also his Bug #89597 - "Valgrind reporting memory leak on MTR test main.validate_password_component" and Bug #89433 - "NULL dereference in dd::tables::DD_properties:unchecked_get". Percona engineers spent a lot of efforts recently testing MySQL 8.0.x and reporting bugs noted. I think Oracle should explicitly admit the impact of Percona's QA effrots for the quality of this GA release.
  • Biug #89127 - "Optimize trx_rw_is_active() by tracking the lowest active transaction id". This bug was reported by Zhai Weixiang, who had suggested a patch also.
  • Bug #89129 - "create table+DML on innodb_ddl_log table=crash in lock0lock.cc:7414:release_lock". This bug was reported by Ramana Yeruva. Tables were made protected and DDL and DML operations on these tables are no longer permitted.
  • Bug #89087 - "Assertion `key->flags & 1' failed". This debug assertion (related to the way PRIMARY key was created based on UNIQUE one) was reported by Roel Van de Paar for 5.7.21, but we see the fix documented only for 8.0.x.
  • Bug #87827 - "Performance regression in "create table" speed and scalability in 8.0.3". It was reported by Alexander Rubin from Percona.
  • Bug #87812 - "Concurrent DDL operation in progress even after acquiring backup lock". Nice bug report from Debarun Banerjee.
  • Bug #87532 - "Replay log record cause mysqld crash during online DDL". I am happy to see impovements in "online ALTER" implementation that covers all GA versions, not just 8.0. I am also happy to see Oracle engineers (Ohm Hong in this case) reporting bugs in public!
  • Bug #88272 - "Assertion `new_value >= 0' failed.". Yet another debug assertion found by Roel Van de Paar, this time related to GTIDs and XA transactions. Check also his Bug #88262 - "ERROR 1598 (HY000): Binary logging not possible + abort".
  • Bug #84415 - "slave don't report Seconds_Behind_Master when running slave_parallel_workers > 0". Yet another contribution from Percona engineers. This bug was reported by Marcelo Altmann and some of related patches were provided by Laurynas Biveinis. This bug is also fixed in MySQL 5.7.22.
  • Bug #89793 - "INFORMATION_SCHEMA.STATISTICS field type change". Unexpected change in early 8.0.x versions was noted and reported by Mark Guinness.
  • Bug #89584 - "5.7->8.0 upgrade crash with default-time-zone set". Nice to see this bug (reported by Shane Bester) fixed in GA release.
  • Bug #89487 - "ALTER TABLE hangs in "Waiting for tablespace metadata lock" state". This regression bug was reported by Sveta Smirnova.
  • Bug #89324 - "main.comment_column2 fails with compression". This regression was noted and reported by Manuel Ung.
  • Bug #89122 - "Severe performance regression in server bootstrap". I am really happy to see this bug reported by Georgi Kodinov fixed. I noted it as soon as I started testing 8.0.x (see a duplicate by Roel Van de Paar, Bug #89444) and it was very annoying. I've already checked (see above) that the problem is gone!
  • Bug #89038 - "Add new column to 'mysql.routines' to accommodate the Polygot project". So, Oracle is planning to support stored programs in different languages! Thank you, Sivert Sørumgård, for reporting this in public! See also his Bug #89035 - "Reject LCTN changing after --initialize".
  • Bug #87836 - "XA COMMIT/ROLLBACK rejected by non-autocommit session with no active transaction". It would be sad if this bug is not fixed in MySQL 5.7.x, where it was originally found by Wei Zhao.
  • Bug #87708 - "MDL for column statistics is not properly reflected in P_S.METADATA_LOCKS". It was reported by Erik Frøseth.
  • Bug #85997 - "inplace alter table with foreign keys causes table definition mismatch". This bug was reported by Magnus Blåudd.
  • Bug #85561 - "Users can be assigned non-existing roles as default". Nice to see this bug reported by Giuseppe Maxia fixed in GA release.
  • Bug #33004 - "integer constants casted to bigints by unions". This bug was reported by Domas Mituzas more than 10 years ago!
Now I have to stop, as I found private bug in release notes, Bug #89512. Based on description:
"Window function row-buffer handling has been refactored to reduce the number of handler reads by 25%. (Bug #89512, Bug #27484133)"I truly do not get why it remains private (or why it was reported in public for such a "sensitive" matter), so I better stop.

MySQL 8 is GA, finally! There are a lot more fixes there that I had not mentioned above. I am surely there is even more bugs to find. So, happy hunting!



Some bugs and spring pilgrimage to Percona Live Santa Clara 2018

I am now in an airport, waiting for one of the four flights that will bring me to Percona Live Santa Clara 2018.  This is a good time to write some details about my tutorial on parallel replication.  But before talking about Percona Live, I will share thoughts on MySQL/MariaDB bugs that caught my attention in the last weeks/months (Valeriy: you clearly have an influence on me). MySQL/MariaDB

Congratulations to Our Friends at Oracle with the MySQL 8.0 GA Release!

It is a great today for whole MySQL community: MySQL 8.0 was just released as GA!

Geir Høydalsvik has a great summary in his “What’s New in MySQL 8.0” blog post. You can find additional information about MySQL 8.0 Replication and MySQL 8.0 Document Store that is also worth reading.

If you can’t wait to upgrade to MySQL 8.0, please make sure to read the Upgrading to MySQL 8.0 section in the manual, and pay particular attention to changes to Connection Authentication. It requires special handling for most applications.

Also keep in mind that while MySQL 8.0 passed through an extensive QA process, this is the first GA release. It is not yet as mature and polished as MySQL 5.7. If you’re just now starting application development, however, you should definitely start with MySQL 8.0 — by the time you launch your application, 8.0 will be good. 

All of us at Percona – and me personally – are very excited about this release. You can learn more details about what we expect from it in our Why We’re Excited about MySQL 8.0 webinar recording.    

We also wrote extensively about MySQL 8.0 on our blog. Below are some posts on various features, as well as thoughts on the various RCs, that you might want to review:

The best way to learn about MySQL 8.0, though, is to attend the Percona Live Open Source Database Conference 2018, taking place in Santa Clara, CA next week. We have an outstanding selection of MySQL 8.0 focused talks both from the MySQL Engineering team and the community at large (myself included):

You can still get tickets to the conference. Come by and learn about MySQL 8.0. If you can’t make it, please check back later for slides.

Done reading? Go ahead go download  MySQL 8.0 and check it out!

The post Congratulations to Our Friends at Oracle with the MySQL 8.0 GA Release! appeared first on Percona Database Performance Blog.

MySQL Enterprise Monitor 8.0.0 GA has been released

We are very happy to announce the general availability of MySQL Enterprise Monitor, Version 8.0. MySQL Enterprise Monitor is the best-in-class tool for monitoring and management of your MySQL assets and is included with your MySQL Enterprise Edition and MySQL Enterprise Carrier Grade subscriptions.

You can find more information on the contents of this release in the change log.

Highlights of MySQL Enterprise Monitor 8.0 include:

  • MySQL Enterprise Monitor 8.0 supports monitoring MySQL Server 8.0.11 and later. You can also use MySQL Server 8.0 as an external data repository for MySQL Enterprise Monitor 8.0.
  • Significant improvements have been made to the performance and user interface of the Query Analyzer. When you click on a SQL statement, the new Statement Details page enables you to browse all example statements in the specified time range, rather than just the one with the worst run time. Drill into any of these examples to see their individual statistics, literals used, and EXPLAIN plan. Graphs display the execution pattern of the selected query during the specified time period. For more information, see Section 30.3.1, "Detailed Query Information".
  • The new Global Memory Usage report builds upon the Memory instrumentation exposed within Performance Schema to provide greater insight into where memory is being allocated within the MySQL Instance - not just at the InnoDB Buffer Pool level, but for all memory allocations, across all connections and threads within MySQL. The report breaks down memory by allocation type, such as memory allocated for sorting, the buffer pool, or various other internal buffers used for statement processing. For more information, see Section 28.6, "Memory Usage Report".

You will find binaries for the new release on My Oracle Support. Choose the "Patches & Updates" tab, and then choose the "Product or Family (Advanced Search)" side tab in the "Patch Search" portlet.

You will also find the binaries on the Oracle Software Delivery Cloud soon.  Type "MySQL Enterprise Monitor 8.0.0" in the search box, or enter a license name to find Enterprise Monitor along with other MySQL products: "MySQL Enterprise Edition" or "MySQL Cluster Carrier Edition".  Then select your platform.

Please open a bug or a ticket on My Oracle Support to report problems, request features, or give us general feedback about how this release meets your needs.

If you are not a MySQL Enterprise customer and want to try the Monitor and Query Analyzer using our 30-day free customer trial, go to http://www.mysql.com/trials, or contact Sales at http://www.mysql.com/about/contact.

Thanks and Happy Monitoring!

- The MySQL Enterprise Tools Development Team

Useful URLs

MySQL Connector/Python 8.0.11 has been released

Dear MySQL users,

MySQL Connector/Python 8.0.11 is the first GA release version of the MySQL Connector Python 8.0 series. This series adds support for the new X DevAPI. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see http://dev.mysql.com/doc/x-devapi-userguide/en/. For more information about how the X DevAPI is implemented in MySQL Connector/Python, and its usage, see http://dev.mysql.com/doc/dev/connector-python.

Please note that the X DevAPI requires MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

To download MySQL Connector/Python 8.0.11, see the “General Available
(GA) releases” tab at http://dev.mysql.com/downloads/connector/python/

Enjoy!

Changes in MySQL Connector/Python 8.0.11 (2018-04-19) * Functionality Added or Changed * Bugs Fixed Functionality Added or Changed * X DevAPI: Previously, when documents without an _id attribute were added to a collection, Connector/Python automatically generated _id for them. Now a MySQL 8 server generates the _id attribute unless a document already contains one. The generated IDs resulting from a document-add operation can be obtained using the new get_generated_ids() method. This capability requires a MySQL 8.0 server. Because MySQL 5.7 does not support document ID generation, the document-add operation returns an error if you do not define the _id's. Incompatibility: The get_generated_ids() method replaces the now removed get_document_ids(). (Bug #27627366) * Added NOWAIT and SKIP_LOCKED support to the ReadStatement.lock_shared() and ReadStatement.lock_exclusive() methods. Example usage: lock_exclusive(mysqlx.LockContention.SKIP_LOCKED). * The C extension (cext) is now enabled by default, as the use_pure option changed from True to False by default. If the C extension is not available on the system then the Python implementation is used instead, and use_pure is set to True. * Added the X DevAPI SHA256_MEMORY authentication mechanism. Example mysqlx.get_session() usages: ?auth=SHA256_MEMORY via a connection string, "auth": mysqlx.Auth.SHA256_MEMORY via a dictionary, or auth=mysqlx.Auth.SHA256_MEMORY via method parameters. Bugs Fixed * Warnings are now stored as a list of dictionaries instead of a list of tuples. In other words, get_warnings() returns the likes of [{"level": _level_, "code": _code_, "msg": _msg_}] instead of [(_level_, _code_, _msg_))]. (Bug #27639119) * The mapped MySQL Server error codes were synced with MySQL Server 8.0.11. (Bug #27634885) * Removed upsert functionality from InsertStatement as it can only be used by collections, so upsert remains available to AddStatement. (Bug #27589450) * MySQLConverter.escape() functionality was added to create_schema()'s count mechanism. (Bug #27528842) * When using prepared statements, string columns were returned as bytearrays instead of strings. The returned value is now a string decoded using the connection's charset (defaults to 'utf8'), or as a bytearray if this conversion fails. (Bug #27364914) * The result from JSON_TYPE() was returned as a bytearray instead of a string. The returned value is now a string decoded using the connection's charset (defaults to 'utf8'), or as a bytearray if this conversion fails. (Bug #24948205, Bug #83516) * JSON integer values were cast to bytes in Python instead of integers. (Bug #24948186, Bug #83513)

On Behalf of Oracle/MySQL Release Engineering Team
Prashant Tekriwal

MySQL 8.0 – Welcome to the DevAPI!

 

By now you’ve read Mike Frank’s excellent introduction to the MySQL 8.0 release and it’s Document Store.  In that post Mike laid out the benefits of the new Document Store model and briefly outlined the different components involved.  While it’s fair to consider the Document Store as the first pillar of the new MySQL 8 Document story, we must identify the X DevAPI to be the second.  The post covers our motivation, goals, and overall design principles of this new API.

Motivation

Most of our connectors have not implemented their own API.  Rather, most of them implement externally defined interfaces.   Examples include our Connector/J that implements the JDBC standard, Connector/Net that implements the ADO.Net standard, and our Connector/ODBC.  None of these standards work well for a document-oriented database.  In fact, there really are no defined standard APIs for document databases.  Therefore we knew we needed to develop a new API for our users.

Another reason why we needed a new API is because we are doing something that has not been done before.  Document databases exist.  Relational databases exist.  We even see databases that support relational and document querying over the same data set.  However we have yet to see a relational database include a document model so that a user can use document objects alongside their existing relational data.

We are only beginning to bring forth the unifying power of MySQL 8.   In the coming releases you’ll see exciting developments like being able to link your existing relational data with your document data in a single query using simple API patterns.

Goals

We have some very clear goals we follow as we develop the X DevAPI.  They are:

  • Simplicity — No one likes a complicated API.  We wanted the API to be easy to understand no matter what language you are using.   It’s important to us that if you write X DevAPI code using Node.JS then a Python developer, for example, can read it and know what is going on.
  • Bridge the gap between relational and document data — MySQL runs some of the world’s largest web properties and they have petabytes of relational data.  Asking them to add tables or columns to their data stores can be very challenging.  The new document store allows connecting these different types of data in the same queries.  We want to provide a powerful API for this.
  • Expressive — We wanted our API to be very expressive and follow a fluent interface style of development.  We wanted this to enable writing and executing the same X DevAPI code in the shell as in your application.
  • Seamless support for InnoDB Cluster farms — We want the API to seamlessly and transparently allow simple development of applications that span from one machine to hundreds.

Please note that these are goals. Some of them may not be fully realized in the initial release of the X DevAPI but these goals outline what we are thinking about during this development.

Design

No matter what connector you use the design of the X DevAPI core components remains the same.  The API involves some central objects outlined below. You’ll hear more about these objects and more in the individual product announcements and in our documentation found at https://dev.mysql.com/doc/.

Session Represents a logical connection to a server or farm Schema Represents a MySQL database/schema Collection Represents a collection of JSON documents Result There’s a series of Result objects that handle different scenarios

Our goal is that each connector will include a core API (set of objects, properties and methods) that are the same across all connectors.  Outside of the core API, each connector can and will provide additional syntax or support additional methods.

Of course we also realize that each language and framework has it’s own style and it’s important to us to be respectful of that.  An example might be a count property that might appear as

.Count  in one language but should be represented as .getCount()  in another.  Again, please recognize they are the same concept just different syntax. Looking at the New X DevAPI Syntax

You’ll learn much more about the X DevAPI and our different languages when you read our announcement blogs and our documentation but I wanted to take just a minute to give quick examples of how you can use the X DevAPI in the shell to do quick ad-hoc prototyping.  First let’s look at opening a session to server.  In this example we open a session  to localhost and get a reference to the test schema so we can work with it.

var mysqlx = require('mysqlx'); // Connect to server on localhost var mySession = mysqlx.getSession(“mysqlx://user:password@localhost:33060”) var myDb = mySession.getSchema('test');

Next we’ll create a collection and add a document to it.

// Use the collection 'my_collection' var myColl = myDb.createCollection('my_collection'); // Insert document myColl.add({name: 'My document name', length: 250}) .execute();

We can modify the document very easily.  This example finds all documents that have a length of 250 and changes that length to 125.

myColl.modify("length = :length") .bind("length", 250) .set("length", 125) .execute();

We can find those documents if we want to work with them

// Find a document var doc = myColl.find('length = :length') .limit(1) .bind('length', 125) .execute(); // Print document print(doc.fetchOne());

And finally we can remove the documents and drop our collection

// Remove all documents with length = 125 myColl.remove("length = 125") .execute() // And drop the collection myDb.dropCollection("my_collection")

These have just been some quick examples but we hope that it has shown some of the basic ideas we have in the X DevAPI and you are interested in learning more and trying it out.

Availability

With the release of MySQL 8.0 we are also making available 8.0 GA versions of our connectors.  Each of them, save ODBC, provide an initial implementation of X DevAPI.  These connectors should be installed from their respective package stores.  You can also find them at our website at https://www.mysql.com/downloads/.

You can read much more about each of the products on their announcement blogs:

We know that we have not covered every language available.  If you are using MySQL 8 with a connector in a different language please let us know.  We want to work with the community to bring X DevAPI to more and more languages.

Thank you!

I want to thank you for your time to read this and learn about our new products.  I hope that you will take the time to get to know what we’ve created and let us know where we fell down.  We have much more planned and we want to hear from you!

MySQL 8.0: New Features in Replication

It is with extreme delight and a big smile on our face that we share the news that a new major MySQL release has been declared Generally Available (GA). The road to MySQL 8 was pretty eventful, sometimes painful and sometimes hard, always challenging, but nonetheless a great ride and an extremely rewarding journey for the engineers that have worked on it.…

Introducing Connector/Node.js for MySQL 8.0

As you may have heard, MySQL 8.0 is now officially GA, and it comes with a bunch of additional goodies. Among those is the brand new Connector/Node.js, which is the official MySQL driver for Node.js and, currently, the only one with support for the latest server versions and features (such as the MySQL document store).

Here’s a rundown of what’s available:

  • Out-of-the box support for MySQL 8.0
  • Document-store API as a first-class citizen
  • TLS/SSL and SHA256 authentication
  • Fluent API with support for flexible parameters
  • Semantic methods to encode common CRUD operations
  • Modern Node.js asynchronous interface based on Promises
  • Abstractions for common database development tasks
  • Transactions, savepoints and row locking
MySQL 8.0

Connector/Node.js is currently the only driver in the Node.js ecosystem that works out-of-the-box with the latest MySQL 8.0 series and implements the brand new X Protocol and X DevAPI, which unlocks exclusive server features such as the MySQL document store.

In a nutshell, the X Protocol is based on the Google Protocol Buffers serialization format, and provides a common interface for a different set of official connectors to bridge into the MySQL server via the X plugin, which contains the server-side implementation of the document store and a surrounding scaffolding ecosystem including things like common CRUD expression trees, bound parameters, or expectations and conditions for statement pipelining.

The X DevAPI is the common client-side API used by all connectors to abstract the details of the X Protocol. It specifies the common set of CRUD-style functions/methods used by all the official connectors to work with both document store collections and relational tables, a common expression language to establish query properties such as criteria, projections, aliases, and a standard set of additional database management features for handling things like transactions, indexes, etc.

The fact that most of these features share the same format and API between connectors, makes the X DevAPI a perfect fit for mordern polyglot development environments such as microservices, and the fact that they are based on a well-documented format allows advanced users to extend client-side implementations and build new middleware components or extensions tailor-made for their use case.

Although, there are (most of the times) matching client APIs to work with relational tables, this overview will focus mostly on document-store related features. Check the official Connector/Node.js documentation or the X DevAPI user guide to get the full picture.

Secure by default

With Connector/Node.js, SSL/TLS is enabled by default for server TCP connections and, additionally, the server identity can be validated against a given certificate authority (CA).

mysqlx.getSession({ user: 'user', sslOptions: { ca: '/path/to/ca.pem' } }) mysqlx.getSession('mysqlx://user@localhost?ssl-ca=(/path/to/ca.pem)') mysqlx.getSession({ sslOptions: { ca: '/path/to/ca.pem', crl: '/path/to/crl.pem' } }) mysqlx.getSession('mysqlx://user@localhost?ssl-ca=(/path/to/ca.pem)&ssl-crl=(/path/to/crl.pem)')

Of course you can explicitely override this behavior (at your own peril).

mysqlx.getSession({ user: 'user', ssl: false }) mysqlx.getSession('mysqlx://user@localhost?ssl-mode=DISABLED')

Local Unix sockets don’t use SSL/TLS since they don’t really benefit much from that level of security. At the same time, that removes the possibility of any additional performance overhead caused by the SSL/TLS handshake.

In the authentication realm, besides the traditional SHA1-based server authentication plugin, Connector/Node.js also supports the latest secure authentication plugins based on SHA-256. Of course you can always use your own custom server plugins, as long as the authentication data can be sent using one of the existing client-side authentication mechanisms (in the simplest form, via plain text).

// works with the caching_sha2_password plugin (as long as the password is cached) mysqlx.getSession({ user: 'user', auth: 'SHA256_MEMORY' }) mysqlx.getSession('mysqlx://user@localhost?auth=SHA256_MEMORY') // works with the mysql_native_password plugin mysqlx.getSession({ user: 'user', auth: 'MYSQL41' }) mysqlx.getSession('mysqlx://user@localhost?auth=MYSQL41') // works with any server-side plugin mysqlx.getSession({ user: 'user', auth: 'PLAIN' }) mysqlx.getSession('mysqlx://user@localhost?auth=PLAIN')

Additional details about Connector/Node.js security can be found here.

Fluent API

The public API flows nicely from a single

getSession()  method. Whereas, when it comes the point of creating and issuing database operations, you get a nice fluent query builder where those operations are encapsulated in specialized and specific methods, which, compared to using raw SQL statements, brings benefits such as:
  • more readable, maintainable (and even testable) code
  • better tooling integration
    • scaffolding for code refactoring
    • text-editor (or IDE) hints and auto-completion
  • smaller SQL injection surface area
  • common standard between different programming languages and environments

Most public API methods provide alternative input handling flavors:

  • multiple individual arguments
  • a single array of arguments
  • an object with named properties (where it applies)

mysqlx.getSession('root@localhost') mysqlx.getSession({ user: 'root' }) collection.add({ name: 'foo' }).add({ name: 'bar' }).execute() collection.add([{ name: 'foo' }, { name: 'bar' }]).execute() collection.find('name = :name').bind('name', 'foo').execute() collection.find('name = :name').bind({ name: 'foo' }).execute() collection.find().fields('foo', 'bar').execute() collection.find().fields(['foo', 'bar']).execute()

Promise-based asynchronous tasks

Being a good Node.js citizen, Connector/Node.js encapsulates all blocking I/O operations with asynchronous methods. Each method that sends a message to the MySQL server is expected to return a JavaScript

Promise , which resolves to the specific result or fails with an error. This pattern also unlocks other platform abstractions such as the async/await  syntax, making it even more flexible for the user than just using traditional error-first callbacks.

So, after building a query, it can be sent to the server via the

execute()  method. In turn, the method receives an optional callback as argument, which runs for each element in the result set. When using relational tables, an additional callback function can be used to tap into the given column metadata.collection.add({ name: 'foo' }).execute() collection.find().execute(doc => { // do something with the result set document currently being processed })

table.insert(['name']).values(['foo']).execute() table.select().execute(row => { // do something with the result set row currently being processed }, metadata => { // do something with the metadata for each column in the row })

Other methods, such as the ones that operate on a single instance of a connection, database object (be it a schema, table/collection, row, document, etc.) will return a

Promise  by themselves (dropping the extra call to execute() ). Some examples:
  • mysqlx.getSession()
  • session.getSchemas()
  • session.createSchema()
  • schema.getCollections()
  • schema.createCollection()
  • collection.getOne()
  • collection.addOrReplaceOne()
  • collection.replaceOne()
  • collection.createIndex()
Data consistency

With MySQL 8.0, you get session-level consistency and document-level isolation via multiple database constructs, such as transactions, savepoints and row locking. This allows to encapsulate a set of operations (particularly DML) encompassing multiple documents or collections in a single atomic procedure within a given session.

Connector/Node.js provides APIs to create, commit or rollback a transaction as well as to create, release or rollback to an intermediate savepoint within that transaction.

(async function () { try { await session.startTransaction() // run some operations (1) await session.createSavepoint('foo') // run more operations (2) await session.releaseSavepoint('foo') await session.commit() } catch (err) { try { await session.rollbackTo('foo') // go to (2) } catch (err) { await session.rollback(); // revert the entire thing } } })();

In the presence of concurrent transactions, the isolation level of each operation within the transaction, operating on a given document, can be determined using row locks.

collection.find('name = "foo"').lockExclusive() collection.find('name = "foo"').lockExclusive(mysqlx.LockContention.DEFAULT) // same as above collection.find('name = "foo"').lockExclusive(mysqlx.LockContention.NOWAIT) collection.find('name = "foo"').lockExclusive(mysqlx.LockContention.SKIP_LOCKED) collection.find('name = "foo"').lockShared() collection.find('name = "foo"').lockShared(mysqlx.LockContention.DEFAULT) // same as above collection.find('name = "foo"').lockShared(mysqlx.LockContention.NOWAIT) collection.find('name = "foo"').lockShared(mysqlx.LockContention.SKIP_LOCKED)

Raw SQL interface

If you are looking for a feature that is still not available on the X DevAPI, or simply something that does not fit the CRUD model, you can always resort to plain old SQL.

// create a table session.sql('CREATE TABLE foo (bar VARCHAR(3))').execute() // add an unique constraint session.sql('ALTER TABLE foo.bar ADD COLUMN baz VARCHAR(3) GENERATED ALWAYS AS (doc->>"$.baz") VIRTUAL UNIQUE KEY NOT NULL').execute() // execute a JOIN query session.sql('SELECT DISTINCT t1.bar FROM foo t1 JOIN baz t2 ON t1.bar = t2.qux WHERE t1.qux = t2.quux').execute()

Getting Started

If you want to use Connector/Node.js in your own project or just want to play around with, download the latest version from npm:

$ npm install --save --save-exact @mysql/xdevapi

New releases DO NOT follow semantic versioning, so, to avoid being affected by breaking changes, make sure you use

--save-exact  when pulling the package, particularly if you don’t have npm-shrinkwrap.json  or package-lock.json  files locking down your project’s dependencies.

If you are an advanced user, being an open source project, the code is hosted on the official GitHub repository and contributions are welcome, either in the form of bugs (

Connector for Node.js  category) or pull requests. Up and Running

Assuming you are running MySQL 8.0 with the default configuration and Node.js

v8.0.0  or later (for async/await  support), using Connector/Node.js and the MySQL document store in your project is as simple as follows:'use strict'; const mysqlx = require('@mysql/xedvapi'); const options = { user: 'user', password: 'password', schema: 'mySchema' }; (async function () { let session; try { session = await mysqlx.getSession(options); const collection = await session.getSchema(options.schema).createCollection('myCollection'); await collection.add({ name: 'foo' }).execute(); await collection.find().fields('name').execute(console.log); // { name: 'foo' } } catch (err) { console.error(err.message); } finally { session && session.close(); } })();

These are just some of the highlights. Make sure you check out the official Connector/Node.js documentation or the X DevAPI user guide for usage examples and even more details on how to get started.

Please give it a try! Your feedback is more than welcome.

Using MySQL Connector/Python 8.0 with MySQL 8.0

The MySQL Connector/Python Team is pleased to announce MySQL Connector/Python 8.0.11, the first GA 8.0 release series of the official MySQL driver for Python. This release introduces the first Python driver that adds full MySQL 8.0 support.

Document Store

MySQL 8.0 includes many improvements and new features, with Document Store being the biggest.

MySQL Document Store is schema-less with a flexible data structure storage system for documents. This removes the requirement for schema design, normalization, foreign keys, constrains, and data types.

The Document Store is accessible by the X DevAPI, an API that introduces a new modern and easy-to-learn way to work with your data that is implemented by all MySQL Connectors that support the X Protocol, such as MySQL Connector/Python.

Getting Started

Installation

Requirements:

The recommended way to install Connector/Python is via pip.

shell> pip install mysql-connector-python

Packages are also available from the MySQL Connector/Python downloads site. For some packaging formats, there are different packages for different versions of Python. Choose the one appropriate for the version of Python installed on your system.

For installation documentation, please visit the Connector/Python installation guide.

Writing your first program using the MySQL Document Store

Assuming Connector/Python 8.0 is installed on your system and you’re accessing a MySQL 8.0 server, you’re ready to write your first program using the MySQL Document Store.

import mysqlx # Connect to server on localhost session = mysqlx.get_session({ "host": "localhost", "port": 33060, "user": "user", "password": "secret" }) # Get schema object schema = session.get_schema("test") # Use the collection "my_collection" collection = schema.get_collection("my_collection") # Specify which document to find with Collection.find() result = collection.find("name like :param") \ .bind("param", "S%").limit(1).execute() # Print document docs = result.fetch_all() print("Name: {0}".format(docs[0]["name"])) session.close()

For a detailed explanation of this code, please visit the Getting Started section of the official MySQL Connector/Python X DevAPI Reference Documentation.

Improved Security

Server TCP connections are now secure by default, meaning the default SSL Mode is now set to REQUIRED (mysqlx.SSLMode.REQUIRED).
Support was also added for the latest secure MySQL 8 authentication plugins based on SHA-256 hashing, such as new the caching_sha2_password authentication plugin. Usage can be implicit or explicit, for example:

# Using the caching_sha2_password plugin mysqlx.get_session({"user": "user", "password": "secret", "auth": mysqlx.Auth.SHA256_MEMORY}) mysqlx.get_session("mysqlx://user:secret@localhost?auth=SHA256_MEMORY") # Using the mysql_native_password plugin mysqlx.get_session({"user": "user", "password": "secret", "auth": mysqlx.Auth.MYSQL41}) mysqlx.get_session("mysqlx://user:secret@localhost?auth=MYSQL41") # Using any server-side plugin mysqlx.get_session({"user": "user", "password": "secret", "auth": mysqlx.Auth.PLAIN}) mysqlx.get_session("mysqlx://user:secret@localhost?auth=PLAIN") # If auth is not set, PLAIN will be used by default if connection is secure mysqlx.get_session({"user": "user", "password": "secret"}) mysqlx.get_session("mysqlx://user:secret@localhost")

Fluent API

The X DevAPI operations are structured to be “fluent”. This means it’s possible to call multiple methods to modify an object prior to execution, which enables a fluent query builder and allows more readable, maintainable, and secure code.

An example is following where where() and sort() are fluent methods to modify the find operation prior to executing it.

collection = schema.get_collection("coll") collection.find().where("age > 28").sort("age DESC").execute()

Support for raw SQL statements

The X DevAPI allows you to execute raw SQL statements to combine the flexibility of the document store model with the power of the relational model.

# Create a table session.sql("CREATE TABLE `mydb`.`person` " "(name VARCHAR(20), age INT)").execute() # Insert rows session.sql("INSERT INTO `mydb`.`person` (name, age) " "VALUES ('Nuno', 42);").execute() # Fetch rows result = session.sql("SELECT name, age FROM `mydb`.`person`").execute() for row in result.fetch_all(): print("Name: {0}, Age: {1}".format(row["name"], row["age"]))

Transactions, Savepoints and Row Locking

Connector/Python provides an API to create, commit, or rollback a transaction, and also to create, release, or rollback to an intermediate savepoint within that transaction.

An optional savepoint name can be defined to create a transaction savepoint, which can later be used to rollback.

# Start transaction session.start_transaction() collection.add({"name": "Wilma", "age": 33}).execute() assert(2 == collection.count()) # Create a savepoint session.set_savepoint("sp") collection.add({"name": "Barney", "age": 42}).execute() assert(3 == collection.count()) # Rollback to a savepoint session.rollback_to("sp") assert(2 == collection.count()) # Commit all operations session.commit()

If a savepoint name is not provided, then mysqlx.Session.release_savepoint() will return a generated savepoint name.

# Start transaction session.start_transaction() collection.add({"name": "Wilma", "age": 33}).execute() assert(2 == collection.count()) # Create a savepoint savepoint = session.set_savepoint() collection.add({"name": "Barney", "age": 42}).execute() assert(3 == collection.count()) # Rollback to a savepoint session.rollback_to(savepoint) assert(2 == collection.count()) # Commit all operations session.commit()

To learn more about writing applications using the MySQL X DevAPI with Connector/Python and other MySQL connectors and clients, see http://dev.mysql.com/doc/x-devapi-userguide/en/

For general documentation about how to get started using MySQL as a document store, see https://dev.mysql.com/doc/refman/8.0/en/document-store.html

For more information about how the X DevAPI is implemented in MySQL Connector/Python, and its usage, see https://dev.mysql.com/doc/dev/connector-python/8.0/

We welcome and appreciate your feedback and bug reports: http://bugs.mysql.com/

Enjoy!

Introducing the MySQL X DevAPI PHP Extension for MySQL 8.0

MySQL 8.0 is now finally GA, bringing into play the powerful Document Store set of feature along with Connectors for many of the most popular languages! Also PHP is coming with it’s own extension designed to support all of the new exciting feature coming with this latest MySQL milestone.

The complete web documentation for the MySQL X DevAPI Extension for PHP is available here.

About Document Store.

The X DevAPI for PHP is an extension which allows the user to access MySQL with installed the X Plugin as a document store via the X DevAPI and the related underlying protocol.

A document store differs substantially from a traditional relational database organization where a schema needs to be provided in order to push data into the database, a document store permit to insert information in a non-uniform manner, thus without the requirement of defining and maintaining a specific set of schema’s –and their links– needed to properly store the object being recorded.

This database model became very popular with NoSQL and other similar products, the MySQL document store X Plugin has the purpose of allowing the MySQL users to retain their current  MySQL configurations and being able to benefit from the new schema-less data organization.

Different language connectors are provided to access MySQL as Document Store, and in this post I’m going to focus on the powerful and widely used PHP language and it’s xdevapi extension which is the key use this MySQL functionality.

What does that mean in short? It means that now you can store non homogeneous data in your database without the need to define and specify meticulously the content of the tables, just open a xdevapi session and push your data into the database!

Installing the PHP extension

The easier way to install the extension is by using pecl tool or is possible to download the tarball file directly from this link. There are some dependencies to fulfill in order to use the extension, the most relevant is certainly boost and the protobufs libraries.

PHP Extension for MySQL 8.0 and Document Store.

First of all we need to create a connection to the database, in order to do so you need to access the mysql_xdevapi namespace and call the getSession function. getSession accepts as parameter the URI string with the credentials and address of the target server, in my example the URI is going to be: “mysqlx://root:XXX@localhost:33060/?ssl-mode=disabled“, probably during your configuration of MySQL you’ve chosen a different password so please use yours instead of XXX!

The URI strings starts with the required “mysqlx” followed by your credentials and the address of the server, the port 33060 is the default one where MySQL is listening for X DevAPI connections. Also, by default the connection with the server is going to be over SSL, is possible to change this default behavior by providing the proper ssl-mode, like in the example below:

$uri = "mysqlx://root:XXX@localhost:33060?ssl-mode=disabled"; $nodeSession = mysql_xdevapi\getSession( $uri );

$nodeSession is the object which handle the session for the current connection. Let’s see how to create a schema, a collection for documents and how to add a simple document:

$schema = $nodeSession->createSchema( "testx" ); $coll = $schema->createCollection( "store" ); $result = $coll->add( '{ "product" : "iPhone X", "price":1000, "stock" : 2 }' '{ "product" : "Nokia Y", "price":900, "stock" : 3, "description": "A good mobile phone" }' )->execute();

In this code example the first two lines are for the purpose of creating a schema and a collection within the schema, in all the following samples I’ll use the variable $coll as a reference to the Collection obtained by createCollection

With the add you can trigger the insertion of a new document into the collection, each add has to be followed by the execute command, in the example code I’m submitting two documents with one add operation, you can add as many documents as you want with a single operation, each document have to be separated by a comma.

The same execute command is required by most of the DevAPI functions, before the execution of the request additional operations could be performed like adding more documents, manipulating the fields &c. The documents I’m inserting in the code sample are easy to understand JSON, if you are not familiar with JSON please have a look here.

We can verify the content of the database as well:

mysql> select * from testx.store; +-----------------------------------------------------------------------------------------------+----------------------------------+ | doc | _id | +-----------------------------------------------------------------------------------------------+----------------------------------+ | {"_id": "F5A1F292E55FBDEDFE8394BAF9B00B88", "price": 1000, "stock": 2, "product": "iPhone X"} | F5A1F292E55FBDEDFE8394BAF9B00B88 | | {"_id": "F5A1F292E55FBDEDFE8394BAF9B00B89", "price": 900, "stock": 3, "product": "Nokia Y", "description": "A good mobile phone"} | F5A1F292E55FBDEDFE8394BAF9B00B89 | +-----------------------------------------------------------------------------------------------+---------------------------------- 2 row in set (0.00 sec) mysql>

So, from this last shell output is clear what a collection is and how a document looks like! A collection is just a table with two columns, one representing the document itself which is a JSON and the other is the unique identifier of the document –which is a varchar-! Those ID’s are generate automatically for each inserted document, you can provide your own ID’s if you want.

The $result object returned by the add operation can be used to verify what changes has been applied to the collection or to obtain a list of the ID’s generated by the server while adding the documents.

$item_count = $result->getAffectedItemsCount(); print($item_count." documents has been added to the collection, printing ID's"); $ids = $result->getGeneratedIds(); for( $i = 0 ; $i < $item_count ; $i++ ) { print("The document ID number ".$i." is ".$ids[$i].PHP_EOL); }

Manipulating the documents

Let’s see how easily those Collections can be manipulated, for example removing documents can be done by using the straightforward removeOne API, which is a function that expect one single argument, the ID of the document to remove:

//Add some documents, note that I'm providing the IDs myself $res = $coll->add( ["_id" => "1", "name" => "Carlotta", "age" => 34, "job" => "Dentista"], ["_id" => "2", "name" => "Antonello", "age" => 45, "job" => "Tassinaro"], ["_id" => "3", "name" => "Mariangela", "age" => 32, "job" => "Attrice"], ["_id" => "4", "name" => "Antonio", "age" => 42, "job" => "Urologo"] )->execute(); //Remove the document with ID 4 $coll->removeOne("4");

Is possible to look for documents using the find operation, in it’s most basic implementation the find function will require an expression that can be used to match the document to extract from the collection:

//Find all the entries for which the 'age' field is greater than 30 $res = $coll->find("age > 30")->execute(); //Fetch the entries $data = $res->fetchAll(); //Print the results for( $i = 0 ; $i < count( $data ) ; $i++ ) { print($data[$i]["name"]." have more than 30 years!"); }

As last example here’s how is possible to modify the content of a document using the powerful modify operation. The only argument to modify is an expression that can be used to identify the documents that have to be modified, it’s then followed by one or more operation that define the modification:

//Fill the collection with some documents $coll->add('{"name": "Sakila", "age": 15, "job": "Programmer"}', '{"name": "Sakila", "age": 17, "job": "Singer"}', '{"name": "Sakila", "age": 18, "job": "Student"}', '{"name": "Arnold", "age": 24, "job": "Plumber"}', '{"name": "Robert", "age": 39, "job": "Manager"}')->execute(); //This modify operation will change the 'job' to 'Unemployed' for all //the three Sakila in the collection $coll->modify("name like 'Sakila'")->set("job", "Unemployed")->execute(); //Add a second job to Arnold, the field 'job' will now on be an array //of two elements: 'Plumber' and 'Nursey' $coll->modify("name like 'Arnold'")->arrayAppend('job','Nursey')->execute();

Conclusion

There’s a strong feeling of excitement around MySQL 8.0 and his features, in particular Document Store is going to be a game changes in the industry by providing a powerful and flexible tool into the most popular and recognized DB.

MySQL Connector/Net 8.0.11 has been released

Dear MySQL users,

MySQL Connector/Net 8.0.11 is the first general availability release of
MySQL Connector/Net to add support for the new X DevAPI. The X DevAPI
enables application developers to write code that combines the strengths
of the relational and document models using a modern, NoSQL-like syntax
that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see
http://dev.mysql.com/doc/x-devapi-userguide/en/index.html. For more
information about how the X DevAPI is implemented in Connector/Net, see
http://dev.mysql.com/doc/dev/connector-net.

Please note that the X DevAPI requires at least MySQL Server version
5.7.12 or higher with the X Plugin enabled. For general documentation
about how to get started using MySQL as a document store, see
http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

To download MySQL Connector/Net 8.0.11, see
http://dev.mysql.com/downloads/connector/net/

Changes in MySQL Connector/Net 8.0.11 (2018-04-19) Functionality Added or Changed * X DevAPI: Connector/Net now supports the NOWAIT and SKIP_LOCKED locking options introduced in the MySQL 8.0 release series (see SELECT Syntax (http://dev.mysql.com/doc/refman/5.7/en/select.html)). The following changes were made to the Connector/Net API: + The LockContention enumeration (with values Default=0, NoWait=1 and SkipLocked=2) was added. The Default enumeration member represents the previous behavior of waiting for the row locks to be released. + The existing LockShared() and LockExclusive() method signatures were modified to include the new LockContention parameter. Both methods are members of the MySqlX.XdevAPI.CRUD.FindStatement and MySqlX.XdevAPI.Relational.TableSelectStatement classes. + Usage examples: // Default behavior - waits for the row locks to release LockShared() LockShared(LockContention.Default) LockExclusive() LockExclusive(LockContention.Default) // New - fails if the rows are locked LockShared(LockContention.NoWait) LockExclusive(LockContention.NoWait) // New - succeeds excluding the locked rows from the result LockShared(LockContention.SkipLocked) LockExclusive(LockContention.SkipLocked) * X DevAPI: Previously, when documents without an _id attribute were added to a collection, Connector/Net automatically generated IDs for them. Now the server generates the _id attribute, unless a document already contains one. The generated IDs resulting from a document-add operation can be obtained using the new Result.GeneratedIds property, which returns a list. This capability requires a MySQL 8.0 GA server. If the server does not support document ID generation, the document-add operation returns an error indicating that document IDs were missing. Incompatibility: The GeneratedIds property replaces the DocumentId and DocumentIds properties, which are now removed. * X DevAPI: Support for the SHA256_MEMORY authentication mechanism was added to enable non-PLAIN insecure connections (without SSL) for user accounts with caching_sha2_password, which is the default authentication plugin introduced in the MySQL 8.0 release series. The changes related to this support include: + New synonyms for the auth connection string option: authentication and authentication mode (see General Options (http://dev.mysql.com/doc/connector-net/en/connector-net-connection-options. html#connector-net-connection-options-general)). + A new authentication mode for the MySqlAuthenticationMode enumeration: SHA256_MEMORY. In addition, the Default member now has a new synonym: Auto=0. + A new class: MySql.Data.MySqlClient.Authentication.Sha256MemoryAu thenticationPlugin. * Support was added for the new caching_sha2_password padding mechanism introduced in the MySQL 8.0 release series. The new padding mechanism is enabled when all of the following conditions apply: + The user account is set with the caching_sha2_password authentication plugin. + SSL is disabled explicitly (SslMode=none). + The AllowPublicKeyRetrieval connection option is enabled (AllowPublicKeyRetrieval=true). When enabled, the new padding mechanism is used to encode the password during RSA key encryption, which applies the correct padding to match the server. Bugs Fixed * Attempting to open the MySQL Web Configuration Tool, with Connector/Net and MySQL for Visual Studio prerequisites installed properly, displayed an error message instead of opening the tool. (Bug #27457398, Bug #88544) * MySQL Installer could not be installed with NuGet packages from Microsoft Visual Studio 2015. (Bug #27251839, Bug #88838) * When a decimal column was defined with a scale of zero, such as DECIMAL(8, 0), the value of the NumericPrecision field returned by the MySqlDataReader.GetSchemaTable method was lower by one. For example, it returned 7 instead of 8 as expected. (Bug #26954812, Bug #88058) * The data table returned by the MySqlDataReader.GetSchemaTable method had an inaccurate value of zero assigned to the ColumnSize field for LONGTEXT and LONGBLOB data types, and also indicated that the IsLong field value was false when it should have returned true. (Bug #26876592, Bug #87876) * The MySqlDataReader.GetSchemaTable method returned different column-size values when used with different character sets. (Bug #26876582, Bug #87868) * Support for making a secure connection to a server configured to use TLSv1.2 was limited by external factors. (Bug #25689154) * Connection strings that included TLS/SSL connection parameters in URI type-string format generated an exception instead of making a connection with the X Protocol. (Bug #24510329) * Attempting to generate an Entity Framework model from a MySQL 5.7 database using either EF5 or EF6 produced an exception that prevented the operation from generating the expected model. (Bug #22173048, Bug #79163)

What is new in Connector/ODBC 8.0

General information about MySQL Connector/ODBC 8.0:

MySQL Connector/ODBC 8.0 is a successor of the Connector/ODBC 5.3 line.
The Connector/ODBC driver 8.0 becomes available starting from the version 8.0.11.

Where is 8.0.10? The family of MySQL products is growing and with so many products and different versions it is easy to get confused about functionality and product compatibility. Therefore, we decided to unify the versioning and synchronize the version numbers across all MySQL products. The current GA version of MySQL Server is 8.0.11 and the family of MySQL Connectors including Connector/ODBC has been aligned with the new versioning model and became 8.0.11 too. This will ensure that Connector/ODBC 8.0.11 can work with MySQL Server 8.0.11.

Among bug fixes and internal improvements the Connector/ODBC 8.0.11 received the support for a new authentication methods introduced in MySQL Server 8.0. These new authentication methods require encrypted connection in case password needs to be sent to the server (cache miss). If, for some reason, SSL connection is not an option, passwords still can be sent to the server after encrypting it with server’s public key. A new connection option GET_SERVER_PUBLIC_KEY requests server to send its public key when it is needed. This way clients can connect even if they do not know server’s public key. However, using this option is prone to man-in-the-middle attacks, so it should be used only in situations where you can ensure by other means that your connections are made to trusted servers.

The value of GET_SERVER_PUBLIC_KEY is a boolean, and added in 8.0.11.By default the option is not enabled, but setting it is really easy and can be done in two ways:

  • Through the GUI DSN Setup dialog. Click “Details >>” and tick [x] Get Server Public Key as shown here:

    ODBC GUI Dialog with GET_SERVER_PUBLIC_KEY option

  • By specifying the option in the connection string:
    “…GET_SERVER_PUBLIC_KEY=1…”

This capability requires a MySQL 8 server, and is supported only for Connector/ODBC built using OpenSSL. This option is ignored when used with MySQL Server 5.7.

NOTE: There is a way to specify RSA Public key if it is located on the client host. In GUI DSN Setup dialog. Click “Details >>”, select the “SSL” tab and specify the location of RSA Public Key as shown here:

ODBC GUI Dialog with RSAKEY option

Alternatively, the RSA Public Key can be given through the connection string as
“…RSAKEY=D:\\ssl\\mykey.pub;…”. The double back-slashes are there because in languages like C or C++ they must be properly escaped.

There are a few things to keep in mind when using MySQL Connector/ODBC 8.0:

  • For maximum security the connection to MySQL Server is established using SSL/TLS by default. All communication between ODBC Driver and MySQL Server would be encrypted using a separate OpenSSL library. For previous versions of MySQL ODBC Driver such as 5.7 the SSL code would be embedded into the driver library file. This is changed in Connector/ODBC 8.0 where OpenSSL shared library is required for the driver to work. The reason for doing it is improving of the security: when the new security update for OpenSSL is released the user can update OpenSSL right away without waiting on the new version of Connector/ODBC driver (which would be necessary if SSL library is embedded). However, in order to work the ODBC driver needs to be able to find and load OpenSSL shared library even if SSL/TLS connection is not used. Most packages released for the version 8.0.11 bundle the latest OpenSSL library files (libeay32.dll/ssleay32.dll for Windows platforms and libcrypto.so/libssl.so for Linux/Unix platforms). Such packages are ready for work out-of-box (assuming that UnixODBC (Linux/Unix) or iODBC (OSX) is installed). Packages for the enterprise platforms such as Red Hat Enterprise Linux or Suse Linux Enterprise Server do not have bundled OpenSSL libraries because in most cases OpenSSL is already installed on these platforms. Also, in enterprise platforms the administrators often update OpenSSL as soon as a new security patch is available.
  • The Windows platform will no longer receive 32-bit builds of MySQL Connector/ODBC 8.0 (Note: there is no 32-bit MySQL Server 8.0 as well).
  • The ODBC Driver 8.0 for Windows needs the Visual C++ 2015 runtime libraries for its work. These libraries are included in Visual C++ 2015 64-bit redistributable package, which can be downloaded from the Microsoft web site.NOTE: VC++ 2017 redistributable package is not a replacement for VC++ 2015. Therefore, even if VC++ 2017 is installed in the system, the 2015 version is still required.
Typical errors that can happen during the installation or setup:

Trying to run an ODBC Driver 8.0 MSI installation package without VC++ 2015 redistributable libraries will result in to following error message dialog:

VC++ 2015 redistributable not found

The solution is to install the 2015 redistributable package and run MSI installer again.

Sometimes the driver is installed without MSI package manually. An attempt to load a Setup module (myodbc8S.dll) from ODBC Administrator without VC++ 2015 redistributable will result in the following error:

Failure to load myodbc8a.dll

Unfortunately, the Windows ODBC Administrator does not give detailed information about the problem why the setup routine could not be loaded. If you see an error dialog like that it is most likely because the VC++ 2015 redistributable package is not installed.

 

 

Pages