Today I was doing some tests with XA transactions in MySQL 5.6.

The output of the XA RECOVER command to list transactions was hard to read because of the representation of the data column:

The good news is that 5.7 has transaction information in performance_schema:

mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode 
    -> from performance_schema.events_transactions_current;
+-----------------+-----------------+--------+--------+----------+-------------+
| trx_id          | isolation_level | state  | xid    | xa_state | access_mode |
+-----------------+-----------------+--------+--------+----------+-------------+
|            NULL | REPEATABLE READ | ACTIVE | x-1    | PREPARED | READ WRITE  |
| 421476507015704 | REPEATABLE READ | …

Somebody ran into the following error message trying to query the innodb_sys_foreign and innodb_sys_foreign_cols tables from the information_schema database:

      ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

It’s easy to fix the error, except you must grant the PROCESS privilege. It’s a global privilege and it should only be granted to super users. You grant the privilege global PROCESS privilege to the student user with the following command:

GRANT PROCESS ON *.* TO student;

Then, you can run this query to resolve foreign keys to their referenced primary key column values:

SELECT   SUBSTRING_INDEX(f.id,'/',-1) AS …

Hey,
Its fairly common. Please refer – http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

First step of successful MySQL data recovery is to find InnoDB pages with your data. Let’s call it first, because prerequisite steps are already done.

InnoDB page type is a two bytes integer stored in the header of a page. For MySQL data recovery two are important:

• FIL_PAGE_INDEX. Pages of this type are nodes of B+ Tree index where InnoDB stores a table.
• FIL_PAGE_TYPE_BLOB. So called external pages, where InnoDB keeps long values of BLOB or TEXT type.

stream_parser reads a stream of bytes, finds InnoDB pages and sorts them per type, per index or page id. It applies sophisticated algorithms tailored for particular page type. Of course, it assumes that page type in the header corresponds to the content of the page, otherwise it will ignore the page.

…

Join 28,000 others and follow Sean Hull on twitter @hullsean. MySQL slow query on RDS If you run MySQL as your backend datastore is there one thing you can do to improve performance across the application?. Those SQL queries are surely key. And the quickest way to find the culprits is to regularly analyze your […]

So I noticed a few different questions and posts about parsing a string out of another string recently. While some solutions included creating new functions and etc it can also be done within a single query in some cases.

For example, let us say that we are looking to pull out the domain from a URL. I will try to go into detail as to why and how this works.
We have the following table.

CREATE TABLE `parse_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `urldemo` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
+----+----------------------------+
| id | urldemo                    |
+----+----------------------------+
|  1 | http://www.mysql.com/      |
|  2 | …

Nos gustaría desearte unas felices fiestas en nombre del equipo de DBAHire.com. ¡Que todos vuestros sueños se hagan realidad en el 2015!

Fotografía tomada en el mercado de navidad de Valencia (España) en diciembre de 2014.

¡Que vuestras innodb_buffer_pool_reads sean bajas y vuestro Uptime alto en 2015!

We would like to wish you a happy holidays on behalf of the DBAHire.com team. May all your dreams come true in 2015!

Photograph taken at the Christmas market of Valencia (Spain) in December 2014.

May your MySQL innodb_buffer_pool_reads be low and your Uptime high in 2015.

This blog aims to make a performance comparison between the different MySQL versions/editions and also comparing the differents MySQL forks such as Percona Server and MariaDB.  Indeed number of improvements as been done to innodb storage engine in the last MySQL versions. You can find below some of the performance improvements applied to InnoDB these last years (non exhaustive list):

MySQL 5.0

1. New compact storage format which can save up to 20% of the disk space required in previous MySQL/InnoDB versions.
2. Faster recovery from a failed or aborted ALTER TABLE.
3. Faster implementation of TRUNCATE TABLE.

MySQL 5.5

1. MySQL Enterprise Thread Pool, As of MySQL 5.5.16, MySQL Enterprise Edition distributions include a thread pool plugin that provides an alternative thread-handling model designed to reduce overhead and improve performance.
2. …

Podcasts Learning Performance Server Tuning

In this episode we finish our series on MaxScale. Ear candy is about using MySQL and Galera for geographic replication, and at the Movies uses MySQL to build big data applications.