Introduction In this article, I’m going to explain how we can index JSON columns when using MySQL. While other relational database systems provide GIN (Generalized Inverted Index) indexes, MySQL allows you to index a virtual column that mirrors the JSON path expression you are interested in indexing. Database table Let’s assume we have the following database book table: The properties column type is json, so we can store JSON objects as book properties. Querying MySQL JSON columns without an index If we try to filter one record by its associated title attribute... Read More
10 Older Entries »
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.
Recently there was a question about which clients were connected to a server being asked in the MySQL Community Slack. The relevant information is available from performance schema, as most connectors will send information about themselves when connecting:
select * from performance_schema.session_connect_attrs; +----------------+-----------------+------------------------+------------------+ | PROCESSLIST_ID | ATTR_NAME | ATTR_VALUE | ORDINAL_POSITION | +----------------+-----------------+------------------------+------------------+ | 130 | _pid | 17412 | 0 | | 130 | _platform | x86_64 | 1 | | 130 | _os | Linux-5.4.0 | 2 | | 130 | _source_host | maniacmansion | 3 | | 130 | …[Read more]
Introduction In this article, we are going to see how we can implement an audit logging mechanism using MySQL database triggers to store the old and new row states in JSON column types. Database tables Let’s assume we have a library application that has the following two tables: The book table stores all the books that are found in our library, and the book_audit_log table stores the CDC (Change Data Capture) events that happened to a given book record via an INSERT, UPDATE, or DELETE DML statement. The book_audit_log table is created... Read More
A new release of MySQL was out on July 13 (8.0.21).
Among all the changes, there are some that I have already tested and that I really appreciate.
Who stopped the MySQL server?
In previous releases, I can already see in the error log file who
stopped the MySQL server if this was done through the shutdown
In MySQL 8.0.20:
mysql> shutdown; Query OK, 0 rows affected (0.00 sec) mysql> exit; Bye # tail -2 /u01/app/mysql/admin/mysqld2/log/mysqld2.err 2020-07-23T20:39:08.049064+02:00 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.20). 2020-07-23T20:39:09.796726+02:00 0 [System] [MY-010910] [Server] /u01/app/mysql/product/mysql-8.0.20/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
In MySQL 8.0.21:
mysql> …[Read more]
In one of the latest post, I discussed how to transform a large MySQL table to JSON using Apache Spark. Well, that approach works for any tables with any volume. But it’s an overkill for simple and small tables. The good news is MySQL by default has a feature for …
Apache Spark is the de facto framework of the big data world. Any serious organization that’s dealing with big data uses Spark almost exclusively. Though, it has some caveats. For the starter, it’s hard to use. And it’s very confusing to get started with, even for those with a solid …
What a great event ! I got very positive feedback for this extended preFOSDEM MySQL Day 2020.
Before my summary, I wanted to thank again all speakers and attendees. Without you this event won’t even exist. And thank you to ICAB for hosting us yet another time !
As you may know, we had 2 parallel tracks for 2 consecutive days. We had very valuable technical content from the MySQL developers and also from the external community members who talked about their favorite database.
There was a large interest in MySQL NDB Cluster and it was obvious that the nice presentations we had, encouraged people to try the worlds highest performance open source in-memory database (and they can even test it on their laptop with DBDeployer). The NDB users were also happy with the new stuff and impressive numbers that Bernd …[Read more]
The schedule of the preFOSDEM Day is now available !
We had a lot of proposals to deal with. Also this is a MySQL event where we, the MySQL Team has the possibility to show to you, our Community, all what we have working on to improve MySQL but also new stuff. We also invite some of our friends from the MySQL Community to talk about their experience.
I think we did a good selection and propose you new content. We are extremely happy to have Saverio Miroddi from TicketSolve talking about MySQL 8.0, Uber talking about InnoDB Cluster, and Facebook about Binlog.
As you can see, we will have 2 rooms, where one will be dedicated mostly to SQL and Optimizer topics but also tutorials.
You can also see that this year we also want to put some spot lights to MySQL NDB Cluster. You will see what is it, how is it used and what’s new. We will also have 2 community speakers sharing their MySQL NDB knowledge: Giuseppe Maxia, …[Read more]
10 Older Entries »