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.