Hi Davi, Sergei,
Thanks for the comments. Please, find below my reply and let me know
if you have more feedback.
Regarding Davi's suggestions:
D1. Pipe through script - Thanks for point this out, it did not occur
to me actually. However, I think this would be the workaround for
this bug, would it not? Furthermore, support requested that the
server should provide some mechanism to avoid flooding error log
files.
D2. Rate limit messages - This might kind of work if not limiting per
session/user.
If limiting per user/session, and in a web scenario where lots of
short lived sessions are going on, with lots of different users,
taking such approach would not solve the initial problem, as the
log would be full of warnings anyway.
On the other hand, if not limiting per user/session, then the
problem may be masked, but it would still be there. For instance,
some applications force usage of 'limit 1', thence the log would
probably still grow at a smaller but impracticable rate in the
long run)... Thus, as I see it, this could be an option if
parameters for controlling rate of printouts were provided by the
server. This way, the DBA would be able to configure and fine
tune rate limit according to their workload.
Regarding Sergei's suggestion:
S1. log if log_warnings > 0 - "Unsafe statement" is a serious
replication warning, as such (and as it is today) it is printed
even without checking log_warnings value. Also, the default value
for log_warnings is 1, meaning that if considering this option
and setting warning printout to only happen when log_warnings > 2
or even 3, we would be implicitly decreasing its level/severity
(I am not very keen on decreasing it).
Furthermore, log_warnings actually controls whether to output or
not, sets of messages (per level/severity). So if decreasing
severity for unsafe warnings, and the DBA wanted the output of
unsafe warnings, then he would have to set log_warnings=2(3?)
which would cause output of warnings he did not necessarily want
or need.
Just a side note. I agree that, if decreasing severity is an
option, the fix for this bug using log_warning is "dead simple",
but with the aforementioned side-effects.
So, IIUIC:
- D1 is a workaround, not a fix.
- D2 and S1 could help in reducing log flooding. D2, might require
some parameters to be provided by the server (like period and
number of messages). S1, might require decreasing warning severity.
- Neither, D2 or S1 provide the ability to selectively and completely
suppress specific warnings from error log.
Regards,
Luís
On Sun, 2009-07-19 at 16:08 +0200, Sergei Golubchik wrote:
> Hi, Davi!
>
> On Jul 19, Davi Arnaut wrote:
> > On 7/18/09 1:52 PM, Luis Soares wrote:
> >> 3024 Luis Soares 2009-07-18
> >> BUG#42851: Spurious "Statement is not safe to log in statement
> >> format." warnings
> >>
> >> Several warnings are printed when using statement based logging
> >> and unsafe operations are logged to the binlog. For example, this
> >> is the case for statements using LIMIT + ORDER BY PK. As a
> >> consequence, this would rapidly increase mysqld error log size,
> >> in some cases to several gigabytes, causing a maintenance
> >> nightmare.
> >>
> >> This patch proposes a mechanism to selectively and voluntarily
> >> suppress warnings from mysqld error log. It adds a dynamic server
> >> variable "suppress_log_warnings", which can be used to set/reset
> >> warning filtering from mysqld error log. Its details are the
> >> following:
> >
> > [..]
> >
> > Excuse my ignore, what part of this patch can't be done by piping the
> > error log through a filter script? AFAIR, its possible to have a pipe
> > as a logging target.
> >
> > Also, wouldn't it be much simpler to just rate limit messages?
>
> And we have --log-warnings=N switch already.
>
> The simplest solution would be to log this "Statement is not safe to log
> in statement format." only when N>0, for example.
>
> Regards / Mit vielen Grüßen,
> Sergei
>
--
Luís
