Jump to content

question re: converting from myISAM to InnoDB

Recommended Posts


I am hoping the server/database experts out there can help a n00b with this. :)@ASTRAPI, hopefully you could say something about this?

I initially tried converting my myISAM database to innoDB for several reasons, but the result was:

ALTER TABLE liquidfr_INVISION.cms_databases ENGINE=InnoDB
MySQL said: 

#1071 - Specified key was too long; max key length is 767 bytes

So I contacted my VPS provider and they told me this:


 one possibility of circumnavigating this, if you are unable to modify and structure your tables as appropriate, is to enable [ innodb_large_prefix ] and change the [ innodb_file_format ] variable to "barracuda", along with enabling [ innodb_file_per_table ]. These would have to be global changes to the entire MySQL service and cannot be enacted on a per database case.

In short, I want to ask: could making these global changes do anything to irrevocably corrupt my existing myISAM database?  Needless to say I've already made a complete site backup, as well as a separate backup of my IPB database.  But I guess I'm wondering if anyone else here has gone through this process and, if so, have there been any negative repercussions?  My provider said they couldn't tell what possible ramifications there might be since they aren't Database Administrators, but I want to know if that's just them (understandably) covering themselves or if this could be a dangerous process?

As it turns out the newest version of Moodle also requires barracuda as well as certain other parameters to be set, so this conversion seems to have become a bit more important for me.

Thanks in advance. :)

Link to comment
Share on other sites

First backup your my.cnf settings and then adjust:

innodb_file_format = Barracuda
innodb_file_per_table = 1


then restart Mysql and run:



Enjoy :)

Link to comment
Share on other sites

@ASTRAPI I did everything you specified in the above reply.  However, there are about 14 tables in my IPS database that will not convert.  The reason is the same as above:

#1071 - Specified key was too long; max key length is 767 bytes

Now the edits to my.cnf - I assume they should be in the root folder of the website in question?  I have a VPS which hosts several sites, so I put your changes in the my.cnf for my site.


show variables like "%innodb_file%"

Variable_name              Value
innodb_file_format         Barracuda
innodb_file_format_check   ON
innodb_file_format_max     Barracuda
innodb_file_per_table      ON

So I assume everything is set up correctly, but maybe something has to be done to the old tables?

Link to comment
Share on other sites

@ASTRAPI unfortunately, my etc/my.cnf already has the changes implemented and I restarted my VPS myself to make sure the changes went through.

I ran the following SQL query:

SET @DATABASE_NAME = 'name_of_your_db';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
ORDER BY table_name DESC;

to acquire a list of tables in my DB that were still myISAM (the 14 tables that will not convert so far).  I then ran the resulting SQL query (again), but for every instance I keep getting the "Index column size too large. The maximum column size is 767 bytes" error specified above.

My VPS provider looked in to this and gave me the following response:


the reason you are receiving this error is due to most likely not specifying a primary key and by default InnoDB picks the first column as primary-key. A suggested fix for this issue would be to add an auto increment column to the table and mark it as the primary key.

Is there an easy, risk-free way to do this without risking my database?  If not, I guess my only alternative is to submit a support ticket.

Link to comment
Share on other sites

On 8/14/2017 at 8:14 PM, ASTRAPI said:

That's normal :)

Now you must optimize your my.cnf settings for Innodb like buffers e.t.c and it will perform much better than before :)

Thanks @ASTRAPI (sorry, I only now got to this).  Could you kindly offer some explanation as to what values to include/modify in my.cnf?  Or if there's a website that explains all this is there a link you could provide?  Thanks again :)

EDIT: I'm on CentOS 6.9 running Cpanel/WHM.  In WHM I notice a setting called "Allow cPanel & WHM to determine the best value for your MySQL innodb_buffer_pool_size configuration?", which is turned OFF.  Apparently, turning this on means that "cPanel & WHM will use your total number of tables to adjust the innodb_buffer_pool_size value during each MySQL restart."  Is this something I want?

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...