Taking the inspiration of Valeriy Kravchuk great series of blog posts “Fun with Bugs” (and not http://funwithbugs.com/ which is about both caring for and eating bugs), and since I recently went and run Coverity against Drizzle, I thought I’d have a small series of posts on bugs that it has found (and I’ve fixed).

An idea that has been pervasive in the Drizzle project (and one that I rather subscribe to) is that there is two types of correct: correct and obviously correct. Being obviously correct is much, much better than merely being correct.

The first category of problems that Coverity …

As promised, this is the first part of details about MySQL 5.6 vs 5.5 benchmark results I've published earlier last week. The following graphs are representing scalability results obtained for both MySQL versions on the published tests (and I have yet more test results to present to you, but these test's are still running)..

Few remarks based on comments and discussions I've got since then:

• I'm using a "true" 32cores server (true 32 cores, each one yet has 2 threads (HT), so 64 threads in total)
• I'm not using "CPU threads" terminology as I'm finding it confusing (for ex. when you're reading "16 CPU threads" you may not really know if there were 16cores with HT-disabled, or 8cores with HT-enabled)..
• during all the tests I've disabled HT (as it took days and days more to test …

Some time ago, I had to convert all tables of a database from MyISAM to InnoDB on a new server. The plan was to take a logical dump on the master, exporting separately the schema and the data, then edit the CREATE TABLE statements to ensure all tables are created with InnoDB, and reload everything on the new server.

Quite easy, isn’t it? Of course I wanted to run a test first before performing the actions on a live system.

So let’s play with the sakila database.

mysqldump has options to export schema and data separately, let’s use them:

# Export schema
$ mysqldump --no-data sakila > schema.sql

# Export data
$ mysqldump --no-create-info sakila > data.sql

Just to check that everything is fine, let’s reimport the data in a new database:

mysql> CREATE DATABASE sakila2;
Query OK, 1 row affected (0.00 sec)

$ mysql sakila2 < schema.sql 
$ mysql sakila2 < data.sql …

Get the most out of the great new release, MySQL 5.6, by taking the MySQL for Database Administrators training course.

During this 5 day instructor-led course you configure replication between multiple instances, design advanced replication topologies and recover from replication, network, or hardware failure taking advantage of MySQL 5.6 replication performance.

You also gain knowledge on the following:

Set up the Server and Databases

This course teaches you how to install, configure, maintain and tune databases for a wide variety of workloads and environments. You will install and configure MySQL and set appropriate configuration options. You will also connect to the server, using …

Archive can solve disk storage issues – but you must always consider the caveats and bugs related to a storage engine before going ahead and using it.

One of our Remote DBA service clients recently had an issue with size on disk for a particular table; in short this table was some 25 million rows of application audit data with an on disk size of 345GB recorded solely for the purposes of debugging which may or may not occur.

Faced with the task of both reducing the size on disk and ensuring that the data is always available at all times (firmly ruling out off device archive), we began to explore the available options for the client.

We settled on the following options for further testing.

1. InnoDB’s inbuilt row compression
2. MySQL’s …

“With MySQL 5.6, developers can now commingle the “best of both worlds” with fast key-value look up operations and complex SQL queries to meet user and application specific requirements” –Tomas Ulin. On February 5, 2013, Oracle announced the general availability of MySQL 5.6. I have interviewed Tomas Ulin, Vice President for the MySQL Engineering team [...]

During one of my last MySQL mission, I encountered a "security error" with mysqldump. The mission was about migrating a MySQL database from one box to another MySQL version and from Windows to Linux with different users. In order to do that, I used mysqldump. During the import of the data, I got the following warning:

ERROR 1449 (HY000) at line 1860: The user specified as a definer ('cdadmin'@'%') does not exist

Following the migration, I did the backup strategy. During the first mysql dump, I got the following error message:

mysql@mysql001: [mysqld2] mysqldump test
Running mysqldump for instance mysqld2 to /u99/mysqlbackup/mysqld2/dump/2012-11-14_15-48-23, please wait...
MySQL dump of instance mysqld2 to /u99/mysqlbackup/mysqld2/dump/2012-11-14_15-48-23 has failed.
mysqldump: Couldn't execute 'show table status like …

If you need to compare the structure or maybe even the content of two MySQL databases you will need a tool which will help you with this not too straight task. Here is an overview of some application which will help you succeeding the work.

Percona Toolkit 
Has several database comparison and syncing tools among other things.

Liquibase
Open source library for tracking, managing and applying database changes.

Toad
Can do both a “Schema Compare” as well as a “Data Compare”

MySQL Diff
Free command line tool. Does a good job comparing the table structures, but does not support all MySQL features.

…

While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are. You can read the rest of the post here.

While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.

The post Implications of Metadata Locking Changes in MySQL 5.5 appeared first on ovais.tariq.