Fri, 2014-10-24 09:21maxmether

This is a continuation of my previous blog, where we will focus on some more advanced features related to Dynamic Columns. For an introduction to Dynamic Columns please refer to my previous blog.

Dynamic Columns within WHERE

I started todays example by adding a few more items into my table, namely my two laptops to be able to produce more meaningful results. As with any function, dynamic column functions can generally be used in the WHERE clause of SELECT and other SQL statements which manipulate data. Let's execute another SELECT statement with COLUMN_GET() in the WHERE clause:

SELECT id, name AS 'White Items'
FROM items
WHERE COLUMN_GET(attributes, 'colour' AS CHAR) = 'white';

+----+----------------------+
| id | White Items          |
+----+----------------------+
|  3 | Samsung Galaxy …

A few weeks ago, we received an interesting Galera Cluster support case from one of our customers that the application is not working well and they face a lot of troubles in their Galera Cluster setup.

After some investigations, we found a lot of insert queries in state "query end" and lasting for long time without being completed. Also some other queries which were sleeping for long time having the info of "XA COMMIT":

SQL> SHOW PROCESSLIST;

27      user    host:33214      foodmart        Query   14440   sleeping        XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x

SQL> SHOW ENGINE INNODB STATUS;

TRANSACTIONS
============
---TRANSACTION 2DE71D, ACTIVE 14459 sec
9 lock struct(s), heap size 1248, 1 row lock(s), undo log entries 115
MySQL thread id 27, OS thread handle 0x7fc21a42c700, query id 96187 host host-ip foodmart …

Taxonomy upgrade extras:  myenv operation MySQL Operations multi instance consolidation Backup catalog

FromDual has the pleasure to announce the release of the new version 1.1.2 of its popular MySQL, Galera, MariaDB and Percona Server multi-instance environment MyEnv.

You can download MyEnv from …

Yesterday (Oct. 22) I gave a presentation titled “MySQL 5.6 Full Text Search Throwdown.” If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:

Q: Does Solr automatically maintain its index against MySQL? Do you have to hit the Solr server with a specific query to keep the index ‘warm’?

There are several strategies for updating a Solr …

As a part of improving the error handling, in MySQL 5.7.5 we have re-implemented STRICT sql mode.

STRICT mode in MySQL affects the errors that arise from invalid, missing, or out of range values in DML statements such as INSERT, UPDATE, and DELETE. The new implementation aims to make the behavior of STRICT mode more consistent, yet maintain backward compatibility as much as possible.

In MySQL 5.7.5, we have made three general improvements to STRICT mode behavior:

 1. STRICT mode got simpler

It was observed that having a large number of sql modes dependent on STRICT mode creates confusion among users. Specifically, we are talking about NO_ZERO_DATE, NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO modes. You can se further details on the above sql modes here. These modes only …

With a last minute change to the host_summary_by_stages view for the 1.2.0 release of the sys schema, I managed to break the views without noticing it.

So, in the shortest release cycle for the sys schema ever (less than 24 hours), 1.3.0 has now been released.

This comes with a bonus though, there was also a new view that was contributed by Jesper Wisborg Krogh waiting to be merged as well, so I’ve added that to the 1.3.0 version (hence the 1.3.0 instead of 1.2.1).

This is the innodb_lock_waits view, which shows all sessions that are waiting for a lock within InnoDB, as well as the details of who is blocking them, here’s an example output:

mysql> SELECT * FROM innodb_lock_waits\G
*************************** 1. row ***************************
     waiting_trx_id: 805505 …

Introduction

Online DDL is a new feature in MariaDB 10.0. Online DDL is processed through below 4 tasks in sequence.

1. InnoDB::ha_prepare_inplace_alter_table(..)
2. InnoDB::ha_inplace_alter_table(..)
3. InnoDB::ha_commit_inplace_alter_table(..)
4. mysql_rename_table(..)

InnoDB storage engine allocates temporal memory buffer for transaction logging in phase 1 where row changes during this phase are logged. Size of this buffer is at start sort_buffer_size and it can be grown up to innodb_online_alter_log_max size. During phase 2 thread processing the ALTER statement will copy old table’s rows to a new altered table. After this MariaDB will take exclusive lock for target table and applies row log buffer to the new altered table.

This introduces a new unpredictable failure case row log buffer overflow. MariaDB server will rollback ALTER statement if row log buffer …

One of the most popular plugin types both in MariaDB and MySQL is INFORMATION_SCHEMA plugin type. INFORMATION_SCHEMA plugins add new tables to the INFORMATION_SCHEMA. There are lots of INFORMATION_SCHEMA plugins, because they can be used to show just anything to the user and are very easy to write.

MariaDB 10.1.1 comes with nine INFORMATION_SCHEMA plugin:

• Feedback — shows the anonymised server usage information and can optionally send it to the configured url.
• Locales — lists compiled-in server locales, implemented by Roberto Spadim
• METADATA_LOCK_INFO — Lists metadata locks in the server. Implemented by Kentoku Shiba
• …

Taxonomy upgrade extras: BackupRestoreRecoveryinnodbtable

Precaution: Before you try this out on your production system do a BACKUP first! FromDual Backup Manager can help you with this.

Situation

A MySQL user has delete its InnoDB table files for example like this:

shell> rm -f $datadir/test/test.*

Analysis

We do some analysis first:

mysql> DROP TABLE test;
ERROR 1051 (42S02): Unknown table 'test' …

MySQL sys version 1.2.0 has just been released.

All views now work with MySQL 5.7.5, and the ONLY_FULL_GROUP_BY changes.

There is also a new script available (generate_sql_file.sh) that will allow RDS users to easily generate a single SQL file, using a specified user, that can be loaded in to an RDS instance. See the README for details on how to use that.

Here’s a full summary of the other changes:

Backwards Incompatible Changes

• The host_summary_by_stages and user_summary_by_stages wait_sum and wait_avg columns were renamed to total_latency and avg_latency respectively, for consistency.
…