ERROR 1048 (23000) Column cannot be NULL, however I am inserting valid data

Question

There are tons of these posts on Stack Overflow, however from the 20 or so that I looked at they were either coding errors faced when interfacing with MySQL (which I am not trying to do) or simply wanted null values but had their table defined incorrectly.

I am seeing an error in MySQL 5.6.19 where I have a column that is not allowed to have a null value. This is fine as it shouldn't have a null value. Here is the table desc below.

    mysql> describe z; 
    +-------+----------+------+-----+---------+----------------+
    | Field | Type     | Null | Key | Default | Extra          |
    +-------+----------+------+-----+---------+----------------+
    | a     | int(11)  | NO   | PRI | NULL    | auto_increment |
    | data  | char(30) | NO   |     | NULL    |                |
    | t     | datetime | YES  |     | NULL    |                |
    +-------+----------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)

My problem is that I am inserting valid data....

    mysql> insert into z (data, t) values('helloworld', sysdate());
    ERROR 1048 (23000): Column 'data' cannot be null

There is one other piece of information that might be of some concern... or may not be.

I have a trigger and procedure that execute upon the implementation of inserts into this column. However I don't see that it should be a problem due to the trigger being activated after the insert statement completes.

Here is the trigger:

    mysql> show triggers\G
    *************************** 1. row ***************************
                 Trigger: insertuser
                   Event: INSERT
                   Table: z
               Statement: begin
    call triggerproc(sysdate(),user(),(select data from z where a = last_insert_id()));
    end
                  Timing: AFTER
                 Created: NULL
                sql_mode: NO_ENGINE_SUBSTITUTION
                 Definer: root@localhost
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: latin1_swedish_ci
    1 row in set (0.00 sec)

And the Procedure:

    mysql> show create procedure triggerproc\G
    *************************** 1. row ***************************
               Procedure: triggerproc
                sql_mode: NO_ENGINE_SUBSTITUTION
        Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `triggerproc`(in a datetime, in b char(30), in c char(30))
    begin
    insert into record (t,u,data) values(a,b,c);
    end
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: latin1_swedish_ci
    1 row in set (0.00 sec)

Just for good measure I will include the definition for the record table as well.

    mysql> desc record;
    +-------+----------+------+-----+---------+-------+
    | Field | Type     | Null | Key | Default | Extra |
    +-------+----------+------+-----+---------+-------+
    | t     | datetime | NO   |     | NULL    |       |
    | u     | char(30) | NO   |     | NULL    |       |
    | data  | char(30) | NO   |     | NULL    |       |
    +-------+----------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

I have looked through the MySQL reference manual for anything that could be of use, however it doesn't seem to have any details on this other than the standard error and to check that your column is not defined as not null... or I missed it...

In any case I would be greatly appreciative if anyone can help me out with finding out either the reason for this error or how I can go about finding the reason.

Thanks in advance.

EDIT: My question was answered wonderfully by TheConstructor he informed me that to grab new information from a column that was just inserted through a trigger that the NEW.column operator may be used. Furthermore he followed up with documentation that helps to understand this issue located at Trigger Syntax.

I only wonder why the trigger that I had wouldn't work with the insert statement even though it should activate after the previous statement, which makes me believe that it should (theoretically) work.

Answer 1

Reading the documentation on LAST_INSERT_ID() I would suggest that the value is only updated after the last trigger runs. I also created a trigger which inserts the result of LAST_INSERT_ID() into another table and it would always insert the id of the row inserted by the INSERT statement before or 0 if there was no previous INSERT.

From within an insert or update trigger you can always refer to the state after the statement by using NEW.column where column is a column-name of your table. See the documentation for examples