question re: converting from myISAM to InnoDB

[LiquidFractal]

Hello,

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:

Quote

 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.

[ASTRAPI]

First backup your my.cnf settings and then adjust:
 

innodb_large_prefix=1
innodb_file_format = Barracuda
innodb_file_per_table = 1

 

then restart Mysql and run:

ALTER TABLE liquidfr_INVISION.cms_databases ENGINE = InnoDB ROW_FORMAT=DYNAMIC;

 

Enjoy

[LiquidFractal]

Thank you very much @ASTRAPI

[LiquidFractal]

@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.

EDIT:

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?

[ASTRAPI]

You must run the same command for all tables that are not converting to Innodb.

The my.cnf file is located most of the times at /etc/my.cnf

Try to do the changes there...

[LiquidFractal]

@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
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
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:

Quote

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.

[ASTRAPI]

You must run the same command for all tables that are not converting to Innodb.

ALTER TABLE tablenamehere ENGINE = InnoDB ROW_FORMAT=DYNAMIC;

Just change the "tablenamehere" with the table name that you want to convert and has this issue.

[LiquidFractal]

@ASTRAPI That worked like a charm.  Entire DB is now innoDB.  Thank you for the awesome help!

[LiquidFractal]

Just a brief update to anyone following this thread: converting from myISAM to InnoDB bumped my DB size up from 58 to 72MB, but even connecting to my SE USA server location from Melbourne area, Australia, I can tell that things actually load faster.

[ASTRAPI]

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

[LiquidFractal]

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?

[ASTRAPI]

Hi

Optimization of Mysql is a part of what i offer as a service so i can't help more on that sorry

Other users may reply on that and get some help ....Just wait a bit....

Thank you