Random thoughts after Percona Live

Percona Live Europe 2017 has ended. As always it’s been a great opportunity to hear great technical talks, ask questions, get in touch with people, drink beer, collect nice & useless gadgets. And, on a personal note, to meet former colleagues and former customers, some of which I never met before. And even joke with a customer about my sleepy voice when I had to start all my Monday mornings with a Skype call with him, at 8, at home. I revealed him I tried everything, including singing, but my voice didn’t improve. Oh, well, if he messaged me after I left Percona, I probably did a decent job.

Here are some completely random thoughts in random order.

  • MariaDB wasn’t there. There was a single talk, officially from the Foundation (I can’t really distinguish the two entities, and I’m not alone). They have their own conference, M18 will be the second edition. So most people have to choose if to attend Percona or Maria. The only consequence I care about is that they’re splitting the community and making every part weaker. As I wrote for the BSL time ago, I can only hope they will change their mind.
  • Tarantool wasn’t there. So bad, I believe in it.
  • Technologies that are growing fast or strongly promoted: ClickHouse, ProxySQL, Vitess, MyRocks storage engine. Wow.
    • But I’ve attended a very nice talk on a TokuDB real life story. Don’t underestimate it.
  • At Percona Live talks, you often hear questions about performance and quality. With MariaDB talk, I was the only one to ask these things (and actually my questions were almost a DoS attack, sorry Vicentiu). I’m not saying that MariaDB has not quality, I’m saying that users have probably a different approach.
  • Sharding is becoming a hot topic.
  • Open source philosophy is consolidating and expanding. It goes far beyond licenses and marketing claims, it is the attitude and pleasure to collaborate and do things that, without a community, wouldn’t be as strong.
  • As remarked by PZ, the first criteria for choosing a technology is usability. There are very good reasons for this, given the current complexity of big or even medium infrastructures. But it’s still a bit dangerous.
  • If you don’t have a reason to be there next year, I’ll give you one: Irish red beer.

Enjoy.
Federico

Advertisements

Docker: how to modify an existing container

This is a problem I had lots of times, and it is possible that I’m not the only one. Scenario:

  1. I create a container;
  2. I do some work in the container;
  3. I need to connect from outside, but I realize that I didn’t map the port.

Or maybe I did, but I need to map it to another port. Or maybe I want to have a volume.

Docker doesn’t natively allow to remap ports or create volumes if a container is already running. When using Docker on localhost for testing, this sucks.

But don’t cry: you don’t need to create a new container.

All you need to do is:

  1. docker stop container bad_container
  2. docker commit bad_container good_image
  3. docker run --name good_container -d -p ... -v ... good_image

Enjoy!

MariaDB/MySQL: ON REPLACE triggers

There are several reasons why, generally, in MySQL/MariaDB one should not use REPLACE as a shortcut for SELECT + (UPDATE or INSERT). One of these reasons is the way REPLACE fires triggers. Of course it does not fire UPDATE triggers, and there is no such thing as a REPLACE trigger; DELETE and INSERT triggers are fired instead. What is not obvious is the order in which REPLACE activates triggers (UPDATE: this beavior was undocumented; I documented it here):

  1. BEFORE INSERT;
  2. BEFORE DELETE (if a row is being replaced);
  3. AFTER DELETE (if a row is being replaced);
  4. AFTER INSERT.

Knowing this, we can create REPLACE triggers. Or, better said, we can create a Stored Procedure that is invoked before or after each row’s replacement. It is not exactly easy, but it can be done. Some user variables will help us in two ways:

  • They will tell each trigger which triggers have been fired before;
  • they will be used to acces NEW values in a DELETE trigger.

Now, take a look at the following example. The notes below will make some points clearer.

CREATE OR REPLACE TABLE xmp
(
    a INT NOT NULL PRIMARY KEY,
    b INT NOT NULL
)
    ENGINE = InnoDB
;

DELIMITER ||
CREATE PROCEDURE xmp_on_replace(IN old_a INT, IN new_a INT, IN old_b INT, IN new_b INT)
    CONTAINS SQL
BEGIN
    SET @out := CONCAT(
        'In column b, we are replacing ', old_b, ' with ', new_b, '. Cool!'
    );
END ||

CREATE TRIGGER xmp_bi
    BEFORE INSERT
    ON xmp
    FOR EACH ROW
BEGIN
    SET @maybe_replacing := TRUE;
    SET @new_a := NEW.a;
    SET @new_b := NEW.b;
END ||

CREATE TRIGGER xmp_ai
    AFTER INSERT
    ON xmp
    FOR EACH ROW
BEGIN
    SET @maybe_replacing := FALSE;
END ||

CREATE TRIGGER xmp_bd
    BEFORE DELETE
    ON xmp
    FOR EACH ROW
BEGIN
    IF @maybe_replacing THEN
        SET @maybe_replacing := FALSE;
        CALL xmp_on_replace(OLD.a, @new_a, OLD.b, @new_b);
        SET @new_a := NULL;
        SET @new_b := NULL;
    END IF;
END ||
DELIMITER ;

When no row is being replaced, the workflow will be the following:

  1. The BEFORE INSERT triggers sets some user variables, that will only be useful if a row will be replaced.
  2. The AFTER INSERT trigger resets these variables.

If a row is being replaced, the following workflow will take place:

  1. The BEFORE INSERT trigger will set some variables.
  2. The BEFORE DELETE trigger will reset them and call xmp_on_replace(); both OLD.* and NEW.* values are passed, thanks to the user variables.
  3. xmp_on_replace() gives us some information about the row being replaced.

Now, let’s test this example:

MariaDB [test]> INSERT INTO xmp VALUES (2, 0);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> REPLACE xmp VALUES (1, 100), (2, 200), (3, 300);
Query OK, 4 rows affected (0.01 sec)
Records: 3 Duplicates: 1 Warnings: 0

MariaDB [test]> SELECT @out;
+-------------------------------------------------+
| @out                                            |
+-------------------------------------------------+
| In column b, we are replacing 0 with 200. Cool! |
+-------------------------------------------------+
1 row in set (0.00 sec)

Hey, it works as expected!

Enjoy!
Federico

Yet Another HELLO_WORLD()

As the title says, this is yet another blog about programming (YABAP). Because every BAP starts with a helloWorld(), here’s Yet Another Hello World (YAHW). And because all technical sites have a FAQ listing questions that no one ever asked (but the authors would really like to answer), let’s start with…

Yet Another FAQ (FAQ YA!)

Who are you?

My name is Federico Razzoli. I am a developer and a Free Software supporter. I live in Italy and my age is a secret, like many things in Italy.

Is the reason why you support Free Software technical, ethical or fanatical?

I don’t like intellectual property, because I don’t like property. If you think that my vision is stupid, you are free to stop using my software or reading my technical articles. Or, you can pay for them. Please, do it: I like sharing, but I also like to steal money from stupid people! 🙂

Your blog has a strange title. Why?

Words are important, because they have a meaning. One of the reason why the people do not think very much is that they have been educated to use random words. If you want to think (and communicate), you must start thinking to the meaning of the words you use.

False is not null: it’s correct. False is something, and null is nothing. Nothing is not false, because what do not exist have no properties.

Also, FALSE IS NOT NULL is a valid (and TRUE) SQL expression.

Did the internet need yet another programming blog?

This question is very interesting. It can’t be answered now. I’ll try to write something interesting. Then, if I succed, the answer will be TRUE.

At least, I will write my contents. I will not do a blind copy/paste work like many blogs I’ve visited.

That’s all, for now. See ya!
Federico