I just converted to MariaDB from MySQL where this trigger worked fine.
DELIMITER ;; CREATE TRIGGER `email_uuid_before_insert` BEFORE INSERT ON `email` FOR EACH ROW BEGIN IF new.id_email IS NULL THEN SET new.id_email = uuid(); END IF; END;; DELIMITER ;;
In MariaDB, it creates with no errors but when adding a row, I get an error
1062 : Duplicate entry '' for key 'PRIMARY'
It’s caused because the last row that inserted wasn’t populated with that UUID – meaning the trigger failed.
What would cause this to fail?
Thank you, Steffan Cline steffan@hldns.commailto:steffan@hldns.com 602-793-0014
You don't share any details of your table structure, insert statement, settings or version, but one way to replicate the behaviour you describe in a current version would be if email_id is not NULL and strict mode is unset. For example::
set sql_mode=''; Query OK, 0 rows affected (0.000 sec)
CREATE OR REPLACE TABLE `email` ( `id_email` varchar(20), `email_text` varchar(30) DEFAULT NULL, PRIMARY KEY(id_email) ); Query OK, 0 rows affected (0.035 sec)
DELIMITER // CREATE TRIGGER `email_uuid_before_insert` BEFORE INSERT ON `email` FOR EACH ROW BEGIN IF new.id_email IS NULL THEN SET new.id_email = uuid(); END IF; END// Query OK, 0 rows affected (0.018 sec)
DELIMITER ; insert into email(email_text) VALUES('y'); Query OK, 1 row affected, 1 warning (0.003 sec)
show warnings; +---------+------+-----------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------+ | Warning | 1364 | Field 'id_email' doesn't have a default value | +---------+------+-----------------------------------------------+
select * from email; +----------+------------+ | id_email | email_text | +----------+------------+ | | y | +----------+------------+
insert into email(email_text) VALUES('z'); ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'
select * from email; +----------+------------+ | id_email | email_text | +----------+------------+ | | y | +----------+------------+
On Fri, Mar 15, 2024 at 11:07 AM steffan--- via discuss < discuss@lists.mariadb.org> wrote:
I just converted to MariaDB from MySQL where this trigger worked fine.
DELIMITER ;;
CREATE TRIGGER `email_uuid_before_insert`
BEFORE INSERT ON `email` FOR EACH ROW
BEGIN
IF new.id_email IS NULL THEN
SET new.id_email = uuid();
END IF;
END;;
DELIMITER ;;
In MariaDB, it creates with no errors but when adding a row, I get an error
1062 : Duplicate entry '' for key 'PRIMARY'
It’s caused because the last row that inserted wasn’t populated with that UUID – meaning the trigger failed.
What would cause this to fail?
Thank you,
Steffan Cline
steffan@hldns.com
602-793-0014
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Ian,
I see your point. Sorry I did not include that but your assumption in your table design was spot on.
I finally got it to work by removing the if statement around setting the UUID. I don’t understand why it worked in MySQL but not MariaDB.
I do have the id_email set to not null and primary key with sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER.
I see you ran into the same error that I did. Are you saying that strict mode has to be set for this to work?
Thank you, Steffan Cline steffan@hldns.commailto:steffan@hldns.com 602-793-0014
On 3/15/24, 3:36 AM, "Ian Gilfillan via discuss" discuss@lists.mariadb.org wrote:
You don't share any details of your table structure, insert statement, settings or version, but one way to replicate the behaviour you describe in a current version would be if email_id is not NULL and strict mode is unset. For example::
set sql_mode=''; Query OK, 0 rows affected (0.000 sec)
CREATE OR REPLACE TABLE `email` ( `id_email` varchar(20), `email_text` varchar(30) DEFAULT NULL, PRIMARY KEY(id_email) ); Query OK, 0 rows affected (0.035 sec)
DELIMITER // CREATE TRIGGER `email_uuid_before_insert` BEFORE INSERT ON `email` FOR EACH ROW BEGIN IF new.id_email IS NULL THEN SET new.id_email = uuid(); END IF; END// Query OK, 0 rows affected (0.018 sec)
DELIMITER ; insert into email(email_text) VALUES('y'); Query OK, 1 row affected, 1 warning (0.003 sec)
show warnings; +---------+------+-----------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------+ | Warning | 1364 | Field 'id_email' doesn't have a default value | +---------+------+-----------------------------------------------+
select * from email; +----------+------------+ | id_email | email_text | +----------+------------+ | | y | +----------+------------+
insert into email(email_text) VALUES('z'); ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'
select * from email; +----------+------------+ | id_email | email_text | +----------+------------+ | | y | +----------+------------+
On Fri, Mar 15, 2024 at 11:07 AM steffan--- via discuss <discuss@lists.mariadb.orgmailto:discuss@lists.mariadb.org> wrote: I just converted to MariaDB from MySQL where this trigger worked fine.
DELIMITER ;; CREATE TRIGGER `email_uuid_before_insert` BEFORE INSERT ON `email` FOR EACH ROW BEGIN IF new.id_email IS NULL THEN SET new.id_email = uuid(); END IF; END;; DELIMITER ;;
In MariaDB, it creates with no errors but when adding a row, I get an error
1062 : Duplicate entry '' for key 'PRIMARY'
It’s caused because the last row that inserted wasn’t populated with that UUID – meaning the trigger failed.
What would cause this to fail?
Thank you, Steffan Cline steffan@hldns.commailto:steffan@hldns.com 602-793-0014
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.orgmailto:discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.orgmailto:discuss-leave@lists.mariadb.org
participants (2)
-
Ian Gilfillan -
steffan＠hldns.com