MySQL Trigger - After Update Insert Not Triggering Insert

Question

I am trying to log updates from one table by inserting records into a log table after an update is triggered from the initial table using MySQL.

I can not seem to get the code below to cause an insert to happen.

What do I need to do resolve the problem?

Trigger code

     CREATE TRIGGER `issueaim_update` AFTER UPDATE ON `issues`
 FOR EACH ROW INSERT INTO issuehistory 
       SELECT 'update', (select max(revision)+1 from issuehistory where issueid = i.issueid), NOW(), '', IssueID, ProjectID, IssueTitle, Criticality, AssignorID, OwnerID, AltOwnerID, ApproverID, AssignedDate, CompletionDate, DueDate, ECD, ClosedDate, IssueStatement, ClosureCriteria, ClosureStatement, RootCause, CorrectiveAction, IssueResolutionVerification, RejectionJustification, NEW.Category1, Category2, Category3, Category4
        FROM ProjectAIM.issues AS i WHERE IssueID = NEW.IssueID and ProjectID = NEW.ProjectID

Error Log - Most Recent Logged Error

2016-06-12 21:55:39 10e0 InnoDB: Error: Tablespace for table "mysql"."innodb_table_stats" is missing.

2016-06-12 21:55:39 10e0 InnoDB: Error: Fetch of persistent statistics requested for table "projectaim"."issues" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

Post all the trigger code which created this trigger please. — Blank, Jun 13 '16 at 05:26
Seems there is no syntax error, and when you insert a record into `issues`, this trigger did not be executed? And is there some other errors occur? — Blank, Jun 13 '16 at 05:43
I revised the code to the most recent version. When I insert a record the other trigger I have is being executed (the one to handle inserts). When I update the record, only then does the trigger fail to execute. As for other errors, I do not see any in the browser as this is a PHP application. — Vahe, Jun 13 '16 at 15:12
Check your MySQL error log. You'll find it in your MySQL data directory, something like `/var/db/mysql/.err`. The actual path should be in your `my.cnf` file. — Darwin von Corax, Jun 13 '16 at 15:19
@DarwinvonCorax, I have updated my post with most recent log error from MySQL (InnoDB) using wampserver — Vahe, Jun 13 '16 at 17:32
Missing table space. Uh oh. http://superuser.com/a/675888/362931 — bishop, Jun 13 '16 at 17:34
@bishop, after investigation I followed the solution outlined here precisely http://stackoverflow.com/a/33094363/1691103 and my tablespace was restored. Then I re created the triggers and tested with an update and I got a record inserted on update. — Vahe, Jun 15 '16 at 01:29
Glad you got it sorted out! May I kindly suggest you self-answer this one, so future people know how to handle this. :) — bishop, Jun 15 '16 at 01:33
Gladly done, just posted now. Details provided via linked answer. — Vahe, Jun 15 '16 at 01:35

score 0 · Accepted Answer · edited May 23 '17 at 11:52

I was able to solve the issue following the 4 outlined steps below.

1) First, I backed up my table in phpmyadmin as an sql import script.

2) Following the instructions below in the Solution Link, I resolved the tablespace issue.

3) Then I ran the sql import script / trigger creation script in phpmyadmin again to create, populate the table, and re-create my triggers.

4) Finally, I tested with an update and got it to insert the updated records into the log.

Solution Link - WAMP Solution for TableSpace Issue

MySQL Trigger - After Update Insert Not Triggering Insert

1 Answers1