Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com
-
Webinar November 10: Google Cloud Platform – MySQL at Scale with Reliable HA
Google Cloud Platform (GCP), with its CloudSQL offering, has become a leading platform for database-as-a-service workload deployments for many organizations. Scale and High Availability have surfaced as primary goals for many of these deployments. Unfortunately, the attainment of these objectives has been challenging. Often, the answer has been to simply add more CloudSQL databases. Many, however, have found a better solution in Percona’s fully managed MySQL environment based in Google’s GCE. Percona’s fully managed MySQL offering provides benefits similar to CloudSQL, plus the ability to run MySQL with an unlimited number of tables and much more reliable database availability. This has empowered these organizations to reclaim control over their architecture decisions. With Percona’s fully managed MySQL, your database architecture choices are once again based on your needs and the needs of your workload, rather than the capabilities of the underlying platform. Learn more about GCP and the benefits of Percona’s fully-managed database service to scale and consolidate your databases. Please join Stephen Thorn and Michal Nosek, Percona Solution Engineers, on Tuesday, November 10th, at 1 pm EDT for their webinar “Google Cloud Platform: MySQL at Scale with Reliable HA“. Register for Webinar If you can’t attend, sign up anyway and we’ll send you the slides and recording afterward.
-
MySQL JSON_TABLE – Map a JSON object to a relational database table
Introduction In this article, I’m going to explain how the MySQL JSON_TABLE function works, and how you can use it to transform a JSON object into a relational database table. When using a relational database system, it’s best to design the database schema according to the relational model. However, there are certain use cases when the relational model is too strict, and we are better off storing data in a JSON column type. For instance, as I explained in this article, when designing an audit log table, it’s much more convenient to... Read More The post MySQL JSON_TABLE – Map a JSON object to a relational database table appeared first on Vlad Mihalcea.
-
Galera Cluster for MySQL 5.6.49, 5.7.31, and 8.0.21 released
Codership is pleased to announce a new Generally Available (GA) release of the multi-master Galera Cluster for MySQL 5.6, 5.7 and 8.0, consisting of MySQL-wsrep 5.6.49 (release notes, download), 5.7.31 (release notes, download), and 8.0.21 (release notes, download) with Galera Replication library 3.31 (release notes, download) implementing wsrep API version 25 for 5.6 and 5.7, and Galera Replication library 4.6 (release notes, download) implementing wsrep API version 26 for 8.0. This release incorporates all changes to MySQL 5.6.49, 5.7.31 , and 8.0.21 respectively, adding a synchronous option for your MySQL High Availability solutions. It is recommend that one upgrades their Galera Cluster for MySQL 5.6, 5.7 and 8.0 because it releases a fix for security vulnerability CVE-2020-15180. The binary tarball is also compiled with OpenSSL 1.1.1g. A highlight of this release is that with MySQL 8.0.21, you will now have access to using the Percona audit log plugin, which will help with monitoring and logging connection and query activity that has been performed on specific servers. This implementation is provided as an alternative to the MySQL Enterprise Audit Log Plugin. In addition to fixing deadlocks that may occur between DDL and applying transactions, in 8.0.21 the write-set replication patch is now optimised to work with the Contention-Aware Transaction Scheduling (CATS) algorithm that is present in InnoDB. You can read more about transaction scheduling in the MySQL manual. For those that requested the missing binary tarball package, the MySQL 8.0.21 build includes just that. Packages continue to be available for: CentOS 7 & 8, Red Hat Enterprise Linux 7 & 8, Debian 10, SLES 15 SP1, as well as Ubuntu 18.04 LTS and Ubuntu 20.04 LTS. The latest versions are also available in the FreeBSD Ports Collection. The Galera Replication library has had some notable fixes, one of which improves memory usage tremendously. The in-memory GCache index implementation now uses sorted std::deque instead of std::map, and this leads to an eightfold reduction in memory footprint. Hardware CRC32 is now supported on x86_64 and ARM64 platforms. There are also three new status variables added: wsrep_flow_control_active (to tell you whether flow cotrol is currently active (replication paused) in the cluster), wsrep_flow_control_requested (to tell you whether the node has requested a replication pause because the received events queue is too long) and wsrep_gmcast_segment (to tell you which cluster segment the node belongs to). For Galera Replication library 3.31, this is the last release for Debian Jessie and openSUSE 15.0. For Galera Replication library 4.6, this is the last release for openSUSE 15.0. For MySQL-wsrep 5.6 and 5.7, this is also the last release for Debian Jessie. For MySQL-wsrep 5.7 and MySQL-wsrep 8.0, this is the last release for openSUSE 15.0.
-
MySQL Shell get_auto_increment_value() method – Python mode
These days, I mostly program in PHP with MySQL as the database, which is just fine by me. I have had a long-time interest in MySQL (SQL in general), and after several steady months of programming in PHP, I must say I have really come into the language and developed a fondness for it. All that being said, I still enjoy using and learning the MySQL Shell in Python mode. As Database Developers, we often need the LAST INSERT ID value from a previous INSERT statement on a column that has the AUTO_INCREMENT attribute. MySQL Shell has a get_auto_increment_value() method we can call against a Shell object result and retrieve that value. Continue reading and see examples of the MySQL Shell get_auto_increment_value() method used in Python mode… Photo by Pierpaolo Riondato on Unsplash OS and DB used: Linux Mint 20 “Ulyana” MySQL 8.0.21 Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! MySQL Shell get_auto_increment_value() method simple example Getting started, I’ll create a practice table containing 2 columns: an ‘id’ column with the AUTO_INCREMENT attribute and a ‘name’ column of type VARCHAR, using the MySQL Shell in \py mode. I’ll store the CREATE TABLE statement in a variable name ‘CREATE_TBL’ for easier processing: mysql-py [learning]> CREATE_TBL = """ CREATE TABLE auto_test(id INTEGER AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY at_id (id)) ENGINE=InnoDB""" The CREATE TABLE command itself does not have a dedicated shell method (at the time of writing that I am aware of). In order to execute that type of MySQL command, we must call the sql() method against a session object, providing the MySQL statement as the argument: mysql-py [learning]> db.session.sql(CREATE_TBL).execute() Query OK, 0 rows affected (2.0548 sec) With the table named, ‘auto_test’, I’ll pass in ‘auto_test’ as the parameter to the get_table() method, creating a working access handle on the table itself. We can see in calling the count() method against the ‘auto_test’ table object, that the ‘auto_test’ table as of now, is empty: mysql-py [learning]> auto_test = db.get_table('auto_test') mysql-py [learning]> auto_test.count()0 Using the insert() and values() methods, I’ll add a single row to the ‘auto_test’ table, chaining on the execute() method and completing the INSERT: mysql-py [learning]> auto_test.insert('name').values('Joshua').execute() Query OK, 1 item affected (0.3200 sec) Since the ‘id’ column has been created with the AUTO_INCREMENT attribute, we can retrieve the LAST_INSERT_ID value by executing the MySQL statement, SELECT LAST_INSERT_ID(). Again, we need to use the sql() method for this query: mysql-py [learning]> last_insert_id = db.session.sql("SELECT LAST_INSERT_ID()").execute().fetch_one() mysql-py [learning]> last_insert_id[ 1] The output shows the value of 1 as the last inserted id. Confirming with a follow-up select(), we see that is correct: mysql-py [learning]> auto_test.select().execute()+----+--------+| id | name |+----+--------+| 1 | Joshua |+----+--------+1 row in set (0.0009 sec) Inherently, there is nothing wrong or incorrect in doing this. However, there is another Shell type of way we can use. Let’s insert another row into the ‘auto_test’ table: mysql-py [learning]> result = auto_test.insert('name').values('Jessica').execute() However, this time around, we use the MySQL Shell get_auto_increment_value() method and retrieve the most recently inserted id: mysql-py [learning]> last_insert_id = result.get_auto_increment_value() mysql-py [learning]> last_insert_id2 In querying with the select() method, we see the expected results: mysql-py [learning]> auto_test.select().execute()+----+---------+| id | name |+----+---------+| 1 | Joshua || 2 | Jessica |+----+---------+2 rows in set (0.0009 sec) MySQL Shell get_auto_increment_value() method with multiple connections Shown in the following example queries, we can see that the MySQL Shell get_auto_increment_value() method works in other sessions just as it should. Let’s establish another connection, insert() a row, and then check the get_auto_increment_value(): --session 2 mysql-py [learning]> auto_test2 = db.get_table('auto_test') mysql-py [learning]> result2 = auto_test2.insert('name').values('Jeremy').execute() mysql-py [learning]> sess_2_insert_id = result2.get_auto_increment_value() mysql-py [learning]> sess_2_insert_id3 Executing this select(), we can see the last inserted id is 3: --session 1 mysql-py [learning]> auto_test.select().execute()+----+---------+| id | name |+----+---------+| 1 | Joshua || 2 | Jessica || 3 | Jeremy |+----+---------+3 rows in set (0.0009 sec) Now back in session 1, I’ll execute() a multi-valued insert() by chaining multiple values() methods together and assigning the results to a result object named ‘another’: mysql-py [learning]> another = auto_test.insert('name').values('Malory').values('Manny').execute() mysql-py [learning]> another.get_auto_increment_value()4 In the query results above, the call to get_auto_increment_value() shows 4 as the last inserted id, which is for the ‘Malory’ row as that particular row is the first one in the multi-row insert: mysql-py [learning]> auto_test.select().execute()+----+---------+| id | name |+----+---------+| 1 | Joshua || 2 | Jessica || 3 | Jeremy || 4 | Malory || 5 | Manny |+----+---------+5 rows in set (0.0011 sec) Semi-related: I mentioned in the opening paragraph that I program in PHP a great deal. Check out the post, PHP PDO lastInsertId() method with examples in MySQL, where I cover examples of retrieving the last insert id using PHP’s PDO. Once again, the MySQL Shell is right on the mark, providing DBA’s and Developers the get_auto_increment_value() for these specific types of queries in which we need the value for that last inserted id value. Recommended Reading I’ve written several blog posts on MySQL Shell Python mode so do visit any of the below posts that you are interested in: Basic Data Analysis with MySQL Shell Python mode MySQL Shell Python mode for multiple ALTER TABLE statements – easily Exploring .count() and COUNT() – MySQL Shell Python mode Transaction in MySQL Shell – Python mode with examples CREATE TABLE using Python in the MySQL Shell – with examples. ALTER TABLE ADD COLUMN – MySQL Shell Python style For in-depth information on the methods used in the post and other MySQL Shell Python mode related material, visit the online X DevAPI User Guide for MySQL Shell in Python Mode documentation. As always, if you see any corrections in the code I need to make or improve on, please let me know via the comments below. MySQL Shell is a fantastic, alternative environment in which to work with data. If you need to retrieve the last insert id from a MySQL column with the AUTO_INCREMENT attribute, try the get_auto_increment_value() method. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The post MySQL Shell get_auto_increment_value() method – Python mode appeared first on Digital Owl's Prose.
-
Plan your MySQL upgrade
I've made a short video that will give you tips and tricks to successfully upgrade to MySQL 8 The post Plan your MySQL upgrade first appeared on dasini.net - Diary of a MySQL expert.
