First, I have to tell you, that the “fragmentation” is not the best word what I should use, but that was the closest to that what I wanted to say. The base of the fragmentation checker script is Peter Zaitchev’s 2008 article about Information_schema, I used that query to get that results what I needed.

Some background: a few weeks ago we got some free space related problems (well, they weren’t a real problem, but they could lead onto one, and we had to act fast.) The innodb is working on a bit strange way, it is not like to free up space what is deleted before, and because of this, the datafiles will grow to the end of the world. The first thing what we could do to reclaim free space is to use

innodb_file_per_table

configuration option, what will split up the database to a lot of …

Percona is glad to announce the release of Percona XtraBackup 2.1.2 for MySQL on May 18, 2013. Downloads are available from our download site here and Percona Software Repositories.

This release fixes number of high-priority bugs since version 2.1 became GA. It’s advised to upgrade your latest 2.1 version to 2.1.2. This release is the latest stable release in the 2.1 series.

Bugs Fixed:

• Using Perl’s DBD::MySQL package for server communication instead of spawning the MySQL command line client …

I actively maintain virtual test instances for my writing and teaching on Windows 7, Red Hat Enterprise Linux, Fedora, and Solaris. Perl on Windows is always interesting. I use ActivePerl on Windows 7 rather than Strawberry Perl, and it was interesting to see this note after I upgraded to the most current Community Edition of ActivePerl (5.16.3).

I thought it might be kind to post ActiveState’s Release Note because you should really read it before you try to install ActivePerl on Windows 7/8. Just make sure you’ve removed any earlier version of ActivePerl before trying the install. That’s what I did, and surprise, there weren’t any problems.

While the installation doesn’t tell you that you need to restart the Windows 7 operating system, you do. If you don’t restart Windows 7 after the ActivePerl install, you’ll get the following error message:

      install_driver(mysql) failed: …

Just recently did the presentation and Q&A for the Using Perl Stored Procedures presentation at Percona Live 2013.

The presentation has been uploaded:

Using Perl Stored Procedures for MariaDB from Antony T Curtis

(repeat posting because planet.mysql.com didn't notice it the first time around)

Just recently did the presentation and Q&A for the Using Perl Stored Procedures presentation at Percona Live 2013.

The presentation has been uploaded:

Using Perl Stored Procedures for MariaDB from Antony T Curtis

This post was initially going to be two sets of polls: “What is the maximum number of columns in MySQL?” and “What is the minimum maximum number of columns in MySQL?”. Before you read on, ponder those questions and come up with your own answers… and see if you’re right or can prove me wrong!

Back in 2009, I finished what seemed an epic task in the Drizzle code base: banishing the FRM file. Why? We felt it was not a good idea to keep arbitrary and obscure limitations from the 1980s alive in the 21st century and instead wanted a modular system where the storage engines themselves owned their own metadata. This was a radical departure from the MySQL philosophy, and one that has truly paid off in the Drizzle code base. However… for those using MySQL, Percona Server, MariaDB or any other of the MySQL branches/forks, you …

This month is a special month. It’s not because of President’s Day or even the exciting day where we revel in groundhogs. No, this month is special because the free book give-away is happening again. This is where you, the reader, gets to win something free for doing nothing more than posting a comment saying that you want a copy of my recently published book – The InnoDB Quick Reference Guide from Packt Publishing. The book is a great reference for DBAs, PHP, Python, or Perl programmers that integrate with MySQL and want to learn more about the InnoDB database engine.

So, all you have to do is post a comment here saying that you want a copy and write out a single (or more) sentence about how you use InnoDB in your development or production environment. At the end of the month two readers will be chosen via a random list sorting script that …

I’m pleased to announce that my first book, the InnoDB Quick Reference Guide, is now available from Packt Publishing and you can download it by clicking here. It covers the most common topics of InnoDB usage in the enterprise, including: general overview of its use and benefits, detailed explanation of seventeen static variables and seven dynamic variables, load testing methodology, maintenance and monitoring, as well as troubleshooting and useful analytics for the engine. The current version of MySQL ships with InnoDB as the default table engine, so whether you program your MySQL enabled applications with PHP, Python, Perl or otherwise, you’ll likely benefit from this concise but comprehensive reference guide for InnoDB databases.

Here are the chapter overviews …

Example of a connection to MySQL with Perl using DBI

 
#! /usr/bin/perl -w
#
#  Example code to connect to MySQL, create a table, fill it with some data and select it again.
#
#  To install DBI using cpan:
#
#  perl -MCPAN -e shell
#  cpan> install DBI
#  cpan> install DBD::mysql
#

use strict;
use DBI;

my $database = "test";
my $user = "user";
my $passwd = "secret";
my $sqlCreate = "CREATE TABLE IF NOT EXISTS test ( pkey int(11) NOT NULL auto_increment, a int, b int, c int, timeEnter timestamp(14), PRIMARY KEY  (pkey) ) ";

my $insert = "insert into test (a,b,c) values (1,2,3),(4,5,6),(7,8,9)"; 
my $select = "select a,b,c from test"; 
my $dsn = "DBI:mysql:host=localhost;database=${database}";
my $dbh = DBI->connect ($dsn, $user, $passwd) or die "Cannot connect to server\n";

# Execute the sql to create the table test
my $stmnt = $dbh->prepare($sqlCreate);
$stmnt->execute();

# Insert the testdata
$stmnt = $dbh->prepare($insert); …

Before I upgrade MySQL to the latest and greatest version, one of the first things that I do is export the user and grant information. In the past, I would keep all of my user information (user name, password, grants) in a text file, with the SQL for each user/grant ready to be executed on the upgraded server. I did use my own form of “mental encryption” for my passwords, so the passwords weren’t in plain English. But then I would have to decode my passwords each time before I executed the SQL statements.

When I upgrade, I usually like to dump all of the data and import it into the new version, so I have a fresh copy of the database. The MySQL server that I have is for my personal use and the data size is relatively small, so for my case it doesn’t take long to import the data.

But there were times when I would add a user in the MySQL database and forget to add it to my text file. Then, when it came time to upgrade and I …