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

Nick57 · Feb 26, 2014

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: [email protected]
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.

cPanelMichael · Feb 26, 2014

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.

Nick57 · Feb 26, 2014

Re: INNODB issue, tables exist but in phpmyadmin it gives error #1146 Table xxx doesn't exist.

Howdy, see above...

cPanelMichael · Feb 26, 2014

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.

Nick57 · Feb 26, 2014

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

Nick57 · Feb 27, 2014

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?

cPanelMichael · Feb 27, 2014

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.

Nick57 · Feb 28, 2014

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?

cPanelMichael · Feb 28, 2014

Have you tried reinstalling Horde and Roundcube? What appears in the /var/cpanel/roundcube/log/errors file?

Thank you.

Nick57 · Feb 28, 2014

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?

cPanelMichael · Feb 28, 2014

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.

Nick57 · Feb 28, 2014

Ticket: 4620643

cPanelMichael · Mar 3, 2014

To update, the issue was resolved after repairing the "mysql" database itself and ensuring "/scripts/update_db_cache" completed successfully.

Thank you.

Thread starter	Similar threads	Forum	Replies	Date
	crashed innodb tables	Databases	5	Jun 10, 2015
	Long queries: SELECT data_free FROM tables WHERE ENGINE = 'InnoDB' LIMIT 1	Databases	4	Jun 2, 2014
N	How to force mysql v.5.5.32 to create MyISAM tables instead of InnoDB?	Databases	3	Nov 30, 2013
H	Convert MySQL all tables in all databases to InnoDB	Databases	0	Dec 2, 2012
	ibdata1 is very large. How to return the tables from innodb to mysql ?	Databases	1	Jan 23, 2012

Search

Search

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

Nick57

Well-Known Member

cPanelMichael

Administrator

Nick57

Well-Known Member

cPanelMichael

Administrator

Nick57

Well-Known Member

Nick57

Well-Known Member

cPanelMichael

Administrator

Nick57

Well-Known Member

cPanelMichael

Administrator

Nick57

Well-Known Member

cPanelMichael

Administrator

Nick57

Well-Known Member

cPanelMichael

Administrator