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)

thank you
no needed to be installed after done ?!

Works on 4.2 thanks :)

how to convert back?

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.

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.

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.

how to convert back?

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

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!

X vBulletin 3.8.12 by vBS Debug 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 (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01127 seconds Memory Usage 1,761KB 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 (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: