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

Question

There are several such messages in Qaru, however with 20 or so that I looked at, they were either coding errors that occurred when interacting with MySQL (which I am not trying to do), or just needed null values, but had their own table defined incorrectly.

I see an error in MySQL 5.6.19, where I have a column that is not allowed to have a null value. This is normal since it should not have a null value. Below is the table 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 more piece of information that may be dangerous ... or may not be.

I have a trigger and a procedure that are executed when injecting inserts into this column. However, I do not see that this should be a problem due to the activation of the trigger 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 a good measure, I will also include the definition of a record table.

  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 looked at the MySQL reference manual for anything that might come in handy, however, it has no details about it other than the standard error, and check that your column is not defined as null. or I missed it ...

In any case, I would be very grateful if someone could help me find out the cause of this error or how I can find the reason.

Thanks in advance.

EDIT: My question was answered perfectly by TheConstructor , he informed me that to capture new information from a column that was simply inserted through a trigger that the NEW.column operator can be used. In addition, he has completed documentation that helps to understand this problem, located in trigger syntax .

I just wonder why the trigger that I had will not work with the insert statement, although it should be activated after the previous statement, which makes me think that it should (theoretically) work.

Answer 1

Reading the documentation for LAST_INSERT_ID() I would assume that the value is updated only after the last run. I also created a trigger that inserts the result of LAST_INSERT_ID() into another table and will always insert the row identifier inserted by the INSERT before or 0 if there was no INSERT before.

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