A screencast about the MariaDB release process.

(I recommend watching it in full screen 720p, so you can see the details.)

Some links mentioned in the video:

• The MariaDB homepage
• The AskMonty Knowledgebase
• Southeast Linux Fest
• Buildbot
• The MariaDB Buildbot
• The MariaDB Tools project on Launchpad
• The MariaDB Downloads page
• The …

The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes, InnoDB will not allow a column referenced by a foreign key constraint to be modified in such a way that the foreign key will reference a column with a mismatched data type. For instance, if we have these two tables:

CREATE TABLE foo (
  i INT NOT NULL PRIMARY KEY,
  j INT NOT NULL,
  INDEX(j),
  FOREIGN KEY (j) REFERENCES bar(j)
) ENGINE=INNODB;

CREATE TABLE bar (
  i INT NOT NULL PRIMARY KEY,
  j INT NOT NULL,
  INDEX(j)
) ENGINE=INNODB;

trying to do something like “ALTER TABLE bar DROP j” or “ALTER TABLE bar MODIFY COLUMN j j SMALLINT NOT NULL” will produce an error unless we first remove the foreign key constraint present in table “foo”. Indeed, if we try it, that’s exactly what happens:

(root@localhost) [foobar]> ALTER TABLE bar drop j;
ERROR 1025 (HY000): Error on rename of …

As a TokuDB storage engine developer, numerous times I’ve been struck by the similarities between MySQL Cluster and TokuDB. Namely, many times where I find myself thinking, “TokuDB would benefit from this feature”, I also end up thinking “MySQL Cluster would benefit from this feature” as well.

At first glance, one may wonder why. TokuDB is a storage engine designed to work well on big data, providing compression, agility, and performance, while MySQL Cluster is a distributed database solution (http://www.mysql.com/products/cluster/) that provides (among many other things) auto sharding and 99.999% availability. TokuDB’s innovation, Fractal Trees® indexes, are designed to drastically reduce the number of disk seeks performed, but TokuDB still operates on a hard disk. MySQL Cluster operates over a network. How can we be two peas in a pod?

But when I …

This post aims to provide detailed steps to get gprof data for MySQL client and server.
Compile time options: During compilation option -DENABLE_GPROF=1 is supposed to be set which would make it possible to collect gprof data for mysql cleint/server.
Thing to be keep in mind that this option doesn't have any effect if it is issued -DWITH_DEBUG=1 during compilation. The reason for this is, gprof aims to be enabled only for optimized non-debug linux builds.
Platform: This option works only for linux platform.
Collecting gprof data: Once compilation is done, change directory to place where MySQL is installed (path given in -DCMAKE_INSTALL_PREFIX option or /usr/local/mysql by default).
Following are the steps to collect gprof data. Start mysql server on a shell:

$ pwd /home/mayank/mysql-bin
$ ls bin COPYING data docs include INSTALL-BINARY lib man …

When I’m doing conventional ALTER TABLE in MySQL I can ignore default value and it will be assigned based on the column type. For example this alter table sbtest add column v varchar(100) not null would work even though we do not specify default value. MySQL will assign empty string as default default value for varchar column. This however does not work for pt-online-schema-change:

root@smt2:~# pt-online-schema-change --execute  --alter="add column v varchar(100) not null" D=sbtest,t=sbtest     Altering `sbtest`.`sbtest`...
Creating new table...
Created new table sbtest._sbtest_new OK.
Altering new table...
Altered `sbtest`.`_sbtest_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 10000060 rows...
Dropping triggers...
Dropped triggers OK.
Dropping new table...
Dropped new table OK.
`sbtest`.`sbtest` was not altered.
        (in cleanup) Error copying rows from `sbtest`.`sbtest` to `sbtest`.`_sbtest_new`: Copying …

You can use this MySQL function to generate names, (e-mail)addresses, phone numbers, urls, bit values, colors, IP address, etc.. As usual, the code is provided in a zip and the code is fully documented.

Yes, it's true, Oracle CEO Larry Ellison has bought 98% of the Hawaiian Island of Lanai for $500m.  The island was previously owned by David Murdock who bought Dole in 1985.  The island has 3,200 residents, two luxury resorts, two golf courses and is 88,000 acres (141 square miles) in size. 

Here are some alternate headlines & subheads

Ellison Buys Island of Lanai
Declares Bill & Melinda Gates Marriage to be in Violation of Oracle Terms of Service

Says "What Island?  I only wanted a closed off veranda.  Doesn't anyone listen to me?"

Thanks Safra Catz for Really …

Having duplicate keys in our schemas can hurt the performance of our database:

• They make the optimizer phase slower because MySQL needs to examine more query plans.

• The storage engine needs to maintain, calculate and update more index statistics

• DML and even read queries can be slower because MySQL needs update fetch more data to Buffer Pool for the same load

• Our data needs more disk space so our backups will be bigger and slower

In this post I’m going to explain the different types of duplicate indexes and how to find and remove them.

Duplicate keys on the same column

This is the easiest one. You can create multiple indexes on the same column and MySQL won’t complain. Let’s see this example:

mysql> alter table t add index(name);
mysql> alter table t add index(name);
…

This post is a step-by-step guide to set up Percona XtraDB Cluster (PXC) in a virtualized test sandbox. I used Amazon EC2 micro instances, but the content here is applicable for any kind of virtualization technology (for example VirtualBox). The goal is to give step by step instructions, so the setup process is understandable and it could serve as a reference architecture.

You will need 4 virtual machines. 3 for PXC and 1 for the client, which will have HaProxy. I used CentOS 6 as the operating system, the instructions are similar for any Linux distribution.

The client node will have HaProxy installed and it will redirect requests to PXC nodes. This approach works well in real-world scenarios too. Running HaProxy on the application servers instead of having them as dedicated entities gives you benefits like you don’t need an extra network roundtrip because of a loadbalancer and scalability of PXC’s load balancing layer scales …

If you are attending Chicago Tech Week this weekend, be sure to drop by the MySQL Hands On Lab Saturday (3:00pm – 4:45pm @ 4 – 7 A/B (222 Merchandise Mart Plaza, Chicago, IL)) as I have some promotional items that I need to give away. Or catch me at Lone Star PHP, June 20 & 30th, where Oracle is sponsoring the second year of this event.