InnoDB tables exist but phpMyAdmin gives error #1146 Table xxx doesn't exist.
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.
-
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: mysql> SHOW ENGINES;
Thank you.0 -
Re: INNODB issue, tables exist but in phpmyadmin it gives error #1146 Table xxx doesn't exist. Howdy, see above... 0 -
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. 0 -
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 0 -
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 [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html]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? 0 -
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. 0 -
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? 0 -
Have you tried reinstalling Horde and Roundcube? What appears in the /var/cpanel/roundcube/log/errors file? Thank you. 0 -
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? 0 -
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. 0 -
Ticket: 4620643 0 -
To update, the issue was resolved after repairing the "mysql" database itself and ensuring "/scripts/update_db_cache" completed successfully. Thank you. 0
Please sign in to leave a comment.
Comments
12 comments