Iterating schemas over time is not an uncommon thing. Often requirements emerge only after you have data, and then directed action is possible. Consequently, working on existing data, and structuring and cleaning it up is a common task.

In todays example we work with a log table that logged state transitions of things in freeform VARCHAR fields. After some time the log table grew quite sizeable, and the log strings are repeated rather often, contributing to the overall size of the table considerably.

We are starting with this table:

CREATE TABLE `log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` int NOT NULL,
  `change_time` datetime NOT NULL,
  `old_state` varchar(64) NOT NULL,
  `new_state` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

That is, our log table has an id field to allow individual row addressing, and then logs the state change of a …

When a table gets dropped, MySQL removes the respective .frm file. This post explains how to recover the table structure if the table was dropped.

You need the table structure to recover a dropped table from the InnoDB tablespace. The B+tree structure of the InnoDB index doesn’t contain any information about field types. MySQL needs to know that in order to access records of the InnoDB table. Normally, MySQL gets the table structure from the .frm file. But when MySQL drops a table the respective frm file removed too.

Fortunately, there’s one more place where MySQL keeps the table structure. It’s the InnoDB dictionary.

The InnoDB dictionary is a set of tables where InnoDB keeps information about the tables. I reviewed them in detail in a separate InnoDB Dictionary post earlier. After the DROP, InnoDB deletes records related to the dropped table from …

You can now watch the replay of our High Volume MySQL HA Use Case Webinar: SaaS Continuous Operations with Terabytes of Data; and learn how to guarantee continuous operations for a SaaS provider with billions of daily transaction and terabytes of data with Tungsten MySQL Clusters.

Tags:  Webinar MySQL use case tungsten clustering mysql cluster marketo High Availability …

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 | …

The Challenge Often with sensitive information, you need to have an audit log. Not just that a table had a select run, but that specific cells within the table were accessed.  Frequently data such as this will contain a classification level as part of the row, defining policies for how it is handled, audited, etc.… Facebook Twitter LinkedIn

I recently watched a fantastic Python Pandas library tutorial series on YouTube. Without a doubt, Pandas is great for all sorts of data stuff. On the same token, MySQL Shell in Python mode is quite powerful in the sense that Python and the MySQL Shell (version >= 8.0) are somewhat united in the same environment. Although Pandas is in a league all its own when it comes to data analysis, between the power of MySQL and Python, we can also perform some basic analysis easily in MySQL Shell Python mode. In this blog post, I will cover some basic data analysis using Python mode in the MySQL Shell. Continue reading to see examples…

Business vector created by freepik – www.freepik.com

OS, Software, and DB used:

• OpenSuse Leap …

ARM processors are fast gaining popularity in the High Performance Computing (HPC) space with multiple cloud providers providing powerful and flexible variants of ARM instances to boot. Users are still in a dilemma about whether running MySQL on ARM is really effective? To help ease this out we introduce a Cost-Performance-Model (#cpm). Model is generic in nature to help normalize computing configuration based on cost and could be used for other HPC kinds of software too.

USP of ARM

ARM is all about a lot of cores (may be less powerful compared to x86) running with lesser power there-by effectively generating cost savings. Let’s understand this fact with some data-points.

Above table shows that booting ARM resources on any cloud-provider is cost effective (compared to x86).

Key is to find out if the said saving could be realized when running software. This sounds easy, just run the same version …

If you are using XA transactions, then you’ve likely run into a few replication issues with the 2PCs (2 Phase Commits). Here is a common error we see in Percona’s Managed Services and a few ways to handle it, including injecting an empty XA transaction.

Last_Error: Error 'XAER_NOTA: Unknown XID' on query. Default database: 'punisher'. Query: 'XA COMMIT X'1a',X'a1',1'

What Does it Mean?

It means that replication has tried to commit an XID (XA transaction ID) that does not exist on the server. We can verify that it does not exist by checking:

replica1 [localhost:20002] {msandbox} ((none)) > XA RECOVER CONVERT XID;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data   |
+----------+--------------+--------------+--------+
|        1 |            1 |            1 | 0x2BB2 | …

In a recent article, I explained how to test MySQL Database Service (aka MDS) on OCI without having to setup a VPN.

A VPN is the recommended way and of course using some man-in-the-middle exposing the MySQL Classic and X Protocol port is something you should absolutely avoid !

So, in this article we will see another way to test or use MDS without VPN but using a more secure option.

SSH

The solution uses a SSH tunnel between your machine (laptop, etc..) and OCI.

How does it work ?

We have a MDS instance (10.0.1.3), and a compute instance (10.0.0.2). The compute instance is in the public subnet and has a public IP assigned to it.

Then we open a SSH tunnel to the compute instance’s public IP that forward all traffic to a certain port on localhost (we will use 3406) …

Author Andriy Lysyuk.

The unDROP for InnoDB tool can be used to recover corrupt MySQL databases. In this post, we will show how to repair a MySQL database if its files got corrupted and even innodb_force_recovery=6 doesn’t help.

The corruption of InnoDB tablespace may be caused by many reasons. A dying hard drive can write garbage, thus, page checksum will be wrong. InnoDB then reports to the error log:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.

MySQL is well known for its poor start-up script. A simple upgrade procedure may end up with two mysqld processes writing to the same tablespace. That leads to corruption too. Sometimes, power reset corrupts not only the InnoDB files, but the whole file system becomes unusable …