Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
+1 for question and answer. It shows how important the config headings are. –  Derek Downey Oct 21 '11 at 12:48
2  
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! –  Dave Rix Oct 21 '11 at 13:04
    
Feels great to find your own answer, eh ??? +1 !!! –  RolandoMySQLDBA Oct 21 '11 at 15:22
add comment

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.