MySQL Bugs: #34597: Warning in error log when calling a function from a trigger

Bug #34597	Warning in error log when calling a function from a trigger
Submitted:	15 Feb 2008 15:28	Modified:	17 Oct 2008 16:18
Reporter:	Sébastien Gourdet	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.1+	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I have some innodb tables in my schema, using character set utf8. All of this schema is replicated on 3 slaves.
To ensure data integrity, I need to use triggers. As many triggers use the same conditions, I can group this conditions in stored functions.

If I proceed this way, each time the trigger is involved, I get a warning in the mysql error log. So I did search in the documentation to find a solution and didn't find anything relevant.

The configuration of my server is :
 - binary logging enabled
 - binlog_format = STATEMENT (since row format has some bugs)
 - log-bin-trust-function-creators = 1
Used functions are deterministic and declared this way.

How to repeat:
Create a table :
create table SIZE_RULE (
  ID_SIZE_RULE     smallint unsigned   not null             comment 'Unique identifier of the rule',
  ID_PROJECT       int unsigned        not null             comment 'Unique identifier of the project',
  SR_NAME          varchar(50)         not null             comment 'Name of the rule',
  SR_TYPE          enum('I', 'L')      not null             comment 'Rule type : I = Interval, L = List',
  SR_LIST          varchar(50)                              comment 'List of values (for list based rules)',
  SR_MIN           smallint unsigned                        comment 'Min value (for interval based rules)',
  SR_MAX           smallint unsigned                        comment 'Max value (for interval based rules)',
  SR_INTERVAL      smallint unsigned                        comment 'Interval between 2 sizes (for interval based rules)',
  SR_FORMAT        smallint unsigned            default 0   comment 'Encoded file output formats',
  SR_UPDATE_DATE   datetime            not null             comment 'Last update date of the rule',
  SR_STATUS        enum('E', 'K')      not null             comment 'Rule status : E = Enabled, K = Killed'
)
  engine = InnoDB
  character set = utf8
  comment = 'Resizing rules for images';

alter table SIZE_RULE
  add constraint PK_SIZE_RULE
    primary key (ID_SIZE_RULE);

then a function to check the values (delimiter is /) :
drop function if exists checkSizeRule/

create function checkSizeRule(inIdRule smallint unsigned, inList varchar(50), inMin smallint unsigned, inMax smallint unsigned, inInterval smallint unsigned)
  returns boolean
  deterministic
  reads sql data
  sql security invoker
begin
  declare result boolean;
  declare idRuleIsNull smallint unsigned;
  declare rType enum('I', 'L');

  set result = true;

  #Check values consistency
  if (ifnull(inIdRule, 0) = 0 and (ifnull(inList, 0) != 0 or ifnull(inMin, 0) != 0 or ifnull(inMax, 0) != 0 or ifnull(inInterval, 0) != 0)) then
    set result = false;
  end if;

  if result = true then
    #Get the rule type
    select SR_TYPE
      into rType
      from SIZE_RULE
      where ID_SIZE_RULE = inIdRule;

    #A list based resizing rule should have only the list filled, and vice-versa
    if rType = 'I' and (ifnull(inMin, 0) = 0 or ifnull(inMax, 0) = 0 or ifnull(inInterval, 0) = 0 or ifnull(inList, '') != '') then
      set result = false;
    elseif rType = 'L' and (ifnull(inMin,0) != 0 or ifnull(inMax, 0) != 0 or ifnull(inInterval, 0) != 0 or ifnull(inList, '') = '') then
      set result = false;
    end if;
  end if;

  return result;
end;
/

And the trigger using the function :
drop trigger if exists TG1_SIZE_RULE/

create trigger TG1_SIZE_RULE
  after insert on SIZE_RULE
    for each row
begin
  if not checkSizeRule(new.ID_SIZE_RULE, new.SR_LIST, new.SR_MIN, new.SR_MAX, new.SR_INTERVAL) then
    set @error = raise_error('Rule limits are not compatible with rule type');
  end if;
end;
/

Now, when inserting a row, the message will appear in the error log : 
080215 15:49:38 [Warning] Statement is not safe to log in statement format. Statement: insert into SIZE_RULE (ID_PROJECT, SR_NAME, SR_TYPE, SR_LIST, SR_MIN, SR_MAX, SR_INTERVAL, SR_FORMAT, SR_UPDATE_DATE, SR_STATUS) values (21, 'My_Rule', 'I', null, 100, 400, 1, 1, now(), 'E')

Many thanks for writing a bug report.

as far as I understand this, the replication will work anyway. Please correct me and open this bug again, if this is wrong.

The message you got is just a warning.

The reason is the now().
The warning tells you, that you have to be careful, because now() wouldn't be the exact similar on every machine.

An example:

At 2008-02-15 18:05:38 you make on the master:
mysql> insert into tab(col) values(now());

The column of the master row will get the value: 2008-02-15 18:05:38

Because statement based replication means that the statement will be replicated the now() function will transfered to the slaves and executed there.

Let's say, your slave has a delay of 3 seconds.

The replicated column on the slave contains now the value: 2008-02-15 18:05:41

2008-02-15 18:05:38 is not exact the same as 2008-02-15 18:05:41

The warning message just want to let you know this.

Sorry, but for the now() function there is a timestamp that is stored in the binary log to make it well replicated.
see http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html

Unfortunately, I can't repeat this.

Please, can you add raise_error function here too.

As Cyril said, the now() function isn't the reason why there's this message. If this was the reason, this would lead to a lot of warnings, but they are present only for this trigger.

The code of the raise_error function is :
my_bool raise_error_init(UDF_INIT *io_pInitId, UDF_ARGS *in_pArgs, char *out_pMessage)
{
  unsigned int l_nStrLength;

  if ((in_pArgs->arg_count == 1) && (in_pArgs->arg_type[0] == STRING_RESULT))
  {
    l_nStrLength = strlen(in_pArgs->args[0]) + 1;
    if (l_nStrLength > MYSQL_ERRMSG_SIZE)
    {
      memcpy(out_pMessage, in_pArgs->args[0], MYSQL_ERRMSG_SIZE);
    }
    else
    {
      memcpy(out_pMessage, in_pArgs->args[0], l_nStrLength);
    }
  }
  else
  {
    memcpy(out_pMessage, "Unspecified error raised", 25);
  }

  return 1;
}

long long raise_error(UDF_INIT *in_pInitId, UDF_ARGS *in_pArgs, char *out_pIsNull, char *out_pError)
{
  return 0;
}

void raise_error_deinit(UDF_INIT *in_pInitId)
{
}

But I'm sure the problem doesn't come from this function, since it's called only in the trigger, end not in the function itself. Now if I suppress the function, then integrate its code within the trigger, there's no problem and everything goes well.
So the problem is really linked to the SQL function itself.

Please, try to repeat with a newer version, 5.1.26, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Sorry, I wasn't here for the last month.
So I tested with 5.1.26, and the problem is still here.

Please, try to repeat with a newer version, 5.1.28, and inform about the results.

Hi!

in the raise_error_init function, can you add this line

*io_pInitId->const_item = 1;

right before the return statement, and check if it solves the problem?

Thank you!

Sébastian,

As you figured out already, I can't reproduce your problem by not using the function. Please try the idea from Roland.

Sébastien,

Under statement-based replication, it is necessary to have the UDF library installed on both the master and the slave. In that case, the statement containing the UDF call will be replicated and executed on the slave.

Maybe, this is an information that would help you too.

Thanks fo your answers. Our test platform is blocked for a few days (qualification process), but I'll try ASAP to test with a 5.1.28 release and to change the code of the raise_error function.
Of course, UDF is installed both on the master and the slave sides.

Hi,
Tests have been donc today, with your ideas :
 - mysql 5.1.28
 - setting const_item of UDF_INIT to 1
These 2 modifications don't change anything, the problem is still here.

Thank you for the report.

Warning occurs, because you use UDF function with statement-based replication. This is correct, but should be properly documented, so I verify this report as documentation bug.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Commit for Docs update is here: http://lists.mysql.com/commits/56487

Slightly different problem:

5.0.67 master replicating to 5.1 slave.
SET SQL_MODE ='STRICT_TRANS_TABLES';

When I execute an update with LIMIT, the master silently writes the statement into the binlog, but the slave raises an error and stops the replication immediately. 

Than means, replication on 5.1 is NOT BACKWARDS COMPATIBLE. Am I right? thanks for your feedback.

Vojtech

Since - as noted in previous comments - I've already taken care of the Documentation issue and the bug category's been changed back to Server:Replication, I've removed myself as assignee.