I was checking a third party server, and I needed to find if there were tables without primary keys. This is important to know, not only because the lack of primary keys affects performance and data accuracy in general, but also because in row-based replication performance can degrade beyond belief when updating tables without primary keys.Anyway, I did not remember off the bat any method to get this information from a server with thousands of tables, and thus I went to find a solution on my own.My first instinct called for using the COLUMNS table from the INFORMATIOn_SCHEMA, and so I came up with this query, where I sum the number of columns that are inside either a PRIMARY or UNIQUE key and filter only the ones where such sum is zero (i.e. no primary or unique keys):

select 
 …

From time to time I see articles in defense of natural primary keys against surrogate keys.I don't take an immovable stand on either side, as I have seen good cases for both. In general, I like the idea of a natural primary key, when I see one that it is really natural. Quite often, though, a natural primary key has proved itself faulty. And most of the times, when such faults happened, it was because of limited understanding of the data. I like to describe this kind of misinformation as data analysis culture clash.When choosing a natural primary key, one should consider which element, or group of elements, are unique in a given set. Not only that, they must be immutable, at least within that set.For example, in a group of people, we may assume that a combination of name, surname, date and place of birth is a good natural primary key. Well, no. It isn't, for several reasons. Read on for a few real cases.If we rule out the above combination, …

Time-Based Blind SQL Injection

 
Overview
=======
Blind SQL Injection is an attack which the attacker gets an indication for the query execution success. The attacker doesn’t get the query results.
Most of the time, the indication bases on server errors or customized application errors.

Time-Based Blind SQL Injection
======================
Sometimes the attacker might not be able to identify the query execution success, because the server/application doesn’t show any error.
One of the techniques to get an indication for the query execution success called Time-Based Blind SQL Injection.
With this technique, the attacker executes functions that take some time to finish (for example: Benchmark, Delay, etc.). By measuring the time took the application to response, the attacker might be able to identify if the query executed successfully or the query execution failed. …

It was my pleasure and privilege to attend Codebits in 2009. As Roland Bouman says, its talk choice method is based on public voting, and therefore everyone cha have contribute to the schedule.But that is not the main reason for attending this extraordinary event. It is not just a conference. It's an innovation fest. For 1 and 1/2 days, it's a conference, where the speakers are encouraged to bring to their audience the most innovative and inspiring talks. In the afternoon of the second day, the event becomes a competition, where the teams that have registered will have 24 hours to bring a project to completion, and they have to start and finish within the allotted time. The project can be anything, and I have seen quite a lot …

Pythian’s Oracle ACE, Fahd Mirza appears in this month’s Community: Peer-to-Peer review “In With the New“, as published in the September/October 2011 issue of Oracle Magazine.

Fahd states that “Oracle Exadata Database Machine” has most changed his life – changing the game, and setting very high standards of performance, support, scalability, reliability and unification.

Shout out to Fahd from your peers at Pythian!

I guess there might be just a little truth to Pythian’s growing reputation as an “Oracle ACE Factory” ;), as recently mentioned by Justin Kestelyn in the May 11, 2011 OPN PartnerCast:

Please join me in congratulating Fahd by adding a …

XKCD (as usual) makes a very good point – this time about password strength, and I reckon it’s something app developers need to consider urgently. Geeks can debate the exact amount of entropy, but that’s not really the issue: insisting on mixed upper/lower and/or non-alpha and/or numerical components to a user password does not really improve security, and definitely makes life more difficult for users.

So basically, the functions that do a “is this a strong password” should seriously reconsider their approach, particularly if they’re used to have the app decide whether to accept the password as “good enough” at all.

Update: Jeff Preshing has written an xkcd password generator. Users probably should choose their own four …

Percona has announced Percona Live MySQL Conference and Expo 2012. Kudos for their vision and entrepreneurship. I have seen comments praising their commitment to the community and their willingness to filling a void. I have to dot a few i's and cross some t's on this matter.
That was not the only game in town.By the end of June, there were strong clues that O'Reilly was not going to organize a conference. The question of who could fill the void started to pop up. The MySQL Council started exploring the options for a community-driven conference to replace the missing one. The general plan was along the lines of "let's see who is in, and eventually run a conference without the big organizer. If nobody steps up, the IOUG can offer a venue in Las Vegas for an independent MySQL conference". The plan required general …

Clustered computing brings with it many benefits: high performance, high availability, scalable infrastructure, etc. But it also brings with it more complexity.

Why?

Well, by its very nature, there are more “moving parts” to monitor and manage (from physical, virtual and logical hosts) to clustering software to redundant networking components – the list goes on. And a cluster that isn’t effectively provisioned and managed will cause more downtime than the standalone systems it is designed to improve upon.

When it comes to the database industry, analysts already estimate that 50% of a typical database’s Total Cost of Ownership is attributable to staffing and downtime costs. These costs will only increase if a database cluster is not effectively monitored and managed.

Monitoring and management has been a major focus in the development of the …

If you have two or more database servers containing the same data, how do you know if the objects are identical. Furthermore, how can you be sure the data is the same on all of the servers? What is needed is a way to determine if the databases are in synch - all objects are present, the object definitions are the same, and the tables contain the same data. Synchronizing data can become a nightmare without the proper tools to quickly identify differences among objects and data in two databases. Perhaps a worst case (and more daunting) is trying find data that you suspect may be different but you don’t have any way of finding out.

This is where the new 'mysqldbcompare' utility comes in handy. The mysqldbcompare utility uses the mysqldiff functionality (mysqldiff allows you to find the differences in object definitions for two objects or a list of objects in two databases) and permits you to compare the object definitions and the data …

Virtualizing all parts of today’s software infrastructure has become a priority for many. Creating a more flexible and dynamic environment with improved availability enables organizations to accelerate innovation, reduce time to market, cut costs and deliver higher uptime.

Databases have rarely been the first candidates for virtualization – mainly as a result of fears in consolidating such critical resources, and in I/O overhead that may have degraded service levels. However with improvements in hypervisor designs coupled with more powerful commodity server hardware and repeatable best practices, many of these concerns are rapidly diminishing.

It was in this context that we began development of the Oracle VM Template for MySQL Enterprise Edition, making the world’s leading web database radically simpler to deploy, manage, and support in a virtualized environment.

Along with the development team, we will be hosting a …