Welcome to Centmin Mod Community
Register Now

MariaDB mysql problems the mysqltuner & tuning-primer

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by seopolis, Sep 12, 2014.

  1. seopolis

    seopolis New Member

    9
    0
    1
    Aug 23, 2014
    Ratings:
    +0
    Local Time:
    10:31 PM
    5.5
    It seems that there is some problem with MySQL db on my server. Here is why.
    I downloaded tuning-primer.sh (to use instead of mysqltuner) and running it, gives this error:
    ....
    /usr/local/bin/tuning-primer.sh: line 1318: *100/: syntax error: operand expected (error token is "*100/")
    I searched for that error and found that link:
    Bug #1157890 “syntax error” : Bugs : MySQL Tuning Primer Script where a user had the same exact problem and after solving it, says that. "Close this, it wasnt a bug, but a innodb issue on the server."

    And here is the $1 million question:
    How do I check and repair my MariaDB install for issues like this one?
     
  2. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    have you edited your /etc/my.cnf at all ? since Centmin Mod install ?

    if you haven't then MariaDB 5.5 would by default have innodb disabled and only innodb enabled for MariaDB 10.x installs for .08 betas

    from google search looks like a bug in tuning-primer that it expects innodb
     
  3. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    @seopolis moved posts to new thread here

    post the contents of your /etc/my.cnf in CODE tags
     
    Last edited: Sep 12, 2014
  4. seopolis

    seopolis New Member

    9
    0
    1
    Aug 23, 2014
    Ratings:
    +0
    Local Time:
    10:31 PM
    5.5
    Here is my.cnf file.
    I run a dedicated server with 32gb ram, where I host 6 wordpress sites, (combined: 6k unique visitors/day).

    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 = 64M
    myisam_sort_buffer_size = 32M
    myisam_max_sort_file_size = 2048M
    join_buffer_size = 128K
    read_buffer_size = 128K
    sort_buffer_size = 256K
    table_definition_cache = 4096
    table_open_cache = 4096
    thread_cache_size = 64
    wait_timeout = 120
    connect_timeout = 10
    tmp_table_size = 128M
    max_heap_table_size = 128M
    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 = 2M
    bulk_insert_buffer_size = 8M
    query_cache_limit = 2M
    query_cache_size = 256M
    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 = MyISAM
    
    log_warnings=1
    slow_query_log=0
    long_query_time=2
    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=1
    
    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
    
     
  5. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    not seeing anything in there that would be out of the ordinary for my.cnf

    most like tuning-primer script problem

    you could try enabling innodb engine by commenting out with # hash in front the line

    Code:
    skip-innodb
    and restart mysql server
     
  6. seopolis

    seopolis New Member

    9
    0
    1
    Aug 23, 2014
    Ratings:
    +0
    Local Time:
    10:31 PM
    5.5
    I tried that and still both mysqltuner and tuning-primer give the same errors..
    If I upgrade to Maria DB 10, is there any chance that this problem will be solved? and if yes how to do the upgrade?
    Thanks.
     
  7. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    unlikely that would fix it, I am using MariaDB 5.5 and mysqltuner works for me
     
  8. Afterward

    Afterward Member

    94
    29
    18
    Jul 20, 2014
    Ratings:
    +29
    Local Time:
    9:31 PM
    1.7.5
    5.5
    I have same exact error and i did not modify my.cnf or anything
    Code:
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.2 mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Thu Sep 18 02:22:19 UTC 2014
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    
    >>  MySQLTuner 1.3.0 mod - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
    >>  Run with '--help' for additional options and output filtering
    [!!] The --forcemem option is required for remote connections
    
    Report Complete:
    Thu Sep 18 02:22:20 UTC 2014
    
    Mariadb version 5.5.
    Centminmod latest stable.
     
  9. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    question are both you using mysqlmymonlite.sh from /centos directory and not /centos_whm directory ?
     
  10. Afterward

    Afterward Member

    94
    29
    18
    Jul 20, 2014
    Ratings:
    +29
    Local Time:
    9:31 PM
    1.7.5
    5.5
    Yea i'm using the one in centos dir.
     
  11. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    ok.. another on my to do list to check out then :)
     
    • Like Like x 2
  12. pamamolf

    pamamolf Well-Known Member

    2,825
    253
    83
    May 31, 2014
    Ratings:
    +449
    Local Time:
    10:31 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    I got that error today :(
     
  13. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    strange indeed, just tested on a Vultr 768MB KVM VPS and no problems with mysqlmymonlite.sh and mysqltuner besides the uninitialized value error (due to the stats value being empty = no innodb usage recorded as innodb is disabled)

    Code:
    /root/tools/mysqlmymonlite.sh mysqltuner
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.2 mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Tue Oct 7 19:30:34 UTC 2014
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]
    
    >>  MySQLTuner 1.3.0 mod - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
    >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.39-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -ARCHIVE +Aria +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6d 5h 57m 19s (143 q [0.000 qps], 55 conn, TX: 254K, RX: 9K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 88.0M global + 800.0K per thread (300 max threads)
    [OK] Maximum possible memory usage: 322.4M (43% of installed RAM)
    [OK] Slow queries: 0% (0/143)
    [OK] Highest usage of available connections: 0% (1/300)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/99.0K
    [!!] Query cache efficiency: 0.0% (0 cached / 51 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
    [OK] Temporary tables created on disk: 0% (0 on disk / 56 total)
    [OK] Thread cache hit rate: 98% (1 created / 55 connections)
    [OK] Table cache hit rate: 273% (41 open / 15 opened)
    [OK] Open file limit used: 0% (51/8K)
    [OK] Table locks acquired immediately: 100% (37 immediate / 37 locks)
    Use of uninitialized value $mystat{"Innodb_log_waits"} in concatenation (.) or
            string at /root/mysqltuner.pl line 1011 (#1)
        (W uninitialized) An undefined value was used as if it were already
        defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
        To suppress this warning assign a defined value to your variables.
      
        To help you figure out what was undefined, perl will try to tell you the
        name of the variable (if any) that was undefined. In some cases it cannot
        do this, so it also tells you what operation you used the undefined value
        in.  Note, however, that perl optimizes your program and the operation
        displayed in the warning may not necessarily appear literally in your
        program.  For example, "that $foo" is usually optimized into "that "
        . $foo, and the warning will refer to the concatenation (.) operator,
        even though there is no . in your program.
      
    [OK] InnoDB log waits:
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Enable the slow query log to troubleshoot bad queries.
        Do not forget to disable slow query logging after troubleshooting
       - For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
       - For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
       - For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
       - For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
    
    Variables to adjust:
        query_cache_limit (> 512K, or use smaller result sets)
    
    
    Report Complete:
    Tue Oct 7 19:30:34 UTC 2014
    ----------------------------
    mysqlreport looks fine too
    Code:
    /root/tools/mysqlmymonlite.sh mysqlreport
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.2 mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Tue Oct 7 19:30:27 UTC 2014
    
    ----------------------------
    mysqlreport output
    ----------------------------
    
    MariaDB 5.5.39-MariaDB      uptime 6 5:57:12    Tue Oct  7 19:30:27 2014
    
    __ Key _________________________________________________________________
    Buffer used         0 of   32.00M   %Used:   0.00
      Current       5.84M              %Usage:  18.24
    Write hit       0.00%
    Read hit        0.00%
    
    __ Questions ___________________________________________________________
    Total             105       0.0/s
      DMS              41       0.0/s  %Total:  39.05
      COM_QUIT         37       0.0/s           35.24
      Com_             31       0.0/s           29.52
      -Unknown          4       0.0/s            3.81
    Slow 1 s            0         0/s            0.00  %DMS:   0.00 Log: OFF
    DMS                41       0.0/s           39.05
      SELECT           41       0.0/s           39.05        100.00
      REPLACE           0         0/s            0.00          0.00
      DELETE            0         0/s            0.00          0.00
      INSERT            0         0/s            0.00          0.00
      UPDATE            0         0/s            0.00          0.00
    Com_               31       0.0/s           29.52
      show_variab      14       0.0/s           13.33
      show_status       7       0.0/s            6.67
      show_databa       4       0.0/s            3.81
    
    __ Rows ________________________________________________________________
    Rows            5.36k       0.0/s
      Using idx        17       0.0/s  %Index:   0.32
    Rows/question   51.07
    
    __ SELECT and Sort _____________________________________________________
    Scan               44       0.0/s %SELECT: 107.32
    Range               0         0/s            0.00
    Full join           0         0/s            0.00
    Range check         0         0/s            0.00
    Full rng join       0         0/s            0.00
    Sort scan           2       0.0/s
    Sort range          0         0/s
    Sort mrg pass       0         0/s
    
    __ Query Cache _________________________________________________________
    Memory usage   17.12k of   16.00M  %Usage:   0.10
    Block Fragmnt 100.00%
    Hits                0         0/s
    Inserts             1       0.0/s
    Insrt:Prune       1:1         0/s
    Hit:Insert     0.00:1
    
    __ Table Locks _________________________________________________________
    Waited              0         0/s  %Total:   0.00
    Immediate          37       0.0/s
    
    __ Tables ______________________________________________________________
    Open               41 of   2048    %Cache:   2.00
    Opened             15       0.0/s
    
    __ Connections _________________________________________________________
    Max used            1 of    300      %Max:   0.33
    Total              39       0.0/s
    
    __ Created Temp ________________________________________________________
    Disk table          0         0/s   %Disk:   0.00
    Table              44       0.0/s    Size:  32.0M
    File                0         0/s
    
    __ Threads _____________________________________________________________
    Running             1 of      1
    Created             1       0.0/s
    Slow                0         0/s
    Cached              0 of     64      %Hit:  97.44
    
    __ Aborted _____________________________________________________________
    Clients             0         0/s
    Connects            1       0.0/s
    
    __ Bytes _______________________________________________________________
    Sent          164.06k       0.3/s
    Received        7.35k       0.0/s
    
    __ Aria Pagecache ______________________________________________________
    Buffer used         0 of    8.00M   %Used:   0.00
      Current     328.00k              %Usage:   4.00
    Write hit       0.00%
    Read hit        0.00%
    
    Report Complete:
    Tue Oct 7 19:30:27 UTC 2014
    ----------------------------
     
  14. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    with InnoDB enabled
    Code:
    /root/tools/mysqlmymonlite.sh mysqltuner
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.2 mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Tue Oct 7 19:40:10 UTC 2014
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]
    
    >>  MySQLTuner 1.3.0 mod - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
    >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.39-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -ARCHIVE +Aria +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] InnoDB is enabled but isn't being used
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 10s (36 q [3.600 qps], 18 conn, TX: 95K, RX: 2K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 144.0M global + 800.0K per thread (300 max threads)
    [OK] Maximum possible memory usage: 378.4M (50% of installed RAM)
    [OK] Slow queries: 0% (0/36)
    [OK] Highest usage of available connections: 0% (1/300)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/99.0K
    [!!] Query cache efficiency: 0.0% (0 cached / 10 selects)
    [OK] Query cache prunes per day: 0
    [OK] Temporary tables created on disk: 0% (0 on disk / 11 total)
    [OK] Thread cache hit rate: 94% (1 created / 18 connections)
    [OK] Table cache hit rate: 100% (26 open / 0 opened)
    [OK] Open file limit used: 0% (21/8K)
    [OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries.
        Do not forget to disable slow query logging after troubleshooting
       - For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
       - For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
       - For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
       - For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
    
    Variables to adjust:
        query_cache_limit (> 512K, or use smaller result sets)
    
    
    Report Complete:
    Tue Oct 7 19:40:10 UTC 2014
    ----------------------------
     
  15. pamamolf

    pamamolf Well-Known Member

    2,825
    253
    83
    May 31, 2014
    Ratings:
    +449
    Local Time:
    10:31 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Yes thi sis exactly what i get:

    Use of uninitialized value $mystat{"Innodb_log_waits"} in concatenation (.) or
    string at /root/mysqltuner.pl line 1011 (#1)

    But what do you mean is normal as i have Innodb tables on my database and i remove from my.cnf the skip innodb and i try also to add this:
    innodb=ON <--- is this realy needed?

    but nothing...


    Do you mean that if i run it later when database collect some stats it will be working ?
     
  16. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    check your error logs for mysql
    Code:
    tail -50 /var/log/mysqld.log
    do you get something like this ?
    Code:
    141007 19:37:42 [Note] /usr/sbin/mysqld: Normal shutdown
    
    141007 19:37:42 [Note] Event Scheduler: Purging the queue. 0 events
    141007 19:37:42 [Note] /usr/sbin/mysqld: Shutdown complete
    
    141007 19:37:42 mysqld_safe mysqld from pid file /var/lib/mysql/vla01.centminmod.com.pid ended
    141007 19:37:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    141007 19:37:46 InnoDB: The InnoDB memory heap is disabled
    141007 19:37:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    141007 19:37:46 InnoDB: Compressed tables use zlib 1.2.3
    141007 19:37:46 InnoDB: Using Linux native AIO
    141007 19:37:46 InnoDB: Initializing buffer pool, size = 16.0M
    141007 19:37:46 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
    141007 19:37:46 [ERROR] Plugin 'InnoDB' init function returned error.
    141007 19:37:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    141007 19:37:46 [Note] Plugin 'FEDERATED' is disabled.
    141007 19:37:46 [Note] Plugin 'ARCHIVE' is disabled.
    141007 19:37:46 [Note] Plugin 'FEEDBACK' is disabled.
    141007 19:37:46 [Note] Server socket created on IP: '0.0.0.0'.
    141007 19:37:46 [Note] Event Scheduler: Loaded 0 events
    141007 19:37:46 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.39-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
    InnoDB engine failed to load as it seems somewhere in MariaDB install routine it creates a 5MB sized innodb log file prior to the custom tweaked my.cnf I created is read which has a 48MB sized innodb log file so it causes problems until you stop mysql server and remove the 5MB innodblog files ib_logfile* from /var/lib/mysql

    after enabling innodb in /etc/my.cnf
    Code:
    mysqladmin flush-tables
    sleep 30
    service mysql stop
    sleep 30
    rm -rf /var/lib/mysql/ib_logfile*
    service mysql start
    edit: I think I found the problem, the workaround I did actually triggers before the custom my.cnf is in place and not after !
     
    Last edited: Oct 8, 2014
  17. pamamolf

    pamamolf Well-Known Member

    2,825
    253
    83
    May 31, 2014
    Ratings:
    +449
    Local Time:
    10:31 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    this is the output:

    Code:
    141008  5:32:11 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
    141008  5:32:11 [ERROR] Plugin 'InnoDB' init function returned error.
    141008  5:32:11 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    141008  5:32:11 [Note] Plugin 'FEDERATED' is disabled.
    141008  5:32:11 [Note] Plugin 'ARCHIVE' is disabled.
    141008  5:32:11 [Note] Plugin 'FEEDBACK' is disabled.
    141008  5:32:11 [Note] Server socket created on IP: '0.0.0.0'.
    141008  5:32:11 [Warning] 'user' entry 'root@localhost.localdomain' ignored in --skip-name-resolve mode.
    141008  5:32:11 [Warning] 'proxies_priv' entry '@ root@localhost.localdomain' ignored in --skip-name-resolve mode.
    141008  5:32:11 [Note] Event Scheduler: Loaded 0 events
    141008  5:32:11 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.39-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
    141008  5:36:53 [Note] /usr/sbin/mysqld: Normal shutdown
    
    141008  5:36:53 [Note] Event Scheduler: Purging the queue. 0 events
    141008  5:36:53 [Note] /usr/sbin/mysqld: Shutdown complete
    
    141008 05:36:53 mysqld_safe mysqld from pid file /var/lib/mysql/server.mydomain.com.pid ended
    141008 05:36:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    141008  5:36:54 InnoDB: The InnoDB memory heap is disabled
    141008  5:36:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    141008  5:36:54 InnoDB: Compressed tables use zlib 1.2.3
    141008  5:36:54 InnoDB: Using Linux native AIO
    141008  5:36:54 InnoDB: Initializing buffer pool, size = 512.0M
    141008  5:36:54 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
    141008  5:36:54 [ERROR] Plugin 'InnoDB' init function returned error.
    141008  5:36:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    141008  5:36:54 [Note] Plugin 'FEDERATED' is disabled.
    141008  5:36:54 [Note] Plugin 'ARCHIVE' is disabled.
    141008  5:36:54 [Note] Plugin 'FEEDBACK' is disabled.
    141008  5:36:54 [Note] Server socket created on IP: '0.0.0.0'.
    141008  5:36:54 [Warning] 'user' entry 'root@localhost.localdomain' ignored in --skip-name-resolve mode.
    141008  5:36:54 [Warning] 'proxies_priv' entry '@ root@localhost.localdomain' ignored in --skip-name-resolve mode.
    141008  5:36:54 [Note] Event Scheduler: Loaded 0 events
    141008  5:36:54 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.39-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
    
     
  18. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    • Like Like x 1
  19. pamamolf

    pamamolf Well-Known Member

    2,825
    253
    83
    May 31, 2014
    Ratings:
    +449
    Local Time:
    10:31 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Yes working great now :

    service mysql stop
    killall mysqld
    rm -rf /var/lib/mysql/ib_logfile*
    service mysql start

    I am using .07 stable :)

    Thanks George :)
     
  20. eva2000

    eva2000 Administrator Staff Member

    30,966
    6,919
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,422
    Local Time:
    6:31 AM
    Nginx 1.13.x
    MariaDB 5.5
    guess need to look into this as I thought I already had a workaround in place at initial MariaDB install time to prevent this