Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There are about 250 tables in my database and they all have InnoDB engine. I imported them into a server.

They were imported and were displayed in the tree structure of phpmyadmin (under the database name), but when I clicked on them MySQL reported the error that table doesn't exist!!

From this link ,I find out that database engine and tables engine are not the same. is it possible to set engine for database itself (not the tables)? if yes how ?


update :

I also tried to create tables manually (by SQL Query and InnoDB engine) but reported the error that can't create table, I changed InnoDB to MyISAM and problem solved!

share|improve this question
    
I can't understand how the error that table doesn't exist!! is related to the engine. Did you import the database or just copied the tables (as files) from one server to the other? –  ypercube Jan 2 at 8:42
    
@ypercube because when I changed InnoDB to MyISAM it worked!!! –  Mohammad Jan 2 at 8:46
1  
Can you try SHOW ENGINES; to check if InnoDB is disabled? –  ypercube Jan 2 at 9:06
    
When you changed InnoDB to MyISAM, that created the external tables, but the data dictionary entries for those tables in ibdata1 are still trapped inside. That means there is no way to go back to InnoDB. If you want to leave everything MyISAM, your problem is solved (with a messy ibdata1). If you ever want to go back to InnoDB, you need to describe how you did the export IN DETAIL. –  RolandoMySQLDBA Jan 2 at 18:45
    
@RolandoMySQLDBA I opend the .sql backup and changed all the engine=InnoDB to engine=MyISAM , if one day I decide to back to InnoDB, I guess reverse of this operation will work –  Mohammad Jan 3 at 5:28

1 Answer 1

There is no "database" engine setting. A database can have tables using different engines.

There are two variables that define which engine will be used when a table is created with a CREATE TABLE that does not include an ENGINE = EngineName specification. The variables are the (global and session) default_storage_engine. The global is configured at startup (from my.cnf or command line) and the session variable can be setup during each session.

share|improve this answer
    
then why such thing happened to database? I tried to create table (with InnoDB engine) manually it again reported an error, but when I changed it to MyISAM there was no error. –  Mohammad Jan 2 at 8:43
    
You mean you can't create a table with InnoDB engine? What exact error do you get if you run CREATE TABLE atest (tid INT) ENGINE = InnoDB; ? –  ypercube Jan 2 at 8:50
    
says can not create table, it gives me a link for more details and there is some information about InnoDB –  Mohammad Jan 2 at 8:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.