The latest from the MySQL community

All over the Internet people are having trouble getting LOAD DATA and LOAD DATA LOCAL to work. Frankly, do not use them, and especially not the LOCAL variant. They are insecure, and even if you get them to work, they are limited and unlikely to do what you want. Write a small data load program as shown below.

Not using LOAD DATA LOCAL

The fine manual says:

The LOCAL version of LOAD DATA has two potential security issues:

• Because LOAD DATA LOCAL is an SQL statement, parsing occurs on the server side, and transfer of the file from the client host to the server host is initiated by the MySQL server, which tells the client the file named in the statement. In theory, a patched server could tell the client program to transfer a file of the …

If you have a New Relic Infrastructure Pro license, and unmonitored MySQL servers, there’s now an easy solution at your fingertips. With the New Relic MySQL integration you can monitor and graph almost any detailed metric you could possibly want. New Relic recently unified its analytics tools with New Relic One, a dashboard that provides quick access to all the New Relic tools. With an Infrastructure Pro subscription, you get access to:

• New Relic Infrastructure: Flexible, dynamic monitoring of your entire infrastructure, from services running in the cloud or on dedicated hosts, to containers running in orchestrated environments.
• New Relic Alerts: A flexible, centralized notification system that unlocks the operational potential of New Relic. Alerts is a single tool to manage alert policies and alert conditions for all of your New Relic data.
…

Completing the data lifecycle is often harder than originally expected: Deleting data can cost sometimes way more than inserting it in the first place. MySQL Partitions can offer a way out. We have an earlier post on the subject.

A sample table, and a problem statement

Let’s define a kind of log table, to which data is added with an auto_increment id value and some data.

#! /usr/bin/env python3

from time import sleep
from random import randint
from multiprocessing import Process

import click
import MySQLdb
import MySQLdb.cursors


db_config = dict(
    host="localhost",
    user="kris",
    passwd="geheim",
    db="kris",
    cursorclass=MySQLdb.cursors.DictCursor,
)

@click.group(help="Load and delete data using partitions")
def sql():
    pass

@sql.command()
def setup_tables():
    sql_setup = [
        "drop table …

A blog series about ProxySQL and Galera…

This post is the first of a series of blogposts on how to easily use ProxySQL to scale-out your application’s database workload on a Galera cluster. This series will explore the main concepts in configuring ProxySQL for Galera across three articles as follows:

– A first introductory post describing the minimal configuration needed for ProxySQL to monitor and manage a Galera Cluster.
– A second post describing how to setup a read/write split configuration for our Galera Cluster, using ProxySQL query rules.
– A third and final post with examples on specific cluster configuration options and more detailed explanations on why and how ProxySQL changes the nodes states based on configuration changes and monitored variables. Requirements

To illustrate how to configure ProxySQL for a Galera Cluster we will use a sample Galera Cluster …

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

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

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 …

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 …

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