Definitive guide to InnoDB vs myISAM and utf8 vs utf8mb4

[Ocean West]

for some time I have had a mix of InnoDB and myISAM tables it seems my server default is myISAM how do I change it to default to innoDB and to convert existing tables to innoDB in one shot not have to use phpmyadmin one table at a time. 

Also all my tables are currently utf8_unicode_ci  and trying to also figure out if there is a similar means to convert the tables to utf8mb4_unicode_ci

so my request is what is the pros and cons to each and the step by step guide to alter these items for best recommended configuration. 

and should these things be one before or after upgrading to 4.1?  

[sobrenome]

I am also interested in a guide to convert utf8_unicode_ci in utf8mb4_unicode_ci.

[ipbfuck]

i've convert just now with phoca converter becouse ipb utility (iun marketplace and/or in admin directory) go with timeout error in my hosting.

then, i've download a copy of database, put in local webserver, run phoca to convert, rename all table in server with old_* prefix and immediately fix in config for "no offline website"; upload new table (without prefix, but now converted) in server; remove prefix from config and verify if website is still online with new table in utf8mb4.

after some test, i've delete all old_* table prefix and verify with ipb utility if all result ok (and yes, seems to be ok)

 

no downtime, no problem at all!

i want try now to convert to innodb via php with something like this:

<?php
$db = 'mydatabase';
$dsn = "mysql:host=localhost;dbname=$db";
$username = "user";
$password = "password";

$from = 'MyISAM';
$to = 'INNODB';

try {
    $pdo = new PDO($dsn, $username, $password);
}
catch(PDOException $e) {
    die("Could not connect to the database\n");
}

$result = $pdo->query("
	SELECT TABLE_NAME
	FROM information_schema.TABLES
	WHERE TABLE_SCHEMA = '$db'
	AND ENGINE = '$from'
");

foreach($result as $row) {
	$success = $pdo->exec("ALTER TABLE {$row['TABLE_NAME']} ENGINE = $to");
	if($success) {
		echo "{$row['TABLE_NAME']} - success<br>\n";
	}
	else {
		$info = $pdo->errorInfo();
		echo "{$row['TABLE_NAME']} - failed: $info[2]<br>\n";
	}
}

and seems to work for most table, except for 4!

*_cms_databases - failed: Specified key was too long; max key length is 767 bytes
*_cms_page_widget_areas - failed: Specified key was too long; max key length is 767 bytes
*_core_search_index - failed: Specified key was too long; max key length is 767 bytes
*_core_sitemap - failed: Specified key was too long; max key length is 767 bytes 

 

i want try to fix soon (maybe with check difference from dev install)!

[ipbfuck]

After migration in new hosting, now with php7 and mariadb, i've try again to run this script, but this tables continue to remain in myisam:

*_cms_databases - failed: Specified key was too long; max key length is 767 bytes
*_cms_page_widget_areas - failed: Specified key was too long; max key length is 767 bytes
*_core_search_index - failed: Specified key was too long; max key length is 767 bytes
*_core_sitemap - failed: Specified key was too long; max key length is 767 bytes 

someone can help me on this please?

i don't know fine databases etc for fix this...

-

maybe i can try to flush "search and sitemap", then convert, and hope that ipb restore this automatically... !?

but remain "databases and widget"... i think is best to not touch this if i do not know fine how do...

thanks in advance,

S.N.

[BomAle]

change engine on innodb decrease index limit to 768... the key with varchar(255) or up to 191 stop execution of query.. to solve it find varchar and try to replace "column" to name of it (191*4=764 when utf8mb4)

ALTER TABLE `mytable` ADD UNIQUE ( column(191) );

or

ALTER TABLE table_name MODIFY column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

OR before run your script try to set global var 

set global innodb_large_prefix = ON;

this increase to 3072

UNTESTED

[ipbfuck]

thank'u very much @BomAle

i'll try and report as soon as possible.

 

regards...

[ipbfuck]

set global innodb_large_prefix = ON;

cannot work for me (no global permission in my hosting)...

but ive click on every single table, search varchar 255 in "structure", and change to 191 (only cms_database - and maybe also core_search_index - with more than one to 255... all other just one).

PS: for test, just after primary from 255 to 191, i've change with success to innodb (from php my admin > operation > table option) ... anyway, i've change this varchar value in all column, is ok?

all other varchar to 250 can be valid?

[ipbfuck]

this manual fix has caused some error in this table (now if i run "check something no work etc, i receive database issue report for this tables).

This is a dump of this tables (valid, by backup - only structure):

... // remove!

atm i reimport my backup of this tables... if someone can help me to fix...

 

[ipbfuck]

fixed!

test in local dev (all tables are innodb here): delete this tables, recreated from support tool in acp (only search and sitemap) - then, for this two, i've make the same in my live website (all ok)!

Other two:

*_cms_databases - failed: Specified key was too long; max key length is 767 bytes
*_cms_page_widget_areas 

download a sql backup from local dev, merge structure from dev (innodb) with data from livesite (myisam); imported without problem

 

run, just to be sure, support tool... nothing to report and no all tables are innodb with utf8mb4

[ipbfuck]

Warning: i think is best to not try this...

seems to be ok, in realty i can't more transfer my table from one database to other (cause of this tables).

then, i've restored (just this tables) from database backup, and all work fine again.

-

maybe caused by different db release, but - stay alert - make many makup before try

 

[sobrenome]

I still don't understand why IPS does not offer an script for this convertion.

[ipbfuck]

me too... we have utf8 to utf8mb4 (/admin/convertutf8/), but not myisam to innodb :/

 

[sobrenome]

Do we have utf8 to utf8mb4?

[ipbfuck]

i've convert to utf8mb4 with phoca tool + ipbinstall.**/admin/convertutf8/ utility (just to be sure all is done ok)