vb.org Archive - Administrative and Maintenance Tools - MyISAM 2 InnoDB - Changes tables engine from MyISAM to InnoDB

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vBulletin 3.8 Add-ons (https://vborg.vbsupport.ru/forumdisplay.php?f=235)

- - Administrative and Maintenance Tools - MyISAM 2 InnoDB - Changes tables engine from MyISAM to InnoDB (https://vborg.vbsupport.ru/showthread.php?t=228846)

PlusVB

11-24-2009 10:00 PM

MyISAM 2 InnoDB - Changes tables engine from MyISAM to InnoDB

1 Attachment(s)

After reading Anders is blog: Part 1 vB4mance: Helping communities grow, performance data model changes in vB 4.0 Thanks Anders, I've decided to make a simple product to bring the power of InnoDB to anyone who wants it.

Just import the product and go to Maintenance->Update Counters and scroll down, more down, then you'll find Change tables engine to InnoDB

It can be used with 3.5 3.6 3.7 3.8 and 4.0, and I don't know if I should start a new thread in the "vBulletin 4.0 Add-ons".

Please make sure to back-up your database before doing this.

Enjoy.

profanitytalker

11-25-2009 12:37 PM

Thank you! I'll wait to see if its functioning properly. Don't want my db to become corrupted.

Kolbi

11-25-2009 05:39 PM

There are the advantages?

CHIPIT

11-25-2009 05:51 PM

...

Coop1979

11-26-2009 02:00 AM

How are you getting around the search issue?

Quote:

The main problem is that InnoDB does not support “fulltext” searching; this means that converting to InnoDB will break the default search engine in vBulletin 3. This is due to InnoDB requiring that “fulltext index” be dropped when the conversion occurs. No full text index, no search; it's as simple as that.

TheComputerGuy

11-26-2009 03:44 AM

<a href="http://www.yqed.com/mysql-storage-myisam-innodb-explained/" target="_blank">http://www.yqed.com/mysql-storage-my...odb-explained/</a>

This is a good read.

PlusVB

11-26-2009 07:40 AM

Quote:

Originally Posted by Coop1979 (Post 1920986)

How are you getting around the search issue?

I skiped any FULLTEXT table. if I'm going to make this product for vb 4.0. I may skip only (searchcore_text, searchgroup_text) and maybe language

actuality, I may not need to skip any table, as MySQL well refuse to convert any table with FULLTEXT inside it.

PlusVB

11-26-2009 08:04 AM

Quote:

Originally Posted by TheComputerGuy (Post 1921016)

http://www.yqed.com/mysql-storage-my...odb-explained/

This is a good read.

Will, this looks more real to me :)
http://www.mysqlperformanceblog.com/...sam-to-innodb/

gmerin

11-29-2009 03:00 PM

I have a few production systems which use innodb (the smallest is a 400gb reporting dbms). So far I only have two issues with them and I'm not sure how that would translate to vb usage: the resource overhead seems significantly higher for innodb, and , when necessary, innodb data recovery has a global impact.

resource overhead is an issue on my non-commercial vb system because i don't use a dedicated server and the demands of ajax database access alone kill it even using myisam.

the data recovery issue is more significant for me: when i have encountered a data structure corruption error (and it's not that frequent an occurrence; it only happened once in 2009) , the entire innodb database instance simply will not come up until the issue is addressed.

using myisam only the affected table(s) becomes unusable (not having foreign key constraints you just don't have the data integrity issues associated with foreign key violations), which makes recovery a simple matter of dropping the structure and reloading it from the most recent backup. since the entire innodb instance will not load when corruption is detected, recovering from a backup becomes unfeasible as reloading all 400GB of table data simply takes too long, so i employ snap mirroring with local protection (dbms redundancy). without these resources, i can't see using innodb with anything over 25gb. i would prefer to use oracle 10/11g for such systems, and as Oracle now owns innodb that's probably going to become the formal migration path over time, but i'll wait for oracle to add similar administrative functionality to innodb before i use it for larger databases.

PlusVB

11-29-2009 04:11 PM

gmerin!, haven't you tried to use "innodb_file_per_table"? No? Then you should read this one:
http://www.mysqlperformanceblog.com/...zation-basics/

saadessa

01-21-2010 09:46 PM

thank you iwill try

orok	10-16-2010 03:23 AM

thank you
no needed to be installed after done ?!

Mr GRiM

08-31-2012 08:18 PM

Works on 4.2 thanks :)

mmikel_atef

09-15-2012 07:32 AM

how to convert back?

Mr GRiM

10-14-2012 01:54 AM

1 Attachment(s)

Yeah it would be nice if this could be reversed but I don't think we are going to get any reply for this

Edit: I just had a go at reversing this by just swapping the names in the xml file and I think it worked OK, just make sure you backup your database first in case anything goes wrong, I am not a coder so if anyone want to have another go at this and make a proper one feel free, just wanted to give the option to reverse this for anyone who found this wasn't for them.

gabrielt

02-17-2013 12:16 AM

Thanks. I was looking for something like this for a while, and for some reason I didn't find this before. I will do some tests and post here is the performance of our forum increased or not.

gabrielt

02-18-2013 11:21 PM

With our very busy forums, converting to InnoDB made the CPU load on our MySQL server to go throught he roof. I had to revert back to MyISAM.

s_cocis

08-11-2016 01:33 PM

how to convert back?

kotkerk

01-12-2017 12:49 AM

create file cv.php with content:

PHP Code:


		
			
<?php

error_reporting(E_ALL & ~E_NOTICE);

define('THIS_SCRIPT', 'cv');

define('CSRF_PROTECTION', false);

require_once('./global.php');

function change_table_engine($engine_from, $engine_to, $show_output = false, $test_mode = false)

{

      global $vbulletin;

      $engine_from_lc = strtolower($engine_from);

      $engine_to_lc = strtolower($engine_to);

      $tables = $vbulletin->db->query_read("SHOW TABLE STATUS");

      while($temp = $vbulletin->db->fetch_array($tables))

      {

            if(strtolower($temp['Engine']) == $engine_from_lc AND $engine_from_lc != $engine_to_lc AND !in_array(strtolower($temp['Engine']), array('heap', 'memory')))

            {

                  if($show_output)

                  {

                        print "Alter Engine of table: $temp[Name] - $temp[Engine] to $engine_to<br />\n";

                        vbflush();

                  }

                  if(!$test_mode)

                  {

                        $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "{$temp[Name]} ENGINE = $engine_to");

                  }

            }

      }

}

change_table_engine('MyISAM', 'InnoDB', true);

?>

change args in the line

PHP Code:


		
			
change_table_engine('MyISAM', 'InnoDB', true);

1 arg = convert FROM
2 arg = convert TO
3 arg = show or no output (optional, default = false)
4 arg = test mode. if set to true - no changes in DB (optional, default = false)

close forum
make Data Base backup
upload file to forum root and execute in browser
confirm chages in DB
delete file
open forum

masterross

01-20-2019 02:21 PM

A little update of the script on vb 4.2.x:

PHP Code:


		
			
<?php
error_reporting(E_ALL & ~E_NOTICE);
define('THIS_SCRIPT', 'cv');
define('CSRF_PROTECTION', false);
require_once('./global.php');
function change_table_engine($engine_from, $engine_to, $show_output = false, $test_mode = false)
{
      global $vbulletin;
      $engine_from_lc = strtolower($engine_from);
      $engine_to_lc = strtolower($engine_to);
      $tables = $vbulletin->db->query_read("SHOW TABLE STATUS");
      while($temp = $vbulletin->db->fetch_array($tables))
      {
            if(strtolower($temp['Engine']) == $engine_from_lc AND $temp[Name] != 'vb_language' AND $engine_from_lc != $engine_to_lc AND !in_array(strtolower($temp['Engine']), array('heap', 'memory')))
            {
                  if($show_output)
                  {
                        print "Alter Engine of table: $temp[Name] - $temp[Engine] to $engine_to<br />\n";
                        vbflush();
                  }
                  if(!$test_mode)
                  {
                        $vbulletin->db->query_write("ALTER TABLE " . "{$temp[Name]} ENGINE = $engine_to");
                  }
            }
      }
}
change_table_engine('MyISAM', 'InnoDB', true);
?>

In this way it will exclude vb_language as it should stay in MyISAM.

Aldo this script should be run in AdminCP, not the root!
And if you have some big tables you should manually convert them trough shell console and after that run the script.

Regards!

All times are GMT. The time now is 07:14 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01170 seconds Memory Usage 1,793KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (3)bbcode_php_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (20)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: