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_prefix

Fair 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-2level

If 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 perl Makefile.PL:

  cflags        (mysql_config) = -I/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/include  -g -Os -arch i386 -fno-common   -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT  -DDONT_DECLARE_CXA_PURE_VIRTUAL
  embedded      (mysql_config) =
  libs          (mysql_config) = -L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqlclient -lz -lm     -lmygcc
  mysql_config  (Users choice) = /Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/bin/mysql_config

That looks all good and nice, the right paths, versions and everything. So I compiled it OK, but when running make test again: (more…)

Welcome to the 117^th edition of Log Buffer, the weekly review of database blogs.

For those of you who don’t know me, my name is Nicklas Westerlund, and I’m a MySQL DBA with The Pythian Group. This is my first time writing Log Buffer, and I hope I’ll do it right.

Let’s start off with SQL Server, where Simon Sabin asks if you know what concurrency is and how to improve it. And on SatisticsIO, Jason Massie focuses on the SQL Server 2008 experience instead, which should provide more inside knowledge of the technology used.

Continuing on with SQL Server 2008, the engineering team is sending loads of engineers to the SQL PASS Conference, as the SQL Server Customer Advisory Team tells us in their post on what, in their opinion, just may be the best PASS Conference yet. And if you’re into meeting engineers, then perhaps you’d also like to know how that patching is done in SQL Server 2008, which PSS SQL informs us about.

The folks over at sqlserver-qa.net also give us an overview of the SQL Server Web Edition.

Let’s move over to Oracle, where there’s still a lot of buzz about Exadata, and let’s start with with the second part of the Exadata FAQ by Kevin Closson. In that post he also mentions his interview on the Exadata with Paul and Christo here at Pythian.

(more…)

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.)

(more…)

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?

(more…)

So, we’re coming up on that time again. When I moved back to Malta in order to work for Pythian one of the things that I wanted to do was to involve myself more in the community. Currently, I’m doing this by trying to keep an active blog with tips and tricks, good standard knowledge, and just overall trying to enlighten people. I’m also doing this by organizing a MySQL User Group here in Malta.

We’re set to have our 2^nd meeting this coming Thursday, the 28^thof August, in Ta’ Xbiex where we have graciously been donated a board room and projector (and parking space, luckily) for use. I’m trying to get one of our members to give a session about security (not only on MySQL, but also on the OS level to secure the process), and I know there is work being done on a presentation on MySQL Cluster, which I personally find very interesting and can’t wait to see.

In this second meeting, we’ll again focus on general best practices and free discussions, in order to share our knowledge as much as we can. The last meeting was in Mellieha and it was a success — we had a good turnout and some very interesting discussions. Darren, one of our members, blogged about our meeting here and so did I in one of my previous posts.

(more…)

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 -5
Enter password:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#080815 19:25:23 server id 101  end_log_pos 107 	Start: binlog v 4, server v 6.0.5-alpha-log created 080815 19:25:23 at startup

Pretty useful!

Now, let’s assume we have a binlog that is 94 lines long*:

(more…)

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: post1164
Create 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=latin1
1 row in set (0.00 sec)

. . . with a small amount of data in it:

sql01 blogs> SELECT * FROM post1164;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | a    | a1   |
| 2 | a    | a2   |
| 3 | b    | b1   |
| 4 | c    | c2   |
| 5 | b    | b2   |
| 6 | c    | c1   |
+---+------+------+
6 rows in set (0.00 sec)

Now, if I were to use his original SQL, I would get a result similar to this:

(more…)

So, the Malta MySQL User Group has had its first ever meet-up today, and it was a success! When organizing it, I had no clue how many people would show up, people have said “yes” and then changed to “no”, and vice versa.

Anyway, so the morning came, and I sent out a few confirmation emails, and it suddenly dropped down to just three of us, which was a shame. Anyway, around 5pm, I noticed that there was four more that had just said they wanted to attend. Unfortunately, one of those dropped out, but it was still a great turnout.

This is a great opportunity for us to promote MySQL use in Malta, as well as share experience and knowledge. Also just to network a bit.

So, this time, for meeting #1, we met up outside a local bank, and walked to Il-Mithna, which is a local restaurant. In this picture, there is the first core group of local user group members, all happily gathered and just about to start our discussions.

(more…)

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.

(more…)

That’s right. MySQL now has a user group in Paradise.

I am always looking into connecting with other MySQL professionals, to share the laughs and tears, and to enjoy what we love working with every day, MySQL. I have always wanted to bring us all together, and I thought that this would have a good chance of doing so. Since I live in Malta, this made for the perfect location for it. If you live in Malta, or perhaps in Sicily or Tunisia, and want to take a trip, please do join us at our first meeting.

We will be having our first meeting in Mellieha, and please RSVP to me personally via email, westerlund (at) pythian.com if you want to attend. The date is set for Thursday, July 31^st at 6pm. We will discuss the current use of MySQL, its future, and whatever else comes into mind. I myself would love to hear usage stories for our first meeting, so we all get an understanding of how MySQL is used in Malta and environs.

I will make sure there are some refreshments to be had.

Let’s keep ourself educated and aware of how other people solve problems that we all sometimes encounter, as well as their interesting technical solutions. And let’s have some fun doing so!