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 …

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 …