Home News Feeds Planet MySQL

Newsfeeds

Planet MySQL
Planet MySQL - https://planet.mysql.com

  • VividCortex & Grafana: Better Together
    Many of our customers have asked us to integrate with Grafana, the popular open platform for analytics and monitoring. The wait is over: we’ve released a plugin that lets you retrieve and display VividCortex metrics in your own Grafana dashboard. With this integration, you can combine metrics and dashboards from all of your systems and tools into a single source of truth that your team is already familiar with, giving them the ability to explore and use VividCortex’s data without having to learn a new tool. We believe in empowering everyone to access performance data, removing barriers to understanding. It’s been our experience that when entire teams find it comfortable and familiar to inspect database and data-tier performance and behavior, adoption spreads quickly and there’s a lot more value. Special people with special knowledge gradually transfer their skills and abilities to whole teams, who are then able to ship better code and fix it faster when it fails. Grafana helps with all of this, and we’re big believers in showing up where our customers already are, so a Grafana integration makes perfect sense. Visualize Any Metric Grafana offers fast and flexible graphs with multiple ways to visualize metrics and logs. Grafana’s visualization features let your team explore and understand VividCortex’s metrics in context with metrics from other sources such as logs, system data, and observability data. A familiar interface and universal accessibility into performance data is an important step in building a data-driven performance and engineering culture.   Explore Metrics Interactively with Autocomplete VividCortex collects a wide variety of metrics about your queries and databases. But when you have hundreds of metrics, it can be difficult to find the one that you want. To make it easier to  find the metric that will help solve your issue, the Grafana plugin auto-completes metric names, so you can browse fluidly through the hierarchy to see what the metric looks like without having to know the name in advance. For example, in the below screenshot, you can view all metrics from SHOW STATUS quickly, shortening the hypothesis-testing cycle to diagnose issues faster and more intuitively.   Slice Your Application and Data-Tier Workload Modern applications rarely use a single database technology: polyglot persistence is much more common. VividCortex provides a unified perspective on database performance and behavior, independent of the underlying technologies. But when your application’s workload is distributed across four types of database and hundreds of nodes, how can you make sense of it all? Drilling down from the bird’s eye view to a single technology at a time is a powerful way to isolate and focus on what matters. VividCortex supports that, and we’ve gone the extra mile to expose it in Grafana too, using Grafana’s ad-hoc filters that allow you to create new key/value filters on the fly. Here we’re showing a simple example of key/value filtering to isolate only the “mysql” portion of the workload in Grafana. The Grafana integration is available at no charge to all VividCortex customers. This feature is currently in beta and in active development; that means your feedback will help guide further enhancements. If you’d like to get started, you can simply install the plugin and use it, or contact us for help using the chat widget in VividCortex’s own app.

  • Using MySQL Shell 8.0.11 “upgrade checker” to upgrade from MySQL 5.7 to MySQL 8.0 successfully
    We are really excited about MySQL 8.0 new features (https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html) and our consultants spend several hours weekly, testing new features and doing research on how best we can create value for our customers from having those in production. Being an pure-play MySQL consulting, support and remote DBA services company, We are fully accountable for our customer database infrastructure operations performance, scalability, high availability and reliability.  As we are aggressive about gaining maximum results from MySQL 8 investments made by our customers, We are equally conservative (our customer data reliability is critical for us !)  on adopting new features, until we are fully confident after several rounds of testing (at different scales on multiple platforms) and technical review (we engage both internal and external consultants for acceptance) and acceptance before deployment in production infrastructure. In the previous versions of MySQL, before every upgrade our consultants manually spend several hours testing compatibility but MySQL 8 made this simple by introducing “upgrade checker” javascript with MySQL Shell 8.0.11 , In this blog we are writing about “upgrade checker” utility and MySQL upgrade from 5.7 to 8.0 : Using MySQL Shell 8.0.11 “upgrade checker” Typical “upgrade checker” run will look similar to this: MySQL JS > util.checkForServerUpgrade("root@localhost:3306") Please provide the password for 'root@localhost:3306': ********** The MySQL server at localhost:3306 will now be checked for compatibility issues for upgrade to MySQL 8.0... MySQL version: 5.7.22-log - MySQL Community Server (GPL) 1) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 2) Usage of utf8mb3 charset Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support. sakila.actor.first_name - column's default character set: utf8 sakila.actor.last_name - column's default character set: utf8 sakila.actor_info.first_name - column's default character set: utf8 sakila.actor_info.last_name - column's default character set: utf8 sakila.actor_info.film_info - column's default character set: utf8 sakila.address.address - column's default character set: utf8 sakila.address.address2 - column's default character set: utf8 sakila.address.district - column's default character set: utf8 sakila.address.postal_code - column's default character set: utf8 sakila.address.phone - column's default character set: utf8 sakila.category.name - column's default character set: utf8 sakila.city.city - column's default character set: utf8 sakila.country.country - column's default character set: utf8 sakila.customer.first_name - column's default character set: utf8 sakila.customer.last_name - column's default character set: utf8 sakila.customer.email - column's default character set: utf8 sakila.customer_list.name - column's default character set: utf8 sakila.customer_list.address - column's default character set: utf8 sakila.customer_list.zip code - column's default character set: utf8 sakila.customer_list.phone - column's default character set: utf8 sakila.customer_list.city - column's default character set: utf8 sakila.customer_list.country - column's default character set: utf8 sakila.customer_list.notes - column's default character set: utf8 sakila.film.title - column's default character set: utf8 sakila.film.description - column's default character set: utf8 sakila.film.rating - column's default character set: utf8 sakila.film.special_features - column's default character set: utf8 sakila.film_list.title - column's default character set: utf8 sakila.film_list.description - column's default character set: utf8 sakila.film_list.category - column's default character set: utf8 sakila.film_list.rating - column's default character set: utf8 sakila.film_list.actors - column's default character set: utf8 sakila.film_text.title - column's default character set: utf8 sakila.film_text.description - column's default character set: utf8 sakila.language.name - column's default character set: utf8 sakila.nicer_but_slower_film_list.title - column's default character set: utf8 sakila.nicer_but_slower_film_list.description - column's default character set: utf8 sakila.nicer_but_slower_film_list.category - column's default character set: utf8 sakila.nicer_but_slower_film_list.rating - column's default character set: utf8 sakila.nicer_but_slower_film_list.actors - column's default character set: utf8 sakila.sales_by_film_category.category - column's default character set: utf8 sakila.sales_by_store.store - column's default character set: utf8 sakila.sales_by_store.manager - column's default character set: utf8 sakila.staff.first_name - column's default character set: utf8 sakila.staff.last_name - column's default character set: utf8 sakila.staff.email - column's default character set: utf8 sakila.staff.username - column's default character set: utf8 sakila.staff.password - column's default character set: utf8 sakila.staff_list.name - column's default character set: utf8 sakila.staff_list.address - column's default character set: utf8 sakila.staff_list.zip code - column's default character set: utf8 sakila.staff_list.phone - column's default character set: utf8 sakila.staff_list.city - column's default character set: utf8 sakila.staff_list.country - column's default character set: utf8 3) Usage of use ZEROFILL/display length type attributes Notice: The following table columns specify a ZEROFILL/display length attributes. Please be aware that they will be ignored in MySQL 8.0 sakila.customer.active - tinyint(1) sakila.staff.active - tinyint(1) 4) Issues reported by 'check table x for upgrade' command No issues found 5) Table names in the mysql schema conflicting with new tables in 8.0 No issues found 6) Usage of old temporal type No issues found 7) Foreign key constraint names longer than 64 characters No issues found 8) Usage of obsolete MAXDB sql_mode flag No issues found 9) Usage of obsolete sql_mode flags No issues found 10) Usage of partitioned tables in shared tablespaces No issues found 11) Usage of removed functions No issues found No fatal errors were found that would prevent a MySQL 8 upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading. 1 At the end,  “upgrade checker” prints a summary and returns an integer value describing he severity of the issues found: 0 – no issues or only ones categorized as notice, 1 – No fatal errors were found, but some potential issues were detected, 2 – UC found errors that must be fixed before upgrading to 8.0. Upgrade from MySQL 5.7 to MySQL 8.0 Step 1 – Uninstall MySQL 5.7  [root@localhost ~]# systemctl stop mysqld [root@localhost ~]# yum remove mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-devel.x86_64 mysql-community-libs.x86_64 mysql-community-libs-compat.x86_64 mysql-community-server.x86_64 Loaded plugins: fastestmirror Resolving Dependencies --> Running transaction check ---> Package mysql-community-client.x86_64 0:5.7.22-1.el7 will be erased ---> Package mysql-community-common.x86_64 0:5.7.22-1.el7 will be erased ---> Package mysql-community-devel.x86_64 0:5.7.22-1.el7 will be erased ---> Package mysql-community-libs.x86_64 0:5.7.22-1.el7 will be erased ---> Package mysql-community-libs-compat.x86_64 0:5.7.22-1.el7 will be erased ---> Package mysql-community-server.x86_64 0:5.7.22-1.el7 will be erased --> Finished Dependency Resolution Dependencies Resolved ======================================================================================= Package Arch Version Repository Size ======================================================================================= Removing: mysql-community-client x86_64 5.7.22-1.el7 @mysql57-community 106 M mysql-community-common x86_64 5.7.22-1.el7 @mysql57-community 2.5 M mysql-community-devel x86_64 5.7.22-1.el7 @mysql57-community 21 M mysql-community-libs x86_64 5.7.22-1.el7 @mysql57-community 9.4 M mysql-community-libs-compat x86_64 5.7.22-1.el7 @mysql57-community 9.2 M mysql-community-server x86_64 5.7.22-1.el7 @mysql57-community 743 M Transaction Summary ======================================================================================= Remove 6 Packages Installed size: 892 M Is this ok [y/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Erasing : mysql-community-devel-5.7.22-1.el7.x86_64 1/6 Erasing : mysql-community-server-5.7.22-1.el7.x86_64 2/6 warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave Erasing : mysql-community-client-5.7.22-1.el7.x86_64 3/6 Erasing : mysql-community-libs-compat-5.7.22-1.el7.x86_64 4/6 Erasing : mysql-community-libs-5.7.22-1.el7.x86_64 5/6 Erasing : mysql-community-common-5.7.22-1.el7.x86_64 6/6 Verifying : mysql-community-libs-compat-5.7.22-1.el7.x86_64 1/6 Verifying : mysql-community-common-5.7.22-1.el7.x86_64 2/6 Verifying : mysql-community-devel-5.7.22-1.el7.x86_64 3/6 Verifying : mysql-community-server-5.7.22-1.el7.x86_64 4/6 Verifying : mysql-community-client-5.7.22-1.el7.x86_64 5/6 Verifying : mysql-community-libs-5.7.22-1.el7.x86_64 6/6 Removed: mysql-community-client.x86_64 0:5.7.22-1.el7 mysql-community-common.x86_64 0:5.7.22-1.el7 mysql-community-devel.x86_64 0:5.7.22-1.el7 mysql-community-libs.x86_64 0:5.7.22-1.el7 mysql-community-libs-compat.x86_64 0:5.7.22-1.el7 mysql-community-server.x86_64 0:5.7.22-1.el7 Complete! [root@localhost ~]# Step 2 – Install MySQL 8.0 [root@localhost MySQL8-Community-Edition]# rpm -ivh mysql-community-server-8.0.11-1.el7.x86_64.rpm mysql-community-client-8.0.11-1.el7.x86_64.rpm mysql-community-common-8.0.11-1.el7.x86_64.rpm mysql-community-devel-8.0.11-1.el7.x86_64.rpm mysql-community-libs-8.0.11-1.el7.x86_64.rpm mysql-community-libs-compat-8.0.11-1.el7.x86_64.rpm warning: mysql-community-server-8.0.11-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-common-8.0.11-1.e################################# [ 17%] 2:mysql-community-libs-8.0.11-1.el7################################# [ 33%] 3:mysql-community-client-8.0.11-1.e################################# [ 50%] 4:mysql-community-server-8.0.11-1.e################################# [ 67%] 5:mysql-community-devel-8.0.11-1.el################################# [ 83%] 6:mysql-community-libs-compat-8.0.1################################# [100%] [root@localhost MySQL8-Community-Edition]# Step 3- Start MySQL 8.0 [root@localhost MySQL8-Community-Edition]# systemctl start mysqld [root@localhost MySQL8-Community-Edition]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 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> Step 4 – Run “mysql_upgrade” , mysql_upgrade checks for all tables in all databases for incompatibilities with the current version of MySQL Server, it also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added. [root@localhost MySQL8-Community-Edition]# mysql_upgrade -u root -p Enter password: Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv OK mysql.component OK mysql.db OK mysql.default_roles OK mysql.engine_cost OK mysql.func OK mysql.general_log OK mysql.global_grants OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.password_history OK mysql.plugin OK mysql.procs_priv OK mysql.proxies_priv OK mysql.role_edges OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Found outdated sys schema version 1.5.1. Upgrading the sys schema. Checking databases. employees.departments OK employees.dept_emp OK employees.dept_manager OK employees.employees OK employees.salaries OK employees.tab1 OK employees.titles OK sakila.actor OK sakila.address OK sakila.category OK sakila.city OK sakila.country OK sakila.customer OK sakila.film OK sakila.film_actor OK sakila.film_category OK sakila.film_text OK sakila.inventory OK sakila.language OK sakila.payment OK sakila.rental OK sakila.staff OK sakila.store OK sys.sys_config OK Upgrade process completed successfully. Checking if update is needed.   Success  , You have successfully completed upgrade from MySQL 5.7 to MySQL 8.0 .     The post Using MySQL Shell 8.0.11 “upgrade checker” to upgrade from MySQL 5.7 to MySQL 8.0 successfully appeared first on MySQL Consulting, Support and Remote DBA Services By MinervaDB.

  • Webinar Weds 20/6: Percona XtraDB Cluster 5.7 Tutorial Part 2
    Including setting up Percona XtraDB Cluster with ProxySQL and PMM Please join Percona’s Architect, Tibi Köröcz as he presents Percona XtraDB Cluster 5.7 Tutorial Part 2 on Wednesday, June 20th, 2018, at 7:00 am PDT (UTC-7) / 10:00 am EDT (UTC-4). Register Now   Never used Percona XtraDB Cluster before? This is the webinar for you! In this 45-minute webinar, we will introduce you to a fully functional Percona XtraDB Cluster. This webinar will show you how to install Percona XtraDB Cluster with ProxySQL, and monitor it with Percona Monitoring and Management (PMM). We will also cover topics like bootstrap, IST, SST, certification, common-failure situations and online schema changes. After this webinar, you will have enough knowledge to set up a working Percona XtraDB Cluster with ProxySQL, in order to meet your high availability requirements. You can see part one of this series here: Percona XtraDB Cluster 5.7 Tutorial Part 1 Register Now! Tibor Köröcz Architect Tibi joined Percona in 2015 as a Consultant. Before joining Percona, among many other things, he worked at the world’s largest car hire booking service as a Senior Database Engineer. He enjoys trying and working with the latest technologies and applications which can help or work with MySQL together. In his spare time he likes to spend time with his friends, travel around the world and play ultimate frisbee.   The post Webinar Weds 20/6: Percona XtraDB Cluster 5.7 Tutorial Part 2 appeared first on Percona Database Performance Blog.

  • Top 10 reasons for NoSQL with MySQL
    As you know, one of the great new feature in MySQL 8.0 is the Document Store. Now with MySQL you can store your JSON documents in collections and manage them using CRUD operations. NoSQL is now part of MySQL ! Instead of a mix of MongoDB and MySQL, now you can eliminate MongoDB and consolidate with MySQL ! This is a historical meeting of NoSQL and SQL in the same database server! To use MySQL 8.0 as Document Store, you need to have the X plugin installed (by default since 8.0.11). This plugin enables the X DevAPI that offers a modern programming interface. Clients that communicate with a MySQL Server using the X Protocol can use the X DevAPI to develop applications. The X Protocol allows then a more flexible connectivity between those clients and the server. It supports both SQL and NoSQL API and can perform non-blocking asynchronous calls. Of course all connectors provided by MySQL and some very popular like for PHP are already compatible with the new X protocol. Let’s see an overview of the main top 10 reasons you should consider using NoSQL with MySQL too: MySQL cares about your data ! NoSQL full ACID compliant CRUD operations (SQL is not mandatory anymore) Schemaless Documents can have the benefit of Data Integrity Allows SQL (very important for analytics) no 16MB limitation for Document Simplest query syntax Security Simplify your DB infrastructure your MySQL DBA already knows how to manage/tune/scale MySQL and more… Lots of instrumentation Makes ORM obsolete MySQL Shell   1. MySQL cares about your data ! NoSQL full ACID compliant Unlike traditional NoSQL databases, MySQL is full ACID (Atomicity, Consistency, Isolation and Durability) compliant. Most of the time NoSQL databases are pointed because they lack Durability for example. This means that sometimes data can be lost after a crash. Indeed, Consistency is also not guaranteed. As the MySQL Document Store relies on the InnoDB Storage Engine, the Document Store benefits from InnoDB’s strength & robustness. By default, out-of-the-box, InnoDB is full Durable and once data is acknowledged as committed, it won’t be loss: innodb_flush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 But MySQL Document Store also supports the Atomicity and Isolation as transactions are also supported ! Let me illustrate this to you: MySQL [localhost+ ssl/docstore] JS> db.users.find() [ { "_id": "00005ad754c90000000000000001", "name": "lefred" } ] 1 document in set (0.0109 sec) MySQL [localhost+ ssl/docstore] JS> session.startTransaction() Query OK, 0 rows affected (0.0069 sec) MySQL [localhost+ ssl/docstore] JS> db.users.add({name: 'dim0'}) Query OK, 1 item affected (0.0442 sec) MySQL [localhost+ ssl/docstore] JS> db.users.find().fields('name') [ { "name": "lefred" }, { "name": "dim0" } ] 2 documents in set (0.0579 sec) MySQL [localhost+ ssl/docstore] JS> session.rollback() Query OK, 0 rows affected (0.1201 sec) MySQL [localhost+ ssl/docstore] JS> db.users.find().fields('name') [ { "name": "lefred" } ] 1 document in set (0.0004 sec) As you can see transactions are supported in MySQL using an easy semantic. 2. CRUD operations (SQL is not mandatory anymore) With the new MySQL Document Store and the X protocol, we have introduced new operations to manage collections and/or relational tables. Those operations are called CRUD (Create, Read, Update, Delete) operations and they allow you to manage data without writing a single line of SQL. We have of course differentiated the CRUD operation in 2 groups, one to operate on Collections and one to operate on Tables: CRUD functions on Collection CRUD functions on Tables add() : CollectionInsertObj insert() : InsertObj find() : CollectionFindObj select() : SelectObj modify() : CollectionUpdateObj update() : UpdateObj remove() : CollectionDeleteObj delete() : DeleteObj You already saw in the above example how to use find() and add(). 3. Schemaless MySQL Document Store brings to you the new universe of schemaless data. Indeed, when storing documents, you don’t need to know all the attributes in advance, you can always modify the document later if you want to. As you don’t need to focus on table design, you don’t need to take care of normalization, foreign keys and constraints or even datatypes. This allows you to have a very quick initial development. As those are documents are dynamic, schema migration is also not a problem anymore and large ALTER statements can be forgotten. For example, let’s use the following data: MySQL [localhost+ ssl/docstore] JS> db.users.find() [ { "_id": "00005ad754c90000000000000001", "name": "lefred" }, { "_id": "00005ad754c90000000000000003", "name": "dim0" }, { "_id": "00005ad754c90000000000000004", "name": "Dave" }, { "_id": "00005ad754c90000000000000005", "name": "Luis" }, { "_id": "00005ad754c90000000000000006", "name": "Sunny" } ] 5 documents in set (0.0007 sec) Now let’s imagine that we want to add a new attribute for all the users, you can do it without having to run an ALTER statement: MySQL [localhost+ ssl/docstore] JS> db.users.modify('1').set('department', 'development') Query OK, 5 items affected (0.1910 sec) Let’s verify one of the records: MySQL [localhost+ ssl/docstore] JS> db.users.find("name = 'Sunny'") [ { "_id": "00005ad754c90000000000000006", "department": "development", "name": "Sunny" } ] 1 document in set (0.0005 sec) So having such possibility, provides more freedom to developers to maintain how they write documents and they don’t rely on an operational team that needs to run large alter statements. 4. Documents can have the benefit of Data Integrity Even if schemaless is very important, sometimes people wants to force data integrity. With MySQL Document Store, constraints and foreign keys can be created and maintained for documents too. This is an example where we have two collections: users and departments, and we created a GENERATED STORED columns to use as foreign key: MySQL [localhost+ ssl/docstore] SQL> alter table departments add column dept varchar(20) generated always as (doc->>"$.name") STORED ; Query OK, 2 rows affected (0.3633 sec) MySQL [localhost+ ssl/docstore] SQL> alter table users add column dept varchar(20) generated always as (doc->>"$.department") STORED ; Query OK, 5 rows affected (0.1302 sec) MySQL [localhost+ ssl/docstore] SQL> select * from users; +---------------------------------------------------------------------------------------+------------------------------+-------------+ | doc | _id | dept | +---------------------------------------------------------------------------------------+------------------------------+-------------+ | {"_id": "00005ad754c90000000000000001", "name": "lefred", "department": "community"} | 00005ad754c90000000000000001 | community | | {"_id": "00005ad754c90000000000000003", "name": "dim0", "department": "community"} | 00005ad754c90000000000000003 | community | | {"_id": "00005ad754c90000000000000004", "name": "Dave", "department": "community"} | 00005ad754c90000000000000004 | community | | {"_id": "00005ad754c90000000000000005", "name": "Luis", "department": "development"} | 00005ad754c90000000000000005 | development | | {"_id": "00005ad754c90000000000000006", "name": "Sunny", "department": "development"} | 00005ad754c90000000000000006 | development | +---------------------------------------------------------------------------------------+------------------------------+-------------+ 5 rows in set (0.0010 sec) MySQL [localhost+ ssl/docstore] SQL> select * from departments; +------------------------------------------------------------------------------------+------------------------------+-------------+ | doc | _id | dept | +------------------------------------------------------------------------------------+------------------------------+-------------+ | {"_id": "00005ad754c90000000000000007", "name": "development", "manager": "Tomas"} | 00005ad754c90000000000000007 | development | | {"_id": "00005ad754c90000000000000008", "name": "community", "manager": "Andrew"} | 00005ad754c90000000000000008 | community | +------------------------------------------------------------------------------------+------------------------------+-------------+ 2 rows in set (0.0004 sec) Let’s add some index on these new columns: MySQL [localhost+ ssl/docstore] SQL> alter table users add index dept_idx(dept); Query OK, 0 rows affected (0.0537 sec) MySQL [localhost+ ssl/docstore] SQL> alter table departments add index dept_idx(dept); Query OK, 0 rows affected (0.1278 sec) And now, let’s create the constraint, I want that if I delete a departments, all users from that departments get removed: MySQL [localhost+ ssl/docstore] SQL> alter table users add foreign key (dept) references departments(dept) on delete cascade; Query OK, 5 rows affected (0.2401 sec) MySQL [localhost+ ssl/docstore] SQL> delete from departments where doc->>"$.manager" like 'Andrew'; Query OK, 1 row affected (0.1301 sec) MySQL [localhost+ ssl/docstore] SQL> select * from departments; +------------------------------------------------------------------------------------+------------------------------+-------------+ | doc | _id | dept | +------------------------------------------------------------------------------------+------------------------------+-------------+ | {"_id": "00005ad754c90000000000000007", "name": "development", "manager": "Tomas"} | 00005ad754c90000000000000007 | development | +------------------------------------------------------------------------------------+------------------------------+-------------+ 1 row in set (0.0007 sec) MySQL [localhost+ ssl/docstore] SQL> select * from users; +---------------------------------------------------------------------------------------+------------------------------+-------------+ | doc | _id | dept | +---------------------------------------------------------------------------------------+------------------------------+-------------+ | {"_id": "00005ad754c90000000000000005", "name": "Luis", "department": "development"} | 00005ad754c90000000000000005 | development | | {"_id": "00005ad754c90000000000000006", "name": "Sunny", "department": "development"} | 00005ad754c90000000000000006 | development | +---------------------------------------------------------------------------------------+------------------------------+-------------+ 2 rows in set (0.0006 sec) So as you can see, it’s possible and very easy to implement foreign key constraints to enhance data integrity. 5. Allows SQL (very important for analytics) As you could see in the previous points, it’s possible to mix SQL and NoSQL and go from one to the other one and back using MySQL Shell. Sometimes the good old SQL is still very useful and I predict him still a long life. SQL is not yet ready to retire completely… or at least not for all operations. In fact how I see things is that developers of the front-end will just use NoSQL to create and consume data that see as objects and those working more in the back-office will  keep using SQL to create reports and analytics . To illustrate to power of MySQL with Documents, I will use the popular restaurant example collection from another popular NoSQL solution. This is an example of document stored in the collection: MySQL [localhost+ ssl/docstore] JS> db.restaurants.find().limit(1) [ { "_id": "5ad5b645f88c5bb8fe3fd337", "address": { "building": "1007", "coord": [ -73.856077, 40.848447 ], "street": "Morris Park Ave", "zipcode": "10462" }, "borough": "Bronx", "cuisine": "Bakery", "grades": [ { "date": "2014-03-03T00:00:00Z", "grade": "A", "score": 2 }, { "date": "2013-09-11T00:00:00Z", "grade": "A", "score": 6 }, { "date": "2013-01-24T00:00:00Z", "grade": "A", "score": 10 }, { "date": "2011-11-23T00:00:00Z", "grade": "A", "score": 9 }, { "date": "2011-03-10T00:00:00Z", "grade": "B", "score": 14 } ], "name": "Morris Park Bake Shop", "restaurant_id": "30075445" } ] You can see that a restaurant can be graded and that of course has also a style of cuisine. One easy query we could try to find the result is : What is the average grade for each restaurant ? (and limit the result to 10) MySQL [localhost+ ssl/docstore] SQL> SELECT name, cuisine, avg(rating) FROM restaurants, -> JSON_TABLE(doc, "$" columns(name varchar(100) path "$.name", -> cuisine varchar(100) path "$.cuisine", -> nested path "$.grades[*]" -> columns (rating int path "$.score"))) -> AS jt GROUP BY name, cuisine LIMIT 10; +--------------------------------+---------------------------------+-------------+ | name | cuisine | avg(rating) | +--------------------------------+---------------------------------+-------------+ | Morris Park Bake Shop | Bakery | 8.2000 | | Wendy'S | Hamburgers | 9.4404 | | Dj Reynolds Pub And Restaurant | Irish | 9.2500 | | Riviera Caterer | American | 9.0000 | | Tov Kosher Kitchen | Jewish/Kosher | 17.7500 | | Brunos On The Boulevard | American | 17.0000 | | Kosher Island | Jewish/Kosher | 10.5000 | | Wilken'S Fine Food | Delicatessen | 10.0000 | | Regina Caterers | American | 9.6000 | | Taste The Tropics Ice Cream | Ice Cream, Gelato, Yogurt, Ices | 8.2500 | +--------------------------------+---------------------------------+-------------+ 10 rows in set, 13 warnings (1.5114 sec) Wanna see something even more cool ? Okay ! How easily would you answer this question in other Document Stores: Give me the 10 best restaurants but they all must be from a different cuisine ! (this means that if the two best restaurants are 2 Italians one, I only want to see the best of the two and the next restaurant in the list should be the third is it doesn’t server Italian food). In MySQL 8.0 Document Store we can achieve this with one query using a Common Table Expression (CTE): MySQL [localhost+ ssl/docstore] SQL> WITH cte AS (SELECT doc->>"$.name" AS name, -> doc->>"$.cuisine" AS cuisine, -> (SELECT AVG(score) FROM JSON_TABLE(doc, "$.grades[*]" -> COLUMNS (score INT PATH "$.score")) AS r) AS avg_score -> FROM restaurants) -> SELECT *, RANK() OVER (PARTITION BY cuisine ORDER BY -> avg_score) AS `rank` -> FROM cte ORDER BY `rank`, avg_score DESC LIMIT 10; +------------------------------------------+------------------+-----------+------+ | name | cuisine | avg_score | rank | +------------------------------------------+------------------+-----------+------+ | Ravagh Persian Grill | Iranian | 15.6667 | 1 | | Camaradas El Barrio | Polynesian | 14.6000 | 1 | | Ellary'S Greens | Californian | 12.0000 | 1 | | General Assembly | Hawaiian | 11.7500 | 1 | | Catfish | Cajun | 9.0000 | 1 | | Kopi Kopi | Indonesian | 8.6667 | 1 | | Hospoda | Czech | 7.8000 | 1 | | Ihop | Pancakes/Waffles | 7.2000 | 1 | | New Fresco Toetillas Tommy'S Kitchen Inc | Chinese/Cuban | 7.0000 | 1 | | Snowdonia Pub | English | 7.0000 | 1 | +------------------------------------------+------------------+-----------+------+ 10 rows in set, 13 warnings (1.4284 sec) Cool isn’t it ? So SQL is not mandatory anymore but it can still help. You can also do much more, like joining Documents with Relational Tables, etc… and of course don’t forget the large panel of JSON functions our engineers have implemented. 6. 16MB limitation for Document Unlike some other NoSQL solution, MySQL doesn’t limit the size of a document to 16MB. We were very surprised about the large amount of users complaining about this limitation.. but now they have a solution: MySQL ! A single document in MySQL Document Store can have a size of 1GB ! It’s limited by the size of max_allowed_packet. So if you have very large documents, MySQL is the best solution for you. In MySQL 8.0 we also improved how MySQL deals with such large documents. InnoDB implemented JSON partial updates and the replication team implemented Partial JSON for binary logs. 7. Simplest query syntax The goal of MySQL Document Store is not to be a drop-in replacement for MongoDB even if it’s very easy to migrate data out of it to MySQL. The goal of the new CRUD API is to provide to developers the most easy way to write application without having to deal too much with the database backend and be able to query that data in the easiest way possible. Therefor, MySQL uses a very simple syntax to query your documents stored in MySQL 8.0. Let’s compare the same query in MongoDB and MySQL: First in MongoDB: > db.restaurants.find({"cuisine": "French", "borough": { $not: /^Manhattan/} }, {"_id":0, "name": 1,"cuisine": 1, "borough": 1}).limit(2) { "borough" : "Queens", "cuisine" : "French", "name" : "La Baraka Restaurant" } { "borough" : "Queens", "cuisine" : "French", "name" : "Air France Lounge" } So what does that mean ? Check first in MySQL: MySQL [localhost+ ssl/docstore] JS> restaurants.find("cuisine='French' AND borough!='Manhattan'").fields(["name","cuisine","borough"]).limit(2) [ { "borough": "Queens", "cuisine": "French", "name": "La Baraka Restaurant" }, { "borough": "Queens", "cuisine": "French", "name": "Air France Lounge" } ] 2 documents in set (0.0853 sec) More obvious isn’t it ? That’s why we used that syntax ! 8. Security MySQL Document Store is by default already very secure: strong root password, password policy, roles, SSL. Several backups solutions are also available Those days, when you install and start a MySQL instances, an SSL certificate will be created to communicate with the clients on a secure link. Also the super privilege user (root) will automatically have a strong password that will be stored in the error log and it must be changed at the first login. The new password will have to follow some security rules that are also enabled by default. 2018-06-14T12:47:55.120634Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: (hdirvypB1iw So we can use that password to connect and set it do what we want: [root@mysql3 mysql]# mysqlsh root@127.0.0.1 Creating a session to 'root@127.0.0.1' Enter password: ************ Fetching schema names for autocompletion... Press ^C to stop. Error during auto-completion cache update: You must reset your password using ALTER USER statement before executing this statement. Your MySQL connection id is 0 (X protocol) No default schema selected; type \use to set one. MySQL Shell 8.0.11 Copyright (c) 2016, 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 '\?' for help; '\quit' to exit. MySQL [127.0.0.1+] JS> session.sql('set password="MyV3ryStr0gP4ssw0rd%"') Query OK, 0 rows affected (0.3567 sec) Now we can quit and login again and this time you can see the prompt changed to notify we are using SSL. We can verify this very easily: [root@mysql3 mysql]# mysqlsh root@127.0.0.1 Creating a session to 'root@127.0.0.1' Enter password: **** Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 9 (X protocol) Server version: 8.0.11 MySQL Community Server - GPL No default schema selected; type \use to set one. MySQL Shell 8.0.11 Copyright (c) 2016, 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 '\?' for help; '\quit' to exit. MySQL [127.0.0.1+ ssl] JS> \s MySQL Shell version 8.0.11 Session type: X Connection Id: 9 Default schema: Current schema: Current user: root@localhost SSL: Cipher in use: DHE-RSA-AES128-GCM-SHA256 TLSv1.2 Using delimiter: ; Server version: 8.0.11 MySQL Community Server - GPL Protocol version: X protocol Client library: 8.0.11 Connection: 127.0.0.1 via TCP/IP TCP port: 33060 Server characterset: utf8mb4 Schema characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 Uptime: 7 min 9.0000 sec And if you are looking for other security features, please check MySQL Enterprise Edition. 9. Simplify your DB infrastructure Instead of having to maintain multiple systems and often have duplicate data that is transferred from one system to another one, as MySQL Document Store can do SQL and NoSQL this is a valid alternative to consolidate your RDBMS and your NoSQL DB. Also, as already explained, you can mix Tables and Collections or run complex queries against your Documents. Additionally if you really want to split your systems but with the same data, it’s possible to rely on the very popular and strong MySQL replication. Easy HA architecture is also possible using MySQL InnoDB Cluster. 10. Your MySQL DBA already knows how to manage/tune/scale MySQL And finally,  if your team already manages MySQL instances and you want (or need) to use NoSQL, what more simple than just use the CRUD operations of MySQL 8.0 and keep using all your known procedures to maintain your infrastructure. Same backups, same monitoring, same way to  troubleshoot eventual problems. There is no need to invest in new team or knowledge to maintain yet another system. MySQL 8.0 Document Store offers you the best of both worlds and makes also both developers and DBA/OPS happy. I really encourage you to test it asap and don’t hesitate to send us your feedback !

  • MariaDB 10.1.34 and latest MariaDB Connectors now available
    The MariaDB Foundation is pleased to announce the availability of MariaDB 10.1.34, the latest stable release in the MariaDB 10.1 series, as well as MariaDB Connector/C 3.0.5, MariaDB Connector/C 2.3.6, MariaDB Connector/J 2.2.5, MariaDB Connector/J 1.7.4, MariaDB Connector/ODBC 3.0.5 and MariaDB Connector/ODBC 2.0.17, the latest stable MariaDB Connector releases. See the release notes and changelogs […] The post MariaDB 10.1.34 and latest MariaDB Connectors now available appeared first on MariaDB.org.

Who's Online
We have 21 guests online