MariaDB, innoDB storage engine problem

[bogdan.sh]

Ah that's awesome!
Thanks for getting back and sharing the root cause.

[ordinal]

So starting with MariaDB 10.8.1 MariaDB will no longer recreate ib_logfile0 if it gets deleted.

https://jira.mariadb.org/browse/MDEV-27199

The plugin I was using for nginx, Engitron, has a panel to edit my.cnf. Once I edit the cnf from there it deletes ib_logfile.

I opened an issue in their github repository and they confirmed and fixed it.

I fixed the issue by deleting ibdata1 but that will cause all innoDB tables to become corrupt, so anyone reading this in the future make sure you backup your database.

[bogdan.sh]

It should not be like that.
Unfortunately I have no answer yet and would need some time to reproduce and if confirmed will consult with our developers. I will keep you posted.

[ordinal]

Hi,

Thanks for your reply.

New Almalinux servers.
Used the Cloudlinux installation script.
Setup Cloudlinux from cpanel.
Added default-storage-engine=MyISAM to my.conf to get the service up because I had a very narrow migration window.
Used the cpanel transfer tool to move the accounts over to the new server.

Old server was on MariaDB 10.3

[bogdan.sh]

Hi, sorry to hear that, but have to ask a few questions.

Can you please outline the steps you do to move the servers? Do you use cPanel migration tools or it's manual process? What are the steps in regards of MySQL? What was the MySQL version on old server?

[ordinal]

Im in the process of moving my old servers from Centos to Cloudlinux.
The 2 I moved have the exact same issue. Both are also running Cpanel.

There is a problem initializing the innoDB engine.

2024-09-28  3:31:29 0 [Note] Starting MariaDB 10.11.9-MariaDB-cll-lve source revision 0e8fb977b00983d98c4c35e39bc1f36463095938 server_uid Xo1QLnX7qBlZ++M8j3saOcqsavk= as process 2088623
2024-09-28  3:31:29 0 [Note] InnoDB: !!! innodb_force_recovery is set to 1 !!!
2024-09-28  3:31:29 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-09-28  3:31:29 0 [Note] InnoDB: Number of transaction pools: 1
2024-09-28  3:31:29 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-09-28  3:31:29 0 [Note] InnoDB: Using Linux native AIO
2024-09-28  3:31:29 0 [Note] InnoDB: Initializing buffer pool, total size = 20.000GiB, chunk size = 320.000MiB
2024-09-28  3:31:30 0 [Note] InnoDB: Completed initialization of buffer pool
2024-09-28  3:31:30 0 [ERROR] InnoDB: File ./ib_logfile0 was not found
2024-09-28  3:31:30 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2024-09-28  3:31:30 0 [Note] InnoDB: Starting shutdown...
2024-09-28  3:31:30 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2024-09-28  3:31:30 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-09-28  3:31:30 0 [Warning] 'innodb-buffer-pool-instances' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-09-28  3:31:30 0 [Warning] 'innodb-log-files-in-group' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-09-28  3:31:30 0 [Note] Server socket created on IP: '127.0.0.1'.
2024-09-28  3:31:30 1 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1286: Unknown storage engine 'InnoDB'
2024-09-28  3:31:30 0 [Note] /usr/sbin/mariadbd: ready for connections.
Version: '10.11.9-MariaDB-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
2024-09-28  3:31:32 3 [Warning] Access denied for user 'Cpanel::MysqlUtils::Unprivileged'@'localhost' (using password: NO)
2024-09-28  3:32:14 27 [Warning] Access denied for user 'Cpanel::MysqlUtils::Unprivileged'@'localhost' (using password: NO)
2024-09-28  3:32:16 30 [Warning] Access denied for user 'root'@'localhost' (using password: NO)​

This is the my.cnf

[mysql]
port                            = 3306
socket                          = /var/lib/mysql/mysql.sock


[mysqld]
# Required Settings
sql_mode=NO_ENGINE_SUBSTITUTION
basedir                         = /usr
bind_address                    = 127.0.0.1 # Change to 0.0.0.0 to allow remote servers to connect to this server's
                                            # database instance
datadir                         = /var/lib/mysql
max_allowed_packet              = 256M
max_connect_errors              = 1000000
pid_file                        = /var/lib/mysql/mysql.pid
port                            = 3306
skip_external_locking
socket                          = /var/lib/mysql/mysql.sock
tmpdir                          = /var/tmp
user                            = mysql

# InnoDB Settings
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 20    
innodb_buffer_pool_size         = 20G    
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 128M


# MyISAM Settings
query_cache_limit               = 4M    # UPD
query_cache_size                = 24M   # UPD
query_cache_type                = 1

key_buffer_size                 = 48M   # UPD

low_priority_updates            = 1
concurrent_insert               = 2

performance_schema = OFF

# Connection Settings
max_connections                 = 100   # UPD

back_log                        = 512
thread_cache_size               = 100
thread_stack                    = 192K

interactive_timeout             = 600
wait_timeout                    = 600

# Buffer Settings
join_buffer_size                = 3M    # UPD
read_buffer_size                = 2M    # UPD
read_rnd_buffer_size            = 3M    # UPD
sort_buffer_size                = 3M    # UPD



max_heap_table_size             = 128M
tmp_table_size                  = 128M

# Search Settings
ft_min_word_len                 = 3     # Minimum length of words to be indexed for search results

# Logging
log_error                       = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes   = 1
long_query_time                 = 5
slow_query_log                  = 0     # Disabled for production
slow_query_log_file             = /var/lib/mysql/mysql_slow.log

default-storage-engine=MyISAM
[mysqldump]
# Variable reference
# For MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
# For MariaDB:   https://mariadb.com/kb/en/library/mysqldump/
quick
quote_names
max_allowed_packet = 512M​

The default-storage-engine=MyISAM, I added to get the server to start.

Any idea on how to fix this?

Thanks