0

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

Improve this question
1
  • 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, 2010 at 20:56

1 Answer 1

Reset to default
1

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         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
Improve this answer
1
  • 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, 2010 at 21:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .