view counter

MySQL Feed

Oracle MySQL resources, news, and support articles.

MySQL Hacks: Preventing deletion of specific rows

Recently, someone emailed me:

I have a requirement in MYSQL as follows:
we have a table EMP and we have to restrict the users not delete employees with DEPT_ID = 10. If user executes a DELETE statement without giving any WHERE condition all the rows should be deleted except those with DEPT_ID = 10.

We are trying to write a BEFORE DELETE trigger but we are not able to get this functionality.

Test-driven SQL development

I'm having a lot of fun writing common_schema, an SQL project which includes views, tables and stored routines.

As the project grows (and it's taking some interesting directions, in my opinion) more dependencies are being introduced, and a change to one routine or view may affect many others. This is why I've turned the development on common_schema to be test driven.

Now, just how do you test drive an SQL project?

Unexplained jumps in Seconds_Behind_Master

I am trying to understand why a server would go from 0 to 45 and then back to 0 seconds of replication lag as reported by the Seconds_Behind_Master column in SHOW SLAVE STATUS output. This occurs over a few seconds so there isn't a statement that runs for 45 seconds on the slave. I then compared consecutive SET TIMESTAMP values in the binlog and the absolute value of the differences is at most 2 seconds.

Blackhole tables and auto-increment keys

Blackhole tables are often used on a so-called “relay slave” where some operation needs to happen but no data needs to exist. This used to have a bug that prevented AUTO_INCREMENT columns from propagating the right values through replication, but that was fixed. It turns out there’s another bug, though, that has the same effect. This one is caused when there is an INSERT into a Blackhole table, where the source data is SELECT-ed from another Blackhole table.

What I want to see at Percona Live

For me, next week’s Percona Live is a lot like other events: I know I will be busy meeting and greeting and helping unpack boxes and so forth, but I’ll also be talking and attending other talks. With five concurrent tracks, it’s tough to decide. This is roughly the equivalent of the MySQL conference every year, which has more tracks, but historically it’s been easy to cross off some talks as sales pitches. Not so with this event! Here’s what I want to see the most:

Tungsten Replicator and MySQL Sandbox at Percona Live London 2011

Percona Live MySQL Conference, London, Oct 24th and 25th, 2011 I will be a speaker at Percona Live - London 2011, and I am looking forward to the event, which is packed with great content.

MySQL Cluster Webinar on Wednesday: What’s New in MySQL Cluster 7.2.1 Development Milestone Release

There’s a webinar this Wednesday (9 am Pacific; 5 pm UK; 6 pm CET) that explains what’s new in the MySQL Cluster Development Milestone Release – register here for free access.

Percona Server 5.1.59-13.0

Percona is glad to announce the release of Percona Server 5.1.59-13.0 on October 15, 2011 (Downloads are available here and from the Percona Software Repositories).

Percona Server 5.5.16-22.0

Percona is glad to announce the release of Percona Server 5.5.16-22.0 on October 14, 2011 (Downloads are available here and from the Percona Software Repositories).

view counter