SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ' for Primary key

Hi @keopx

Firstly, thank you very much for the great module.

1) Regarding the above error, it comes whenever you generate the sitemap. On installation and subsequent sitemap regeneration both.

I believed it came from the below function in the file: DomainSimpleSitemapGenerator.php


public function generateSitemap(array $links, $remove_sitemap = FALSE) {
$new_links_array = [];
foreach ($links as $link) {
$chunk_id = $this->db->query('SELECT id FROM {simple_sitemap} WHERE domain_id = :domain_id', [':domain_id' => $link['domain_id']])->fetchField();
if (!$chunk_id) {
$chunk_id = $this->db->query('SELECT MAX(id) FROM {simple_sitemap}')->fetchField() + 1;
$values = [
'id' => $chunk_id,
'domain_id' => $link['domain_id'],
'sitemap_string' => $this->generateSitemapChunk([]),
'sitemap_created' => REQUEST_TIME,
];

$this->db->insert('simple_sitemap')->fields($values)->execute();
}
$new_links_array[$chunk_id][$link['domain_id']][] = $link;
}
// Invoke alter hook.
$this->moduleHandler->alter('simple_sitemap_links', $links);
if ($remove_sitemap) {
$this->db->truncate('simple_sitemap')->execute();
}
foreach ($new_links_array as $id => $new_links) {
$values = [
'id' => $id,
'domain_id' => key($new_links),
'sitemap_string' => $this->generateSitemapChunk(array_shift($new_links)),
'sitemap_created' => REQUEST_TIME,
];

$this->db->insert('simple_sitemap')->fields($values)->execute();
}
}

I found two issues:
a) If $remove_sitemap is set TRUE then the sitemap table should truncate at the very first not after checking and creating fresh chunk for the domain,
b) - It is firstly checking whether sitemap chunk is already created for the domain if not then a new one with domain_id is inserted => CORRECT
- Then it should update the table entry in the end not insert with the line "$this->db->insert('simple_sitemap')->fields($values)->execute();" => WRONG
- I implemented a update command instead of "$this->db->insert('simple_sitemap')->fields($values)->execute();"

I tried to fix the errors with provided patch. Not sure whether it follows your concept. I would appreciate if you review it once.

2) Regarding 8.x-1.0-beta8 release the other issue error is gone since now sitemap is not generating on installation. Although the truncate action is a better replacement :)
But this duplication error was still there.

Best Regards,
Neha

Aah, I corrected the name but seemed like upload the wrong file.

Please find new patch with corrected name. See if it works.

@keopx,

Understood! does that splitting of sitemap was working before with domain access behavior ?

@keopx,

I will also take a look if can provide a solution.

@coding_standards : I know there are many .. even deprecated functions too. thought like fixing the error first ;)