Secret Santa Script

My partner comes from a large family. At Christmas time, rather than making everyone buy presents for everyone else (a rather expensive venture), we have a Secret Santa tradition. Unfortunately, as the years have gone on, it has become more difficult to organize for everyone to be in the one place to “pick a name out of a hat”.

And so, in my never-ending quest to remove the magic and joy from Christmas, I wrote a script to automatically assign everyone their Santa/Santee, and send them an SMS. It has a few cool things to it:

  • Allows you to prevent some people from being paired up. For example, you might not want a couple in the family to be assigned each other, because they’ll probably buy each other presents anyway.
  • Makes sure that everyone has a Santee before it starts sending messages, to prevent SMS spam.
  • Checks that all messages went through successfully.

I found that ValueSMS worked nicely for sending messages in Australia, you might need to use a different one for your country. Any gateway that has a HTTP(S) interface will do the job.

To set it up for yourself, you’ll need to do the following:

  • Edit the @names array to create a list of everyone participating (beginning line 12).
  • Edit the @people array to give the details of everyone participating (beginning line 20).
  • Edit in your ValueSMS username/password, or setup the HTTP(S) URL for your preferred SMS provider (beginning lines 101, 139).

Here it is, for your enjoyment:

0.1 – 2009-02-16: secretsanta.pl.gz

Case Insensitive REPLACE() for MySQL

One request I occasionally see is for a case insensitive version of REPLACE() for MySQL. I wrote this a while back, but here it is now for all of you to play around with. It uses a basic naïve string search algorithm, so can be slow under some circumstances.
[code lang=”sql”]DELIMITER $$

DROP FUNCTION IF EXISTS replace_ci$$
CREATE FUNCTION replace_ci ( str TEXT,needle CHAR(255),str_rep CHAR(255))
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE return_str TEXT DEFAULT ”;
DECLARE lower_str TEXT;
DECLARE lower_needle TEXT;
DECLARE pos INT DEFAULT 1;
DECLARE old_pos INT DEFAULT 1;

IF needle = ” THEN
RETURN str;
END IF;

SELECT lower(str) INTO lower_str;
SELECT lower(needle) INTO lower_needle;
SELECT locate(lower_needle, lower_str, pos) INTO pos;
WHILE pos > 0 DO
SELECT concat(return_str, substr(str, old_pos, pos-old_pos), str_rep) INTO return_str;
SELECT pos + char_length(needle) INTO pos;
SELECT pos INTO old_pos;
SELECT locate(lower_needle, lower_str, pos) INTO pos;
END WHILE;
SELECT concat(return_str, substr(str, old_pos, char_length(str))) INTO return_str;
RETURN return_str;
END$$

DELIMITER ;

SELECT replace_ci( ‘mySQL’, ‘M’, ‘M’ );[/code]
It’s also available on MySQL Forge.

Replication with InnoDB and MyISAM Transactions

There’s a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table. It’s a side effect of fixing Bug #40116. Take this simple example:

After this, the Master innodb_tbl would look like this:

item val
1 1
2 2

And the Master myisam_tbl will look like this:

item val
1 2

In 5.1.30 and earlier, the Slave tables will be correct. However, in 5.1.31, the Slave myisam_tbl will be correct, but the innodb_tbl will look like this:

item val
1 0
2 1

As a bonus, there’s no workaround. Statement Based Replication has never worked for this case. For an SBR Slave (In MySQL 5.0.x and 5.1.x), the Slave myisam_tbl will be correct, but the Slave innodb_tbl will look like this:

item val
1 2
2 2

And so, we come to the moral of the story. Don’t use non-transactional tables in the middle of a transaction. Ever. You will only cause yourself more pain than you can possibly imagine. Instead, move the writes to the non-transactional tables outside of the transaction.