Welcome to Pandora FMS Community!

Find answers, ask questions, and connect with our community around the world.

Welcome to Pandora FMS Community Forums Community support Problem Upgrading from MySQL 5.7 to MySQL 8.0 – Agents Missing

  • Problem Upgrading from MySQL 5.7 to MySQL 8.0 – Agents Missing

    Posted by aeleus on September 7, 2023 at 16:36

    Now that I am running Pandora 773.x, I’ve been getting a warning about upgrading MySQL from 5.7 to 8.0. I followed the instructions found here: Upgrading from MySQL 5.7 to MySQL 8 [current] (pandorafms.com)

    I am running CentOS 7, so I followed that part of the instructions. All seems to go well. When I restart and browse the console, I see that users, plugins, alert actions, and alert commands are all restored. What is not restored are all of my custom remote agents and associated modules.

    The only errors shown during the database restore are these:

    ERROR 1054 (42S22): Unknown column ‘transactional_agent’ in ‘field list’

    ERROR 1146 (42S02): Table ‘pandora.tfiles_repo’ doesn’t exist

    ERROR 1146 (42S02): Table ‘pandora.tfiles_repo_group’ doesn’t exist

    ERROR 1146 (42S02): Table ‘pandora.tnetwork_matrix’ doesn’t exist

    ERROR 1146 (42S02): Table ‘pandora.tmetaconsole_event’ doesn’t exist

    ERROR 1146 (42S02): Table ‘pandora.tmetaconsole_event_history’ doesn’t exist

    Is there an extra step I’m missing?

    Versions:

    Version v7.0NG.773.3 – Community

    MR version MR65

    BuildPC230830

    mysql Ver 14.14 Distrib 5.7.42-46, for Linux (x86_64) using 6.2

    • This discussion was modified 10 months, 2 weeks ago by  aeleus.
    aeleus replied 10 months, 1 week ago 2 Members · 6 Replies
  • 6 Replies
  • vic

    Administrator
    September 7, 2023 at 19:26
    1426 Karma points
    Community awards: bulb Bright ideas
    Community rank: tentacle_master_icon Tentacle Master
    Like it
    Up
    0
    Down
    Drop it
    ::

    Hi Aeleus,

    In the MySQL pre-migration backup, can you check if those tables are there?

    It seems that those tables have not been created in the new DB.

    If you make a pre backup of each affected table and insert it into the new DB with the MySQL source command, do you get any errors?

    Best regards,

    Víc.

  • aeleus

    Member
    September 11, 2023 at 22:38
    511 Karma points
    Community rank: tentacle-rookie Tentacle rookie
    Like it
    Up
    0
    Down
    Drop it
    ::

    Hi Vic,

    I followed the directions and used this command to backup the db before upgrading:

    mysqldump -u root -p pandora –skip-add-drop-table –complete-insert –no-create-info> backup_pandora.sql

    Searching backup_pandora.sql shows that all of my agents and modules are included in the dump. They are not, however, restored to the database.

  • aeleus

    Member
    September 11, 2023 at 22:39
    511 Karma points
    Community rank: tentacle-rookie Tentacle rookie
    Like it
    Up
    0
    Down
    Drop it
    ::

    [I’m posting in short segments because the forum won’t work otherwise]

    This is the sequence of commands I use to restore based on the documentation:

    mysql -u root -p pandora

    drop database pandora;

    [takes a minute]

    [Create a new pandora database]

    create database pandora;

  • aeleus

    Member
    September 11, 2023 at 22:40
    511 Karma points
    Community rank: tentacle-rookie Tentacle rookie
    Like it
    Up
    0
    Down
    Drop it
    ::

    [Open the pandora database and import the schema found in the PFMS Web Console]

    use pandora;

    source /var/www/html/pandora_console/pandoradb.sql;

    [takes a while]

    [Import the data that special MySQL 8 compatible backup]

    source backup_pandora.sql;

    [takes a while]

  • aeleus

    Member
    September 11, 2023 at 22:45
    511 Karma points
    Community rank: tentacle-rookie Tentacle rookie
    Like it
    Up
    0
    Down
    Drop it
    ::

    [Forum wouldn’t let me post the last few lines, but they came right from the documentation]

    CREATE USER …

    ALTER USER …

    GRANT ALL PRIVILEGES …

    flush privileges …

    exit and reboot

  • aeleus

    Member
    September 11, 2023 at 22:46
    511 Karma points
    Community rank: tentacle-rookie Tentacle rookie
    Like it
    Up
    0
    Down
    Drop it
    ::

    Vic,

    Can you explain what you mean by, “make a pre backup of each affected table and insert it into the new DB with the MySQL source command”? Isn’t that what I’m doing with the mysqldump command?