[resolved] WordPress – WordPress database error: [Multiple primary key defined]

Problem

When using a WordPress plugin you receive an error message which reads:

The plugin generated 4191 characters of unexpected output during activation. If you notice “headers already sent” messages, problems with syndication feeds or other issues, try deactivating or removing this plugin.

When checking the event logs you see the error message:

WordPress database error: [Multiple primary key defined]
ALTER TABLE wp_table ADD PRIMARY KEY  (`id`)

Solution

The first error message is a generic message that indicates that an error has occured, there is very little use you can get out of this message.

The second message can be seen when you debug the plugin. You can use the Debug “unexpected output” During Plugin Activation plugin to help you.

The ‘WordPress database error: [Multiple primary key defined]’ error indicates an error in the use of the dbDelta, a core WordPress function which handles the management of the WordPress database.

The error is in the use of

ADD PRIMARY KEY  (`id`)

The column name is incorect or not properly formatted – in this example backticks have been used, which is not correct when using dbDelta.

To fix the issue you will need to:

Determine the plugin that is causing the issue

This can be done by disabling all the plugins until the issue is resolved.

When the error message is no longer appearing, re-activate the last plugin to confirm the errors have started again.

Determine what part of the plugin is causing the issue

dbDelta is typically ran when a plugin is activated, and sometimes when it is deactivated.

If this is the cause you will only see the error message whe the plugin is activated/deactivated.

You will need to read through the plugin’s PHP files to find where the dbDelta function is being used.

For example, you will see something like this:

dbDelta( $sql );

Above it you will find the $sql variable containing a database query.

For example:

$table = $wpdb->prefix . "table";
$sql = "CREATE TABLE ".$table." (
              `id` bigint(20) NOT NULL AUTO_INCREMENT,
              `Program_ID` int(11) NOT NULL,
              `Program_Code` varchar(10) NOT NULL,
              `Short_Name` varchar(40) NOT NULL,
              `Description` varchar(50) NOT NULL,
               PRIMARY  KEY  (`id`)
               ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
            
dbDelta( $sql );

Fix the cause of the issue

Where I’ve experienced this issue the cause was using backward ticks around the column names.

Backward ticks are normally used when using the phpMyAdmin database tool – but are not used when using dbDelta.

The fix here would be to remove the backward ticks. Note also that there is a DOUBLE SPACE between PRIMARY KEY and the column.

$table = $wpdb->prefix . "table";
$sql = "CREATE TABLE ".$table." (
               id bigint(20) NOT NULL AUTO_INCREMENT,
               Program_ID int(11) NOT NULL,
               Program_Code varchar(10) NOT NULL,
               Short_Name varchar(40) NOT NULL,
               Description varchar(50) NOT NULL,
               PRIMARY  KEY  (id)
               ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
            
dbDelta( $sql );

See the creating tables with plugins on the WordPress website for more information on how to correctly use dbDelete.