Frank asks Can MySQL Triggers Update Another Table?

Frank then sets his blog to only allow comments from registered Blogger users. Bad Frank, no comment for you.

If I could have commented, I would have shown him how it is done in the SampleDB:

–
– Table structure for table `film`
–

CREATE TABLE film (
film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
…
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
–
– Table structure for table `film_text`
–

CREATE TABLE film_text (
film_id SMALLINT NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT,
PRIMARY KEY  (film_id),
FULLTEXT KEY idx_title_description …

Revision control is of course very important in software development, particularly when many developers are doing a lot of commits in a very distributed environment - like with MySQL: our developers are based all over the planet, but also travelling, off-line at times, and so on.
So revision control interests us a great deal, and distributed (and off-line) systems in particular.

A few years ago, BitKeeper used to be the only one that pretty much had all this licked.
Some emergent Open Source projects didn't continue, but the current "contenders" appear to be GIT (started by Linus Torvalds), Mercurial (by Matt Mackall) and Monotone (Graydon Hoare), with Bazaar NG (Martin Pool, Canonical) also showing …

Just placing this here so I do not forget it:

A friend had an issue where he added a new web server to his network, but wanted to use the MySQL of the old server. Problem was he could not connect to the MySQL server from the new web box.

I took a look and it appeared to be a firewall issue. I generally steer clear of iptables, so I joined #iptables on freenode.

Here’s what I learned:

1) Save the current firewall settings:

iptables-save > ~/iptablessave

2) Make sure you saved them:

cat ~/iptablessave

3) Drop the firewall rules to check if the firewall is at fault (danger Will Robinson, no protection!)

iptables -P INPUT ACCEPT && iptables -P OUTPUT ACCEPT && iptables -P FORWARD ACCEPT && iptables -F && iptables -X && iptables -Z

4) Repeat 1-3 on second machine, then try connecting. If it works, you have a firewall …

LinuxConfAU is generally held at a university in the Australian/New Zealand summer holidays. This gives the event enough space with big lecture theatres and other infrastructure.

The easiest accomodation is at the campus colleges (ye roughing it ;-), which have shared bathrooms. Someone who doesn't need to shave can optimise their bathroom presence and therefore better take advantage of any shower becoming available. Just a thought.

I got an email reminder message (several actually) from Matt Asay about the upcoming OSBC conference in San Francisco.  For those of you in high tech in silicon valley who want to understand open source, or anyone looking for a fun way to spend Valentine's day, OSBC is the place to be.  It's the first and best business conference about open source.  Two years ago, when Matt started the conference, open source business sounded like an oxymoron.  Now it's practically a mantra in the valley.  Keynotes include the likes of Nick "Does IT matter?" Carr from Harvard, John Roberts from SugarCRM, Bill Hilf from Microsoft, Peter Thielf from Paypal, Jonathan Schwartz from Sun and Lawrence Lessig from Stanford.

Don't think about it, just sign up.

• OSBC: …

Markus has a very nice writeup on how to use Triggers to emulate check constraints in MySQL. Is there another method? Absolutely. Using views.

Views in MySQL support a concept called Cascaded Check Options. These types of check options are powerfull in my opinion. Traditional check constraints are defined in the schema (ala Oracle), and are not "inheritable" in nature. By using the CASCADED CHECK OPTION when creating a view, the developer can have a complex set of filters for constraint checking. Views can based on other views as well, and if the new view also contains the CASCADED CHECK OPTION clause, then the constraint traverses down the chain of views, constraint checking at each level.

Here is a really simple example that illustrates how this works:

CREATE TABLE Cards (
  card char(1),
  suit varchar(8)
);

CREATE VIEW CardsSpades AS
SELECT card,suit
FROM Cards
WHERE suit = 'Spades' …

In August, I've filed a Feature Request and suggested to allow logging into database tables additionally to writing log files to the file system. The development is in progress and it's told that the feature will probably be available in one of the earlier 5.1 releases.

We also know, that in MySQL 5.1.6 the Event Scheduling Feature will be available - read more about it here: http://dev.mysql.com/tech-resources/articles/event-feature.html

The combination of these two new features will give great new possibilities. The log data will be available more conveniently and it will also be very easy to filter those records that are of interest (with simple SQL statements). The Event Scheduling Feature can be run at regular intervals to filter out particular …

Frank asked in Can MySQL triggers update another table just that.

Here is how to have a users table with a summary field, and a detail table with a value field. When records are inserted, modified or deleted with changing values in the detail table, the matching summaries in the users table are updated.

Continue reading "Triggers maintaining summaries"

Check constraints are one of the features that MySQL's still missing (unfortunately), so I tried if there's a workaround using triggers.

My idea was quite simple. I created one AFTER INSERT trigger and one AFTER UPDATE trigger. They should verify, if they meet the conditions and if they do not, the AFTER INSERT should delete the just inserted record again and the AFTER UPDATE trigger should update the values back to their previous values.

So I created this table:

mysql> CREATE TABLE check_test (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(200) NOT NULL,
    -> email VARCHAR(200) NOT NULL,
    -> score INT NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

The email address should be verified with the regular expression '^[a-z0-9_\.-]+@[a-z0-9_-]+\.[a-z0-9_\.-]+$' and the score should be between 0 and 100. …

I was wondering if it would work to create a user, grant him all privileges on all databases (so make him a superuser) and then revoke all permissions from one particular database.

I knew that this would be quite a complicated process inside the mysql privilege database. All the global privileges would have to be set to 'N' and MySQL would have to insert a new record for each existing database (except the one where the privileges are revoked) to the db table to grant the privileges at database level instead of globally. So I tried it out:

mysql> CREATE USER testuser@localhost
    -> IDENTIFIED BY 'abc123';
Query OK, 0 rows affected (0.00 sec)


mysql> GRANT ALL ON *.*
    -> TO testuser@localhost
    -> WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)


mysql> REVOKE ALL ON test.*
    -> FROM testuser@localhost;
ERROR 1141 (42000): There is no such …