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

MySQLtriggerusers

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) NOT NULL DEFAULT USER() 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 following this link:
Is there a MySQL option/feature to track history of changes to records?
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

Best Answer

I found the problem: everything works out right now. The problem was the order of the fields in trigger, copy-paste technique while creating them made problem unseen until test stage. The triggers should look like:

CREATE TRIGGER `CAPI-TEST`.`addresses_ai` AFTER INSERT ON `CAPI-TEST`.`addresses` FOR EACH ROW
INSERT INTO `addresses_do-not-use` (`action`, `username`, `revision`, `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`, `username`,`revision`,  `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`, `username`, `revision`,  `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.*

OR the most recent version I tested for the update trigger:

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

PASSWORD FUNCTIONS

Please run the following:

show variables like 'old_passwords';
select password('rolando'),old_password('rolando');

Depending on where old_passwords is enabled or not, you should get either the following:

MySQL 5.0 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.1 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

PASSWORDS STORED

Please run this query:

SELECT COUNT(1) password_length_count,password_length FROM
(SELECT LENGTH(password) password_length FROM mysql.user) A
GROUP BY password_length;

If you get any password_length of 16, you have to enable old_passwords for the old style authentication to work.

PASSWORDS FROM CLIENT

In some cases, using mysql libraries that came from old yum installs of MySQL, PHP, Perl or Apache may have pre-MySQL4.1 user authentication code. Please upgrade such libraries.

Mysql – Why are simple SELECTs on InnoDB 100x slower than on MyISAM

YOUR QUERY

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;

At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.

REALITY CHECK

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

InnoDB Architecture

CLUSTERED INDEX

Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.

REDUNDANT INDEXES

You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:

ALTER TABLE newbb_innopost
    DROP INDEX threadid,
    DROP INDEX threadid_2,
    DROP INDEX threadid_visible_dateline,
    ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;

Why strip down these indexes ?

The first three indexes start with threadid
threadid_2 and threadid_visible_dateline start with the same three columns
threadid_visible_dateline does not need postid since it's the PRIMARY KEY and it's embedded

BUFFER CACHING

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.

Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.

TABLE LAYOUT

You could shave of some space from the row by considering importthreadid and importpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.

You should run this

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

This will recommend what data types these columns should be for the given dataset.

CONCLUSION

MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.

While you revealed the amount of RAM (32GB) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), there are still other pieces to this puzzle you have not revealed

The InnoDB settings
The Number of Cores
Other settings from my.cnf

If you can add these things to the question, I can further elaborate.

UPDATE 2014-08-28 11:27 EDT

You should increase threading

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)

query_cache_size = 0

I would preserve the Buffer Pool

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Increase purge threads (if you do DML on multiple tables)

innodb_purge_threads = 4