The function works on MySQL server well but it can not create a table on the MariaDB server. What could be the problem?

    public function create_table() {
    $current_version = get_option('wpsm_db_table_version');
    if($current_version && $current_version == $this->db_version && $this->db->get_var("SHOW TABLES LIKE '$this->table_name'") == $this->table_name){
        return;
    }

    $sql = "CREATE TABLE ". $this->table_name ." (
            id bigint(20) unsigned NOT NULL auto_increment,
            name varchar(255) NOT NULL default '',
            rows int(11) NOT NULL default 0,
            cols int(11) NOT NULL default 0,
            subs varchar(255) NOT NULL default '',
            color varchar(255) NOT NULL default '',
            responsive tinyint(1) DEFAULT '0',
            tvalues longtext NOT NULL,
            UNIQUE KEY id (id)
            ) $this->charset_collate;";

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

    // since v.1.1
    if($this->db->get_var("SHOW TABLES LIKE '$this->old_table_name'") == $this->old_table_name){
        $this->upgrade_new_name_table();
    }

    update_option('wpsm_db_table_version', $this->db_version);
}

This is a record from the log file:

WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows int(11) NOT NULL default 0,
            cols int(11) NOT NULL default 0,
            subs v' at line 4 for query CREATE TABLE...

The function works on MySQL server well but it can not create a table on the MariaDB server. What could be the problem?

    public function create_table() {
    $current_version = get_option('wpsm_db_table_version');
    if($current_version && $current_version == $this->db_version && $this->db->get_var("SHOW TABLES LIKE '$this->table_name'") == $this->table_name){
        return;
    }

    $sql = "CREATE TABLE ". $this->table_name ." (
            id bigint(20) unsigned NOT NULL auto_increment,
            name varchar(255) NOT NULL default '',
            rows int(11) NOT NULL default 0,
            cols int(11) NOT NULL default 0,
            subs varchar(255) NOT NULL default '',
            color varchar(255) NOT NULL default '',
            responsive tinyint(1) DEFAULT '0',
            tvalues longtext NOT NULL,
            UNIQUE KEY id (id)
            ) $this->charset_collate;";

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

    // since v.1.1
    if($this->db->get_var("SHOW TABLES LIKE '$this->old_table_name'") == $this->old_table_name){
        $this->upgrade_new_name_table();
    }

    update_option('wpsm_db_table_version', $this->db_version);
}

This is a record from the log file:

WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows int(11) NOT NULL default 0,
            cols int(11) NOT NULL default 0,
            subs v' at line 4 for query CREATE TABLE...

• customization
• sql
• table
• dbdelta

• Have a problem that is probably the same with WordPress's dbDelta() on recent MariaDB; it has worked with no problem on MySQL for years, and I can't see any comments or otherwise obvious strangeness in the table declaration. Any ideas appreciated! – Brian C Commented Sep 15, 2019 at 8:19

1 Answer 1

I am not sure why you are creating a UNIQUE constraint for your "ID", which can be a primary key.

Also, please note that the dbDelta fails in certain versions of MariaDB when you create a table with a Unique Key Index or a Composite Key or any other indexes except the Primary Key.

The Workaround would be to create the table first:

 $sql = "CREATE TABLE ". $this->table_name ." (
        id bigint(20) unsigned NOT NULL auto_increment,
        name varchar(255) NOT NULL default '',
        rows int(11) NOT NULL default 0,
        cols int(11) NOT NULL default 0,
        subs varchar(255) NOT NULL default '',
        color varchar(255) NOT NULL default '',
        responsive tinyint(1) DEFAULT '0',
        tvalues longtext NOT NULL,
        PRIMARY KEY (id)
        ) $this->charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

And then you can use the $wpdb to create the Unique Index using the "ALTER TABLE" method.

global $wpdb;
$wpdb->query("ALTER TABLE `{$this->table_name}` ADD UNIQUE KEY(`column_name`)");

NOTE:You may want to run a check if the index exists before running the above query in your logic.

Hope this helps :-)