A customer reported last month that MySQL does a full table scan (FTS) if a query was filtered by a INT value on a VARCHAR column. First I told him that this is not true any more because MySQL has fixed this behaviour long time ago. He showed me that I was wrong:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `data` (`data`)
) ENGINE=InnoDB;

EXPLAIN SELECT * FROM test WHERE data = 42\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ALL
possible_keys: data
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 522500
     filtered: 10.00
        Extra: Using where

EXPLAIN SELECT * FROM test WHERE data = …

I posted recently Lossless RBR for MySQL 8.0 about a concern I have about moving to minimal RBR in MySQL 8.0.  This seems to be the direction that Oracle is considering, but I am not sure it is a good idea as a default setting. I talked about a hypothetical new replication mode lossless RBR and also about … Continue reading The differences between IDEMPOTENT and AUTO-REPAIR mode

The post The differences between IDEMPOTENT and AUTO-REPAIR mode first appeared on Simon J Mudd's Blog.

In this blog, we’ll look at the MySQL 5.7 document store feature, and how it is implemented.

Document Store

MySQL 5.7.12 is a major new release, as it contains quite a number of new features:

1. Document store and “MongoDB” like NoSQL interface to JSON storage
2. Protocol X / X Plugin, which can be used for asynchronous queries (I will write about it as well)
3. New MySQL shell

Peter already wrote the document store overview; in this post, I will look deeper into the document store implementation. In my next post, I will demonstrate how to use document store for Internet of Things (IoT) and event logging.

Older MySQL 5.7 …

In this blog post we’ll discuss pt-online-schema-change, and how to correctly use it.

Always use pt-osc?

Altering large tables can be still a problematic DBA task, even now after we’ve improved Online DDL features in MySQL 5.6 and 5.7. Some ALTER types are still not online, or sometimes just too expensive to execute on busy production master.

So in some cases, we may want to apply an

ALTER

 first on slaves, taking them out of traffic pool one by one and bringing them back after the

ALTER

 is done. In the end, we can promote one of the already altered slaves to be new master, so that the downtime/maintenance time is greatly minimized. The ex-master can be altered later, without affecting production. Of course, this method works best when the schema change is …

Join Sveta on Thursday, May 26, 2016, at 10 am PDT (UTC-7) for her webinar Troubleshooting MySQL hardware resource usage.

MySQL does not just run on its own. It stores data on disk, and stores data and temporarily results in memory. It uses CPU resources to perform operations, and a network to communicate with its clients.

In this webinar, we’ll discuss common resource usage issues, how they affect MySQL Server performance, and methods to find out how resources are being used. We will employ both OS-level tools, and new features in Performance Schema that provide detailed information on what exactly is happening inside MySQL Server.

…

I've seen some confusion on what constitutes a keyword or a reserved word in MySQL. The manual defines them, and has a complete list. Reserved words are a special subset of keywords, and you can't use a reserved word as an identifier unless you quote it with backticks. I discourage the use of backticks to quote identifiers, because it allows you to use lots of words and characters in your identifier that you'll probably regret later. For example:

`` mysql> create tableYou will regret this!((╯°□°）╯︵ ┻━┻` int); Query OK, 0 rows affected (0.03 sec)

mysql> desc You will regret this!; +--------------------------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------------+---------+------+-----+---------+-------+ | …

This post is about setting up Master- Slave replication on MySQL database. Here we assume that, primary master server is up and running and you need to add another server as a slave to an existing server. The following step by step guide will help you to set up the replication.Step 1: Enable binary log on master server:

Binary logs are the source of information for the slave server to replicate the database from master server. The slave server downloads the binary logs and executes it as a SQL statement. In order to enable the binary logs add the following parameters to configuration file and restart the server.[mysqld]log-bin = /path/to/log/mysql-binserver-id = 1expire_logs_days = 15max_binlog_size = 1Gbinlog_format = MIXED

In order to verify whether the binary log enabled or not, just run of the following command.SHOW MASTER STATUS \G
SHOW BINARY LOGS;
SHOW VARIABLES LIKE ‘%log-bin%’;

Step 2: …

Percona is glad to announce the GA release of Percona XtraBackup 2.4.3 on May 23rd, 2016. Downloads are available from our download site and from apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered …

Windows Nano Server is a deployment option of the upcoming Windows Server 2016. It is a “headless” version of Windows Server, pared back to provide the bare essentials necessary to host server / “born in the cloud” applications such as MySQL.…

MySQL Replication can be fragile: whenever it encounters a connectivity error, it will retry and if it is a serious error, it will simply stop. Obviously in the latter case, you will need to repair the broken replication yourself.

The most common problems with replication are when it stops due to either master failure or network problems. In case the master fails, the whole topology becomes read-only and this means the write queries cannot be applied anymore. This is where normally you would promote one of the replicas to become the new master. To illustrate the difference in promotion between GTID and non-GTID cases, we will go through the manual promotion process below.

Most advanced slave without GTID

The first step in this promotion is to find the most advanced slave. As the master is no longer available, not all replicas may have copied and applied the same amount of transactions. So it is key to find the most …