The install-db.sh script worked flawlessly for me on Gentoo using Mysql 5.0.70-r1
This is a MySQL bug and can been fixed by simply upgrading.
If you don't want to do so, then remove the sections that are using the delimiter and run them manual. The MySQL bug only appear when you source a file and not when you run write queries in the mysql client.
Another possibility is to avoid using the delimiter by using the \g delimiter which can normally be used in mysql as an alternative to the usual semicolon `;ยด.
So find the following ...
Code:
DELIMITER //
CREATE TRIGGER `cc_ratecard_validate_regex_ins` BEFORE INSERT ON `cc_ratecard`
FOR EACH ROW BEGIN
DECLARE valid INTEGER;
SELECT '0' REGEXP REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT('^', NEW.dialprefix, '$'), 'X', '[0-9]'), 'Z', '[1-9]'), 'N', '[2-9]'), '.', '+'), '_', '') INTO valid;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `cc_ratecard_validate_regex_upd`;
DELIMITER //
CREATE TRIGGER `cc_ratecard_validate_regex_upd` BEFORE UPDATE ON `cc_ratecard`
FOR EACH ROW BEGIN
DECLARE valid INTEGER;
SELECT '0' REGEXP REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT('^', NEW.dialprefix, '$'), 'X', '[0-9]'), 'Z', '[1-9]'), 'N', '[2-9]'), '.', '+'), '_', '') INTO valid;
END
//
DELIMITER ;
... and replace it with ...
Code:
CREATE TRIGGER `cc_ratecard_validate_regex_ins` BEFORE INSERT ON `cc_ratecard`
FOR EACH ROW BEGIN
DECLARE valid INTEGER\g
SELECT '0' REGEXP REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT('^', NEW.dialprefix, '$'), 'X', '[0-9]'), 'Z', '[1-9]'), 'N', '[2-9]'), '.', '+'), '_', '') INTO valid\g
END;
DROP TRIGGER IF EXISTS `cc_ratecard_validate_regex_upd`;
CREATE TRIGGER `cc_ratecard_validate_regex_upd` BEFORE UPDATE ON `cc_ratecard`
FOR EACH ROW BEGIN
DECLARE valid INTEGER\g
SELECT '0' REGEXP REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT('^', NEW.dialprefix, '$'), 'X', '[0-9]'), 'Z', '[1-9]'), 'N', '[2-9]'), '.', '+'), '_', '') INTO valid\g
END;
Than, find this ...
Code:
DELIMITER //
CREATE TRIGGER `cc_card_serial_set` BEFORE INSERT ON `cc_card`
FOR EACH ROW BEGIN
UPDATE cc_card_seria set value=value+1 where id=NEW.id_seria ;
SELECT value INTO @serial from cc_card_seria where id=NEW.id_seria ;
SET NEW.serial=@serial;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `cc_card_serial_update`;
DELIMITER //
CREATE TRIGGER `cc_card_serial_update` BEFORE UPDATE ON `cc_card`
FOR EACH ROW BEGIN
IF NEW.id_seria<>OLD.id_seria OR OLD.id_seria IS NULL THEN
UPDATE cc_card_seria set value=value+1 where id=NEW.id_seria ;
SELECT value INTO @serial from cc_card_seria where id=NEW.id_seria ;
SET NEW.serial=@serial;
END IF;
END
//
DELIMITER ;
And replace it with that ...
Code:
CREATE TRIGGER `cc_card_serial_set` BEFORE INSERT ON `cc_card`
FOR EACH ROW BEGIN
UPDATE cc_card_seria set value=value+1 where id=NEW.id_seria \g
SELECT value INTO @serial from cc_card_seria where id=NEW.id_seria \g
SET NEW.serial=@serial\g
END;
DROP TRIGGER IF EXISTS `cc_card_serial_update`;
CREATE TRIGGER `cc_card_serial_update` BEFORE UPDATE ON `cc_card`
FOR EACH ROW BEGIN
IF NEW.id_seria<>OLD.id_seria OR OLD.id_seria IS NULL THEN
UPDATE cc_card_seria set value=value+1 where id=NEW.id_seria \g
SELECT value INTO @serial from cc_card_seria where id=NEW.id_seria \g
SET NEW.serial=@serial\g
END IF\g
END;
This works. I have tried it. The best thing you can do at this point is to upgrade your MySQL. You may be thinking that you have the lastest version, but that will only be the latest that your Linux distro will let you install.
Cheers
Login
Register
FAQ
Search
