The function can not create a table on MariaDB server

Question

The function can not create a table on MariaDB server

Andrew B.

2020年3月17日 20:55

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...

Topic dbdelta table customization Wordpress sql

Category Web

Ramesh Elamathi · Accepted Answer · 2020年3月17日 20:55

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 :-)

The function can not create a table on MariaDB server

About