Can't update an table's column but works after using update sql query in phpmyadmin

Question

When a user tries to update their settings, I inserted into my audit log table called userUpdateLogand it calls a before insert trigger to insert into userProfiles and users based off the columns insert into their respective table.

But for an column, nickName that I added after the userProfiles table was created and have data, it doesn't update via trigger using the query

UPDATE userProfiles 
   SET nickName = NEW.newNickName 
 WHERE userID = NEW.userID

but when I execute the query in phpmyadmin with written values

UPDATE userProfiles 
   SET nickName = 'random' 
 WHERE userID = 2

it works and after having data in the column for that specific row, the trigger works and was able to update for that row after inserting into userUpdateLog and the triggers executes,

the other rows encounter the same issue until I insert manually via phpmyadmin.

What is the cause of this issue that requires an manually insert via phpmyadmin to work?

CREATE TABLE `userUpdateLog` (
  `userUpdateLogID` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  `userID` int(11) DEFAULT NULL,
  `updateDate` datetime NOT NULL,
  `newUserName` varchar(128) DEFAULT NULL,
  `newEmail` varchar(128) DEFAULT NULL,
  `newNickName` varchar(20) DEFAULT NULL,
  `newFirstName` varchar(128) DEFAULT NULL,
  `newLastName` varchar(128) DEFAULT NULL,
  `oldUserName` varchar(128) DEFAULT NULL,
  `oldEmail` varchar(128) DEFAULT NULL,
  `oldNickName` varchar(20) DEFAULT NULL,
  `oldFirstName` varchar(128) DEFAULT NULL,
  `oldLastName` varchar(128) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER $$
CREATE TRIGGER `usersUpdate_bi` BEFORE INSERT ON `userUpdateLog` FOR EACH ROW BEGIN
    SET @userName = (SELECT userName FROM users WHERE userID = NEW.userID);
    SET @email = (SELECT email FROM users WHERE userID = NEW.userID);
    SET @firstName = (SELECT firstName FROM users WHERE userID = NEW.userID);
    SET @lastName = (SELECT lastName FROM users WHERE userID = NEW.userID);
    SET @nickName = (SELECT nickName FROM userProfiles WHERE userID = NEW.userID);
    SET NEW.oldUserName = @userName;
    SET NEW.oldEmail = @email;
    SET NEW.oldFirstName = @firstName;
    SET NEW.oldLastName = @lastName; 
    SET NEW.oldNickName = @nickName;
    IF NEW.newUserName IS NOT NULL AND NEW.newUserName <> NEW.oldUserName THEN
        UPDATE users 
        SET userName = NEW.newUserName 
        WHERE userID = NEW.userID;
    END IF;
    IF NEW.newEmail IS NOT NULL AND NEW.newEmail <> NEW.oldEmail THEN
        UPDATE users 
        SET email = NEW.newEmail 
        WHERE userID = NEW.userID;
    END IF;
    IF NEW.newFirstName IS NOT NULL AND NEW.newFirstName <> NEW.oldFirstName THEN
        UPDATE users 
        SET firstName = NEW.newFirstName 
        WHERE userID = NEW.userID; 
    END IF;
    IF NEW.newLastName IS NOT NULL AND NEW.newLastName <> NEW.oldLastName THEN
        UPDATE users 
        SET lastName = NEW.newLastName 
        WHERE userID = NEW.userID;
    END IF;
    IF NEW.newNickName IS NOT NULL AND NEW.newNickName <> NEW.oldNickName THEN
        UPDATE userProfiles SET nickName = NEW.newNickName 
        WHERE userID = NEW.userID;
    END IF;
END $$
DELIMITER ;

CREATE TABLE `userProfiles` (
  `profileID` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `imgName` varchar(128) DEFAULT NULL,
  `nickName` varchar(128) DEFAULT NULL,
  `aboutUser` text DEFAULT NULL,
  `genderID` int(11) NOT NULL DEFAULT 0,
  `bDay` date DEFAULT NULL,
  `favCard` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `userID` int(11) PRIMARY KEY  NOT NULL AUTO_INCREMENT,
  `firstName` varchar(128) NOT NULL,
  `lastName` varchar(128) NOT NULL,
  `userName` varchar(128) UNIQUE KEY NOT NULL,
  `email` varchar(128) UNIQUE KEY NOT NULL,
  `pwd` varchar(128) NOT NULL,
  `loginDateTime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER $$
CREATE TRIGGER `userCreate` AFTER INSERT ON `users` FOR EACH ROW BEGIN 
INSERT INTO userProfiles (userID) VALUES(NEW.userID);
END$$
DELIMITER ;

Is there something that I need to do after alter table add column etc.. to reload/refresh the tables so UPDATE userProfiles SET nickName = NEW.newNickName WHERE userID = NEW.userID query will work in php file or in triggers. Thanks in advance!

UPDATE: I just added a new column to the userProfiles table and I am unable to add values/input into that column but any column was defined upon creation of the table works.

UPDATE - 2 I created a new table, using the same columns, fks, and all called usersProfiles and the table did not update aswell, until I edited the column via phpmyadmin. Also I noticed when I simulate query in phpmyadmin it says matched 0 row, but query worked afterwords upon execute.

Would having 18 tables in the database be the issue? Any help or tips to find the error/issue would be great!

Brandon · Accepted Answer · 2021-02-13T03:33:17.013

Not the best solutions but I found a workaround,

I changed the nickName column from:

ALTER TABLE userProfiles 
CHANGE 'nickName' 'nickName' varchar(128) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci 
DEFAULT NULL;

to:

ALTER TABLE `userProfiles` 
CHANGE `nickName` `nickName` VARCHAR(128) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci 
NOT NULL DEFAULT 'N/A';

then the columns work and any new row inserted as well, but if I change it back to default null then it glitches as stated in my post.

Can't update an table's column but works after using update sql query in phpmyadmin

1 Answers1