Skip to content

Instantly share code, notes, and snippets.

@ErHaWeb

ErHaWeb/FixMmDuplicatesCommand.php

Last active September 9, 2024 15:18
Show Gist options
  • Save ErHaWeb/67c9b561e9a38084d9322e3e8c41f988 to your computer and use it in GitHub Desktop.
Save ErHaWeb/67c9b561e9a38084d9322e3e8c41f988 to your computer and use it in GitHub Desktop.
Download ZIP
Fix duplicates in TYPO3 MM tables
Raw
README.md

Fix duplicates in TYPO3 MM tables

→ to the german version

About

In earlier TYPO3 versions it could happen that MM entries were created twice. This command singularises the existing MM entries and ensures that MM entries are unique.

Affected instances

If you either execute the command

typo3 database:updateschema

or perform a DB comparison under ‘Analyse Database Structure’ in the Admin Tools, and receive an Duplicate entry 'xxx-xxx' for key 'PRIMARY' error message, then you are affected.

Error Message Example:

The following errors occurred:
+------------+--------------------------------------------------------+-----------------------------+
| Type       | SQL Statement                                          | Message                     |
+------------+--------------------------------------------------------+-----------------------------+
| Add fields | ALTER TABLE                                            | Duplicate entry '108-349'   |
|            | `tx_lccontact_contact_fieldworkerpostalcodes_postalcod | for key 'PRIMARY'           |
|            | e_mm` ADD PRIMARY KEY (uid_local, uid_foreign)         |                             |
|            |                                                        |                             |
|            | ALTER TABLE                                            | Duplicate entry '0-106' for |
|            | `tx_lccontact_contact_fieldworkercountryfallback_count | key 'PRIMARY'               |
|            | rygroup_mm` ADD PRIMARY KEY (uid_local, uid_foreign)   |                             |
|            |                                                        |                             |
|            | ALTER TABLE `sys_category_record_mm` ADD PRIMARY KEY   | Duplicate entry             |
|            | (uid_local, uid_foreign, tablenames, fieldname)        | '23-83-tx_news_domain_model |
|            |                                                        | _news-categories' for key   |
|            |                                                        | 'PRIMARY'                   |
|            |                                                        |                             |
+------------+--------------------------------------------------------+-----------------------------+

Execution

Place this script under EXT:sitepackage/Classes/Command/FixMmDuplicatesCommand.php and adjust the namespace specification if necessary.

As this process is a database update, it must be performed before the upgrade wizards are executed. Accordingly, this is a command that can be executed by

typo3 upgrade:fixmmduplicates

Attention

Please note that this command sets the fields sorting and sorting_foreign to the value 0. If you have a better solution for these fields, you are welcome to contribute it here.

Raw
FixMmDuplicatesCommand.php
<?php
declare(strict_types=1);
namespace VendorName\Sitepackage\Command;
use Doctrine\DBAL\Exception;
use Psr\Log\LoggerInterface;
use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Helper\ProgressBar;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Style\SymfonyStyle;
use TYPO3\CMS\Core\Database\ConnectionPool;
#[AsCommand(
name: 'upgrade:fixmmduplicates',
description: 'In earlier TYPO3 versions it could happen that MM entries were created twice. This command singularises the existing MM entries and ensures that MM entries are unique.',
)]
class FixMmDuplicatesCommand extends Command
{
private SymfonyStyle $io;
public function __construct(
private readonly ConnectionPool $connectionPool,
private readonly LoggerInterface $logger,
?string $name = null
) {
parent::__construct($name);
}
protected function execute(InputInterface $input, OutputInterface $output): int
{
$this->io = new SymfonyStyle($input, $output);
$this->io->title('Fix MM duplicates');
$affectedTables = $this->getAffectedMmTables();
if ($affectedTables) {
foreach ($affectedTables as $table => $records) {
$this->io->writeln("\n" . 'Process table `' . $table . '`:');
$progressBar = new ProgressBar($output, count($records));
foreach ($records as $fields) {
$this->deleteRecords($table, $fields);
$this->insertRecord($table, $fields);
$progressBar->advance();
}
$progressBar->finish();
}
} else {
$this->io->info('Nothing to process');
}
$this->io->success('finished!');
return Command::SUCCESS;
}
private function deleteRecords(string $table, array $fields): void
{
// Delete records
$queryBuilder = $this->connectionPool->getQueryBuilderForTable($table);
$where = [];
foreach ($fields as $field => $value) {
$where[] = $queryBuilder->expr()->eq($field, $queryBuilder->createNamedParameter($value));
}
$queryBuilder
->delete($table)
->where(...$where)
->executeStatement();
}
private function insertRecord(string $table, array $fields): void
{
// Insert new record
$queryBuilder = $this->connectionPool->getQueryBuilderForTable($table);
$queryBuilder
->insert($table)
->values($fields)
->executeStatement();
}
private function getAffectedMmTables(): array
{
$mmTables = $this->getMmTables();
$affectedTables = [];
foreach ($mmTables as $mmTable) {
$result = null;
if ($this->tableHasMmMatchFields($mmTable) === true) {
$queryBuilder = $this->connectionPool->getQueryBuilderForTable($mmTable);
$result = $queryBuilder
->select('uid_local', 'uid_foreign', 'tablenames', 'fieldname')
->from($mmTable)
->groupBy('uid_local', 'uid_foreign', 'tablenames', 'fieldname')
->having('COUNT(uid_local) > 1')
->having('COUNT(uid_foreign) > 1')
->having('COUNT(tablenames) > 1')
->having('COUNT(fieldname) > 1')
->executeQuery();
} elseif ($this->tableHasMmMatchFields($mmTable) === false) {
$queryBuilder = $this->connectionPool->getQueryBuilderForTable($mmTable);
$result = $queryBuilder
->select('uid_local', 'uid_foreign')
->from($mmTable)
->groupBy('uid_local', 'uid_foreign')
->having('COUNT(uid_local) > 1')
->having('COUNT(uid_foreign) > 1')
->executeQuery();
}
if ($result) {
try {
$rows = $result->fetchAllAssociative();
if ($rows) {
$affectedTables[$mmTable] = $rows;
}
} catch (Exception $exception) {
$this->logError($exception->getMessage());
}
}
}
return $affectedTables;
}
private function tableHasMmMatchFields(string $table): ?bool
{
$queryBuilder = $this->connectionPool->getQueryBuilderForTable($table);
$result = $queryBuilder
->select('*')
->from($table)
->executeQuery();
try {
while ($row = $result->fetchAssociative()) {
$keys = array_keys($row);
if (\in_array('tablenames', $keys, true) && \in_array('fieldname', $keys, true)) {
return true;
}
$required = ['uid_local', 'uid_foreign', 'sorting', 'sorting_foreign'];
$arrayDiff = array_diff($keys, $required);
if ($arrayDiff) {
$this->logError('Table ' . $table . ' has unknown match fields ' . implode(', ', $arrayDiff));
return null;
}
}
} catch (Exception $exception) {
$this->logError($exception->getMessage());
}
return false;
}
private function getMmTables(): array
{
$mmTables = [];
foreach ($GLOBALS['TCA'] as $table) {
foreach (($table['columns'] ?? []) as $column) {
$config = $column['config'] ?? [];
if ($config) {
$mmTable = $config['MM'] ?? '';
if ($mmTable) {
$mmTables[] = $config['MM'];
}
}
}
}
return array_unique($mmTables);
}
private function logError(string $message): void
{
$this->io->error($message);
$this->logger->error($message);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment