If we have a replication architecture and we are using stored procedures, we need to pay attention to the use of rand() inside the SPs.

Usually, single queries replicates correctly rand() values. MySQL writes to binlog the random number seed. You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.

You can see that below

mysql> create table prova(a double);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into prova values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000035' from 865\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000035
        Pos: 865
 Event_type: Query
  Server_id: 1
End_log_pos: 957
       Info: use `prova`; create table prova(a double)
*************************** 2. row ***************************
   Log_name: …

If we have a replication architecture and we are using stored procedures, we need to pay attention to the use of rand() inside the SPs.

Usually, single queries replicates correctly rand() values. MySQL writes to binlog the random number seed. You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.

You can see that below

mysql> create table prova(a double);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into prova values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000035' from 865G
*************************** 1. row ***************************
   Log_name: mysql-bin.000035
        Pos: 865
 Event_type: Query
  Server_id: 1
End_log_pos: 957
       Info: use `prova`; create table prova(a double)
*************************** 2. row ***************************
   Log_name: …

Went to the dentist today. No cavities which has been the case for
two decades. I do need to have one part of my gums repaired. The
dentist was trying to take a picture of the gum so that I can see the
required work.

And what appears on the screen?

A windows warning complaining about Sybase crashing via the ODBC driver.

The dentist laughed when he saw my face and asked me if I knew
anything about computers :)

Sometimes you need to think backwards.

Here was the problem. I needed to match up some IP address ranges to the company that owns them. Looking for a simple solution to the problem I came up with storing the IP address block patterns in the database as follows:

ip_pattern
----------------
127.%.%.%
192.168.%.%
10.%.%.%

Any idea why I choose % as the wildcard?

That's right - it's the wildcard operator in SQL for the LIKE statement.

So now when I have have an IP address 192.168.1.1, I can do what I like to call a backwards LIKE query:

SELECT company, ip_pattern
FROM company_blocks
WHERE '192.168.1.1' LIKE ip_pattern

This works on SQL Server and MySQL, and I would think it should work fine on any database server.

As you may already know or soon will find out MySQL had released a new version of their community server, 5.0.33. First all congratulations to developers, any release is a lot of work and finally pushing it out the public is definitely an achievement.

There are however some interesting and in my eyes less then positive developments pertaining to this release. As you can see from Kaj's announcement as well as the state of the MySQL's download page pre-compiled binaries are no longer offered. The only files available for MySQL 5.0.33 are sources for *NIX and Windows platforms.

While this is not an issue for *NIX users where lack of binaries will be resolved by distros and if not, the compiler is always available and compiling MySQL is big issue, it does pose a major …

MySQL Community Server 5.0.33 has now been released. It is a pure bugfix release, delivered in a source-only form as a tarball for Unix and for Windows (we provide separate sources for these, as the build procedure differs) on dev.mysql.com/downloads/mysql/5.0.html#Source.

The release contains all bug fixes applied to MySQL Server 5.0 since the last Community release 5.0.27 in October 2006. The jump in numbers from .27 to .33 is to make it clear that .33 is up-to-date to the level of MySQL Enterprise Server 5.0.32, released about two weeks ago. We reserve the even version numbers for MySQL Enterprise, while odd version numbers indicate a Community Server Release. Both use the same “5.0″ version part, as they share the same code base.

We are in the process of applying several patches …

To check for query performance improvements followed indexing/query changes or MySQL configuration changes our customers often decide to run the query and see if there is any significant improvement.

Leaving aside question of checking single query alone might not be the best way to see real improvement for your application, the problem they usually run into is - query speed when it is run first time may be very different from second and further runs, especially when it comes to disk IO workloads.

The attempt to fix it is often using SELECT SQL_NO_CACHE or disabling QueryCache for the test. This works by blocking query cache but still repeated runs execute much faster, why ?

The reason is - there are all kind of other caches and QueryCache is only first in line. There is also all kinds of page caches inside MySQL (key_buffer and …

First of all: Happy New Year everyone! For reasons I don't understand, or maybe no reason at all, there are a couple thousand of you who end up here every day (actually, closer to 4000 a day but many end up on old archive pages and, I'm guessing, quickly leave).

By now you've probably seen the oh so promising Apple iPhone. Woah, does that thing look neat!

I'm attached to my Treo, but I think I found my upgrade. I might even switch to Cingular for it, at least if they get an unlimited data plan that's not vastly super-duper overpriced. Heck, with the beautiful "visual voicemail" feature I might switch to Cingular anyway.

One thing that I'll occasionally will be missing is, of course, an ssh client which brings me to the big question: Will Apple provide an SDK and let us install …

Sean McCown writes in the latest Database Underground about how DBAs should try and make things better for users of an organization. I think Sean hits upon an important point not just for DBAs but for all IT workers. Too often IT is seen as a cost center, an impediment, as something as necessary for an organization but not necessarily glorified or appreciated. As technicians we may find ourselves amazed at how the business people don't see value in what we do, we may groan and complain about they don't understand how hard we work or we may think they don't care. From the business perspective, though, it's hard to understand why it takes so much to do some of the things we do. But then again, we sometimes marvel at some of the things the business folks do and we shake our …

EnterpriseDB Closes Extraordinary Year, EnterpriseDB (Press Release)

Compiere Launches Expanded Partner Program to Extend Global Reach, Compiere (Press Release)

Key Players In The Linux and Open Source Industry Sign On As Sponsors For LinuxWorld OpenSolutions Summit, LinuxWorld (Press Release)

Decision Intelligence Joins Pentaho Partner Program, Pentaho (Press Release)

Open …