Referring to the Codex on "Creating Tables with Plugins", I can't find any reference on specifying the table storage engine during creation.

Consider the below function to create the table initially:

public function xy_plugin_install() {
        global $wpdb;
        global $xy_db_version;

        $table_name = $wpdb->prefix . 'xy_foobar';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
          id mediumint(9) NOT NULL AUTO_INCREMENT,
          email varchar(55) DEFAULT '' NOT NULL,
          postcode varchar(55) DEFAULT '' NOT NULL,
          message text NOT NULL,
          PRIMARY KEY  (id)
        ) $charset_collate;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta( $sql );

        add_option( 'xy_db_version', $xy_db_version );
      }

With the above, the table is set to MyISAM by default.

As a preference, the table should be set to InnoDB if the MySQL ver is greater than or equal to 5.6, as it has crash recovery built into the Storage Engine's initialization, amongst other advantages.

1. Would this be possible to explicitly specify during the initial creation?
2. And is there anyway to include a conditional check to determine if MySQL ver is greater or equal to ver 5.6?

Referring to the Codex on "Creating Tables with Plugins", I can't find any reference on specifying the table storage engine during creation.

Consider the below function to create the table initially:

public function xy_plugin_install() {
        global $wpdb;
        global $xy_db_version;

        $table_name = $wpdb->prefix . 'xy_foobar';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
          id mediumint(9) NOT NULL AUTO_INCREMENT,
          email varchar(55) DEFAULT '' NOT NULL,
          postcode varchar(55) DEFAULT '' NOT NULL,
          message text NOT NULL,
          PRIMARY KEY  (id)
        ) $charset_collate;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta( $sql );

        add_option( 'xy_db_version', $xy_db_version );
      }

With the above, the table is set to MyISAM by default.

As a preference, the table should be set to InnoDB if the MySQL ver is greater than or equal to 5.6, as it has crash recovery built into the Storage Engine's initialization, amongst other advantages.

1. Would this be possible to explicitly specify during the initial creation?
2. And is there anyway to include a conditional check to determine if MySQL ver is greater or equal to ver 5.6?

• php
• plugin-development
• database
• innodb

• "as it has crash recovery built into the Storage Engine's initialization, amongst other advantages." This is none of your plugin's business. It's up to the site owner/host what they want to use. – Jacob Peattie Commented Jan 12, 2018 at 2:29
• So to put it a better way, and for the sake of clarity (for other readers as well); "I have no control over this and therefore shouldn't concern myself with it". In other words it's not possible. Is that correct? – UncaughtTypeError Commented Jan 12, 2018 at 5:22
• Yeah, essentially. – Jacob Peattie Commented Jan 12, 2018 at 7:33
• Thank you Jacob, I'll consider that an answer (since it does technically address the question). If you post it as an answer, it will allow me to accept it, and could prove helpful for other readers in future as well. @JacobPeattie – UncaughtTypeError Commented Jan 13, 2018 at 10:43
• Simply add 'engine = InnoDB' after '$charset_collate' $charset_collate engine = InnoDB; – Tofandel Commented Jul 29, 2019 at 9:58

1 Answer 1

It is possible with MySQL, and it doesn't seem to create any problem with wordpress's dbDelta()

Here is the line you need to change

$charset_collate = $wpdb->get_charset_collate() . ' engine = innoDB';

You simply have to add engine = innoDB at the end of the SQL query and the correct engine will be used by MySQL