A question to the internal #DBA channel at work: »Is it possible to change a column type from BIGINT to VARCHAR ? Will the numbers be converted into a string version of the number or will be it a byte-wise transition that will screw the values?«

Further asking yielded more information: »The use-case is to have strings, to have UUIDs.«

So we have two questions to answer:

• Is ALTER TABLE t CHANGE COLUMN c lossy?
• INTEGER AUTO_INCREMENT vs. UUID

Is ALTER TABLE t CHANGE COLUMN c lossy?

ALTER TABLE is not lossy. We can test.

mysql> create table kris ( id integer not null primary key auto_increment);
Query OK, 0 rows affected (0.16 sec)

mysql> insert into kris values (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into kris select NULL from kris;
Query OK, 1 …

MySQL uses connection and config parameters from a number of possible sources. The easiest way to find out where it is looking for config files is to run

$ mysql --help | grep cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /Users/kkoehntopp/homebrew/etc/my.cnf ~/.my.cnf

As can be seen, my version of the MySQL client checks in this order

• /etc/my.cnf
• /etc/mysql/my.cnf
• /Users/kkoehntopp/homebrew/etc/my/cnf
• ~/.my.cnf

The cnf file is a file in dot-ini syntax, so you have [groups] and each group contains lines with key = value pairs. Which groups are read?

$ mysql --help | grep "groups are"
The following groups are read: mysql client

So in my case, I would create a /Users/kkoehntopp/.my.cnf looking like this:

[client] …

Percona Monitoring and Management (PMM) is an effective tool in tracking stats of the running MySQL servers. Especially, the timelines capability helps users to get the picture of how the given stats changes over tenure of the workload. PMM official packages are not yet available on ARM but part of the PMM (importantly the stats collector aka exporter) could be compiled on ARM that would facilitate reporting stats of the MySQL instance running on ARM to PMM-Server there-by allowing it to track MySQL on ARM.

Background

Few days back Agustin from Percona tried compiling PMM Client on ARM. You can read more about it here. I just extended the process to also compile mysqld_exporter that is needed to connect and collect MySQL stats.

Compiling mysqld-exporter

Steps assume …

During the MDS webinar on how to deploy WordPress on OCI using MDS (slides & video), I briefly explained how to deploy the full architecture on OCI using Resource Manager and Stacks.

The Stack for that architecture is now available on my github: https://github.com/lefred/oci-wordpress-mds/releases/tag/0.0.1

To deploy it, it’s very easy. In OCI’s Dashboard, go on “Resource Manager” and then choose “Stacks“:

Create a new stack and just drop the …

Today we will see how to use MySQL Database Service aka MDS with WordPress.

To achieve this easy task, we will use the architecture we already deployed in this article.

We have then two Compute Instances on OCI, 1 running WordPress (Apache and PHP) and one running MySQL 8.0.

The Plan

This is how we will proceed to migrate to MDS with minimal maintenance time, we will:

1. create a MDS instance
2. verify if the database is ready to act as replication source
3. dump the MySQL instance running on OCI for being migrated to MDS.
4. load the dump in MDS
5. create a user dedicated to the replication
6. create a replication channel on MDS (from OCI to MDS)
7. modify WordPress config to point to MDS

Create a MDS …

This is just a note to help out anyone that might want to use the mysql_config_editor command in their automation tools. 

the mysql_config_editor does not take a password argument so automation tools that might have before set your password in the .my.cnf file trying to use mysql_config_editor fails. 

It is possible and quite simple though with the expect tool. 

 yum -y install expect  

it works for apt-get also. 

So in this example, I will show a simple bash script version. 

1st.. my login path does not work... 

mysql --login-path=local

ERROR 1045 (28000): Access denied for user

Set this with expect 

You would execute this via your bash script.  

expect <<EOD

spawn mysql_config_editor set --login-path=local …

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