So I’m on Ubuntu 8.04 64bit and MySQL 5.0.51a. Today I decided I wanted to use InnoDB tables. But MySQL Administrator told me that InnoDB is not enabled currently.

So I first went into /etc/mysql/my.cnf and removed the skip-innodb variable, and added a few other performance oriented settings for using InnoDB tables and restarted MySQL. But alas it still wouldn’t work.

When I tried to create a table MySQL told me “Warning: Using storage engine MyISAM for table …”

and my log had all of this weird stuff in it:

mysqld[22162]: 080516 13:30:21 [Warning] Changed limits: max_open_files: 1024  max_connections: 100  table_cache: 457
mysqld[22162]: InnoDB: Error: data file /var/lib/mysql/ibdata1 is of a different size
mysqld[22162]: InnoDB: 640 pages (rounded down to MB)
mysqld[22162]: InnoDB: than specified in the .cnf file 128000 pages!
mysqld[22162]: InnoDB: Could not open or create data files.
mysqld[22162]: InnoDB: If you tried to add new data files, and it failed here,
mysqld[22162]: InnoDB: you should now edit innodb_data_file_path in my.cnf back
mysqld[22162]: InnoDB: to what it was, and remove the new ibdata files InnoDB created
mysqld[22162]: InnoDB: in this failed attempt. InnoDB only wrote those files full of
mysqld[22162]: InnoDB: zeros, but did not yet use them in any way. But be careful: do not
mysqld[22162]: InnoDB: remove old data files which contain your precious data!

So I tried restarting MySQL again and saw these weird log messages:

mysqld[22396]: 080516 13:37:19 [Warning] Changed limits: max_open_files: 1024  max_connections: 100  table_cache: 457
mysqld[22396]: InnoDB: Error: log file /var/lib/mysql/ib_logfile0 is of different size 0 5242880 bytes
mysqld[22396]: InnoDB: than specified in the .cnf file 0 524288000 bytes!

Finally out of desperation I tried following the advice in the log files above “… and remove the new ibdata files InnoDB created in this failed attempt”.

So I went into /var/lib/mysql and deleted these three files: ibdata1, ib_logfile0, and ib_logfile1. Then I restarted MySQL. It failed to start but I restarted again and everything worked!

Yay! I can now use InnoDB files like a professional.

Posted by Greg Pinero (Primary Searcher) as Ubuntu, SQL at 1:28 PM MST

Comments Off

sudo kill -STOP [PID]

worked for me. I’m not sure why.

Posted by Greg Pinero (Primary Searcher) as Ubuntu at 9:45 AM MST

Comments Off