This Week I Learned 2018 - Week 19

Last week post or something else instead.

Surprised and shocked. The only two words I can think off to describe my feeling for the past week. First, surprised because this one particular item on my bucket list which I guestimate will took me next 20-plus years or never to check off have been accomplished this week. Secondly, shocked it came so soon and such a massive shift and the non-stop flair of dramas. The staycation was well-needed as I was still recovering from my injury. Enough said, I've done my part and moved on to next item on my list.

Infestation of the Blue Green Algae (BGA) is getting worse and we have to do almost daily water changes on certain aquarium tanks. Prevention method is quite straightforward, use blackout method. First, 30% to 50% water changes, add air stone, cover tank for 3 to 4 days, and did another 30% to 50% water changes again.

Some MySQL stuff I've encountered recently.

When creating a MySQL stored function, do remember if the function does not modify data, you will need to set extra explicit options for binary logging.
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

The sample SQL statement as follows.
CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN i;
END;

The limitation or restriction of subquery in MySQL. It doesn't supports `LIMIT` in subquery yet? Encountered this error message. To work around, you've to rewrite you SQL query using `JOIN` instead.
SQL Error (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Can't delete using subquery? Wrap your subquery in additional subquery.
DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
    FROM (SELECT id
        FROM tableE
        WHERE arg = 1 AND foo = 'bar'
    ) x
);


No comments:

Post a Comment