Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33838

Data still present in backup / on disk after table delete or/and import from sql

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.5.24, 10.11.7
    • None
    • Storage Engine - InnoDB
    • None
    • Debian 12, Debian 11

    Description

      Most probably it's a setting I am missing, but I cannot find any answer nowhere.

      Everything started with some unusual messages in mariabackup log like:
      [00] 2024-04-05 02:48:02 mariabackup: Generating a list of tablespaces
      [00] 2024-04-05 02:48:02 DDL tracking : create 241 "./racktables/innodb_test.ibd"
      [00] 2024-04-05 02:48:02 DDL tracking : delete 241 "./racktables/innodb_test.ibd"
      [00] 2024-04-05 02:48:02 DDL tracking : create 242 "./racktables/innodb_test.ibd"
      [00] 2024-04-05 02:48:02 DDL tracking : delete 242 "./racktables/innodb_test.ibd"

      those are probably some temp tables created by racktables web app

      but also, on a simple and usual mysql racktables < racktables.sql (so a simple import with drop/create table) the backup log on next day there were full of that kind of messages, and also the space on disk was doubled, also backup space; it's like those tables we're "kept", even they we're dropped

      after restarting mysql everything was clean, next day backup was as usual

      I'm using innodb_file_per_table = ON

      even migrations from a php framework cause the same behavior, all DDL operations remain "somewhere"

      mariadb versions:
      10.11.6-0+deb12u1
      10.5.23-0+deb11u1

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30000 make mariadb-backup to force an innodb checkpoint

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            What is the start LSN of your backups? It seems possible to me that the log checkpoint was not advanced between the backup runs. In MDEV-30000 there are some suggestions how to make a checkpoint happen.

            marko Marko Mäkelä added a comment - What is the start LSN of your backups? It seems possible to me that the log checkpoint was not advanced between the backup runs. In MDEV-30000 there are some suggestions how to make a checkpoint happen.
            alex.cernat Alex Cernat added a comment -

            Thank you very much, it seems this should be the problem. There was not so much activity on that server(s), so probably that's the reason for not triggering a log checkpoint. And that behaviour was introduced in 10.5.7, so it's consistent with tested versions (10.3 from debian 10 was ok).

            I've read MDEV-30000, but found no simple (non-hacky) solution from the user point of view. And as far as I've read, innodb_log_checkpoint_now is present only in debug builds.

            There is something like an SQL command to simply invoke the making of a log checkpoint ?

            [00] 2024-04-03 02:48:09 mariabackup: The latest check point (for incremental): '28324444'
            [00] 2024-04-03 02:48:09 Redo log (from LSN 28324444 to 28566163) was copied.

            [00] 2024-04-04 02:48:08 mariabackup: The latest check point (for incremental): '28324444'
            [00] 2024-04-04 02:48:09 Redo log (from LSN 28324444 to 28635832) was copied.

            [00] 2024-04-05 02:48:09 mariabackup: The latest check point (for incremental): '28324444'
            [00] 2024-04-05 02:48:10 Redo log (from LSN 28324444 to 28636911) was copied.

            alex.cernat Alex Cernat added a comment - Thank you very much, it seems this should be the problem. There was not so much activity on that server(s), so probably that's the reason for not triggering a log checkpoint. And that behaviour was introduced in 10.5.7, so it's consistent with tested versions (10.3 from debian 10 was ok). I've read MDEV-30000 , but found no simple (non-hacky) solution from the user point of view. And as far as I've read, innodb_log_checkpoint_now is present only in debug builds. There is something like an SQL command to simply invoke the making of a log checkpoint ? [00] 2024-04-03 02:48:09 mariabackup: The latest check point (for incremental): '28324444' [00] 2024-04-03 02:48:09 Redo log (from LSN 28324444 to 28566163) was copied. [00] 2024-04-04 02:48:08 mariabackup: The latest check point (for incremental): '28324444' [00] 2024-04-04 02:48:09 Redo log (from LSN 28324444 to 28635832) was copied. [00] 2024-04-05 02:48:09 mariabackup: The latest check point (for incremental): '28324444' [00] 2024-04-05 02:48:10 Redo log (from LSN 28324444 to 28636911) was copied.

            People

              Unassigned Unassigned
              alex.cernat Alex Cernat
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.