In August 2006 I made a post about the MySQL blackhole engine and its possible use for replication scenarios.

Back then the primary focus was to reduce the amount of data being transferred between master and possible many slaves during normal business. This speeds up processing on the slaves, because they have considerably less data to first store to their relay logs and then immediately discard again.

What still takes a lot of time is the initial setup of a slave system. This is because there is a potentially large SQL based dump file to be executed on maybe relatively low-end systems (e. g. Celeron CPUs and single IDE hard drives). While this can be made to work reliably with a set of scripts to automate the process of downloading a zipped SQL file from a local server and running it against the clients' databases, it …

In a previous post I mentioned about patches needed to accomadate the new DST changes for Sql Server. (I know, this is one of Dannyman’s favorite subjects ;))

For MySQL, obviously you need to patch the host server first. Then you need to find out if MySQL needs separate work. Here is a note I gathered. Let me know if I am wrong on this or if there is a better way.

1. Get into mysql and do \s to find out the version of your MySQL.

If it is prior to 4.1.3, don’t worry about it.

Else

2. Do select @@global.time_zone;

If the result is SYSTEM, don’t worry about it.

Else

You need to load time zone info, usually at /usr/share/zoneinfo into your mysql database, by running something like:

# …

I just thought I might link to an article on RegDeveloper called MySQL is the company's SQL now...

Go read it yourself, it is rather short, but interesting nevertheless. I especially like the sentence at the bottom: It remains to be seen whether the company fully understands, and can cope with, the culture shock it is about to suffer; as callers change from 'friends' to 'customers'.

This is what I have been thinking about too, when they started releasing enterprise builds more often than community builds (binary builds that is). In the past MySQL always suggested to use the binaries provided by them to rule out any effects that may be introduced by a 3rd party build process or patches. That made much sense to me.

However now they tell you that you can check the source …

I just thought I might link to an article on RegDeveloper called MySQL is the company's SQL now...

Go read it yourself, it is rather short, but interesting nevertheless. I especially like the sentence at the bottom: It remains to be seen whether the company fully understands, and can cope with, the culture shock it is about to suffer; as callers change from 'friends' to 'customers'.

This is what I have been thinking about too, when they started releasing enterprise builds more often than community builds (binary builds that is). In the past MySQL always suggested to use the binaries provided by them to rule out any effects that may be introduced by a 3rd party build process or patches. That made much sense to me.

However now they tell you that you can check the source …

In both Oracle and MySQL, you can do:

create table T1 as select * from T1

This CREATE TABLE AS statement basically clones table T1 with its structure and data in T2. This can be pretty handy at times.

The equivalent of that in Sql Server is SELECT INTO. For example, you can do:

select * into T2 from T1

to achieve similar results.

In case anyone has seen spurious problems using MySQL version 5.0.32 (and presumably the identical 5.0.33 community version) you might want to take a look at MySQL Bugs #25653 and #25596. They are about a little (but serious) InnoDB bug.

If you do not yet use this version, you might consider waiting for a fixed release to become available.

In case anyone has seen spurious problems using MySQL version 5.0.32 (and presumably the identical 5.0.33 community version) you might want to take a look at MySQL Bugs #25653 and #25596. They are about a little (but serious) InnoDB bug.

If you do not yet use this version, you might consider waiting for a fixed release to become available.

Last week I needed to write an update SQL script that would insert some records into a database. However as the script was to be incorporated into a software update that was going to be deployed to all our customers, it needed to check some condition first and see, whether the insert was required at all.

Even though MySQL provides some non-standard SQL enhancement there is no INSERT IF EXISTS kind of statement.

I managed to do it using some temp-tables and a combination of INSERT IGNORE ... SELECT FROM and UPDATE ... WHERE statements.

The following example demonstrates how to insert a new row of data in the table named real_table. The data must only be inserted into this table, if another record in a table named condition_table exists. No change of real_table must occur, if there is no record matching the condition.

Moreover (because I …

Last week I needed to write an update SQL script that would insert some records into a database. However as the script was to be incorporated into a software update that was going to be deployed to all our customers, it needed to check some condition first and see, whether the insert was required at all.

Even though MySQL provides some non-standard SQL enhancement there is no INSERT IF EXISTS kind of statement.

I managed to do it using some temp-tables and a combination of INSERT IGNORE ... SELECT FROM and UPDATE ... WHERE statements.

The following example demonstrates how to insert a new row of data in the table named real_table. The data must only be inserted into this table, if another record in a table named condition_table exists. No change of real_table must occur, if there is no record matching the condition.

Moreover (because I …

I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.

I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.

Suppose you have:

1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.

In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either

desc t1

or

sp_columns t1

In both Sql Server and MySql, the only thing you can do is:

delete from t1;

which essentially wipes out the whole table. You can do the same thing in Oracle.

However, if you do:

…