Follow Me
Subscribes Hell Buddy Solution.

Google+
^
Back To Top

Feel Glad finally done the Meta Tags generator and video :) project
- type: seo html - 30 September 2014 Tue @Hell Buddy

close
Home Message Share Translate

Solution - MySQL Alter Storage Engine Error

10August 2014
Sunday



Alter MySQL storage engine error #1217 and #1214

The errors happen between 2 different storage engines conversion: InnoDB and MyISAM.


Read the content or code / instruction -

Create, Alter, Show MySQL table storage engine type

There has many types of storage engines with their feature functions as InnoDB, MyISAM, Memory, Merge, Archive, Federated, NDB...

  1. Default Engine - Mostly, the default engine is InnoDB (some would be MyISAM) if you didn't set any.
  2. CREATE TABLE table_name(
    id INT(2) NOT NULL AUTO_INCREMENT,
    name VARCHAR(5),
    PRIMARY KEY(id)
    )


    How to check the default engine?

    SHOW ENGINES; // Engine: InnoDB - Support: Default


  3. CREATE table engine type:
  4. CREATE TABLE table_name(
    id INT(2) NOT NULL AUTO_INCREMENT,
    name VARCHAR(5),
    PRIMARY KEY(id)
    )ENGINE=InnoDB;
    -or any kinds- CREATE TABLE table_name(
    id INT(2) NOT NULL AUTO_INCREMENT,
    name VARCHAR(5),
    PRIMARY KEY(id)
    )ENGINE=MyISAM;

  5. ALTER table engine type:
  6. ALTER TABLE table_name ENGINE=MyISAM; -- ALTER TABLE table_name ENGINE=InnoDB;

  7. SHOW table engine type:
  8. SHOW TABLE STATUS FROM database_name;

Error #1217 when convert engine from InnoDB to MyISAM

ALTER TABLE table_name ENGINE=MyISAM;
Error message #1217 - Cannot delete or update a parent row; a FOREIGN KEY constraint fail!
Check back the table details:
SHOW CREATE TABLE table_name;
If you found any "foreign key" that you set it before. MyISAM type engine unable of FOREIGN KEY SUPPORT. See MySQL 5.1 Manual
* Beware if drop key constraint, it would be impacted data accuracy.

Error #1214 when convert engine from MyISAM to InnoDB

ALTER TABLE table_name ENGINE=InnoDB;
Error message #1214 - the used table type doesn't support FULLTEXT indexes.
Check back the table details:
SHOW CREATE TABLE table_name;
If you found any "fulltext index" that you set it before. InnoDB type engine unable of lower MySQL 5.6.4 version support.. The InnoDB engine is available support for FULLTEXT indexes in MySQL 5.6.4 or higher. See MySQL 5.1 Manual

open the content >>

see the code OR slideshow instruction



//<--STEP 1 - Create MySQL table in phpMyadmin database-->
//<--Make sure no data in table before drop the table-->

DROP TABLE IF EXISTS Message_1;
CREATE TABLE Message_1
(
id INT(100) NOT NULL AUTO_INCREMENT,
title VARCHAR(100),
message TEXT,
PRIMARY KEY(id),
FULLTEXT INDEX(title, message)
)ENGINE=MyISAM;

//<--STEP 2 - INSERT table-->

INSERT INTO Message_1(title, message)
VALUES
('Hello', 'blah ba blab nanta ingonyana bagithi baba');

//<--STEP 3 - Show the SELECT table-->

SELECT * FROM Message_1;

Watch next videos and relative source page(s)

tag MySQL - error 1217 1214
Programming Web Development Networking FunG project

Topic Discussion:

No Topic Discuss Now...