# mysql: where is innodb ?



## nORKy (Sep 29, 2009)

I have a problem with mysql 5.1.39
mysql_upgrade :
Error    : Unknown table engine 'InnoDB'

Engines :

```
MyISAM	Default engine as of MySQL 3.23 with great performance
MRG_MYISAM	Collection of identical MyISAM tables
BLACKHOLE	/dev/null storage engine (anything you write to it disappears)
CSV	CSV storage engine
MEMORY	Hash based, stored in memory, useful for temporary tables
FEDERATED	Federated MySQL storage engine
ARCHIVE	Archive storage engine
```

So, where is innodb ????

My database are (was ?) innodb and now, their engine is "View". I can't select,write,show xx => nknown table engine 'InnoDB' 

my make.conf

```
# added by use.perl 2009-08-17 19:27:06
PERL_VERSION=5.8.9

WITH_CHARSET=utf8
WITH_COLLACTION=utf8_general_ci
```

my my.cnf come from share/my-small.cnf with innodb lines uncommented

Thanks you


----------



## SirDice (Sep 29, 2009)

It certainly has them:

```
Server version: 5.1.38 FreeBSD port: mysql-server-5.1.38

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines \G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
8 rows in set (0.02 sec)

mysql>
```


----------



## nORKy (Sep 29, 2009)

I posted it in my first post :

```
ysql> show engines \G
*************************** 1. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
7 rows in set (0.00 sec)

mysql>
```


----------



## SirDice (Sep 29, 2009)

Did you compile the port yourself or did you use a package?

AFAIK there are no options to turn InnoDB off during build.

FWIW I don't even have a my.cnf.


----------



## nORKy (Sep 29, 2009)

cd /usr/ports/databases/mysql51-server ; make install clean

I don't find innodb options too.. that's why hope to find help here


----------



## SirDice (Sep 29, 2009)

Try starting it without the my.cnf file in place.


----------



## nORKy (Sep 29, 2009)

(mv my.cnf my.old ; mysql-server restart)

no changes.


----------



## DutchDaemon (Sep 29, 2009)

I found this rather mystifying comment in /usr/local/share/mysql/ChangeLog:


```
revno: 2949.7.9
    tags: mysql-5.1.38
    committer: Jonathan Perkin <-@->
    branch nick: mysql-5.1.38-release
    timestamp: Tue 2009-09-01 08:40:13 +0200
    message:
      [I]Only install the innodb plugin if the plugin directory exists[/I].
```

I have no idea which plugin directory is meant.


----------



## DutchDaemon (Sep 29, 2009)

Wait, got it:

/usr/local/lib/mysql/plugin

Partial contents:


```
-rw-r--r--  1 root  wheel  1879604 Sep 21 13:25 ha_innodb_plugin.a
-rwxr-xr-x  1 root  wheel     1007 Sep 21 13:25 ha_innodb_plugin.la
lrwxr-xr-x  1 root  wheel       21 Sep 21 13:25 ha_innodb_plugin.so -> ha_innodb_plugin.so.0
-rwxr-xr-x  1 root  wheel  1320623 Sep 21 13:25 ha_innodb_plugin.so.0
```

So, my best guess: create /usr/local/lib/mysql/plugin and reinstall mysql-server.


----------



## SirDice (Sep 29, 2009)

That's weird. I recently build and installed 5.1.38 and I'm pretty sure I didn't have that directory. I do have InnoDB support though.


----------



## DutchDaemon (Sep 29, 2009)

I saw a mysql-server-5.0.86 installation elsewhere that does have InnoDB support and no plugin directory at all -- not even those innodb libraries anywhere . I don't know what kind of 5.1-specific plugin madness this is ... and how well thought out it is by Sun (who committed this message).


----------



## DutchDaemon (Sep 29, 2009)

Extremely specific 


```
for dir in mysql323-server mysql40-server mysql41-server mysql50-server mysql51-server mysql54-server mysql60-server; do grep -l innodb_plugin $dir/pkg-plist; done

mysql51-server/pkg-plist
```

Though plugins are less specific, apparently:


```
for dir in mysql323-server mysql40-server mysql41-server mysql50-server mysql51-server mysql54-server mysql60-server; do grep -l lib/mysql/plugin/ $dir/pkg-plist; done
mysql51-server/pkg-plist
mysql54-server/pkg-plist
mysql60-server/pkg-plist
```

But only 51-server contains the specific innodb libs.


----------



## SirDice (Sep 29, 2009)

It looks like it got created when I installed my own build package:

```
root@molly:~#ll -d /usr/local/lib/mysql/plugin
drwxr-xr-x  2 root  wheel  512 Sep 18 13:39 /usr/local/lib/mysql/plugin
root@molly:~#ll -d /var/db/pkg/mysql-server-5.1.38/
drwxr-xr-x  2 root  wheel  512 Sep 18 13:39 /var/db/pkg/mysql-server-5.1.38/
```

And I do have those files:

```
root@molly:~#ll /usr/local/lib/mysql/plugin
total 3240
-rw-r--r--  1 root  wheel    35782 Sep 18 12:50 ha_example.a
-rwxr-xr-x  1 root  wheel      965 Sep 18 12:50 ha_example.la
lrwxr-xr-x  1 root  wheel       15 Sep 18 12:50 ha_example.so -> ha_example.so.0
-rwxr-xr-x  1 root  wheel    31950 Sep 18 12:50 ha_example.so.0
-rw-r--r--  1 root  wheel  1879532 Sep 18 12:50 ha_innodb_plugin.a
-rwxr-xr-x  1 root  wheel     1007 Sep 18 12:50 ha_innodb_plugin.la
lrwxr-xr-x  1 root  wheel       21 Sep 18 12:50 ha_innodb_plugin.so -> ha_innodb_plugin.so.0
-rwxr-xr-x  1 root  wheel  1320591 Sep 18 12:50 ha_innodb_plugin.so.0
```

I had build the package a few minutes before I installed it, which probably accounts for the differences in time.


----------



## DutchDaemon (Sep 29, 2009)

Ditto for newer version:


```
drwxr-xr-x  2 root  wheel  512 Sep 21 13:25 /var/db/pkg/mysql-server-5.1.39/
drwxr-xr-x  2 root  wheel  512 Sep 21 13:25 /usr/local/lib/mysql/plugin
```

Not sure whether that plugin dir was created or updated (timestamped) though.


----------



## SirDice (Sep 29, 2009)

Seeing as 5.1.39 is out, I'll try to update mine tonight. I'll remove the old package _and_ the plugin dir before installing the new one.


----------



## nORKy (Sep 29, 2009)

hmmm.

```
% ls -la /usr/local/lib/mysql/plugin 
total 3693
drwxr-xr-x  2 root  wheel       10 Sep 29 10:41 .
drwxr-xr-x  3 root  wheel       19 Sep 29 10:41 ..
-rw-r--r--  1 root  wheel    50924 Sep 29 10:41 ha_example.a
-rwxr-xr-x  1 root  wheel      965 Sep 29 10:41 ha_example.la
lrwxr-xr-x  1 root  wheel       15 Sep 29 10:41 ha_example.so -> ha_example.so.0
-rwxr-xr-x  1 root  wheel    44990 Sep 29 10:41 ha_example.so.0
-rw-r--r--  1 root  wheel  2321998 Sep 29 10:41 ha_innodb_plugin.a
-rwxr-xr-x  1 root  wheel     1007 Sep 29 10:41 ha_innodb_plugin.la
lrwxr-xr-x  1 root  wheel       21 Sep 29 10:41 ha_innodb_plugin.so -> ha_innodb_plugin.so.0
-rwxr-xr-x  1 root  wheel  1291647 Sep 29 10:41 ha_innodb_plugin.so.0
```

Plugin is here.
Date, Sep 29 10:41, I upgraded mysql today. (before my first post)


----------



## DutchDaemon (Sep 29, 2009)

Slowly running out of options ... 

What gives [cmd=]ldconfig -r | grep sql[/cmd]?


----------



## SirDice (Sep 30, 2009)

Ok.. I upgraded my mysql to 5.1.39 and InnoDB is working.


----------



## nORKy (Sep 30, 2009)

```
ldconfig -r | grep sql
	search directories: /lib:/usr/lib:/usr/lib/compat:/usr/local/lib:/usr/local/lib/compat/pkg:/usr/local/lib/mysql:/usr/local/lib/zsh
	188:-lsqlite3.8 => /usr/local/lib/libsqlite3.so.8
	191:-lmysqlclient.16 => /usr/local/lib/mysql/libmysqlclient.so.16
	192:-lmysqlclient_r.16 => /usr/local/lib/mysql/libmysqlclient_r.so.16
```


----------



## DutchDaemon (Sep 30, 2009)

Same ldconfig results as over here, and the previous post by SD suggests that the plugins directory gets created and populated on the fly; and you already have it and its contents. Can't find any way to disable the plugin either, and you already tried without the config file. Ran out of ideas here, sorry ...


----------



## SirDice (Oct 1, 2009)

Same here, out of ideas...

The only thing I can think of are the mysql_* variables in /etc/rc.conf. I only have mysql_enable and mysql_dbdir defined. Nothing more.

Are there any clues in the mysql error log?


----------



## MarS (Jan 28, 2010)

I use MySQL 5.5.1 and I was also missing the InnoDB engine. Setting one of these three variables in /etc/my.cnf resulted in InnoDB not being available:


```
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
```

Commented all three and voilÃ  ... InnoDB was there!


----------



## myha (Mar 27, 2010)

Hi guys,

it seems like I have the same problem...:

```
mysql-server-5.1.45

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)

-rw-r--r--  1 root  wheel  1902668 Mar 19 10:01 ha_innodb_plugin.a
-rwxr-xr-x  1 root  wheel     1007 Mar 19 10:01 ha_innodb_plugin.la
lrwxr-xr-x  1 root  wheel       21 Mar 19 10:01 ha_innodb_plugin.so -> ha_innodb_plugin.so.0
-rwxr-xr-x  1 root  wheel  1336033 Mar 19 10:01 ha_innodb_plugin.so.0
```

Has anyone found any solution?


----------



## myha (Mar 27, 2010)

After some searching I found the problem:

```
InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
100327 15:07:55 [ERROR] Plugin 'InnoDB' init function returned error.
100327 15:07:55 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
```
So I removed all ib_* files from /var/db/mysql and now eerything seems to be fine:

```
100327 15:08:43  InnoDB: Started; log sequence number 0 0
100327 15:08:43 [Note] Event Scheduler: Loaded 0 events

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         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
```


----------

