The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Mysql Error Message

Discussion in 'Database Discussions' started by subin_hutton, Aug 2, 2010.

  1. subin_hutton

    subin_hutton Member

    Joined:
    Jul 25, 2008
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    India
    Hi Guys,

    We've a vps with cpanel installed.Periodically customers complaining about mysql connection errors in the server.I've checked the mysql error log and I can see the following error message [It seems like mysql is getting down and cpanel tried to restart mysql service].

    ==========================================================================
    100606 10:45:06 mysqld started
    InnoDB: Error: auto-extending data file ./ibdata1 is of a different size
    InnoDB: 192 pages (rounded down to MB) than specified in the .cnf file:
    InnoDB: initial 640 pages, max 0 (relevant if non-zero) pages!
    InnoDB: Could not open or create data files.
    InnoDB: If you tried to add new data files, and it failed here,
    InnoDB: you should now edit innodb_data_file_path in my.cnf back
    InnoDB: to what it was, and remove the new ibdata files InnoDB created
    InnoDB: in this failed attempt. InnoDB only wrote those files full of
    InnoDB: zeros, but did not yet use them in any way. But be careful: do not
    InnoDB: remove old data files which contain your precious data!
    100606 10:45:07 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.0.90-community' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition (GPL)
    ==========================================================================

    When I checked the details of storage engines, I could see that innodb is disabled.

    ===========================================================================
    mysql> show engines;
    +------------+----------+----------------------------------------------------------------+
    | Engine | Support | Comment |
    +------------+----------+----------------------------------------------------------------+
    | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
    | MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
    | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys |
    | BerkeleyDB | NO | Supports transactions and page-level locking |
    | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) |
    | EXAMPLE | YES | Example storage engine |
    | ARCHIVE | YES | Archive storage engine |
    | CSV | YES | CSV storage engine |
    | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
    | FEDERATED | YES | Federated MySQL storage engine |
    | MRG_MYISAM | YES | Collection of identical MyISAM tables |
    | ISAM | NO | Obsolete storage engine |
    +------------+----------+----------------------------------------------------------------+
    12 rows in set (0.00 sec)
    ===========================================================================

    It's a live production server. my.cnf details is as follows.

    ============================================================================
    [mysqld]
    thread_concurrency=4
    max_connections=500
    max_user_connections=8
    key_buffer=512M
    myisam_sort_buffer_size=64M
    join_buffer_size=1M
    read_buffer_size=1M
    sort_buffer_size=1M
    table_cache=1500
    thread_cache_size=128
    interactive_timeout=45
    wait_timeout=20
    connect_timeout=8
    max_allowed_packet=16M
    max_connect_errors=10
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    flush
    flush_time=60

    [mysql.server]
    user=mysql

    [mysqld_safe]
    open_files_limit=8192

    [mysqldump]
    quick
    max_allowed_packet=16M

    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    [mysqlhotcopy]
    interactive-timeout
    ============================================================================

    I don't wanna change the default storage engine.

    1.If I could enable the innodb engine without changing it as the default storage engine,will it make any issues with the existing databases which is in the server?

    2.If it's not how can I enable innodb?

    3.Error message is saying about innodb datafile.If innodb is disabled why we are getting this error message?

    It would be very helpful for us if could share some ideas.


    :confused:
     
    #1 subin_hutton, Aug 2, 2010
    Last edited: Aug 2, 2010
  2. subin_hutton

    subin_hutton Member

    Joined:
    Jul 25, 2008
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    India
    At last I've fixed this issue by deleting ibdata1,ib_logfile0,ib_logfile1 from /var/lib/mysql and restarted mysql server

    =========================================================
    mysql> show engines;
    +------------+---------+----------------------------------------------------------------+
    | Engine | Support | Comment |
    +------------+---------+----------------------------------------------------------------+
    | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
    | MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
    | InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
    | BerkeleyDB | NO | Supports transactions and page-level locking |
    | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) |
    | EXAMPLE | YES | Example storage engine |
    | ARCHIVE | YES | Archive storage engine |
    | CSV | YES | CSV storage engine |
    | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
    | FEDERATED | YES | Federated MySQL storage engine |
    | MRG_MYISAM | YES | Collection of identical MyISAM tables |
    | ISAM | NO | Obsolete storage engine |
    +------------+---------+----------------------------------------------------------------+
    12 rows in set (0.00 sec)
    =========================================================\
    The issue seems to be resolved.
    :D
     
Loading...

Share This Page