MySQL Bugs: #106883: during upgrade from MySQL Cluster 7.6 to 8.0.28 all triggers are gone

Bug #106883	during upgrade from MySQL Cluster 7.6 to 8.0.28 all triggers are gone
Submitted:	1 Apr 2022 6:21	Modified:	10 May 2022 11:35
Reporter:	Hendrik Woltersdorf	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We upgraded some of our mySQL Clusters from 7.6.20 to 8.0.28 inplace.
After the upgrade all trigger were gone. Tables and stored procedures survived the upgrade.
The server log shows no messages about triggers.

How to repeat:
Upgrade a MySQL Cluster 7.6 with triggers on tables to 8.0(.28).

log file of one sql node

Attachment: dit_l1_cl1.zip (application/x-zip-compressed, text), 1.55 KiB.

Hi,

Triggers are not directly linked with ndbcluster, they are on SQL nodes only. Upgrading ndbcluster is mainly management and data nodes and they do not have data about triggers. Depending on how the SQL nodes were upgraded this might not be a bug.

How did you exactly perform the upgrade from 7.6.20 to 8.0.28 ?

Thanks

We use the "Linux Generic" packages.
how do we update:
- copy the new software package to the machine(s) and unpack
- stop the server(s) (using a systemd service unit)
- change the symbolic link "/opt/mysql" to point to the new software version
- start the server(s)

Hi,

So to upgrade one node you unpack the tgz binaries and relink, great. But what is more important is the procedure how do you do the actual upgrade "one level up".

Do you 
 - shutdown cluster (all data, mgm, sql nodes)
 - replace all binaries
 - start all nodes

or you
 - upgrade nodes one by one

or you do something else?

Also, is this the same thing you do with SQL nodes? Just relink the binaries and start them on top of old datadir? 

Please confirm the order of operations as it is important. Doing a rolling upgrade (best way to upgrade imho) I was not able to rerpoduce this issue.

Thanks

Actually we did it both ways:
- one cluster "rolling", first the management nodes, then the data nodes, sql nodes last; all one by one.
- one cluster completely down, starting order of the node types as above after changing the software.

All upgrades were done "inplace". That means, we started the new binaries against the old data.

Our triggers are created this way:

delimiter //

CREATE DEFINER=CURRENT_USER trigger TR_CALC_FEE_AFTER_INSERT AFTER INSERT on CALC_FEE
FOR EACH ROW
BEGIN
   declare $ID_Q2X_HEAD bigint;
/* more code ... */
END;
//

Hi,

Thanks for the update. Really weird. I cannot reproduce this, will have to analyze more. The triggers are on the SQL node, if you did not clean the datadir on the SQL node and just replaced the binaries the triggers should be there, I do not see how triggers could vanish on them own... I just did 2 upgrades and triggers are all still there.

Are you 100% sure you had triggers on all the SQL nodes before you started the upgrade? Often users "lose" triggers because they create them only on one SQL node and expect them to exist on other SQL nodes. Did you check all SQL nodes, are they gone on all of them? Upgrading SQL node in-place should work like any other mysqld server, triggers should not be touched.

I will do more analysis on this issue but I need to find a way to reproduce this in order for us to continue working on solving the problem

Thanks

The triggers existed an all sql nodes and were gone on all of them.
Before I upgraded the second cluster, I reinstalled all triggers, just to make sure, they exist.

Hi,

great, thanks for confirmation

Hi,

Thanks for this report! I reproduced the issue.

Documented fix as follows in the NDB 8.0.30 changelog:

    NDB tables are skipped in the MySQL Server upgrade phase and are
    instead migrated by the ndbcluster plugin at a later stage. As a
    result, triggers associated with NDB tables were not created
    during upgrades from 5.7 based versions.

    This occurred because it is not possible to create such triggers
    when the NDB tables are migrated by the ndbcluster plugin, since
    metadata about the triggers is lost in the upgrade finalization
    phase of the MySQL Server upgrade in which all .TRG files are
    deleted.

    To fix this issue, we make the following changes:

        ·Migration of NDB tables with triggers is no longer deferred
        during the Server upgrade phase.

        ·NDB tables with triggers are no longer removed from the data
        dictionary during setup even when initial system starts are
        detected.

Closed.