Topic: Mysql won't switch storage engine to innodb

[jodahall]

I am having a problem switching storage engines in mysql, everything seems to work fine but it just doesn't switch from myISAM to InnoDB am i doing something wrong ?

here is what i did

enabled the InnoDB engine in mysql using the following commands

db configuration setprop mysqld innodb enabled
expand-template /etc/my.cnf
sv t /service/mysqld

in the /var/log/messages it looks like it worked

Jun 22 08:35:13 fubar /sbin/e-smith/db[28160]: /home/e-smith/db/configuration: OLD mysqld=service|LocalNetworkingOnly|yes|status|enabled
Jun 22 08:35:13 fubar /sbin/e-smith/db[28160]: /home/e-smith/db/configuration: NEW mysqld=service|LocalNetworkingOnly|yes|innodb|enabled|status|enabled

I am running sme server 7.3, my mysql version is 4.1.20.  from everything i've read that version supports InnoDB

the command i used to change and check if it changed was

mysql> alter table pools engine=innodb;
Query OK, 3 rows affected, 1 warning (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table pools
    -> ;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                               |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pools | CREATE TABLE `pools` (
  `pool_id` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`pool_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

mysql> show warnings;
Empty set (0.00 sec)

[cactus]

The only thing you should be doing is something like this:

Code: [Select]

ALTER TABLE t1 ENGINE = InnoDB;
Mind you that statements might be case sensitive. Furthermore you should investigate what the warning is when you receive one, as you seem to receive one when you try to alter the table engine:

Code: [Select]

mysql> alter table pools engine=innodb;
Query OK, 3 rows affected, 1 warning (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> show warnings;
Empty set (0.00 sec)

Furthermore you should use the show warnings statement immediately after a statement that shows a warning according to the manual:

SHOW WARNINGS shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session. It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.) Statements that do not use tables and do not generate messages have no effect on the message list.

The last thing you could do to retrieve the warning message is analyze the MySQL logfiles as they most certainly will be logged there, they can be found in /var/log/mysqld/. Try and locate the error around the time you tried and see if that yields any clues. Please post back your findings and eventual solution for future readers.

[jodahall]

Thanx for the reply.

I looked at the proper warnings and it gave me a warning number which is searched on (1266) and i found a post that told me to look at the my.cnf file and sure enough it shows "skip-innodb".

I did the commands earlier to enable innodb and it looked like it worked in the /var/log/messages but somehow it didn't...

Is there something i did wrong with the commands i listed in my first post ?

I looked int the /var/log/mysqld and it doesn't show anything useful.  in fact the current log is only 8 lines long, all the rest of the logs are at least 6 months old.

@4000000049fe0428086c6134 /usr/libexec/mysqld: ready for connections.
@4000000049fe0428086c845c Version: '4.1.20'  socket: '/var/lib/mysql/mysql.sock'  port: 0  Source distribution
@400000004a3f96c214b51bfc 090622  8:35:36 [Note] /usr/libexec/mysqld: Normal shutdown
@400000004a3f96c214b5430c
@400000004a3f96c31a55a554 090622  8:35:37 [Note] /usr/libexec/mysqld: Shutdown complete
@400000004a3f96c31a55c494
@400000004a3f96c4013a1834 /usr/libexec/mysqld: ready for connections.
@400000004a3f96c4013a3b5c Version: '4.1.20'  socket: '/var/lib/mysql/mysql.sock'  port: 0  Source distribution

[jodahall]

Ok i looked some more and found out that the expand-template wasn't picking up the template properly.

what it would do is assume that the InnoDB engine wasn't enabled and it would put an entry into the my.cnf that said "skip-InnoDB"....I'm not sure why it was seeing that.  I don't even know where it was trying to find that information.  I did see a reference in the /var/log/messages that showed innodb was enabled.

to get around this i commented out the line in the template that did the check and when i did the expand-template command it added the innodb configuration to the my.cnf.

I know this isn't the right thing to do, i should have done some more troubleshooting but i may get to looking for the root cause a little later.  for now this has worked for me

thanx for your help it got me going in the right direction.

[Stefano]

Ok i looked some more and found out that the expand-template wasn't picking up the template properly.

what it would do is assume that the InnoDB engine wasn't enabled and it would put an entry into the my.cnf that said "skip-InnoDB"....I'm not sure why it was seeing that.  I don't even know where it was trying to find that information.  I did see a reference in the /var/log/messages that showed innodb was enabled.

to get around this i commented out the line in the template that did the check and when i did the expand-template command it added the innodb configuration to the my.cnf.

I know this isn't the right thing to do, i should have done some more troubleshooting but i may get to looking for the root cause a little later.  for now this has worked for me

thanx for your help it got me going in the right direction.

you said, this is the wrong way..

please raise a bug giving all the info, thank you

Stefano

[johnp]

I would check your db configuration

db configuration setprop mysqld innodb enabled

should read db configuration setprop mysqld InnoDB enabled

[cactus]

I would check your db configuration  should read db configuration setprop mysqld InnoDB enabled

I already told you some things might be case sensitive, and indeed this one is (from /etc/e-smith/templates/etc/my.cnf/009innodb):

Code: [Select]

my $innodb = $mysqld{InnoDB} || 'disabled';

    return "skip-innodb" unless $innodb eq 'enabled';
So to fix your issues:

Code: [Select]

db configuration delprop mysqld innodb
db configuration setprop mysqld InnoDB enabled
expand-template /etc/my.cnf
sv t /service/mysqld