重复密钥更新在我的本地wordpress中工作，但在另一个站点中不起作用

Question

I created a table to hold meta data about a custom taxonomy and everything works fine in my local instance (created with ampps) of wordpress when I go to insert using ON DUPLICATE KEY UPDATE it finds the row with the unique key group_id and does the update instead of the insert.

however, when I moved this code to my dev instance (instance which is live on the internet on a subdomain) ON DUPLICATE KEY UPDATE creates duplicates. no code has changed.

at this point i'm stumped. is there a mysql version limitation for ON DUPLICATE KEY UPDATE? are there plugins that can disrupt the default behavior of ON DUPLICATE KEY UPDATE on a global scale?

UPDATE: this code is what I'm using to create my table

function create_prg_team_grouping_table(){
global $wpdb;

//create the name of the table including the wordpress prefix (wp_ etc)
$search_table = $wpdb->prefix . "prg_team_grouping";
//$wpdb->show_errors(); 

//check if there are any tables of that name already
if($wpdb->get_var("show tables like '$search_table'") !== $search_table) 
{
    //create your sql
    $sql =  "CREATE TABLE ". $search_table . " (
                  stat_id mediumint(12) NOT NULL AUTO_INCREMENT,
                  use_in_grouping text,
                  group_name text,
                  group_id mediumint(12) NOT NULL,
                  group_order mediumint(9),
                  UNIQUE KEY (stat_id,group_id));";
}

//include the wordpress db functions
require_once(ABSPATH . 'wp-admin/upgrade-functions.php');
dbDelta($sql);

//register the new table with the wpdb object
if (!isset($wpdb->prg_team_grouping)) 
{
    $wpdb->prg_team_grouping = $search_table; 
    //add the shortcut so you can use $wpdb->prg_team_grouping
    $wpdb->tables[] = str_replace($wpdb->prefix, '', $search_table); 
}
}

//add to front and backend inits
add_action('after_switch_theme', 'create_prg_team_grouping_table');

Answer 1

I'd double-check that your index is unique / primary. That is, make sure you have a unique constraint on the value that should be triggering the update.

If the only difference is in the database, chances are the schema is incomplete.

MySQL INSERT IGNORE / ON DUPLICATE KEY UPDATE not detecting duplicates