Augusto Bott and myself had a wonderful time at April’s MySQL Conference in Santa Clara, California, and also at the coinciding MySQL Camp and Percona Performance Conference.

We presented two sessions at the conference, and we wanted to share the slides with you. The first one is called Proactive Operational Measures.

The second one is called 8 Simple Rules to Design Secure Applications.

Augusto and I promised that we would make the slides available online, so we’re sorry for the slight delay in getting them published. Enjoy!

Welcome to the 138^th edition of Log Buffer, the weekly review of database blogs. If you aren’t aware of who I am, my name is Nick and I am a Senior DBA at The Pythian Group. This is my second run at hosting Log Buffer, and I hope you will enjoy it as much as I did creating it.

As we see winter giving way to summer, I thought I would start with Informix.
Over at Informix-technology Fernando talks about FUD for thought where he talks about the future of Informix vs. DB2.

Sticking with IBM, I thought that we should take a look at what Bryan Smith says in this post about call for feedback, where he discuss the need for feedback on the Data Studio administration console.

I don’t know about you, but I’m tired of Informix. Let’s switch to DB2, and Henrik Loeser where he discuss the DB2 Information Center and does still mention Informix as well as DB2.

Let’s do some general SQL Read the rest of this entry »

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_access

Let’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:

Read the rest of this entry »

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 refactoring? Refactoring normally sits between parameter-tuning and throwing more hardware at the problem, and can be a very cost-effective way of solving performance or scalability issues.

So let’s start with a good point about composite indexes that seems to be forgotten sometimes. Given the following data . . . 

Read the rest of this entry »

So, I actually started my trip early, the taxi picked me up from my house in Malta at 5:15am, and after a short stop in the lounge for some breakfast, I boarded KM100 bound for Heathrow. After a layover there, I eventually arrived at Dulles International, and there was no queue for US customs and border patrol!

Picked up the rental car and drove the 101 miles to Charlottesville, VA — in dense fog.

Met with Sheeri and talked for a bit on Thursday, before just falling asleep, due to time difference and the long travel.

So on Friday, we had a few hours to run errands and pick up last minute stuff that we had forgot back home and so on, before the start at 6pm. Met up with everyone, had some nice interesting conversations and towards the end of the night, 14 of us decided that it was time for dinner, so we went over to a local restaurant. Lots of interesting talk, mainly MySQL related, and lots of food, just like it should be. There were some pictures taken, but I’ve yet to see them online, but keep watching Planet MySQL and I’m sure you’ll see them.

It was a nice start to the weekend, so I’m hoping that today (Saturday) will exceed my expectations as well! I’ll post updates and reviews of the talks, in a daily digest.

Until then,
Nick.

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: Read the rest of this entry »

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.

Read the rest of this entry »

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

Read the rest of this entry »

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?

Read the rest of this entry »

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.

Read the rest of this entry »