Yesterday evening, a friend of mine had some issues with installing DBD::mysql, and asked if I had encountered the same issue. The problem, as the output from "make test" showed, was that certain symbols was missing:# Tried to use 'DBD::mysql'.# Error: Can't load '/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Symbol not found: _is_prefixFair enough, this is related to a 64-bit issue with MySQL---at least with my Perl version, which is now: Summary of my perl5 (revision 5 version 10 subversion 0) configuration: Platform: osname=darwin, osvers=9.5.0, archname=darwin-thread-multi-64int-2levelIf you try to link to a x86_64 version of MySQL, then you get the above mentioned error. So, I downloaded an x86 version of MySQL and tried again. The output from  []

So, how does one gather statistics on indexes? With InnoDB it's one thing, with MyISAM it's another thing. This post however, will focus on MyISAM. There are various ways, each having their own drawbacks and positive sides.What about "ANALYZE TABLE"? It's great -- very fast in InnoDB. But with MyISAM a lock occurs while scanning the table. "OPTIMIZE TABLE"? Still relatively quick with MyISAM, unless the table in question is huge. InnoDB requires an "ALTER TABLE" -- it might take forever, depending on the server configuration, as "OPTIMIZE TABLE" for InnoDB maps to a "ALTER TABLE tableName ENGINE=InnoDB"We all know how "ANALYZE TABLE", "REPAIR TABLE", and "OPTIMIZE TABLE" work with MyISAM. However, there's a less explored way, for MyISAM -- the "myisam_stats_method".

It felt like the right time for us to look back at some useful commands for table maintenance that some of us may not have mastered as much as we might like to think.In my post about gathering index statistics, I referred to "OPTIMIZE TABLE", "ANALYZE TABLE", and "REPAIR TABLE" -- but I never explained in depth what the different commands do, and what the differences between them are. That is what I thought I would do with this post, focusing on InnoDB and MyISAM, and the differences in how they treat those commands. I will also look at different cases and see which one is right for in each case.

After I moved back to Europe and Malta in order to set up our operations here, I was approached by a old friend of mine who wanted to know how to add a "UNIQUE" constraint and remove duplicates on a table, while keeping the newest records. He had been trying with "ALTER TABLE" but ran into problems as the older values were taken. So, to help him out, I first solved it based on his original idea, and then figured I would post a small note about the solution here.So, let's say we have the following structure . . .sql01 blogs> SHOW CREATE TABLE post1164\\G*************************** 1. row *************************** Table: post1164Create Table: CREATE TABLE `post1164` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(5) DEFAULT NULL, `c` varchar(5) DEFAULT NULL, PRIMARY KEY (`a`)) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin11 row in set (0.00  []

So, you have a binlog. You want to find out something specific that happened inside of it. What to do? "mysqlbinlog" has some neat features, which I thought we would look at here. I should first explain what "mysqlbinlog" really is. It is a tool that lets you analyze and view the binlogs/relaylogs from mysql, which are stored in binary format. This tool converts them to plaintext, so that they're human-readable.For the first tip, let's start with the "--read-from-remote-server" option, which allows you to examine a binlog on a master server in order, perhaps, to dump it onto your slave and compare master/slave logs for potential problems*.$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h 127.0.0.1 -P 3306 | head -5Enter password: /*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;#  []

Or, "Missing information in the MySQL Manual". Just earlier today, I was using "POW()", which I've grown quite fond of, simply because it makes life easier. I prefer using it like "SELECT 512*POW(1024,2)" to find out the number of bytes to put in a variable, for example.First, let's take a look at the "POW" function: Name: 'POW'Description:Syntax:POW(X,Y)Returns the value of X raised to the power of Y.Okay, so it gives us a value; but what about the data type? Let's take "512*POW(1024,2)" as an example.5067 (blogs) > SELECT 512*POW(1024,2) AS example;+-----------+| example |+-----------+| 536870912 | +-----------+1 row in set (0.00 sec)What is that? Well, it sure does look like an "INT" at this point, doesn't it?

So there's a whole bunch of MySQL scripts included with any release, who can say they know them all? In this blog post, we will take a look at some of these and show usage examples that may help you in your environment.mysql_accessLet's say you want to manage your users' rights, and you have this user 'john.doe' that can connect from 'host1', but you aren't sure what he can do to your db: company-staging as well as company-dev.What do you do? Well, for example, you could use "mysql_access" and take a look:

A while ago me and Singer Wang (another DBA here at pythian) was working on a problem where the slave went from 800M to 1200 to 2700 to 3200 to ~800M again. I have to admit I haven't seen this before.The specific setting was 5.0.45 slave that also acted as a master, and the instance the experienced the problems was running 5.0.50.After upgrading to 5.0.74, it worked again. But here's a few of the show slave status while experiencing these problems:

First off, let me wish you all a Happy 2009, and my apologies for being a bit slack with blogging in the last two months of 2008. It's been a bunch of busy weeks, but I'm fully expecting to remedy that in this year. Second, let me welcome you to this series on refactoring MySQL applications. I will cover best practices and useful tips, as well as show working examples of potential loopholes and solutions.So, you are a DBA or a developer, and you're faced with a problem. Your years-old application (perhaps you inherited it from a former co-worker) is now starting to keel over, and your choice of responses is limited.Let's start by saying that there are other ways to reach performance, but in some cases refactoring may be the only way that is possible to pursue, for one reason or another. Let's take the refactoring way, focusing on SQL rather than applications, as there's a lot that can be done on this side.But first, why  []

Welcome back to my series of blogposts, "Back to Basics". Today I wanted to share a few commands that could be useful in various situations, such as delaying replication, stopping the slave (or parts of it).I'm sorry, but I will not be going through how to set up replication, so my assumption is that you already have replication working and that you want to know some of the details surrounding it.Let's begin by taking a look at "STOP SLAVE"STOP SLAVE stops the slave threads

The Malta MySQL User Group (MMUG) met for the second time this Thursday, and compared to last time, we had a much better venue: Ixaris Systems let us use their board room, so we had all the tools we needed to have a good meeting. We managed to get a group picture before everyone has arrived, so I guess we can call the people in this picture "early birds". Once we all arrived, however, Sandro Gauci from EnableSecurity gave us a very interesting talk on SQL Injection security, and general security flaws from a developer point a view. You can find the slides here: sql-injection.pdf.Here's a picture of Mr. Gauci while presenting. (Sorry for the obvious problem with the over-white picture -- seems like I forgot to turn down the flash, and this was the only non-blurry shot I got.)