A cross tabulation query, or cross tab/crosstab for short, is used to display the result of an aggregation on two or more fields. The trick to this type of query is the transformation of rows of data to columns. That presents the data in a table format in which one field is displayed down the left side of a matrix (row headings) and the other across the top (column headings).
am using MariaDB mysql command. How do I see MySQL users in a MySQL/MariaDB database stored on my server? You need to use mysql database. The user table stores username/password, user privileges, and all other information.
Mirth Connect is an Open Source Integration Engine that is primarily used in the Health Care domain. This support various health care standards (HL7, XML, EDI/X12, DICOM etc) that will help enterprise to integrate different systems, and let them talk to each other in a common language.
MySQL parallel replication is a nice feature, but we still need to make sure we are aware of any potential issues it might bring. Most monitoring systems use the output of SHOW SLAVE STATUS to verify whether or not the slave is lagging behind the master. In today’s blog, I will show an issue with seconds_behind_master that one of our clients faced when running slave_parallel_works > 0. We found out that the reported seconds_behind_master from SHOW SLAVE STATUS was lying. To be more specific, I’m talking about bugs #84415 and #1654091.
Learn how to use and query JSON data in your MYSQL databases. SQL databases tend to be rigid. If you have worked with them, you would agree that database design though it seems easier, is a lot trickier in practice. SQL databases believe in structure, that is why it's called structured query language.
In my previous post, we saw the usage of MySQL Group Replication (MGR) in single-primary mode. We know that Oracle does not recommends using MGR in multi-primary mode, but there is so much in the documentation and in presentations about MGR behavior in multi-primary, that I feel I should really give it a try, and especially compare this technology with the already existing multiple master solution introduced in 5.7: multi-source replication.
In this blog, we’ll look at how to manually build Percona Server for MySQL RPM packages. Several customers and other people from the open source community have asked us how they could make their own Percona Server for MySQL RPM binaries from scratch. This request is often made by companies that want to add custom patches to our release. To do this, you need to make some modifications to the percona-server.spec file in the source tree, and some preparation is necessary.
In this blog post, we’ll look at how to set up ProxySQL for high availability. During the last few months, we’ve had a lot of opportunities to present and discuss a very powerful tool that will become more and more used in the architectures supporting MySQL: ProxySQL.
MySQL user management mostly done by database administrator and usually done via command line. But did you know that there is another way to do this task via GUI. On this tutorial I am going to show you how to add new MySQL/MariaDb using MySQL Workbench. MySQL Workbench is a powerful tool to manage MySQL server. We can easily connect and manage our server that installed locally or on a remote server.
In this blog post, I’ll discuss how to use the InnoDB buffer pool pre-load feature in MySQL 5.7 Starting MySQL 5.6, you can configure MySQL to save the contents of your InnoDB buffer pool and load it on startup. Starting in MySQL 5.7, this is the default behavior. Without any special effort, MySQL saves and restores a portion of buffer pool in the default configuration. We made a similar feature available in Percona Server 5.5 – so the concept has been around for quite a while.
MySQL Test Run or MTR for short, is a MySQL test program. It was developed to ensure that the MySQL server’s operation is as expected whether it be in terms of testing the functionality of new features or integrity of the old. There are suites of existing tests which are to be run whenever a change is introduced in any of the components of the server to see if there are any side effects as a direct result of the change.
This blog post discusses how better MySQL 8.0 benchmarks can improve MySQL in general. Like many in MySQL community, I’m very excited about what MySQL 8.0 offers. There are a lot of great features and architecture improvements. Also like many in the MySQL community, I would like to see MySQL 8.0 perform better. Better performance is what we always want (and expect) from new database software releases.
I was going through the MySQL Slow Query logs, as I often do, and came across a query that was consistently doing full table scans. If you know anything about relational database systems, the phrase, "full table scan," probably makes you squirm a little bit - they're generally bad for performance. So, I started investigating the problem only to be completely stumped. The query looked like it should have been using an index.
Clients with an existing application sometimes ask me to fix bugs, improve efficiency by speeding up the application, or add a new feature to some existing software. The first stage of this is researching the original code – so-called reverse engineering. With SQL databases, it is not always immediately obvious which SQL queries MySQL executed – especially if these queries were generated by a framework or some kind of external library.
Using tools like HeidiSQL for Windows, Sequel Pro for macOS, or the cross-platform MySQL Workbench, you can connect securely to your database over SSH, bypassing those cumbersome and potentially insecure steps. This brief tutorial will show you how to connect to a remote database using MySQL Workbench.
This blog post elaborates on the architecture and design of the transactional data dictionary that will be part of MySQL 8.0. Some descriptions of architecture will be implemented in later versions. See MySQL 8.0 Data Dictionary: Background and motivation.
In this blog, we’ll discuss things to watch out for during a MySQL downgrade. Previously, I wrote the blog MySQL upgrade best practices. Besides upgrading your MySQL version, sometimes you need to downgrade. When it comes to downgrading MySQL, there are two types of downgrade methods supported:
The future MySQL 8.0 will (probably) have a great new feature: support for index sort order on disk (i.e., indexes can be physically sorted in descending order). In the MySQL 8.0 Labs release (new optimizer preview), when you create an index you can specify the order “asc” or “desc”, and it will be supported (for B-Tree indexes). That can be especially helpful for queries like “SELECT … ORDER BY event_date DESC, name ASC LIMIT 10″ (ORDER BY clause with ASC and DESC sort).
So it is time to give some justifications for these constraints, which come from the SQL standard (except the exclusion of ORDER BY, LIMIT and DISTINCT, which is MySQL-specific, though also present in several well-known RDBMSs). Consider this example, producing numbers from 1 to 10:
By default, MySQL 5.7 is much “stricter” than older versions of MySQL. That can make your application fail. To temporarily fix this, change the SQL_MODE to NO_ENGINE_SUBSTITUTION (same as in MySQL 5.6):