Welcome to Centmin Mod Community
Register Now

MariaDB Cannot set default-storage-engine to InnoDb

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by Milano, Oct 1, 2014.

  1. Milano

    Milano New Member

    15
    3
    3
    Oct 1, 2014
    Ratings:
    +3
    Local Time:
    12:24 PM
    Getting started with centmin mod for the first time on Digital Ocean CentOS 6.5x64

    I edited my.cnf file, comment skip-innodb line, change default-storage-engine = InnoDB then restart mysql and get this error:


    Code:
    141001 08:47:38 mysqld_safe mysqld from pid file /var/lib/mysql/xxx.pid ended
    141001 08:52:52 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    141001  8:52:52 InnoDB: The InnoDB memory heap is disabled
    141001  8:52:52 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    141001  8:52:52 InnoDB: Compressed tables use zlib 1.2.3
    141001  8:52:52 InnoDB: Using Linux native AIO
    141001  8:52:52 InnoDB: Initializing buffer pool, size = 48.0M
    141001  8:52:52 InnoDB: Completed initialization of buffer pool
    InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
    InnoDB: than specified in the .cnf file 0 67108864 bytes!
    InnoDB: Possible causes for this error:
    (a) Incorrect log file is used or log file size is changed
    (b) In case default size is used this log file is from 10.0
    (c) Log file is corrupted or there was not enough disk space
    In case (b) you need to set innodb_log_file_size = 48M
    141001  8:52:52 [ERROR] Plugin 'InnoDB' init function returned error.
    141001  8:52:52 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    141001  8:52:52 [Note] Plugin 'FEDERATED' is disabled.
    141001  8:52:52 [Note] Plugin 'ARCHIVE' is disabled.
    141001  8:52:52 [Note] Plugin 'FEEDBACK' is disabled.
    141001  8:52:52 [ERROR] Unknown/unsupported storage engine: InnoDB
    141001  8:52:52 [ERROR] Aborting
    
    Please help me, i'm newbie :(
     
  2. eva2000

    eva2000 Administrator Staff Member

    53,537
    12,134
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,677
    Local Time:
    3:24 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    did you change the innod_log_file_size too ? the error and solution are mentioned in the error log you posted for MySQL

    48M is default innodb_log_file_size set by Centmin Mod, if you need to change it read MySQL :: MySQL 5.5 Reference Manual :: 14.8.4 Changing the Number or Size of InnoDB Log Files and Resizing the InnoDB Tablespace
     
  3. Milano

    Milano New Member

    15
    3
    3
    Oct 1, 2014
    Ratings:
    +3
    Local Time:
    12:24 PM
    Thanks for your help but i'm not change anything, for whatever reason Centmin Mod set it to 68MB by default, i changed it back to 48MB but it didn't solve the problem
     
  4. eva2000

    eva2000 Administrator Staff Member

    53,537
    12,134
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,677
    Local Time:
    3:24 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    try setting it to 64M
     
  5. Milano

    Milano New Member

    15
    3
    3
    Oct 1, 2014
    Ratings:
    +3
    Local Time:
    12:24 PM
    Same error, here is my my.cnf

    Code:
    [mysqld]
    character-set-server=utf8
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    #bind-address=127.0.0.1
    # optimized my.cnf for MariaDB 5.5.x
    # by eva2000
    # vbtechsupport.com
    
    tmpdir=/home/mysqltmp
    
    #skip-innodb
    skip-federated
    #skip-pbxt
    #skip-pbxt_statistics
    skip-archive
    #skip-name-resolve
    #old_passwords
    back_log = 75
    max_connections = 300
    key_buffer_size = 32M
    myisam_sort_buffer_size = 32M
    myisam_max_sort_file_size = 2048M
    join_buffer_size = 64K
    read_buffer_size = 64K
    sort_buffer_size = 128K
    table_definition_cache = 4096
    table_open_cache = 2048
    thread_cache_size = 64
    wait_timeout = 120
    connect_timeout = 10
    tmp_table_size = 32M
    max_heap_table_size = 32M
    max_allowed_packet = 32M
    max_seeks_for_key = 1000
    group_concat_max_len = 1024
    max_length_for_sort_data = 1024
    net_buffer_length = 16384
    max_connect_errors = 100000
    concurrent_insert = 2
    read_rnd_buffer_size = 256K
    bulk_insert_buffer_size = 8M
    query_cache_limit = 512K
    query_cache_size = 16M
    query_cache_type = 1
    query_cache_min_res_unit = 2K
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    default-storage-engine=InnoDB
    
    log_warnings=1
    slow_query_log=0
    long_query_time=1
    slow_query_log_file=/var/lib/mysql/slowq.log
    log-error=/var/log/mysqld.log
    
    # innodb settings
    #innodb_extra_rsegments = 4
    innodb_purge_threads=1
    innodb_doublewrite = 1
    
    innodb_file_per_table = 1
    innodb_open_files = 1000
    innodb_data_file_path= ibdata1:10M:autoextend
    innodb_buffer_pool_size = 48M
    innodb_additional_mem_pool_size = 32M
    
    innodb_log_files_in_group = 2
    innodb_log_file_size = 64M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 2
    innodb_thread_concurrency = 0
    innodb_lock_wait_timeout=50
    #innodb_flush_method = O_DIRECT
    innodb_support_xa=1
    
    # 200 * # DISKS
    innodb_io_capacity = 100
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    
    # mariadb settings
    [mariadb]
    #thread-handling = pool-of-threads
    #thread-pool-size= 20
    #mysql --port=3307 --protocol=tcp
    #extra-port=3307
    #extra-max-connections=1
    
    userstat = 0
    key_cache_segments = 1
    aria_group_commit = none
    aria_group_commit_interval = 0
    aria_log_file_size = 32M
    aria_log_purge_type = immediate
    aria_pagecache_buffer_size = 8M
    aria_sort_buffer_size = 8M
    
    [mariadb-5.5]
    #ignore_db_dirs=
    query_cache_strip_comments=0
    
    innodb_read_ahead = linear
    innodb_adaptive_flushing_method = estimate
    innodb_flush_neighbor_pages = 1
    innodb_stats_update_need_lock = 0
    innodb_log_block_size = 512
    
    log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
    
    [mysqld_safe]
    socket=/var/lib/mysql/mysql.sock
    log-error=/var/log/mysqld.log
    #nice = -5
    open-files-limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 32M
    
    [myisamchk]
    key_buffer = 32M
    sort_buffer = 16M
    read_buffer = 16M
    write_buffer = 16M
    
    [mysqlhotcopy]
    interactive-timeout
    I have to change it back to MyISAM and this is the result from
    Code:
    show engines
    phpMyAdmin 4.2.9.png
     
  6. eva2000

    eva2000 Administrator Staff Member

    53,537
    12,134
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,677
    Local Time:
    3:24 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    what is current size of ib_logfile0/1 ?

    you can find it via command
    Code:
    ls -lah /var/lib/mysql | grep ib_logfile
    so you tried in /etc/my.cnf either
    Code:
    innodb_buffer_pool_size = 48M
    or 64M (not what you posted 68M but 64M)
    Code:
    innodb_buffer_pool_size = 64M
    and both don't work on mysql restart ?
     
  7. Milano

    Milano New Member

    15
    3
    3
    Oct 1, 2014
    Ratings:
    +3
    Local Time:
    12:24 PM
    Untitled.png

    Yes, both of them don't work
     
  8. eva2000

    eva2000 Administrator Staff Member

    53,537
    12,134
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,677
    Local Time:
    3:24 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Ah i re-read the error log and see the problem for some reason innodb log file was created using default 5MB first
    so what you need to do is shutdown MySQL server and move the old logs out first
    Code:
    service mysql stop
    mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
    mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
    service mysql start
    
    if all works, then remove the moved files
    Code:
    rm /var/lib/mysql/ib_logfile0.bak
    rm /var/lib/mysql/ib_logfile1.bak
     
  9. Milano

    Milano New Member

    15
    3
    3
    Oct 1, 2014
    Ratings:
    +3
    Local Time:
    12:24 PM
    It's working now, thank you so much!
     
    Last edited: Oct 2, 2014
  10. eva2000

    eva2000 Administrator Staff Member

    53,537
    12,134
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,677
    Local Time:
    3:24 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    You're welcome :)
     
  11. Stephen Dennis

    Stephen Dennis New Member

    6
    2
    3
    Feb 21, 2018
    Ratings:
    +2
    Local Time:
    12:24 AM
    1.13.8
    10.1.30
    Thanks, eva2000, still works in 2019
     
  12. eva2000

    eva2000 Administrator Staff Member

    53,537
    12,134
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,677
    Local Time:
    3:24 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Cheers :)