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.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.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