mysql: where is innodb ?

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

Engines :
Code:
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
Code:
# 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

Administrator
Staff member
Administrator
Moderator
It certainly has them:
Code:
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>
 
I posted it in my first post :
Code:
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

Administrator
Staff member
Administrator
Moderator
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.
 
cd /usr/ports/databases/mysql51-server ; make install clean

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

DutchDaemon

Administrator
Staff member
Administrator
Moderator
Developer
I found this rather mystifying comment in /usr/local/share/mysql/ChangeLog:

Code:
    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

Administrator
Staff member
Administrator
Moderator
Developer
Wait, got it:

/usr/local/lib/mysql/plugin

Partial contents:

Code:
-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

Administrator
Staff member
Administrator
Moderator
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

Administrator
Staff member
Administrator
Moderator
Developer
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

Administrator
Staff member
Administrator
Moderator
Developer
Extremely specific ;)

Code:
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:

Code:
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

Administrator
Staff member
Administrator
Moderator
It looks like it got created when I installed my own build package:
Code:
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:
Code:
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

Administrator
Staff member
Administrator
Moderator
Developer
Ditto for newer version:

Code:
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

Administrator
Staff member
Administrator
Moderator
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.
 
hmmm.
Code:
% 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

Administrator
Staff member
Administrator
Moderator
Developer
Slowly running out of options ... ;)

What gives [cmd=]ldconfig -r | grep sql[/cmd]?
 
Code:
 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

Administrator
Staff member
Administrator
Moderator
Developer
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

Administrator
Staff member
Administrator
Moderator
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?
 
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:

Code:
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3

Commented all three and voilà ... InnoDB was there!
 
Hi guys,

it seems like I have the same problem...:
Code:
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?
 
After some searching I found the problem:
Code:
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:
Code:
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)
 
Top