I am using mysql and mysql workbench. I created 5 tables with innodb engine. I checked their engine and it was innodb before I insert data into them. I inserted data from 5 MyISAM tables and now my innodb tables are MyISAM. I can&#39;t change them. I used the alter table engine=innodb but it doesn&#39;t work.


Changing Table Engine in MySQL

Below query I&#39;m executing in Ubuntu 12, MySQL 5.1 version and receiving error as mentioned:

    CREATE TABLE mantis_config_table (
        config_id VARCHAR(64) NOT NULL,
        project_id INTEGER NOT NULL DEFAULT 0,
        user_id INTEGER NOT NULL DEFAULT 0,
        access_reqd INTEGER DEFAULT 0,
        type INTEGER DEFAULT 90,
        value LONGTEXT NOT NULL,
        PRIMARY KEY (config_id, project_id, user_id)
    ) TYPE=MyISAM;

&gt; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;TYPE=MyISAM&#39; at line 9

Can anyone suggest what&#39;s wrong? 

mysql error &#39;TYPE=MyISAM&#39;

I understand that this question has been asked before, but most of the time it is asked in relation to a specific database or table. I cannot find an answer on this site that describes the two engines and their differences without respect to someones specific database. 

I want to be able to make more informed decisions in the future with respect to designing a table or database, so am looking for a comprehensive answer on the differences between the two storage engines.

What&#39;s the difference between [MyISAM][1] and [InnoDB][2], and what should I be looking for when trying to decide between one or the other?


  [1]: http://en.wikipedia.org/wiki/MyISAM
  [2]: http://en.wikipedia.org/wiki/InnoDB

What&#39;s the difference between MyISAM and InnoDB?

Here is a gross oversimplification of an intense setup I am working with. `table_1` and `table_2` both have auto-increment surrogate primary keys as the ID. `info` is a table that contains information about both `table_1` and `table_2`.

    table_1 (id, field)  
    table_2 (id, field, field)
    info ( ???, field)



I am trying to decided if I should make the primary key of `info` a composite of the IDs from `table_1` and `table_2`. If I were to do this, which of these makes most sense?    
*( in this example I am combining ID 11209 with ID 437 )* 

`INT(9)` 11209437   *(i can imagine why this is bad)*  
`VARCHAR (10)`    11209-437   
`DECIMAL (10,4)` 11209.437   

Or something else? 

Would this be fine to use this as the Primary Key on a MYSQL MYISAM DB?


How to properly create composite primary keys - MYSQL

I&#39;m working on a projects which involves a lot of database writes, I&#39;d say (*70% inserts and 30% reads*).  This ratio would also include updates which I consider to be one read and one write.  The reads can be dirty (e.g. I don&#39;t need 100% accurate information at the time of read).  
The task in question will be doing over 1 million database transactions an hour.  

I&#39;ve read a bunch of stuff on the web about the differences between MyISAM and InnoDB, and MyISAM seems like the obvious choice to me for the particular database/tables that I&#39;ll be using for this task.  From what I seem to be reading, InnoDB is good if transactions are needed since row level locking is supported.

Does anybody have any experience with this type of load (or higher)?  Is MyISAM the way to go?

MyISAM versus InnoDB

Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I&#39;d like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won&#39;t budge on the INT AUTO_INCREMENT as a primary key for all tables. 

My argument, as detailed [here][1], is that the performance difference between INT and VARCHAR is negligible, since every INT foreign key reference will require a JOIN to make sense of the reference, a VARCHAR key will directly present the information.

So, does anyone have experience with this particular use-case and the performance concerns associated with it?

  [1]: http://database-programmer.blogspot.com/2008/01/database-skills-sane-approach-to.html#rule1

Is there a REAL performance difference between INT and VARCHAR primary keys?

What is `InnoDB` and `MyISAM` in `MySQL` ?

What is InnoDB and MyISAM in MySQL?

In some cases, running an UPDATE statement in production can save the day. However a borked update can be worse than the initial problem.

Short of using a test database, what are options to tell what an update statement will do before running it?

How to test an SQL Update statement before running it?

In MySQL, there is no way to specify a storage engine for a certain database, only for single tables.  However, you can specify a storage engine to be used during one session with:

    SET storage_engine=InnoDB;

So you don&#39;t have to specify it for each table.

**How do I confirm, if indeed all the tables are using InnoDB?**

How do I know if a mysql table is using myISAM or InnoDB Engine?

My MySQL database contains several tables using different storage engines
(specifically myisam and innodb).  How can I find out which tables are
using which engine?

All Myisam Solutions on Gang of Coders

Total of 10 Myisam Solutions