Hello Martin!
* Martin Hansson <Martin.Hansson@stripped> [09/08/18 13:34]:
> #At file:///data0/martin/bzr/bug45235/5.1bt-gca-commit/ based on
> revid:davi.arnaut@stripped
>
> 2936 Martin Hansson 2009-08-18
> Bug#45235: 5.1 does not support 5.0-only syntax triggers in any way
>
> Execution of DROP TRIGGER expected the .TRG file to be fully parsed before
> deleting the trigger files. This is not always possible, however, as the
> trigger files may have been created with an older verion of MySQL and
> contain deprecated syntax. Fixed by making DROP TRIGGER silently delete
> trigger files if .TRG file is not parseable.
I see two problems with such approach:
- In cases when there are several triggers on the table, of which
only one contains unsupported syntax, all triggers are dropped.
Quite probably this is not what user expects when he issues
DROP TRIGGER statement. Especially, since this happens silently,
so he has an illusion that operation was performed as usually.
Also, I think that forcing user to re-create all triggers on
table during upgrade even although only one of them needs any
modifications is not really nice.
- It does not allows to avoid manual system operations, as in order
to keep data-dictionary consistent and fully drop triggers user
still needs to manually remove .TRN files for other triggers which
were present in .TRG file and were also dropped by this DROP TRIGGER
statement (and again we are completely silent about this fact).
So after a bit of thinking and discussing this problem with Kostja
I came up with the following proposal which tries to solve these
issues:
We can use the fact that in order to properly drop the particular
trigger we don't really need its body to be fully parsed. It is
enough for us to know the name of the trigger so we can find out
what trigger definition should be removed from .TRG file and
which .TRN file should be dropped.
Since trigger name is stored at the beginning of trigger definition
it is quite probable that we will be able to successfully parse it
and later extract from LEX::spname member even if parsing of trigger
definition as whole has failed (and if it is not possible then it is
likely that .TRG file was corrupted so we are better to ask user to
drop triggers by using manual system operations).
And knowing correct trigger name we can produce Table_triggers_list
object (particularly properly fill names_list member in it)
which won't be suitable for trigger execution but which still can
be used for performing DROP TRIGGER operation in usual fashion
(i.e. dropping only specified trigger and removing .TRN file).
Thus by analyzing results of failed parsing of trigger definition
and trying to produce usable Table_triggers_list even in this case
(i.e. when we fail to parse trigger body but got its name) we can
solve problem described in the bug report without hitting two
issues mentioned at the begging of this e-mail.
What do you think about this proposal?
--
Dmitry Lenev, Software Developer
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification
