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 …

Frank Mash did some testing with triggers and inspired me to try to create a log table based on triggers. This also answers his question "After playing earlier with triggers, I wanted to see whether one can update another table using MySQL triggers. I tried something like ... but it didn't work. Is it possible or am I shooting in the dark here?" with a clear yes!

First I created a data and a log table:

mysql> CREATE TABLE data_table (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> d1 VARCHAR(200) NOT NULL UNIQUE,
    -> d2 DECIMAL(9,2) NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE log_table (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> id_data INT UNSIGNED NOT NULL,
    -> old_d1 VARCHAR(200) NULL,
 …

In 5.1.5:

SELECT ExtractValue(’<a>foo<b>bar</b></a>’, ‘//b’);

bar

SELECT UpdateXML(’<a>foo<b>bar</b></a>’, ‘//b’, ‘zig’);

<a>foozig</a>

XSLT implementation on its way, maybe? :)

In 5.1.6:

CREATE EVENT e
ON SCHEDULE EVERY 1 DAY
ON COMPLETION PRESERVE
DO INSERT INTO log VALUES (1);

(think cron)

And:

CREATE EVENT e
ON SCHEDULE AT ‘2006-01-01 …

I used to be almost exclusively a database developer, writing complex queries and stored procedures in Oracle and MySQL. Of late I've been doing more and more web based work, HTML, JavaScript, CSS, PHP and PERL, this means of course that I'm doing less and less SQL especially the more complex stuff that I would have been doing in the past.

Personally I've been using SQL long enough that while it might not flow from my fingers as it did previously with a bit of thinking it comes back fairly quickly. I tend to think of it like fitness, if you go to the gym regularly your fitness levels improve quickly, slack off and they drop back down almost as fast.

To keep my SQL skills "fit" I visit another sort of gym, the MySQL Forums, to be exact the Newbie forum. In that forum there are plenty of people offering you the chance of a mental work out at all skill levels, because of it's very nature there are all sorts of questions …

Lately I have been playing with MySQL stored procedures and came across a comment posted by Scot G that uses procedures to populate a table.

I thought it was a great example for beginners to see the power of MySQL procedures.

mysql> DROP PROCEDURE IF EXISTS build_table;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8976
Current database: odp

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DELIMITER '/';
mysql> CREATE PROCEDURE build_table()
-> BEGIN
-> DECLARE i INTEGER;
-> DECLARE v INTEGER;
-> SET i = 1;
-> SET v = 100;
-> WHILE i <= 125 DO
-> INSERT into mytable VALUES (i, v);
-> SET i = i + 1;
-> SET v = v + 2;
-> END WHILE;
-> END/
DELIMITER ';'/Query OK, 0 …

First create a table

CREATE TABLE points (user VARCHAR(255), points INT(11) );

Triggers are created with "CREATE TRIGGER" and dropped with "DROP TRIGGER" statement.

The following will create a trigger named mytrigger BEFORE (can use AFTER) INSERT (can use DELETE and UPDATE) on the table points
CREATE TRIGGER mytrigger BEFORE INSERT ON points
-> FOR EACH ROW SET @sum_points = @sum_points + NEW.points;

The statement following FOR EACH ROW defines the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering statement In the example, the triggered statement is a simple SET that accumulates the …

Doc Searls, co-author of the ClueTrain Maniphesto, gave a closing address called "What I'm Still Learning from Open Source" at the SD Forum open source gathering last week.  Doc made a number of interesting observations which I will try to paraphrase. 

• Open Source adoption typically happens when the engineers take over.  Adoption decisions are rarely made at the top, it's done by the developers themselves.  It's Do-it-yourself (DIY) IT. Its used when customers need to build solutions for themselves.  They want to save money and get stuff done.
• There's a lot more open source usage going on than we know about (or will read about in the press.)  Lawyers don't want to have their …

Ok, from time to time I get asked the same question by database administrators who surprisingly are still using MySQL 3.XX or MySQL 4.

I know there is a new version of MySQL out. Is it wise for me to upgrade? My previous experience with non-incremental upgrades hasn't been very good. I have heard that there isn't much benefit to upgrading.

As you can imagine, the answer to this question can be found in scattered forms everywhere. MySQL blogs, tutorials and forums are filled with information about why upgrading is the solutions to most problems and definitely a good idea.

Here on MySQL Database Administration blog, I have also posted quite a few posts about why and how to upgrade to MySQL 5.

What I am aiming for is to compile the top ways to convince a reluctant DBA.

Here's my list (please add more)

• Query caching was introduced in MySQL 4 which …

At conferences like LCA 2006, there are all these laptops running out of juice.... of course, those who try a compile of say MySQL or the Linux kernel, run out faster ;-)

This is pretty cool: SplashPower. It's a mat you put on your desktop, and then toss devices like your mobile, headset, and organiser on top of it - and voila, it will get power and charge. Less wire-clutter and very very handy.
It is of course induction based, and each device needs a tiny little strip of magic to make it work. So it is really a fairly generic solution.

SplashPower is not yet available in stores, they're currently talking with distributors.

I sent mail to RMS the other day asking what he thought of MySQL's licensing scheme:

From cjcollier colliertech.org Wed Jan 18 13:38:38 2006
Return-path: 
Envelope-to: cjcollier colliertech.org
Delivery-date: Wed, 18 Jan 2006 13:38:38 -0800
Received: from [216.39.139.201] (helo=[127.0.0.1])
        by moonunit.colliertech.org with esmtp (Exim 4.50)
        id 1EzL0o-0000sU-3j; Wed, 18 Jan 2006 13:38:38 -0800
Message-ID: <43CEB557.6050909@colliertech.org>
Date: Wed, 18 Jan 2006 13:38:31 -0800
From: "C.J. Collier" 
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To:  rms gnu.org
Subject: MySQL connector licenses
X-Enigmail-Version: 0.93.0.0
OpenPGP: id=6CC27EA8;
        url=http://colliertech.org/~cjcollier/gpg/cjcollier.pub
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Spam-Checker-Version: SpamAssassin 3.0.3 (2005-04-27) on …