Mysql, insertion in trigger inserting NULL values while shouldn't

Question

The logic is to add a row in a table in another DB when a row is inserted in the Keycloak user table.(simple enough I guess) What happened was only the Key has value but the rest were NULL inserted (they do have values , they were all correctly inserted in KeyCloak table before the trigger happened)

Caesar had the same problem but it did not work with his solution. MySQL "After Insert" Trigger keeps inserting nulls

I am using PHPMyAdmin for Mysql some code will be added automatically like "for each row..."

Name: add
Table: USER_ENTITY
Time: AFTER
Event: INSERT
Definition:

BEGIN

INSERT INTO AnotherDB.application_user 
(`user_id`,`first_name`,`last_name`,`hash`) VALUES (NEW.ID, NEW.FIRST_NAME, 
NEW.LAST_NAME, md5(NEW.ID));

END

I expect the row inserted to be ID , FIRST, LAST, HASH But I get ID, NULL , NULL, HASH So what is the cause for this problem?

Edit June 21: thanks for the edits and suggestions. The target insertion table's creation sql is

CREATE TABLE `application_user` (. 
    `user_id` varchar(256) NOT NULL,  
    `type` varchar(256) DEFAULT NULL,  
    `first_name` varchar(256) CHARACTER SET utf8 DEFAULT NULL,  
    `last_name` varchar(256) CHARACTER SET utf8 DEFAULT NULL,  
    `group_id` varchar(256) DEFAULT NULL,  
    `wallet_id` varchar(256) NOT NULL,  
     PRIMARY KEY (`user_id`). 
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1.

Keycloak's USER_ENTITY table is way longer but it could be where the problem is:

CREATE TABLE `USER_ENTITY` (
  `ID` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
  `EMAIL` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `EMAIL_CONSTRAINT` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `EMAIL_VERIFIED` bit(1) NOT NULL DEFAULT b'0',
  `ENABLED` bit(1) NOT NULL DEFAULT b'0',
  `FEDERATION_LINK` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `FIRST_NAME` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `LAST_NAME` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `REALM_ID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USERNAME` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `CREATED_TIMESTAMP` bigint(20) DEFAULT NULL,
  `SERVICE_ACCOUNT_CLIENT_LINK` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
  `NOT_BEFORE` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_DYKN684SL8UP1CRFEI6ECKHD7` (`REALM_ID`,`EMAIL_CONSTRAINT`),
  UNIQUE KEY `UK_RU8TT6T700S9V50BU18WS5HA6` (`REALM_ID`,`USERNAME`),
  KEY `IDX_USER_EMAIL` (`EMAIL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And also I created this test table in a new DB and put in the same trigger, it works fine with both tables:

CREATE TABLE `test` (
  `ID` varchar(256) NOT NULL,
  `FIRST_NAME` varchar(256) NOT NULL,
  `LAST_NAME` varchar(256) NOT NULL,
  `SOMETHING` varchar(256) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The thing is I don't know what sql Keycloak will be executed when it receives a register request from its adapter and I can't reproduce the problem with my table.

executeSHOW CREATE TABLE AnotherDB.application_user and other related tables in the question and paste the output(s) in the question by a edit.. — Raymond Nijland
– Raymond Nijland, Commented Jun 20, 2019 at 11:46
Code should work. Please add table definitions for keycloak,application_user and all trigger code together with an insert to keycloak. — P.Salmon
– P.Salmon, Commented Jun 20, 2019 at 11:47
Also see Why should I provide a Minimal Reproducible Example for a very simple SQL query? — Raymond Nijland
– Raymond Nijland, Commented Jun 20, 2019 at 11:47

MikeZ · Accepted Answer · 2019-07-04 07:57:47Z

0

OK... It turns out to be something like this:

when Keycloak adds a row to that table, it tries to add a row with only ID(to test with UUID conflict?) first, then update to that row with full context.

So that trigger is correct in logic but it will only get the primary key back because there is nothing else there at the point of insertion.

answered Jul 4, 2019 at 7:57

MikeZ

34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql, insertion in trigger inserting NULL values while shouldn't

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related