Monday Jan 26, 2009

New York, Boston, Providence

Time for another East Coast tour -- not to worry am planning on a central and western tour as well, but we're still working out the details to see if it will happen!

Next week, I'll be attending a Sun Microsystems "Hatchery" event talking to startups in New York so I'll be in NYC Monday and Tuesday (Feb 2/3). Wednesday I'll Amtrak it to Boston where I'll meet the Boston MySQL Meetup and talk about MySQL 5.1's Event Scheduler. I'll then travel to Providence. I don't have any meetings scheduled for Providence but I'll work from a friend's house and come back on a cheap flight to Montreal the Monday after.

Will you be in any of these areas? Do you want to meet up? I have no problems ducking out during the day to meet any developer, mysql user, customer or dba. Just let me know on this blog how to get in touch with you or drop me an email to dups -at- sun -dot- com.

Now the cool part is that I will be trying my very best to be as environmentally friendly as possible on these travels so I have elected to take the 12 hour+ journey to New York City from Montreal by train. As I understand it my former colleague Morgan Tocker did it a while back and am hoping it will be somewhat relaxing and enjoyable (though I understand the border crossing is a pain). On the other hand, I get to walk downtown in Montreal, get on the train all relaxed, work on stuff on the computer in peace for the day, get out at Penn Station and walk to the hotel near the Sun office.

Friday Jan 23, 2009

"Grant All" to a Web DB User?

Please don't. Please, please don't. I don't know the number of times I have seen this, heck I know that certain software installations recommend you allowing a "grant all" to the user which will connect to the db from that software (see WordPress installation guide).

So... please don't.

One of the quotes I live by is one I remember from my university days and uttered by the German playwright Bertolt Brecht (I've never found the actual quote, if someone knows let me know) where he basically says, if you put a telephone on stage, use it.

MySQL grants for a user should only include what that user needs, never more, never less. If a piece of software, or if you as a programmer/dba decide to use "grant all", make sure the software or you actually needs that and remove it once it is not necessary. If not you are setting yourself up for disaster.

So here's what happens typically: You have a web server with a database and the user from the web site connects locally. You are fairly smart so you disable access to the database from the outside world and the user can only access from localhost. So far, so good. Now, there are very few ways you can hack and gain shell access onto a linux server that keeps its packages updated, so you feel fairly confident (you should never assume this btw). Your misplaced confidence leads you to believe you can give "all" access to a db user used by a web site. You have basically said that web user can do whatever they want. They can "drop" your database even.

How can someone do that without shell access or direct access to the DB? Well one way is that they can do it by means of SQL injection.

Wait! You say, "Wait! I know my code, I know my skill, I have taken care of SQL injection." Well, unless you are smarter than almost every coder I know and you do not use any third-party packages or software (like WordPress, or Gallery or whatever) then you are mistaken.

Why am I writing this? Well this week I witnessed the "trifecta": A directory opened for global public read with the SQL password in a plain text file, the grant statement for that user being "all" and a SQL injection possibility (even if minor and unlikely to do any damage) on a single site.

So please, even if accidentally, even if temporarily, be mindful of what grants you give what user.

Paul DuBois has an older but still relevant article on securing a MySQL installation. There is also notes on securing a MySQL Installation in the MySQL manual.

Thursday Jan 22, 2009

PlanetMySQL now in Japanese!

Language support and collation is an issue which affects most web sites with a global audience. Neither PlanetMySQL or are immune to these problems. The problem both these sites face is that neither site was designed with asian languages in mind, the database and collations and connections are all mixed and none of them in utf-8. It means migration is always an issue, a matter of downtime and not necessarily a sure thing.

With PlanetMySQL we really wanted Japanese and in the future Chinese character set support. As you might notice on PlanetMySQL as of late you can now choose to see a Japanese feed which has made our Japanese colleagues incredibly happy!

To be honest, I cheated.

There were a few problems to solve quickly, one was that the MySQL DB tables are not in the UTF-8 collation. Converting the table to UTF-8 was an option but I opted for the quicker solution which I'm sure will come back to haunt me in the future but for now provided the best solution while we look towards a greater redesign and reworking of PlanetMySQL in the future.

A second problem was that SimplePie which we use to do our feed aggregation was not writing in as utf-8. That was a simple switch and Lenz removed all traces of any reference to MagpieRSS which we used before SimplePie.

A third problem was that the pages were being served in the latin1 character set.

To solve these problems in the quickest time possible with no downtime, I added a character set/collation type into our current languages table, switched SimplePie to read and write as UTF-8 and fixed the web pages to display as UTF-8 for any new languages.

Sometime in the future I'll have to bite the bullet and convert those tables to UTF-8, but for now, this solution works and we were able to add this quite quickly over the holidays.

Over the next little while we'll have to expand our language collection, language choices and fix/improve the interface to choosing languages. As with all things, PlanetMySQL is under a renewed movement for renovation. If you have any ideas or suggestions we always like to hear them. I can't promise everything will be addressed, but we will put them on the list to consider.

Wednesday Jan 21, 2009

5 Days of Sun and MySQL

So it's been a year since we were all sitting thunderstruck in the ballroom of the Swan and Dolphin Hotel in Florida as Marten announced that we were being acquired by Sun Microsystems. To be honest, normally with these things people say it feels like "ages ago", in fact for me it feels like it just happened yesterday and it feels like we're only getting started.

One of the coolest things about Sun and the combination of Sun and MySQL is that Sun is a technology oriented company with some incredibly bright people who are there to come up with neat technology stuff during their waking hours. In some ways, that's very much like MySQL. After all, we build technologies that drive other technologies and business.

For the last year people have talked about how MySQL has been affected by Sun. But you know, influence is a two-way street. Former MySQL-ers (now called Sun Dolphins) are spread throughout the organization outside of the Database Group. We have people working on Drizzle elsewhere, others working on volume business, sales, support and other research projects. Also, some of our Sun colleagues were already busy using MySQL when they woke up to discover that MySQL was now Sun.

To celebrate a year of MySQL within Sun, Lenz and I have done five interviews which have been published each day this week. The interviews are focused on Sun "Classic" folks on their thoughts on having MySQL part of Sun over the past year. The interviews have been accompanied by wonderful banners from Aaron White of the MySQL Web Team and are available in the Developer Zone.

We have two more days to go, but I encourage you to have a look at the ones already done and follow along over the next couple of days:

: Vince Carbone talks about DTrace in Solaris and OpenSolaris and his work with MySQL.

Tuesday: Detlef Ulherr and Thorsten Früauf talk about Open HA Cluster and Solaris Cluster and how MySQL fits into the project.

Neelakanth Nadgir talks about MySQL and ZFS and how a file system could revolutionize your use of a database.

Thursday and Friday: Stay Tuned!

Monday Dec 22, 2008

More Interviews: Masood, Stewart and Lars

Somehow these interviews did not pop up on PlanetMySQL this past week so I link them here for your holiday reading. These are a couple interviews by Lenz Grimmer and one by yours truly.

Interview with Lars Heill, Release Engineering Manager
Born in Northern Norway 41 years ago and Lars has lived in Trondheim for the last 22 years. He is a Physicist by education, has a master degree on semiconductor heterojunctures and has earned a PhD on high temperature superconductors. He worked briefly on nuclear power fuel optimization and petroleum related rock mechanics before joining Clustra in the year 2000, which was acquired by Sun Microsystems in 2002. Clustra was a database software vendor that specialized in clustered, high-availability databases that were required by telecoms and service providers.

Interview with Stewart Smith, Drizzle/MySQL Cluster
Stewart Smith, a former member of the MySQL Cluster team recently decided to move on and work as a programmer on the Drizzle project. We wanted to catch with Stewart on both MySQL Cluster on Windows and what's he up to now.

Interview with Masood Mortazavi, MySQL Engineering Manager at Sun
Masood Mortazavi is an Engineering Manager at the Sun Database Group. After the acquisition of MySQL, and along with the rest of Sun's original database technology group, he joined the MySQL organization to form the larger Sun Database Group. In this interview, Masood talks with Lenz about the flexibility and diversity of Sun as a workplace, his life prior to joining Sun and his current assignment to improve the MySQL code contribution process.

Happy Holidays everyone, see you in 2009!

Ideas on Integrating Memcached into MySQL Queries

There's any number of ways to integrate your application with Memcached to take advantage of Memcached's power. Here's a list of some of them (and because I am most familiar with PHP in this case so that's what I've listed, and by no means is it exhaustive):

  1. Using the PECL PHP Memcached libraries you can write direct queries to Memcached with failover to your SQL queries.
  2. Using the memcached UDFs so that you can write SQL queries into MySQL/Using the memcached storage engine
  3. Using MySQL Proxy to interface with Memcached
  4. Using something from a framework such as Zend_Cache from the Zend Framework (which allows you to use more than one caching system btw.)

So, yes there are many ways to integrate Memcached into your PHP application, so might I suggest one more way.

The problem with the first option above is that your code tends to get littered with memcached calls, with the second options you end up having to modify your server. In many environments such as hosted environments this is not that clean. With the fourth option, you now need to use a framework potentially and potentially you may not want that overhead. The third option of using MySQL Proxy is one of my favourites but let's face it, MySQL Proxy is not GA yet, the available version has stability issues and the memcached scripts I've seen/heard about seem to use memcached as a full on query cache (please do correct me if I am wrong).

My belief is that memcached is a caching solution and it should be used by the developer wherever possible to make the application faster by placing/caching only the data that the developer needs. I also personally want my application to run when memcached is turned off and I want the application to be easy to read. In other words, I want a modification to the SQL query that will both work with memcached and MySQL but gives me control over what I want to save to memcached and what I need to expire/replace etc.

My solution which I tested over the last couple of weeks will only work if you already have the ability to modify/extend your database handler. At, for example, we use Zend Framework but for various reasons, including performance, we actually have our own custom database handler object. Most of my personal sites also do the same; I do not intend to move away from MySQL ;)

I do have to admit, my first crack at the syntax was quite clunky but in chatting with Adam Donnison (who will, by the way, be giving a beginners talk on Memcached at the MySQL Users Conference) we came up with the following.

SELECT /\*INTO MEMCACHED namespace=table key=id\*/ x, y, z FROM table WHERE id=1;

In this case the data will be stored into memcached with a key if table_1 and store an array of x, y and z. In my database handler this will easily parse the query and select from the MySQL database if it is not in memcached and on the way out save it into memcached for the next query.

To round out the queries, I also added support for things like

INSERT /\*REPLACE MEMCACHED namespace=table key=id\*/ ....


DELETE /\*EXPIRE MEMCACHED namespace=table key=id\*/ ....

I wanted to see how a real world use of this would work and so I rewrote my session handler for Zend Framework to take this into account and sure enough it works and it works well. Now my code is a lot neater, will always work with MySQL and I can move my memcached code around as I need it.

By rights, my perfect scenario is to now complete a MySQL Proxy script that understands the above and does the above then I could even remove the database handler code that does this all. To be honest though, the performance of this is quite good on my limited tests.

While I have not done it, I would imagine that extending Zend Framework to be able to handle these kind of queries should not be too difficult, nor is it difficult to simply use Zend_Cache into your database handler object and thereby even further enhancing your application's abilities to cache things.

Thursday Dec 11, 2008

Interview with Alexander "Salle" Keremidarski

I forgot to post about this but I uploaded a small catch up interview with Salle, the Manager for EMEA Support for MySQL on life and what it means to be in the Support Team of MySQL. Let me introduce you to another long time employee of MySQL :)

Tuesday Dec 09, 2008

MySQL Buzz

One of the many tasks ahead of me is to help improve the many sites that the Community Team manages for the MySQL Community web presence. I won't get into too much here, but we're looking at a whole host of different things to help make our MySQL community more functional, more interactive and more relevant to all users.

Now this may not seem like much but we've added a "MySQL Buzz" page to PlanetMySQL. It's a small little experiment, bringing some data that we currently don't have anywhere together onto one page. It features some data from our Forums, an addition of Google's Search tool with quick links to certain terms across Google's Blogs, News, Video and Web searches and some fun word frequency analysis.

This is by no means an end, just a quick little experiment, your thoughts are welcome and appreciated.

Hope you enjoy the little addition.

Friday Dec 05, 2008

Wanted: Web Developer @ MySQL

So I guess I have no choice now but to stick with Giuseppe and the Community Team as it appears my former job (or something similar to it) has now been posted on the Sun jobs site. So here is my encouragement for someone to apply for this position.

Now here's also the inside scoop. The MySQL Web Developer is a position which combines pure PHP Development, some DBA work and some Sysadmin work so if that appeals to you then great. You'd be working on improving the external sites as it relates to Demand Generation which is mainly and our partner sites. Believe me when I say there is a lot of work and you get to really customize that work to make it fun for you.

In addition, I can say that I've had a lot of fun working with the team. The #web IRC channel is always busy and usually quite fun with subjects ranging from strange storms as is happening as I blog this ("niall: the gods are angry with you dups") or help with whatever you might be working on. There's also the fact that the sites with its millions of visits/day leads to some interesting collaborations with our developer and engineering teams. Check out Adam Donnison on running MySQL 5.1 on production. There's great collaboration between Developer, Community, Support and Web which makes it a great environment to be in.

So who's in the team? Kristina Hadges is the Project Manager and Manager of the team, Markus Popp, a developer in Austria, Aaron White, a developer in Colorado, Niall Brown, a developer in my home town of Montreal and lastly, but certainly not least: Adam Donnison, the person with whom this position would collaborate heavily with and who hails from somewhere near Melbourne in Australia. Watch out he brews a mean cider, which he kindly smuggled in for us at our last company meeting in Florida. He also likes being woken up in the wee hours of the morning (sshhh don't tell him I told you).

Oh yes, and sadly you'll be working with me and the others on the Community Team as well so I have a vested interest in this position being filled with someone with a great attitude, humour and experience.

So if you dream about PHP, have an interest in working for MySQL and seeing/changing things from the inside, like working from home (though if there is a Sun office you can work from there too), are independent, have no problems enjoying a few laughs on IRC and want to make a difference as we work our way through the pitfalls of all that is MySQL and open source databases, then I strongly urge you to apply.

Wednesday Nov 26, 2008

Happy Thanksgiving v. 5.1

If you are in the US and eating your turkey and giving thanks, you can look forward to also giving thanks that version 5.1 of MySQL is now marked Generally Available for production use (GA). Yes after quite the number of releases, a plethora of bugs fixed, it is now there for use by all.

Not that that's stopped us from using it live on our production systems here at, check out this interview with Adam Donnison of's webteam where he details a bit of running 5.1 over the last year. 

Congratulations to all the devs, it's been a long road, time to enjoy the fruits of their labour, along with a bit of cranberry sauce and some stuffing.

Sunday Nov 23, 2008

Providence, RI and area

This week (November 24/25/26) I'm visiting the Providence RI, Attleboro MA and Marlboro MA areas just before thanksgiving in the US. If anyone in the MySQL community, or heck any open-source community in those areas wants to meet up, please drop a message onto this blog. For everyone else in the US, I wish you a happy thanksgiving.

Tuesday Nov 18, 2008

Photos from Open SQL Camp

Finally managed to upload the photos from Open SQL Camp to Flickr... btw below is the photo of a very bashful Baron receiving his surprise signed shirt from the gathering for all his hard work in helping it come together. I also want to note some thanks to everyone else who contributed in making it a success. Very impressive, very useful, very fun.

Bashful Baron

Saturday Nov 15, 2008

Brian Aker, Keynote, Open SQL Camp (Summary)

Is the projector on? Apparently the projector is lying, there are red lights and screams of "cut the blue one". And suddenly there was light.

The keynote is on the "State of Open Source Databases (just where are we?)", Brian Aker is now with Drizzle at Sun/MySQL.

Summarizing as much of Brian as possible (apologies Brian if I missed anything important):

We have achieved ubiquity - Skype, Google... open source databases are entirely ubiquitous. There is no question about when we will "win" -- we have already won. 

As technology has gone for the increase of computing with CPUs following Moore's Law, and as we have more cores now and databases need to scale on more cores and how can databases use all these cores. What about other types of machines, xbox's etc.?

As time moves on, the amount of data expansion is happening very rapidly. Smugmug, LSST and the Large Hadron Collider at CERN are producing huge amounts of data so DBs need to be able to deal with huge amounts of data, Petabytes upon Petabytes.

Solid State Disks, synchronizing data will get faster. Changes how we build databases, "little spinny disk will not be our bottleneck going forward".

Some things to think about, how to make power use in datacenters more efficient. Currently 6% of power in a datacentre is from use of memory, something databases use a fair bit of. Brian is a bit frightened of Map/Reduce which might result in huge amounts of computers and hence increase power consumption etc.

Every household now has a couple terabytes of data. That's a lot of movies.

Brian finishes off with a note that MySQL sold for a billion dollars but won't be the last. So who's next? There is money to solve many of these problems.

Jim Starkey in the audience "pontificated" that we should not be looking at single node computers but look at the network and millions of computers. Brian agreed especially when you look at Zombie Nets and bot nets which are now passing huge amounts of data. He noted some of these bot nets were probably passing around more credit card information than Visa or Mastercard :)

A week of community.

A summary of a week of travels while I wait for the Open SQL Camp to start with Brian Aker's "State of the Union" Keynote.


Monday started in Columbus, Ohio spending an excellent two days with Jay Pipes and his family while I received "on-the-job" training from Jay. We had discussions of the importance of MySQL ubiquity, "magic" code and a stirring analysis of blob storage in databases compared to stuffing cows in fridges.


From Columbus I headed off to New York to meet Giuseppe "Datacharmer" Maxia during his US North East Tour (in-progress). The hotel room was literally a closet, but it  was right next to Times Square and the Sun offices on Park Avenue (fully stocked with coffee and tea). I discovered Giuseppe outside Giusseppe's Pizza (note misspelling which led to a snort of disgust from the correct Italian).

The presentation that night was a talk in front of the New York PHP group at Suspenders Bar and Restaurant on "Writing a social application in php/mysql and what happens when a million people show up on opening day". Slides are now available on Launchpad. Good to see my former colleague from BioWare, Julian Karst.


Morning discovered us on a Greyhound bus on our way to Baltimore. We were the annoying geeks on the bus discussing how to do Memcached on MySQL Proxy for use by php and other applications. Despite the best efforts of our Baltimore cab driver we did find our way to the hotel in Elkridge, MD. Our task was Giuseppe's presentation for the MySQL Meetup organized by Greg Haase.


We decided to rent a car and find our way to Charlottesville, VA where we promptly got lost on multiple first, second, third and so on streets before paching someone's free Internet outside their house. Thank goodness there were no guard dogs.

From here I'll post on Open SQL Camp seperartely. Thanks to all those who I've met, please get in touch with me so we can keep chatting (I have no business cards currently... which is a bit... well, annoying).

Wednesday Nov 05, 2008

MySQL Community in Ecuador?

Hey all, a quick and short note that I will be spending much of December near and around Quito, Ecuador. If there are any MySQL or MySQL-related community, Open Source community, developers or partners that would like to get together, please let me know. My spanish collation is problematic ;) (currently non-existent) but would still love to meet you.

Drop me a note via this blog!


This is the blog of Dups... currently I'm one of MySQL's Community Relations Managers for Sun Microsystems, post, contact me, I want to hear from you!


« July 2016