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

FATAL ERROR: <time> Was only able to copy log from <seq_no> to <seq_no>, not <seq_no>; try increasing innodb_log_file_size

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.11.6
    • N/A
    • mariabackup
    • None

    Description

      Hello,

      When running backup during ongoing ALTER operation backup fails with error:

      [00] FATAL ERROR: 2024-02-02 10:16:28 Was only able to copy log from 2825766826 to 2892744515, not 3030682034; try increasing innodb_log_file_size
      

      Attached full log.

      It is easy to reproduce issue. In my test case I used all the defaults running simple rebuild on a table.

      +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table     | Create Table                                                                                                                                                                                                         |
      +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | test_data | CREATE TABLE `test_data` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `data` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=10000015 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
      +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [testdb]> select * from test_data limit 5;
      +----+-----------------------+
      | id | data                  |
      +----+-----------------------+
      |  1 | This is a test string |
      |  2 | This is a test string |
      |  3 | This is a test string |
      |  4 | This is a test string |
      |  5 | This is a test string |
      +----+-----------------------+
      5 rows in set (0.001 sec)
       
       
      MariaDB [testdb]> select count(*) from test_data;
      +----------+
      | count(*) |
      +----------+
      | 10000014 |
      +----------+
      1 row in set (9.947 sec)
      

      ## SESSION 1
      MariaDB [testdb]> alter table test_data engine=innodb;
      Query OK, 0 rows affected (1 min 2.641 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      ## SESSION 2
      mariabackup --backup --target-dir=/backup --user=root --password=root
      

      The same error happens on a server with workload and bigger log file size.

      Received the same error on Percona (5.7.42) with xtrabackup, just error log was slightly different:

      240202 11:19:36 >> log scanned up to (1948476139)
      InnoDB: Last flushed lsn: 1947667398 load_index lsn 1948481497
      InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet.
      PXB will not be able to make a consistent backup. Retry the backup operation
      

      I am not sure if this is a bug.
      If this is expected behavior what actions should we do to avoid backup failures.
      Increase innodb_log_file_size? Change other parameters?

      Thank you

      Attachments

        Issue Links

          Activity

            marko Marko Mäkelä added a comment - - edited

            Yes, this is expected, and SET GLOBAL innodb_log_file_size to a larger value (MDEV-27812) should help. Another thing that you could try is to execute SET GLOBAL innodb_log_file_buffering=ON (MDEV-28766) before starting the backup. In that way, mariadb-backup --backup should have a chance of reading the log data from the operating system’s file system cache and not have to read it from the physical storage.

            The error message was changed, because in MDEV-14425 the log file format was changed and a log block sequence number was shrunk to 1 bit. Previously the log was divided into 512-byte blocks. In the MDEV-14425 format, each mini-transaction is a logical log block on its own.

            I believe that this design issue would be fixed by implementing the log copying inside the server process (MDEV-14992).

            Edit: innodb_log_file_buffering=ON disables the use of O_DIRECT. The O_DIRECT would bypass the operating system’s file system cache. The default is innodb_log_file_buffering=OFF, because bypassing the cache could improve normal write performance.

            Edit: Another thing that could help is to trigger a full InnoDB log checkpoint before initiating backup, so that there will be less log to be copied. MDEV-30000 is a request for mariadb-backup --backup to do that automatically.

            marko Marko Mäkelä added a comment - - edited Yes, this is expected, and SET GLOBAL innodb_log_file_size to a larger value ( MDEV-27812 ) should help. Another thing that you could try is to execute SET GLOBAL innodb_log_file_buffering=ON ( MDEV-28766 ) before starting the backup. In that way, mariadb-backup --backup should have a chance of reading the log data from the operating system’s file system cache and not have to read it from the physical storage. The error message was changed, because in MDEV-14425 the log file format was changed and a log block sequence number was shrunk to 1 bit. Previously the log was divided into 512-byte blocks. In the MDEV-14425 format, each mini-transaction is a logical log block on its own. I believe that this design issue would be fixed by implementing the log copying inside the server process ( MDEV-14992 ). Edit: innodb_log_file_buffering=ON disables the use of O_DIRECT . The O_DIRECT would bypass the operating system’s file system cache. The default is innodb_log_file_buffering=OFF , because bypassing the cache could improve normal write performance. Edit: Another thing that could help is to trigger a full InnoDB log checkpoint before initiating backup, so that there will be less log to be copied. MDEV-30000 is a request for mariadb-backup --backup to do that automatically.
            Sutic Marko Sutic added a comment -

            Thank you Marko for the comment and valuable information.

            "SET GLOBAL innodb_log_file_buffering=OFF" did not solve issue in my case but increasing innodb_log_file_size to 512MB did.

            I will watch implementation for MDEV-14992.

            Sutic Marko Sutic added a comment - Thank you Marko for the comment and valuable information. " SET GLOBAL innodb_log_file_buffering=OFF " did not solve issue in my case but increasing innodb_log_file_size to 512MB did. I will watch implementation for MDEV-14992 .

            User tried

            {{ SET GLOBAL innodb_log_file_buffering=1;}}

            and the issue no longer occurs.

            masonmariadb Mason Sharp (Inactive) added a comment - User tried {{ SET GLOBAL innodb_log_file_buffering=1;}} and the issue no longer occurs.

            People

              marko Marko Mäkelä
              Sutic Marko Sutic
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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