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.

InnoDB tables exist but phpMyAdmin gives error #1146 Table xxx doesn't exist.

Discussion in 'Database Discussions' started by Nick57, Feb 26, 2014.

  1. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    InnoDB trouble again :-(

    Slowly getting tired of this new feature, it breaks to many websites.
    Until last night everything was running fine, this morning I got flooded with emails about high load and of course almost anything what uses mysql was broken because mysql was gone away... broken somehow.

    After several attempts, I got mysql up and running again, but now it won't display or uses any InnoDB tables.

    Anyone any suggestions???

    This is the content of /etc/my.cnf

    [mysqld]
    innodb_file_per_table=1
    local-infile=0
    #
    character-set-server=utf8
    collation-server=utf8_general_ci
    #
    default-storage-engine=MYISAM
    #
    open_files_limit=3308
    #
    [client]
    default-character-set=utf8
    #

    Database server

    Server: Localhost via UNIX socket
    Server type: MySQL
    Server version: 5.5.35-cll - MySQL Community Server (GPL)
    Protocol version: 10
    User: cpses_xxxxxx@localhost
    Server charset: UTF-8 Unicode (utf8)

    Web server

    cpsrvd 11.40.1.11
    Database client version: libmysql - 5.0.96
    PHP extension: mysqli Documentation

    phpMyAdmin

    Version information: 4.0.8, latest stable version: 4.1.8, latest stable version: 4.1.8

    mysql> SHOW ENGINES;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine | Support | Comment | Transactions | XA | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
    | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
    | CSV | YES | CSV storage engine | NO | NO | NO |
    | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
    | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
    | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
    | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
    | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
    | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)

    More info:

    It seems the server run out of memory: Swap Used 0.42% (17,196 of 4,128,760)
    InnoDB uses memory as a default and crashed, but no autorecover is done.

    https://dev.mysql.com/doc/refman/5.6/en/innodb-default-se.html

    If your server crashes because of a hardware or software issue, regardless of what was happening in the database at the time, you don't need to do anything special after restarting the database. InnoDB crash recovery automatically finalizes any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off. This process is now much faster than in MySQL 5.1 and earlier.
     
    #1 Nick57, Feb 26, 2014
    Last edited: Feb 26, 2014
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Re: INNODB issue, tables exist but in phpmyadmin it gives error #1146 Table xxx doesn't exist.

    Hello :)

    Please post the output of the following command from the mysql prompt:

    Code:
    mysql> SHOW ENGINES;
    Thank you.
     
  3. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Re: INNODB issue, tables exist but in phpmyadmin it gives error #1146 Table xxx doesn't exist.

    Howdy, see above...
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Re: INNODB issue, tables exist but in phpmyadmin it gives error #1146 Table xxx doesn't exist.

    Do you notice any specific error messages in the MySQL error log when this happens?

    Thank you.
     
  5. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Yes, some errors about charactersets in /etc/my.cnf, i removed the lines which gaves errors...

    This is the mysql errror log after a mysql restart.

    140226 23:56:28 mysqld_safe mysqld from pid file /var/lib/mysql/xxxxx.xxxxxxxxxxxxx.pid ended
    140226 23:56:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    140226 23:56:28 [Note] Plugin 'FEDERATED' is disabled.
    140226 23:56:28 InnoDB: The InnoDB memory heap is disabled
    140226 23:56:28 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    140226 23:56:28 InnoDB: Compressed tables use zlib 1.2.3
    140226 23:56:28 InnoDB: Using Linux native AIO
    140226 23:56:28 InnoDB: Initializing buffer pool, size = 128.0M
    140226 23:56:28 InnoDB: Completed initialization of buffer pool
    InnoDB: The first specified data file ./ibdata1 did not exist:
    InnoDB: a new database to be created!
    140226 23:56:28 InnoDB: Setting file ./ibdata1 size to 10 MB
    InnoDB: Database physically writes the file full: wait...
    140226 23:56:28 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile0 size to 5 MB
    InnoDB: Database physically writes the file full: wait...
    140226 23:56:28 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile1 size to 5 MB
    InnoDB: Database physically writes the file full: wait...
    InnoDB: Doublewrite buffer not found: creating new
    InnoDB: Doublewrite buffer created
    InnoDB: 127 rollback segment(s) active.
    InnoDB: Creating foreign key constraint system tables
    InnoDB: Foreign key constraint system tables created
    140226 23:56:28 InnoDB: Waiting for the background threads to start
    140226 23:56:29 InnoDB: 5.5.35 started; log sequence number 0
    140226 23:56:29 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
    140226 23:56:29 [Note] - '0.0.0.0' resolves to '0.0.0.0';
    140226 23:56:29 [Note] Server socket created on IP: '0.0.0.0'.
    140226 23:56:29 [Note] Event Scheduler: Loaded 0 events
    140226 23:56:29 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.35-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
    26/Feb/2014 23:56
     
  6. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Still not solved, what keeps me puzzled is that this VPS is just a few weeks old, running on STABLE 11.40.1.11
    Clean install no problems, then suddenly over night high load and running out of memory [using the swap] and now this innodb issue.
    Never issues with mysql, and now it won't accept the settings for the default character set any more, this is worrying, because it might effect my other servers as well if this is due to some patching from cPanel.

    I can see all the tables in phpmyadmin [left column], but they innodb ones are not there in the right column, in the website they are no longer available and gone missing, when trying to create them if comes with error, so it looks like the innodb tables are locked. No idea how to solve this.

    Only when trying to do something with the tables generate an error in the mysql server log.
    For example like this:

    140226 23:57:11 [ERROR] Cannot find or open table xxxxx_xxxxxx/xx_xxxxxxx from
    the internal data dictionary of InnoDB though the .frm file for the
    table exists. Maybe you have deleted and recreated InnoDB data
    files but have forgotten to delete the corresponding .frm files
    of InnoDB tables, or you have moved .frm files to another database?
    or, the table contains indexes that this version of the engine
    doesn't support.
    See MySQL :: MySQL 5.5 Reference Manual :: 14.3.19 InnoDB Troubleshooting
    how you can resolve the problem.

    Looking in the folder, both the frm and innodb are there, having mysql ownership and nothing wrong with at first glance.

    Anyone, any idea?
     
  7. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Feel free to open a support ticket using the link in my signature if you want us to take a closer look as to why InnoDB is not working. We won't be able to repair any InnoDB corruption, but we can let you know if that's why it's not working. You can post the ticket number here so we can update this thread with the outcome.

    Thank you.
     
  8. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I solved the InnoDB issue by dropping the tables, but it looks now more serious, because I can't use Roundcube or Horde, it comes with errors, and when creating a new mysql DB it is not showing in the cpanel [left side] when importing data in the new table it shows 0.00mb, but it has over 92 tables with a 33MB of data and 77k records,...

    Roundcube
    DATABASE ERROR: CONNECTION FAILED!

    Unable to connect to the database!
    Please contact your server-administrator.

    what can I run for scripts to fix mysql issues?
     
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  10. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I have run this script and that has solved the Horde/roundcube issue, but still having this issue with mysql new DB created not showing in the total counts and db appears to be empty...

    /usr/local/cpanel/bin/update-horde --force

    Any other scripts to run to fix mysql?
     
  11. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Feel free to open a support ticket using the link in my signature if you want us to take a closer look at why new databases are not creating properly. You can post the ticket number here so we can update this thread with the outcome.

    Thank you.
     
  12. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Ticket: 4620643
     
  13. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    To update, the issue was resolved after repairing the "mysql" database itself and ensuring "/scripts/update_db_cache" completed successfully.

    Thank you.
     
Loading...

Share This Page