Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've got a problem setting innodb as the default database engine in MySQL 5.1 on Ubuntu 10.04. As soon as I switch to innodb as default engine the server refuses to start. This is what I get in the syslog when I try to start MySQL after enabling innodb:

Oct  6 21:18:29 artemis init: mysql main process ended, respawning
Oct  6 21:18:58 artemis init: mysql post-start process (5825) terminated with status 1
Oct  6 21:18:59 artemis init: mysql main process (5943) terminated with status 7
Oct  6 21:18:59 artemis init: mysql main process ended, respawning
Oct  6 21:19:28 artemis init: mysql post-start process (5944) terminated with status 1
Oct  6 21:19:29 artemis init: mysql main process (6056) terminated with status 7
Oct  6 21:19:29 artemis init: mysql main process ended, respawning
Oct  6 21:19:58 artemis init: mysql post-start process (6057) terminated with status 1
Oct  6 21:20:00 artemis init: mysql main process (6163) terminated with status 7
Oct  6 21:20:00 artemis init: mysql main process ended, respawning
Oct  6 21:20:28 artemis init: mysql post-start process (6164) terminated with status 1
Oct  6 21:20:30 artemis init: mysql main process (6239) terminated with status 7
Oct  6 21:20:30 artemis init: mysql main process ended, respawning
Oct  6 21:20:59 artemis init: mysql post-start process (6240) terminated with status 1
Oct  6 21:21:00 artemis init: mysql main process (6358) terminated with status 7
Oct  6 21:21:00 artemis init: mysql main process ended, respawning
Oct  6 21:21:29 artemis init: mysql post-start process (6359) terminated with status 1
Oct  6 21:21:30 artemis init: mysql main process (6434) terminated with status 7
Oct  6 21:21:30 artemis init: mysql main process ended, respawning
Oct  6 21:21:59 artemis init: mysql post-start process (6435) terminated with status 1
Oct  6 21:22:01 artemis init: mysql main process (6540) terminated with status 7
Oct  6 21:22:01 artemis init: mysql main process ended, respawning

In attempt to remove any interfering programs I've uninstalled apparmor and rebooted. After doing a clean install, actually deleting the dirs /etc/mysql and /var/lib/mysql to be sure no stale files are lying around, the server starts just fine with MyIsam as the default engine. InnoDB is among the engines available when showing engines:

+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| 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         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

The InnoDB configuration I've tried with and that is appended under [mysqld] in the default my.cnf that comes with the mysql-server-5.1 package looks like this:

# TEST #####################################################

init_connect='SET collation_connection = utf8_general_ci; SET NAMES utf8;'

default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

max_allowed_packet = 16M

# Network buffer length (I think this is the linux default)
#net_buffer_length = 8K

# Query caching
query_cache_type = 1

# Default table storage engine when creating new tables
# (comment out when running mysql_install_db)
#default_storage_engine=InnoDB
#default_table_type=InnoDB

# Default transaction isolation level, levels available are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# see: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
transaction_isolation = READ-COMMITTED

innodb_data_file_path = ibdata1:50M:autoextend


# TEST #####################################################

I can add the snippet above without causing any hassle, but as soon as I uncomment default_storage_engine=InnoDB or default_table_type=InnoDB the server won't start. After commenting the lines again and doing a restart InnoDB is missing among the engines listed.

    mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | 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         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

What am I doing wrong, what is missing? Any hint that can shed some light on this is appreciated.

Cheers! // John

share|improve this question
    
By just adding default-storage-engine = innodb to my.cnf I can get the server to use InnoDB as default engine, but I can't change add the innodb_data_file_path = ibdata1:20M:autoextend or the innodb_log_file* directives. Regarding the innodb log files I know that it is a bit picky if one changes the size and try to start mysql and that one can not change the InnoDB file size might be reasonable. –  John P Oct 6 '10 at 20:56

1 Answer 1

up vote 1 down vote accepted

I install MySQL server(Ubuntu 10.04):

apt-get install mysql-server

Then check default engine(MyISAM):

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| 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         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

Then change /etc/mysql/my.cnf - add default-storage-engine = innodb in mysqld section:

[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking

default-storage-engine  = innodb

It works:

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| 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         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | YES     | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
share|improve this answer
    
That is exactly how I did to get it up running. After just setting the default-storage-engine=innodb I added new directives one by one. I vote on your answer as it is the rght way to go. Thanks! –  John P Oct 6 '10 at 21:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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