Technological hindsight; we don’t has itI just read a write-up on the latest-and-greatest database to come out in the new db wars. It’s all about how MemSQL can do 80k queries per second on a set that MySQL can only do 3.5k.First I want to say bullshit. Anyone who has...

When computers were still using large black text oriented screens or no screens at all, a computer only knew how to store a limited set of characters. Then it was normal to store a name with the more complicated characters replaced by more basic characters. The ASCII standard was used to make communication between multiple systems (or applications) easier. Storing characters as ASCII needs little space and is quite strait forward.

Then DOS used CP850 and CP437 and so on to make it possible to use language /location specific characters.
Then ISO8859-1, ISO8859-15 and more of these character sets were defined as standard.

And now there is Unicode: UTF-8, UTF-16, UCS2, etc. which allow you to …

In my database class, students write solutions as group exercises against the Oracle 11g XE database and then they port the solution individually to the MySQL 5.5 database. One of the students copied over a query like the one below to MySQL (a query used to track the expected number of row returns).

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population);

It got an error they didn’t understand:

ERROR 1248 (42000): Every derived TABLE must have its own alias

Providing a dt query alias fixes the problem in MySQL for the following query. The fact that it was just an alias was a revelation to the student. That’s because Oracle databases don’t require aliases …

There are mistakes that drive you crazy when you try to understand what went wrong.

One of the most annoying and hard to catch was this, apparently harmless line:

tungsten-sandbox -m 5.5.24 --topology all-masters -n 2 -p 7300 -l 12300 -r 10300 –t $HOME/mm -d tsb-mm

The person reporting the error told me that the installation directory (indicated by "-t") was not taken into account.

I usually debug by examples, so I copied the line, and pasted it into one of my servers. Sure enough, the application did not take trat option into account. The installation kept happening in the default directory.

I knew that I had done a good job at making the application configurable, but I checked the code nonetheless. The only place where the default directory is mentioned is when the related variable is initialized. Throughout the code, there are no literal values used for this purpose. And yet, the …

Techweek started today in Chicago with the mayor promising to change the name of the town from ‘he Second City’ to ‘The Startup City’. Those who want to start with MySQL should attend my MySQL Hands On Lab Saturday from 3:00 to 4:45 in 4-7 A/B.

Intro

Oracle is widely use to support back-end systems.  On the other hand, MySQL is the "go-to" data management solution for the web-facing part of many businesses.  If you have both Oracle and MySQL in-house, you may already also have the need to share data between them.  In this article I'll describe software that my colleagues and I have been working on to move data from Oracle to MySQL in real-time without costing an arm and a leg.

Tungsten to the Rescue!

Latest Tungsten Replicator has many features, most of which are open-source, but the recent one for me is particularly exciting - thanks to the development done by my colleague Stephane Giron in the …

Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the tables had to be dropped more frequently (like every other hour), or when there was no such thing as off-peak hours, in such cases you need consistent performance. But dropping tables is known to cause stalls that exhibit themselves as reduced QPS during the table drops or stalls. Percona Server since version 5.1 has a feature know as “Lazy Drop Table” that alleviates the problem to a great extent but does not get rid of it completely. In the new releases of MySQL (versions >= 5.5.23) work has been done on reducing …

With the rapid advancement in the database technologies, the legacy systems are either being upgraded or they are being replaced or in some cases technologists are finding ways to support them in new ways showing us the flexible nature of databases and the belief of professionals that the sky is the limit. For this Log [...]

Somebody wanted to understand why you can backquote a single wildcard operator (that’s the underscore _ character) in MySQL, but can’t in Oracle. The answer is you can in Oracle when you know that you required an additional clause.

While I prefer using regular expression resolution, the LIKE operator is convenient. Here’s an example of backquoting an underscore in MySQL, where it looks for any string with an underscore anywhere in the string:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%';

You can gain the same behavior in Oracle by appending the ESCAPE '\' clause, like this:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%' ESCAPE '\';

The ESCAPE '\' clause is one of those Oracle details that often gets lost. It only works when the SQL*Plus ESCAPE …

It is a short week for me this week, because I have tomorrow off as I prepare for the 1st Latin American MySQL, NoSQL and Cloud conference in Buenos Aires next week. However, this week in database, we have done a lot, mostly within the Bugzilla property.

• Backed up several database servers that were not previously backed up
  
• Dealt with more puppet dashboard disk space issues
  
• Repaired a few tables after changing ft_min_word_length on some bugzilla slaves
  
• Upgraded bugzilla slaves to Percona 5.1
  
• Rebuilt our secondary addons cluster after a few machines crashed and needed updated firmware
  
• Added new bugzilla …