Make WordPress Core

Opened 6 years ago

Last modified 13 months ago

#43416 new feature request

Function to Maybe Convert Tables to InnoDB

Reported by: bhubbard's profile bhubbard Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Upgrade/Install Keywords:
Focuses: performance Cc:

Description

We already have a function called maybe_convert_table_to_utf8mb4(), so I think it might make sense to have one to convert the table engine to InnoDB. This could be really useful for developer who want to convert tables they have added, and core can use it on core tables during upgrades.

We can have checks in place for making sure the install is running MySQL 5.6.4 or higher.

maybe_convert_table_to_innodb()

Change History (4)

#1 @swissspidy
6 years ago

and core can use it on core tables during upgrades.

Note that WordPress doesn't specify a database engine and just uses MySQL's default-storage-engine setting.

#2 @pento
5 years ago

  • Version trunk deleted

#3 @bhubbard
14 months ago

<?php
function maybe_convert_table_to_innodb($table_name) {
  global $wpdb;
  
  // Get the current storage engine for the table
  $current_engine = $wpdb->get_var(
    $wpdb->prepare('SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = %s', $table_name)
  );
  
  // Check if the table is already using InnoDB
  if ($current_engine === 'InnoDB') {
    return; // Nothing to do here
  }
  
  // Convert the table to InnoDB
  $wpdb->query(
    $wpdb->prepare('ALTER TABLE %s ENGINE=InnoDB', $table_name)
  );
}

class ConvertTableToInnoDBTest extends WP_UnitTestCase {
  
  public function setUp() {
    parent::setUp();
    
    // Create a test table using MyISAM engine
    global $wpdb;
    $wpdb->query("
      CREATE TABLE IF NOT EXISTS `wp_test_table` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(50) NOT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    ");
  }
  
  public function tearDown() {
    parent::tearDown();
    
    // Drop the test table
    global $wpdb;
    $wpdb->query("DROP TABLE IF EXISTS `wp_test_table`");
  }
  
  public function test_table_not_converted_if_already_using_innodb() {
    // Arrange
    global $wpdb;
    $table_name = 'wp_test_table';
    
    // Change the engine to InnoDB
    $wpdb->query("ALTER TABLE $table_name ENGINE=InnoDB");
    
    // Act
    maybe_convert_table_to_innodb($table_name);
    
    // Assert
    $this->assertEquals('InnoDB', $wpdb->get_var("SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$table_name'"));
  }
  
  public function test_table_is_converted_to_innodb() {
    // Arrange
    global $wpdb;
    $table_name = 'wp_test_table';
    
    // Act
    maybe_convert_table_to_innodb($table_name);
    
    // Assert
    $this->assertEquals('InnoDB', $wpdb->get_var("SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$table_name'"));
  }
  
}

In this example, we create a test table with the MyISAM engine in the setUp method and drop it in the tearDown method. We then write two tests: one to check if the function correctly detects that the table is already using InnoDB and does nothing, and another to check if the function converts the table to InnoDB when needed.

We use global $wpdb object to execute SQL queries to create and manipulate tables and to run our assertions.

#4 @alexcumbers
13 months ago

What does this request actually do / how does it benefit ?

Note: See TracTickets for help on using tickets.