MySQL InnoDB Plugin default engine type not accepting config setting

Question

I have a strange MySQL InnoDB issue that I've been struggling with for a few hours now. I'm asking this here, and also on the main StackOverflow site - I will cross-post the answer to the other site when answered.

I have enabled the InnoDB plugin as a replacement for the built-in version, and setting the innodb_file_format and innodb_file_per_table are not having the required effect. The MySQL status variables still show Anaconda as the default engine.

Any suggestions would be gratefully received!

Relevant section of my.cnf settings

The settings below do not exist elsewhere in the config overriding these options.

innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_strict_mode = 1

Relevant entries in SHOW VARIABLES

This is how I know that Barracuda is available

| innodb_file_format | Antelope |
| innodb_file_format_check | Barracuda |
| innodb_file_per_table | OFF |
| innodb_strict_mode | OFF |
| innodb_version | 1.0.9 |

Section of MySQL Log

The log is also showing that Barracuda is available...

111020 18:10:13 mysqld_safe Starting mysqld daemon with databases from /.../mysql_data/
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
111020 18:10:13 InnoDB: highest supported file format is Barracuda.
111020 18:10:13 InnoDB Plugin 1.0.9 started; log sequence number 5609019
111020 18:10:13 [Note] Event Scheduler: Loaded 0 events
111020 18:10:13 [Note] /.../mysql/libexec/mysqld: ready for connections.
Version: '5.1.48-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution

Answer 1

I have finally found the answer, and it was [unsurprisingly] staring me in the face the whole time...

I had inherited the system from an earlier admin, who had made a mistake in the my.cnf file putting all the InnoDB specific parameters under a [innodb] heading, instead of under the [mysqld] heading where it should be.

Why did this take so long to locate? It was assumed that the config was working correctly in the first place. [slaps head]...

Incorrect file segment

[mysqld]
# Stuff here
[innodb]
# innodb stuff here

Correct file segment

[mysqld]
# Stuff here
# innodb stuff here

And now everything works as intended.

• Derek Downey: +1 for question and answer. It shows how important the config headings are.
• Dave Rix: Tell me about it! Having taken over the system which was already running with InnoDB, I was taken aback when on further investigation I found that all the buffer, log, config, etc settings were using the default values! Not good for current performance, but good for me, as I'm about to make this system fly with just a few minor tweaks!
• RolandoMySQLDBA: Feels great to find your own answer, eh ??? +1 !!!