3

Running MariaDB v10.9.3 on Arch Linux. I've hosted a file storage cloud with it for years. Recently decided to create a database for another project, and am very new to MySQL interactions, so it's possible I messed something up while doing so. Every time I try to start the mariadb.service, I get the following log:

23:30:11 0 [Note] /usr/bin/mariadbd (server 10.9.3-MariaDB) starting as process 23038 ...
23:30:11 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
23:30:11 0 [Note] InnoDB: Number of transaction pools: 1
23:30:11 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
23:30:11 0 [Note] InnoDB: Using Linux native AIO
23:30:11 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
23:30:11 0 [Note] InnoDB: Completed initialization of buffer pool
23:30:12 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)
23:30:12 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT(988439302) at 988439302
23:30:12 0 [ERROR] InnoDB: Log scan aborted at LSN 988439302
23:30:12 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
23:30:12 0 [Note] InnoDB: Starting shutdown...
23:30:12 0 [ERROR] Plugin 'InnoDB' init function returned error.
23:30:12 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
23:30:12 0 [ERROR] Unknown/unsupported storage engine: InnoDB
23:30:12 0 [ERROR] Aborting

I've done a lot of research at this point and I can't find anything on the subject. The closest I've found is this JIRA page: https://jira.mariadb.org/browse/MDEV-28976

As far as I can tell it's a different problem with the same message. Following the example used in that report I attempted the following command using the hex value of my own error (988439302 -> 0x3AEA6306):

# od -Ax -t x1 -t a -j 0x3AEA6306 ib_logfile0
od: cannot skip past end of combined input

Combining that with what I've read here, my best guess is that the LSN checkpoint is set to a value outside the redo log? This is outside the scope of my knowledge and I'm not really sure where to go from here. Can anyone offer any insight as to what the issue may be, or what I should look into next?

Improve this question

3 Answers 3

Reset to default
7

My solution (which was not an elegant one) ended up being:

  • set innodb_force_recovery=6 in the configuration files
  • start MariaDB again
  • force dump all databases with mysqldump -f --all-databases > all.sql
  • stop MariaDB
  • delete the entire contents of /var/lib/mysql EXCEPT for /var/lib/mysql/mysql
  • removing innodb_force_recovery=6 from the configuration files
  • force importing all databases back to MariaDB with mysql < all.sql -f

Some databases had experienced corruption, but fortunately the most important one was salvaged perfectly. As this was more of an emergency workaround instead of an actual answer to what the problem actually was, I will not accept this answer and encourage anyone with more insight into the issue to share your wisdom.

Improve this answer
3
  • You are probably right in this is the best outcome possible. The causes of MDEV-28976 are multiple MariaDB instances running on the same set of active data. There's no co-ordination between them and hence bad things like a crash recovery why the other process has the table open. MDEV-28495 re-adds protection against multiple process on a datadir to 10.3.36, 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2, 10.10.1 and later versions.
    – danblack
    Commented Oct 14, 2022 at 5:45
  • This fixed it for me!
    – Helvio
    Commented Feb 28, 2023 at 2:41
  • Same fix for macOS Sonoma, except for the directory being /opt/homebrew/var/mysql instead of /var/lib/mysql, and a small variant: in some cases innodb_force_recovery=6 works but innodb_force_recovery=1 doesn't and in some cases it's the opposite. Also, your Homebrew directory may differ. Since June 2024, I've had to do this on every single MariaDB upgrade by Homebrew.
    – FGM
    Commented Aug 25 at 7:39
0

Something you can try, especially if you don't have any valuable data in the database:

  1. Make a backup copy of the data directory (retain the file ownerships and permissions), just in case.
  2. Set innodb_force_recovery=6 in the [mysqld] section of your MariaDB server .cnf files. You can also read the documentation about InnoDB Recovery Modes.
  3. Attempt starting MariaDB server again.
  4. Log in to mariadb as root
  5. Execute SET GLOBAL innodb_log_file_size=4096*1024; to set the minimal size for the file.
  6. Remove the innodb_force_recovery setting again.
  7. Restart the MariaDB Server.

I have not tested any of the above anywhere.

My answer was inspired by a quick read of the discussion in ticket MDEV-27199, so you may want to read that yourself.

Improve this answer
1
  • Unfortunately this did not work, as MySQL is set to read-only mode when innodb_force_recovery=6 is set. It was a great thought, though!
    – Kal
    Commented Oct 14, 2022 at 4:48
-1

Mariadb wasn't started.

Attempts to restart failed with this showing up in /var/log/mysql.err

2023-02-01 13:33:50 0 [Warning] You need to use --log-bin to make --binlog-format work.
2023-02-01 13:33:50 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-02-01 13:33:50 0 [Note] InnoDB: Number of pools: 1
2023-02-01 13:33:50 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2023-02-01 13:33:50 0 [Note] InnoDB: Initializing buffer pool, total size = 268435456, chunk size = 134217728
2023-02-01 13:33:50 0 [Note] InnoDB: Completed initialization of buffer pool
2023-02-01 13:33:50 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT at 20181461 between the checkpoint 20181461 and the end 20181449.
2023-02-01 13:33:50 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2023-02-01 13:33:50 0 [Note] InnoDB: Starting shutdown...
2023-02-01 13:33:50 0 [ERROR] Plugin 'InnoDB' init function returned error.
2023-02-01 13:33:50 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2023-02-01 13:33:50 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-02-01 13:33:50 0 [Warning] 'innodb-large-prefix' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2023-02-01 13:33:50 0 [Warning] 'innodb-file-format' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2023-02-01 13:33:50 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2023-02-01 13:33:50 0 [ERROR] Aborting

I deleted mv ib_logfile0 and mariadb restarted normally

However I found my sites showed corruption issues from db

I then deleted all tables within sm_portal as a test and attempted to reimport them from a backup.

This failed with error ERROR 1005 (HY000) at line 25: Can't create table sm_portal.cm_def_media_ext (errno: 184 "Tablespace already exists")

Searching for tablespace already exists it recommends to delete orphan tables from the CLI of the database ie;

Running: ls /var/lib/mysql/sm_portal/ -l Showed many tables still present even through phpmyadmin showed them gone.

-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 cm_def_media_ext.ibd
-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 cm_def_media_properties.ibd
-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 cm_def_media_types.ibd
-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 cm_def_site_models.ibd
-rw-rw---- 1 mysql mysql     65 Jan 29 19:06 db.opt
-rw-rw---- 1 mysql mysql  98304 Jan 29 19:37 sm_cm_children.ibd
-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 sm_cm_contacts.ibd
-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 sm_cm_fb_posts.ibd
-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 sm_cm_media_galleries.ibd
-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 sm_cm_media.ibd
-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 sm_cm_media_properties.ibd
-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 sm_cm_media_symlinks.ibd
-rw-rw---- 1 mysql mysql 131072 Jan 29 19:37 sm_cm_options.ibd
-rw-rw---- 1 mysql mysql  65536 Jan 29 19:37 sm_cm_parents.ibd
-rw-rw---- 1 mysql mysql  98304 Jan 29 19:37 sm_commentmeta.ibd
-rw-rw---- 1 mysql mysql 147456 Jan 29 19:37 sm_comments.ibd
-rw-rw---- 1 mysql mysql  81920 Jan 29 19:37 sm_links.ibd
-rw-rw---- 1 mysql mysql 212992 Feb  1 13:40 sm_options.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  1 13:40 sm_postmeta.ibd
-rw-rw---- 1 mysql mysql 212992 Feb  1 13:40 sm_posts.ibd
-rw-rw---- 1 mysql mysql  98304 Jan 29 19:37 sm_termmeta.ibd
-rw-rw---- 1 mysql mysql  81920 Jan 29 19:37 sm_term_relationships.ibd
-rw-rw---- 1 mysql mysql  98304 Jan 29 19:37 sm_terms.ibd
-rw-rw---- 1 mysql mysql  98304 Jan 29 19:37 sm_term_taxonomy.ibd
-rw-rw---- 1 mysql mysql  98304 Jan 29 19:37 sm_usermeta.ibd
-rw-rw---- 1 mysql mysql 114688 Jan 29 19:37 sm_users.ibd

I noticed there is also a db.opt file. Not sure if I can delete that. ChatGPT says: A db.opts file is a configuration file used by some database management systems. It typically contains settings and options that are used to configure the behavior of the database, such as the location of data files, the size of memory buffers, and other performance-related parameters. The exact contents and format of the db.opts file can vary depending on the specific database system that is being used.

I left db.opt file there but deleted all *.ibd files

Ran the command: zcat mysavedexportfile.sql.gz | mysql -u 'root' -pDBpassword... targetdatabase

Worked perfectly and survived a service mariadb restart :-)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.