Mysql error 1062 duplicate entry for key primary

FYI I've also applied below patch but the error seems to still happen meaning it doesn't look like the read uncommitted is causing this.

diff --git a/core/DataAccess/LogAggregator.php b/core/DataAccess/LogAggregator.php
index cada4b1571..eb2a94611b 100644
--- a/core/DataAccess/LogAggregator.php
+++ b/core/DataAccess/LogAggregator.php
@@ -305,7 +305,7 @@ class LogAggregator
             // set uncommitted is easily noticeable in the code as it could be missed quite easily otherwise
             // we set uncommitted so we don't make the INSERT INTO... SELECT... locking ... we do not want to lock
             // eg the visits table
-            if (!$transactionLevel->setUncommitted()) {
+            if (0 && !$transactionLevel->setUncommitted()) {
                 $canSetTransactionLevel = false;
             }
         }

I am getting a MySQL error regarding a duplicate entry when using the following code. 

array_push($job_strings, 'list_refresh');
function list_refresh()

// This function adds any new contacts on present day into the target list, runs daily.
{
$db = DBManagerFactory::GetInstance();
$date = date('Y-m-d');
// Time for Date Modified Parameter insertion
$time = date('Y-m-d h:i:s a');
// Query to pull contacts only from today.
$contactquery = "SELECT id FROM contacts WHERE date_entered >= '$date'";
$results = $GLOBALS['db']->query($contactquery);
foreach ($results as $result){
$contactid = strval($result['id']);

// Loop through accounts module to get associative id. 
$accountquery = "SELECT account_id FROM accounts_contacts WHERE contact_id = '$contactid'";
$secondresults = $GLOBALS['db']->query($accountquery);
// Loop through Prospects List module to get final variable prepared.
foreach ($secondresults as $secondresult) {
$listquery = "SELECT prospect_list_id FROM prospect_lists_prospects WHERE related_id = '{$secondresult['account_id']}'";
$thirdresults = $GLOBALS['db']->query($listquery);
// Use all of the Selected Data to create new entry in target list.
// Execute results.
foreach ($thirdresults as $thirdresult) {
$createquery = "INSERT INTO prospect_lists_prospects (prospect_list_id, related_id, related_type, date_modified) VALUES ('{$thirdresult['prospect_list_id']}', '$contactid', 'contacts', '$time')";
$GLOBALS['db']->query($createquery);
}
}
}

//return true for completed
return true;
}

The error happens when the script gets to the Insert INTO execution.

I was under the impression that Sugar would auto assign the id for the primary key, but instead it is creating a blank column id. (I'm not inserting any data into the id field, only the other id fields.) What can I do to resolve this? Do I need to create a random id myself or is there some other method?

When creating a primary key or unique constraint after loading the data, you can get a “Duplicate entry for key 'PRIMARY'” error.

If the data in the source database is valid and there are no any duplicates you should check which collation is used in your MySQL database. By default, MySQL uses case-insensitive collation when creating a database, this means that the values that differ only in case are considered as equal.

Assume your source database is Oracle and you have the following data:

Oracle:

CREATE TABLE names
(
  name VARCHAR2(30) PRIMARY KEY  
);
 
INSERT INTO names VALUES ('Maria');
INSERT INTO names VALUES ('maria');

If you run this code in a case-insensitive MySQL database, you will get the error:

MySQL:

CREATE TABLE names
(
  name VARCHAR(30) PRIMARY KEY  
);
 
INSERT INTO names VALUES ('Maria');
INSERT INTO names VALUES ('maria');
-- ERROR 1062 (23000): Duplicate entry 'maria' for key 'PRIMARY'

To check the collation of your database in MySQL, run:

SELECT @@collation_database;
-- utf8_general_ci

_ci suffix means that it is case-insensitive.

SQLines Data Behavior

SQLines Data tool uses utf8mb4 character set with utf8_bin collation by default, so you should not see this problem unless you change -mysql_character_set and -mysql_collate options in sqldata.cfg file.

In case you need other character set and collation choose a collate with suffix _cs in MySQL.

How do I fix error 1062 in MySQL?

1062 - Duplicate Entry To solve this, Set the primary key column as AUTO_INCREMENT . And when you are trying to insert a new row, ignore the primary key column or insert NULL value to primary key.

How do I fix a duplicate entry in MySQL?

How to Remove Duplicate Rows in MySQL.
Option 1: Remove Duplicate Rows Using INNER JOIN..
Option 2: Remove Duplicate Rows Using an Intermediate Table..
Option 3: Remove Duplicate Rows Using ROW_NUMBER().

What is duplicate entry for key primary?

When creating a primary key or unique constraint after loading the data, you can get a “Duplicate entry for key 'PRIMARY'” error. If the data in the source database is valid and there are no any duplicates you should check which collation is used in your MySQL database.

Can primary key be duplicate in MySQL?

When you insert a new row into a table if the row causes a duplicate in UNIQUE index or PRIMARY KEY , MySQL will issue an error. However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will update the existing row with the new values instead.