1

I have installed MySQL 5.6.10 on Mac OS X 10.6.5. My issue is that MySQL is using InnoDB as its default engine.

I have checked following engines are supported in MySQL:

show engines\G


*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO

I want to change it to MyISAM, so I have changed the setting in my.cnf:

[mysqld]
default-storage-engine=MyISAM

After that I restarted MySQL from the system preferences. But if I check check the engines again and create a table, it is still being created with InnoDB, not MyISAM.

Can anyone tell me how would I change it to MyISAM?

Here is my my.cnf file content:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
default-storage-engine=MyISAM

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# sql_mode=STRICT_TRANS_TABLES
Improve this question
1
  • Did you do a fresh installation of MySQL 5.6.10, or did you install MySQL 5.6.10 over /var/lib/mysql of MySQL 5.5 ?
    – RolandoMySQLDBA
    Commented Apr 30, 2013 at 17:46

2 Answers 2

Reset to default
2

MySQL 5.6.10 is quite new to me as well, so bear with me on this answer

I installed MySQL 5.6.10 on a Linux VM

I added default-storage-engine=MyISAM to my.cnf and restarted MySQL

It went from this:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

to 

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

No problem.

If you placed

default-storage-engine=MyISAM

in my.cnf, restarted MySQL and still the default is InnoDB, then there is only one explanation: You placed default-storage-engine=MyISAM in the wrong part of my.cnf

I once caught a mistake like that in a ServerFault question : Set group_concat_max_len permanently (MySQL config)

Make sure the option is under the [mysqld] section of my.cnf

[mysqld]
default-storage-engine=MyISAM

SUGGESTION

I think MyISAM in MySQL 5.6.10 is run as a plugin. Check your error logs for any plugin errors.

Also, do this:

In one SSH session, tail -f /var/log/mysqld.log

In another SSH session, service mysql restart

Look for any plugin messages associated with MyISAM. For example, note that when I ran a shutdown I saw these messages:

2013-02-26 15:20:13 1725 [Note] InnoDB: Starting shutdown...
2013-02-26 15:20:15 1725 [Note] InnoDB: Shutdown completed; log sequence number 1600309
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'CSV'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MEMORY'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MRG_MYISAM'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MyISAM'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'sha256_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'mysql_old_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'mysql_native_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'binlog'
2013-02-26 15:20:15 1725 [Note] /usr/sbin/mysqld: Shutdown complete

130226 15:20:15 mysqld_safe mysqld from pid file /var/lib/mysql/chtn-web2v.pid ended

Note that MyISAM is a plugin. If you do not see anything like

2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MyISAM'

your MyISAM plugin may have an issue with the Mac OS X version of MySQL.

Improve this answer
2
  • Can you please check the content of my.cnf file , i have updated the question
    – Hunt
    Commented Mar 1, 2013 at 4:08
  • 1
    IS there more than one my.cnf on the server ???
    – RolandoMySQLDBA
    Commented Mar 1, 2013 at 5:35
0

this one works

ALTER TABLE t1 ENGINE = InnoDB;

although you will have to make a series of this sql statements to change the engines of your tables.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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