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 …

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.

Does your dataset consist of InnoDB tables with large BLOB data such that the data is stored in external BLOB pages? Was the dataset created in MySQL version 5.1 and below and without using the InnoDB plugin, or with InnoDB plugin but with MySQL version earlier than 5.1.55? If the answer to both the questions are "YES" then it could very well be that you have a hidden corruption lying around in your dataset.

The post Beware of MySQL BLOB Corruption in Older Versions appeared first on ovais.tariq.

It has been a while since I have looked at InnoDB crash recovery. A lot has change in the last few years – we have serious crash recovery performance improvements in MySQL 5.5 and MySQL 5.6, we have solid state drives raising as typical high performance IO subsystem and we also have the ability to set much larger log files and often have a much larger InnoDB Buffer Pool to work with.

First let me revisit the challenge with have with InnoDB configuration. For write-intensive workloads it is extremely important to size innodb_log_file_size for good performance, however the longer log file size you have the longer you might have to wait for InnoDB to complete crash recovery, which impacts your recovery strategy.

How much can innodb_log_file_size impact performance? Massively! Doing intensive writes to a database that well fits in memory, I’d say there’s a 10x …

Does your dataset consist of InnoDB tables with large BLOB data such that the data is stored in external BLOB pages? Was the dataset created in MySQL version 5.1 and below and without using the InnoDB plugin, or with InnoDB plugin but with MySQL version earlier than 5.1.55? If the answer to both the questions are “YES” then it could very well be that you have a hidden corruption lying around in your dataset. The only way you would be able to find out about the corruption is when you have a crash with InnoDB assertion messages similar to the following:

InnoDB: Serious error! InnoDB is trying to free page 4
InnoDB: though it is already marked as free in the tablespace!
InnoDB: The tablespace free space info is corrupt.


In this post I will summarize what the bug is and how it corrupts the dataset. If you want more details of why and how the corruption manifests itself then you can additionally …

Somebody wanted to know how to find any non-unique indexes in information_schema of the MySQL. The query takes a session variable with the table name and returns the non-unique indexes by column names. It uses a correlated subquery to exclude the table constraints. A similar query lets you find unique indexes in MySQL. Both queries are in this post.

You set the session variable like this:

SET @sv_table_name := 'member_lab';

You can query the indexes result with the following query:

SELECT   s.table_name
,        s.index_name
,        s.seq_in_index
,        s.column_name
FROM     information_schema.statistics s
WHERE    s.table_name = @sv_table_name
AND      s.non_unique = TRUE
AND      NOT EXISTS
          (SELECT   null
           FROM     information_schema.table_constraints tc
           WHERE    s.table_name = tc.table_name …

My students often ask me about popular PHP frameworks and MySQL approaches. I wish a PHP framework choice was as clear as a JavaScript framework, like AngularJS. Unfortunately, PHP frameworks aren’t that clear cut.

It seems that the most popular PHP frameworks are: Laravel (1st), Phalcon (2nd), and Symphony2 (3rd). I found the following graphic (from December 2013) that highlights popularity by percentage of the market (though I can’t guarantee its accuracy). As far as jobs go, on Dice.com only Laravel had more than 3 positions referring to the framework by name. There were actually 42 PHP developer positions that mention Laravel out of 2,115 PHP developer …

The MySQL 5.6 Release has introduced some changes to how two phase commit works and is managed.  In particular, the commit phase of transactions to the binary log is now serialized and this behavior is something we identified fairly immediately.  We implement a group commit algorithm that needed to be altered so that TokuDB’s group commit to its recovery log would function effectively.

As part of our effort to verify the new Binary Log Group Commit functionality introduced in TokuDB 7.5.4 for Percona Server, we wanted to demonstrate the substantial increase in throughput scaling but also show the bottleneck caused by the skewed interaction between the binary log group commit algorithm in MySQL 5.6 and the transaction commit mechanism used in TokuDB 7.5.3 for Percona Server.  During our testing, we noticed that the throughput scaling was diminished when we turned on the binlog.

Here are the relevant system …

This is a long overdue blog post from London’s 44con Cyber Security conference back in September. A lot of old memories were brought to the front as it were; the one I’m going to cover in this blog post is: file carving.

So what is file carving? despite the terminology it’s not going to be a full roast dinner; unless you have an appetite for data which as you’re here I’m assuming you have.

The TL;DR of “what is file carving” is taking a target blob of data (often a multi GB / TB file) and reducing it in to targeted pieces of data, this could be for instance grabbing all the jpeg images in a packet capture / mysqldump; or pulling that single table/schema out of a huge mysqldump with –all-databases (if you’re not using mydumper you really …

The London MySQL Meetup Group hosted a new evening of MySQL related talks on December 11, this time in Canonical’s offices in the Blue Fin Building. Conveniently, it was timed so that it coincided with the Debian/Ubuntu packaging sprint, which I also attended.

We started with pizza, beer and small talk. Since this was my first meetup in London, there were lots of new and interesting people to meet and talk to.

The topic of the evening was “MySQL distributions: in the cloud and on bare metal”, but before we got that far, James Page and Robie Basak presented the results so far of our ongoing …