Mysql: Should I always prefer MySQL InnoDB over MyISAM?

Someone just told me that InnoDB is much better than MyISAM. So when I create a table, should I always try to use InnoDB Engine instead of MyISAM? Or do both have it's big benefits?

 Answers

MyISAM is transactionless and heap-organized. The records are identified by the row offset in the table and the indexes store this offset as a row pointer.

InnoDB supports transactions and is index-organized. The records are identified by the value of the PRIMARY KEY (or a hidden internal column is there is no PRIMARY KEY defined) and are stored in a B-Tree. The secondary indexes store the value of the PRIMARY KEY as a row pointer.

Queries that involve full table scans or secondary index lookups are usually faster on MyISAM tables.

Queries that involve PRIMARY KEY seeks are usually faster on InnoDB tables.

MyISAM tables store the number of records in the table in the table's metadata, that's why the queries like this:

SELECT  COUNT(*)
FROM    myisamtable

are instant.

MyISAM tables are completely locked on the DML operations (with several exceptions).

InnoDB tables lock individual records and index gaps, however these are the records and the gaps that are scanned, not only those matched by the WHEREcondition. This can lead to the records being locked despite the fact they don't match.

InnoDB tables support referential integrity (FOREIGN KEYs) . MyISAM tables don't.

There are several scenarios that can show benefits of both engines.

---

To put it simply:

You should use InnoDB:

• if you need transaction support
• if you need foreign keys

You should use MyISAM:

• if you don't need the above AND
• you need speed (faster database operations)

---

InnoDB is a fully ACID compliant database engine and therefore offers support for transactions, etc. As such, it can therefore be slower than the MyISAM database which tends to be optimised in a different direction.

Therefore if you need transactions InnoDB (or another RDBMS such as PostgreSQL) is the obvious choice.

There's a reasonable comparison over on Wikipedia

Mysql: Renaming an InnoDB table without updating foreign key references to it?

I am trying to replace an InnoDB table with a new table, and I want all foreign key references that point to the old table to point to the new table.

So I tried this:

SET foreign_key_checks = 0;
ALTER TABLE foo RENAME foo_old;
ALTER TABLE foo_new RENAME foo;

Unfortunately, even with foreign_key_checks disabled, all references pointing to foo are changed to point to foo_old. Now I am looking for either

• a way to change the foreign key references back without rebuilding the entire table, OR
• a way to rename a table without updating foreign key references.

I tried dropping the foreign keys and recreating them, but since the tables are huge, it takes hours. The whole point of replacing the table was to make a schema change with limited downtime.

 Answers

Old questions, but following is a possible way around. Basically move the data rather than renaming the tables. You need to of course make sure the new data adhere to the foreign key rules.

SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS foo_old LIKE foo;
INSERT INTO foo_old SELECT * FROM foo;
TRUNCATE foo;
INSERT INTO foo SELECT * FROM foo_new;

Make sure you run it as one query so the foreign_key_checks applies to the whole thing. Hope this helps.

---

On MySQL 5.6 with innodb_file_per_table=ON allows you to swap the table spaces on the fly. This can't be done completely using SQL as file operations need to be performed separately. First prepare the foo_new table to be copied and drop the foo data:

SET foreign_key_checks = 0;
ALTER TABLE foo DISCARD TABLESPACE;
FLUSH TABLES foo_new FOR EXPORT;

At this point you need to copy the relevant InnoDB files to correct name. Files are stored in your data directory. On Debian, for example, they are by default in /var/lib/mysql/yourdatabase and files are foo_new.ibd, foo_new.cfg and foo_new.frm. Copy them to foo.ibd, foo.cfg and foo.frm, respectively. For example:

$ cp foo_new.ibd foo.ibd
$ cp foo_new.frm foo.frm
$ cp foo_new.cfg foo.cfg

Pay attention that MySQL has access to the new files (e.g. they have correct owner, access rights). Once done you can import the table again and enable foreign keys:

UNLOCK TABLES;
ALTER TABLE foo IMPORT TABLESPACE;
SET foreign_key_checks = 1;

This only copies foo_new to foo. Repeat the steps if you need to copy foo to foo_old.

---

I found a way around this... you just drop the source table instead of renaming it.

For this example, we'll call the table 'mytbl'.

1. create copy of the source table, e.g. 'mytbl_new'
2. copy data into the new table
3. drop the source table 'mytbl'
4. rename 'mytbl_new' to 'mytbl'

The only downside is you can't keep a backup of your original table, but you could mysqldump it before hand. Alternatively, you could create an additional table copy if you wanted a verbatim copy of the original table.

Mysql: How to disable index in innodb

I'm trying to speed up bulk insert in an InnoDB table by temporary disabling its indexes:

ALTER TABLE mytable DISABLE KEYS;

But it gives a warning:

+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'mytable' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

How can we disable the indexes?

What alternatives are there to avoid using the index when doing bulk inserts?

How can we speed up the process?

 Answers

Have you tried the following?

    SET autocommit=0; 
    SET unique_checks=0; 
    SET foreign_key_checks=0;

From the MySQL References https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

See Section "Bulk Data Loading Tips"

---

to reduce the costs for re-calculating the indexes you should insert the data either using DATA INFILE or using Mysql Multi Row Inserts, like

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

-> so inserting several rows with one statement.

How many rows one can insert with one statement depends on the max_allowed_packet mysql setting.

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

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't have to specify it for each table.

How do I confirm, if indeed all the tables are using InnoDB?

 Answers

If you use SHOW CREATE TABLE, you have to parse the engine out of the query.

Selecting from the INFORMATION_SCHEMA database is poor practice, as the devs reserve the right to change its schema at any time (though it is unlikely).

The correct query to use is SHOW TABLE STATUS - you can get information on all the tables in a database:

SHOW TABLE STATUS FROM `database`;

Or for a specific table:

SHOW TABLE STATUS FROM `database` LIKE 'tablename';

One of the columns you will get back is Engine.

---

show create table <table> should do the trick.

Mysql: InnoDB - Attempted to open a previously opened tablespace

I have been working on a problem for a few days now. Our local mediawiki page that sits on our box account, destroyed itself and we've been working to get it online. Using XAMPP Control Panel v3.2.1, the errors were numerous so we decided to update XAMPP (v3.2.2) and move the 'htdocs' and 'mysql/data' files over to the new data base.

First error:

    9:50:21 AM  [mysql]     Attempting to start MySQL app...
    9:50:22 AM  [mysql]     Status change detected: running
    9:50:22 AM  [mysql]     Status change detected: stopped
    9:50:22 AM  [mysql]     Error: MySQL shutdown unexpectedly.
    9:50:22 AM  [mysql]     This may be due to a blocked port, missing dependencies, 
    9:50:22 AM  [mysql]     improper privileges, a crash, or a shutdown by another method.
    9:50:22 AM  [mysql]     Press the Logs button to view error logs and check
    9:50:22 AM  [mysql]     the Windows Event Viewer for more clues
    9:50:22 AM  [mysql]     If you need more help, copy and post this
    9:50:22 AM  [mysql]     entire log window on the forums

As it says, I then went to the logs and found this:

    2015-11-20 09:50:22 11f8 InnoDB: Warning: Using      innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
    2015-11-20  9:50:22 4600 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    2015-11-20  9:50:22 4600 [Note] InnoDB: The InnoDB memory heap is disabled
    2015-11-20  9:50:22 4600 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
    2015-11-20  9:50:22 4600 [Note] InnoDB: Memory barrier is not used
    2015-11-20  9:50:22 4600 [Note] InnoDB: Compressed tables use zlib 1.2.3
    2015-11-20  9:50:22 4600 [Note] InnoDB: Not using CPU crc32 instructions
    2015-11-20  9:50:22 4600 [Note] InnoDB: Initializing buffer pool, size = 16.0M
    2015-11-20  9:50:22 4600 [Note] InnoDB: Completed initialization of buffer pool
    2015-11-20  9:50:22 4600 [Note] InnoDB: Highest supported file format is Barracuda.
    2015-11-20  9:50:22 4600 [Note] InnoDB: The log sequence numbers 1665234 and 1665234 in ibdata files do not match the log sequence number 50125498 in the ib_logfiles!
    2015-11-20  9:50:22 4600 [Note] InnoDB: Database was not shutdown normally!
    2015-11-20  9:50:22 4600 [Note] InnoDB: Starting crash recovery.
    2015-11-20  9:50:22 4600 [Note] InnoDB: Reading tablespace information from the .ibd files...
    2015-11-20  9:50:22 4600 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace phpmyadmin/pma__tracking uses space ID: 21 at filepath: .\phpmyadmin\pma__tracking.ibd. Cannot open tablespace wiki/archive which uses space ID: 21 at filepath: .\wiki\archive.ibd
    InnoDB: Error: could not open single-table tablespace file .\wiki\archive.ibd
    InnoDB: We do not continue the crash recovery, because the table may become
    InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
    InnoDB: To fix the problem and start mysqld:
    InnoDB: 1) If there is a permission problem in the file and mysqld cannot
    InnoDB: open the file, you should modify the permissions.
    InnoDB: 2) If the table is not needed, or you can restore it from a backup,
    InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
    InnoDB: crash recovery and ignore that table.
    InnoDB: 3) If the file system or the disk is broken, and you cannot remove
    InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
    InnoDB: and force InnoDB to continue crash recovery here.

Now this looks like a standard error that I've seen with many different suggestions throughout the web on how to fix it. I will go over them briefly.

The first thing I tried was to follow the suggestions in the log.

1. The were no permission problems
2. It is not clear if I need the table or not, OR whether to get rid of phpmyadmin/pma__tracking or archive.ibd. When I got rid of the archive.ibd, the error just past on to another .ibd file.
3. 'innodb_force_recovery=1' was added to my.cnf and this cause a bunch of errors.

The next thing I noticed is that when we built the new database, I got this error in my phpMyAdmin (localhost/phpMyAdmin): phpMyAdmin error

I'm not sure if this is causing all of my problems or not. I found that people were saying to switch a password to =''. This error might be happening because I'm entering old data folders in a new database. I'm not sure.

The first suggestion on the web was to remove the following files from

\mysql\data:
    innodb_index_stats.frm
    innodb_index_stats.ibd 
    innodb_table_stats.frm 
    innodb_table_stats.ibd 
    slave_master_info.ibd 
    slave_relay_log_info.frm 
    slave_relay_log_info.ibd 
    slave_worker_info.frm 
    slave_worker_info.ibd

The 2nd:

I've tried removing 'ibdata1' None of these have worked.

 Answers

I had the same problem. I tried all the solution others has proposed, and unfortunately nothing worked.

After spending hours on searching for the solution in Google I finally found this

1. Open my.ini (my.cnf on linux-based systems and Mac)
2. Look for [mysqld]
3. Just below [mysqld] insert innodb_force_recovery = 1
4. Start MySQL Service
5. Stop MySQL Service
6. Remove the line from my.ini (innodb_force_recovery = 1)
7. Start MySQL Service

Worked perfect in my case.

I hope this will solve your problem.

---

Solution is for MAC 10.11.3 El Captian

• Go to /Applications/XAMPP/xamppfiles/var/mysql/
• Delete all random files (except the actual database folders)
• Restart Apache and MySQL.

This worked for me.

---

try to rename /Applications/XAMPP/xamppfiles/var/mysql/ib_logfile0 to /Applications/XAMPP/xamppfiles/var/mysql/ib_logfile0.bkp

and /Applications/XAMPP/xamppfiles/var/mysql/ib_logfile1 to /Applications/XAMPP/xamppfiles/var/mysql/ib_logfile1.bkp

---

Tgr's answer looks appropriate. The message about permissions etc. is a generic one; the actual error message is

2015-11-20 9:50:22 4600 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace phpmyadmin/pma__tracking uses space ID: 21 at filepath: .\phpmyadmin\pma__tracking.ibd. Cannot open tablespace wiki/archive which uses space ID: 21 at filepath: .\wiki\archive.ibd

Your wiki database and phpmyadmin database somehow ended up with the same tablespace ID. Each would work fine if the other wasn't present; as it is now, you'll have to renumber one of them somehow.

---

Another solution for the issue discribed above for MAMP Pro, as I found impossible to edit my.cnf properly :

When InnoDB is crashing, spot in the error message the DB that is causing trouble. Here it is phpmyadmin/pma__tracking so the table is the one with the pma_ extension.

Then go to /Library/Application Support/appsolute/MAMP PRO/db/mysql and remove the folder named after the problem causing DB.

Restart your MAMP server. Once you restarted with success, you can stop servers again, put back the DB folder where it belongs and start the servers again. Everything should be fine again.

What's the difference between MyISAM and InnoDB?

This question already has an answer here:

• MyISAM versus InnoDB 27 answers

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's the difference between MyISAM and InnoDB, and what should I be looking for when trying to decide between one or the other?

 Answers

The main differences between InnoDB and MyISAM ("with respect to designing a table or database" you asked about) are support for "referential integrity" and "transactions".

If you need the database to enforce foreign key constraints, or you need the database to support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted) then you would choose the InnoDB engine, since these features are absent from the MyISAM engine.

Those are the two biggest differences. Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table.

Those two specific engines you asked about (InnoDB and MyISAM) have different design goals. MySQL also has other storage engines, with their own design goals.

So, in choosing between InnoDB and MyISAM, the first step is in determining if you need the features provided by InnoDB. If not, then MyISAM is up for consideration.

A more detailed discussion of differences is rather impractical (in this forum) absent a more detailed discussion of the problem space... how the application will use the database, how many tables, size of the tables, the transaction load, volumes of select, insert, updates, concurrency requirements, replication features, etc.

---

The logical design of the database should be centered around data analysis and user requirements; the choice to use a relational database would come later, and even later would the the choice of MySQL as a relational database management system, and then the selection of a storage engine for each table.

Mysql: Joining InnoDB tables with MyISAM tables

We have a set of tables which contain the meta level data like organizations, organization users, organization departments etc. All these table are going to be read heavy with very few write operations. Also, the table sizes would be quite small (maximum number of records would be around 30K - 40K)

Another set of table store OLTP data like bill transactions, user actions etc which are going to be both read and write heavy. These tables would be quite huge (around 30 Million records per table)

For the first set of tables we are planning to go with MyISAM and for the second ones with InnoDb engine. Many of our features would also require JOINS on tables from these 2 sets.

Are there any performance issues in joining MyISAM tables with InnoDB tables? Also, are there any other possible issues (db backups, tuning etc) we might run into with this kind of design?

Any feedback would be greatly appreciated.

 Answers

What jumps out immediately at me is MyISAM.

ASPECT #1 : The JOIN itself

Whenever there are joins involving MyISAM and InnoDB, InnoDB tables will end up having table-level lock behavior instead of row-level locking because of MyISAM's involvement in the query and MVCC cannot be applied to the MyISAM data. MVCC cannot even be applied to InnoDB in some instances.

ASPECT #2 : MyISAM's Involvement

From another perspective, if any MyISAM tables are being updated via INSERTs, UPDATEs, or DELETEs, the MyISAM tables involved in a JOIN query would be locked from other DB Connections and the JOIN query has to wait until the MyISAM tables can be read. Unfortunately, if there is a mix of InnoDB and MyISAM in the JOIN query, the InnoDB tables would have to experience an intermittent lock like its MyISAM partners in the JOIN query because of being held up from writing.

Keep in mind that MVCC will still permit READ-UNCOMMITTED and REPEATABLE-READ transactions to work just fine and let certain views of data be available for other transactions. I cannot say the same for READ-COMMITTED and SERIALIZABLE.

ASPECT #3 : Query Optimizer

MySQL relies on index cardinality to determine an optimized EXPLAIN plan. Index cardinality is stable in MyISAM tables until a lot of INSERTs, UPDATEs, and DELETEs happen to the table, by which you could periodically run OPTIMIZE TABLE against the MyISAM tables. InnoDB index cardinality is NEVER STABLE !!! If you run SHOW INDEXES FROM *innodbtable*;, you will see the index cardinality change each time you run that command. That's because InnoDB will do dives into the index to estimate the cardinality. Even if you run OPTIMIZE TABLE against an InnoDB table, that will only defragment the table. OPTIMIZE TABLE will run ANALYZE TABLE internally to generate index statistics against the table. That works for MyISAM. InnoDB ignores it.

My advice for you is to go all out and convert everything to InnoDB and optimize your settings accordingly.

Believe it or not, there is still an open ticket on InnoDB/MyISAM joining during a SELECT FOR UPDATE. If you read it, it sums up the resolution as follows : DON'T DO IT !!!.

Mysql: Bogus foreign key constraint fail

I get this error message:

ERROR 1217 (23000) at line 40: Cannot delete or update a parent row: a foreign key constraint fails

... when I try to drop a table:

DROP TABLE IF EXISTS `area`;

... defined like this:

CREATE TABLE `area` (
  `area_id` char(3) COLLATE utf8_spanish_ci NOT NULL,
  `nombre_area` varchar(30) COLLATE utf8_spanish_ci NOT NULL,
  `descripcion_area` varchar(100) COLLATE utf8_spanish_ci NOT NULL,
  PRIMARY KEY (`area_id`),
  UNIQUE KEY `nombre_area_UNIQUE` (`nombre_area`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

The funny thing is that I already dropped all other tables in the schema that have foreign keys against area. Actually, the database is empty except for the area table.

How can it possibly have child rows if there isn't any other object in the database? As far as I know, InnoDB doesn't allow foreign keys on other schemas, does it?

(I can even run a RENAME TABLE area TO something_else command :-?)

 Answers

Two possibilities:

1. There is a table within another schema ("database" in mysql terminology) which has a FK reference
2. The innodb internal data dictionary is out of sync with the mysql one.

You can see which table it was (one of them, anyway) by doing a "SHOW ENGINE INNODB STATUS" after the drop fails.

If it turns out to be the latter case, I'd dump and restore the whole server if you can.

MySQL 5.1 and above will give you the name of the table with the FK in the error message.

---

Disable foreign key checking

SET FOREIGN_KEY_CHECKS=0

---

hopefully its work

SET foreign_key_checks = 0; DROP TABLE table name; SET foreign_key_checks = 1;

---

Maybe you received an error when working with this table before. You can rename the table and try to remove it again.

ALTER TABLE `area` RENAME TO `area2`;
DROP TABLE IF EXISTS `area2`;

---

Cannot delete or update a parent row: a foreign key constraint fails (table1.user_role, CONSTRAINT FK143BF46A8dsfsfds@#5A6BD60 FOREIGN KEY (user_id) REFERENCES user (id))

What i did in two simple steps . first i delete the child row in child table like

mysql> delete from table2 where role_id = 2 && user_id =20;

Query OK, 1 row affected (0.10 sec)

and second step as deleting the parent

delete from table1 where id = 20;

Query OK, 1 row affected (0.12 sec)

By this i solve the Problem which means Delete Child then Delete parent

i Hope You got it. :)