inserting actual username of a person who edited record into a table with trigger MySQL

[Maciej Domanski]

I have a problem with inserting current user who actually insert,update or delete the data into the table.
I know that the subject was described many times and I did a lot of research, yet ended up here asking for help with this problem.

I have 2 tables: addresses and addresses_do-not-use

table addresses:

CREATE TABLE `addresses` (
`AddressID` INT(11) NOT NULL AUTO_INCREMENT,
`HHNO` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`NicolaID` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertyName` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertySubName` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertyNumber` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertyStreet` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertyTown` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertyCounty` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`Postcode` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`ADD1` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`ADD2` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`ADD3` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`ADD4` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`Number_of_eligible_participants` VARCHAR(5) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
PRIMARY KEY (`AddressID`),
INDEX `NicolaID` (`NicolaID`) USING BTREE,
CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`NicolaID`) REFERENCES `ids` (`NicolaID`) ON UPDATE CASCADE ON DELETE CASCADE
) COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=8539;




Table addresses_do-not-use:

CREATE TABLE `addresses_do-not-use` LIKE `addresses`;

ALTER TABLE `addresses_do-not-use` MODIFY COLUMN AddressID INT(11) NOT NULL,
DROP PRIMARY KEY, ENGINE = MyISAM,
ADD action VARCHAR(8) DEFAULT 'insert' FIRST,
ADD username VARCHAR(60) AFTER action,
ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER username,
ADD dt_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER revision,
ADD PRIMARY KEY (AddressID, revision);

then I created triggers.
I added USER function in order to collect the data who changed the record.

DROP TRIGGER IF EXISTS `CAPI-TEST`.`addresses_ai`;
DROP TRIGGER IF EXISTS `CAPI-TEST`.`addresses_au`;
DROP TRIGGER IF EXISTS `CAPI-TEST`.`addresses_bd`;

CREATE TRIGGER `CAPI-TEST`.`addresses_ai` AFTER INSERT ON `CAPI-TEST`.`addresses` FOR EACH ROW
INSERT INTO `addresses_do-not-use` (`action`, `revision`, `username`, `dt_datetime`, `AddressID`, `HHNO`, `NicolaID`, `PropertyName`, `PropertySubName`, `PropertyNumber`, `PropertyStreet`, `PropertyTown`, `PropertyCounty`, `Postcode`, `ADD1`, `ADD2`, `ADD3`, `ADD4`, `Number_of_eligible_participants`) SELECT 'insert', SUBSTRING_INDEX(USER(),'@',1), NULL, NOW(), d.*
FROM `addresses` AS d WHERE d.AddressID = NEW.AddressID

CREATE TRIGGER `CAPI-TEST`.`addresses_au` AFTER UPDATE ON `CAPI-TEST`.`addresses` FOR EACH ROW
INSERT INTO `addresses_do-not-use` (`action`, `revision`, `username`, `dt_datetime`, `AddressID`, `HHNO`, `NicolaID`, `PropertyName`, `PropertySubName`, `PropertyNumber`, `PropertyStreet`, `PropertyTown`, `PropertyCounty`, `Postcode`, `ADD1`, `ADD2`, `ADD3`, `ADD4`, `Number_of_eligible_participants`) SELECT 'update', SUBSTRING_INDEX(USER(),'@',1), NULL, NOW(), d.*
FROM `addresses` AS d WHERE d.AddressID = NEW.AddressID

CREATE TRIGGER `CAPI-TEST`.`addresses_bd` BEFORE DELETE ON `CAPI-TEST`.`addresses` FOR EACH ROW
INSERT INTO `addresses_do-not-use` (`action`, `revision`, `username`, `dt_datetime`, `AddressID`, `HHNO`, `NicolaID`, `PropertyName`, `PropertySubName`, `PropertyNumber`, `PropertyStreet`, `PropertyTown`, `PropertyCounty`, `Postcode`, `ADD1`, `ADD2`, `ADD3`, `ADD4`, `Number_of_eligible_participants`) SELECT 'delete', SUBSTRING_INDEX(USER(),'@',1), NULL, NOW(), d.*
FROM `addresses` AS d WHERE d.AddressID = OLD.AddressID



The username field remains empty in the addresses_do-not-use table after amending records in addresses table. The rest of the fields are OK but not username. The MySQL version is 5.1.73.
Users connect using HeidiSQL/Navicat being logged in as themselves, so their username should be passed without any problems.

Any ideas why?

I would really appreciate any help with this.

Maciej