is it possible to set engine for database (not the tables)?

Question

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!

• ypercubeᵀᴹ: 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?
• M a m a D: @ypercube because when I changed InnoDB to MyISAM it worked!!!
• ypercubeᵀᴹ: Can you try SHOW ENGINES; to check if InnoDB is disabled?
• RolandoMySQLDBA: 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.
• M a m a D: @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

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.

• M a m a D: 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.
• ypercubeᵀᴹ: 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; ?
• M a m a D: says can not create table, it gives me a link for more details and there is some information about InnoDB