From 29446a29f58b484817e6c9798c736e5f531c21ee Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 14 Sep 2025 22:36:01 +0200 Subject: Recovery: skip broken entries during CLI export/import (#7949) * Recovery: skip broken entries during CLI export/import fix https://github.com/FreshRSS/FreshRSS/discussions/7927 ``` 25605/25605 (48 broken) ``` Help with *database malformed* or other corruption. * Compatibility multiple databases --- app/Controllers/feedController.php | 2 +- app/Models/DatabaseDAO.php | 32 ++++++++++++++++++++------------ app/Models/EntryDAO.php | 32 +++++++++++++++++++++++++------- app/Models/EntryDAOPGSQL.php | 6 ++++++ app/Models/EntryDAOSQLite.php | 6 ++++++ 5 files changed, 58 insertions(+), 20 deletions(-) diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 4884b237f..a7c1d15bc 100644 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -813,7 +813,7 @@ class FreshRSS_feed_Controller extends FreshRSS_ActionController { $entryDAO = FreshRSS_Factory::createEntryDao(); $applyLabels = []; - foreach (FreshRSS_Entry::fromTraversable($entryDAO->selectAll($nbNewEntries)) as $entry) { + foreach (FreshRSS_Entry::fromTraversable($entryDAO->selectAll(order: 'DESC', limit: $nbNewEntries)) as $entry) { foreach ($labels as $label) { $label->applyFilterActions($entry, $applyLabel); if ($applyLabel) { diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php index 8ec3ce3ca..a33d38e76 100644 --- a/app/Models/DatabaseDAO.php +++ b/app/Models/DatabaseDAO.php @@ -435,22 +435,30 @@ SQL; $nbEntries = $entryFrom->count(); $n = 0; + $brokenEntries = 0; $entryTo->beginTransaction(); - foreach ($entryFrom->selectAll() as $entry) { - $n++; - if (!empty($idMaps['f' . $entry['id_feed']])) { - $entry['id_feed'] = $idMaps['f' . $entry['id_feed']]; - if (!$entryTo->addEntry($entry, false)) { - $error = 'Error during SQLite copy of entries!'; - return self::stdError($error); + while ($n < $nbEntries) { + foreach ($entryFrom->selectAll(offset: $n) as $entry) { + $n++; + if (!empty($idMaps['f' . $entry['id_feed']])) { + $entry['id_feed'] = $idMaps['f' . $entry['id_feed']]; + if (!$entryTo->addEntry($entry, false)) { + $error = 'Error during SQLite copy of entries!'; + return self::stdError($error); + } + } + if ($n % 100 === 1 && defined('STDERR') && $verbose) { //Display progression + fwrite(STDERR, "\033[0G" . $n . '/' . $nbEntries . ($brokenEntries > 0 ? " ($brokenEntries broken)" : '')); } } - if ($n % 100 === 1 && defined('STDERR') && $verbose) { //Display progression - fwrite(STDERR, "\033[0G" . $n . '/' . $nbEntries); + if ($n < $nbEntries) { + $brokenEntries++; + // Attempt to skip broken records in the case of corrupted database + $n++; + } + if (defined('STDERR') && $verbose) { + fwrite(STDERR, "\033[0G" . $n . '/' . $nbEntries . ($brokenEntries > 0 ? " ($brokenEntries broken)" : '') . PHP_EOL); } - } - if (defined('STDERR') && $verbose) { - fwrite(STDERR, "\033[0G" . $n . '/' . $nbEntries . "\n"); } $entryTo->commit(); $feedTo->updateCachedValues(); diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 4aa7c5056..cd7bcd2ff 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -27,6 +27,21 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { return str_replace('INSERT INTO ', 'INSERT IGNORE INTO ', $sql); } + protected static function sqlLimitAll(): string { + // https://dev.mysql.com/doc/refman/9.4/en/select.html + return '18446744073709551615'; // Maximum unsigned BIGINT in MySQL, which neither supports ALL nor -1 + } + + public static function sqlLimit(int $limit = -1, int $offset = 0): string { + if ($limit < 0 && $offset <= 0) { + return ''; + } + if ($limit < 1) { + $limit = static::sqlLimitAll(); + } + return "LIMIT {$limit} OFFSET {$offset}"; + } + public static function sqlRandom(): string { return 'RAND()'; } @@ -739,18 +754,21 @@ SQL; } } - /** @return Traversable */ - public function selectAll(?int $limit = null): Traversable { + /** + * @param 'ASC'|'DESC' $order + * @return Traversable + */ + public function selectAll(string $order = 'ASC', int $limit = -1, int $offset = 0): Traversable { $content = static::isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content'; $hash = static::sqlHexEncode('hash'); + $order = in_array($order, ['ASC', 'DESC'], true) ? $order : 'ASC'; + $sqlLimit = static::sqlLimit($limit, $offset); $sql = <<= 0) { - $sql .= ' ORDER BY id DESC LIMIT ' . $limit; - } $stm = $this->pdo->query($sql); if ($stm !== false) { while (is_array($row = $stm->fetch(PDO::FETCH_ASSOC))) { @@ -762,7 +780,7 @@ SQL; $info = $this->pdo->errorInfo(); /** @var array{0:string,1:int,2:string} $info */ if ($this->autoUpdateDb($info)) { - yield from $this->selectAll(); + yield from $this->selectAll($order, $limit, $offset); } else { Minz_Log::error(__METHOD__ . ' error: ' . json_encode($info)); } diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php index e7999f601..68b52d071 100644 --- a/app/Models/EntryDAOPGSQL.php +++ b/app/Models/EntryDAOPGSQL.php @@ -23,6 +23,12 @@ class FreshRSS_EntryDAOPGSQL extends FreshRSS_EntryDAOSQLite { return rtrim($sql, ' ;') . ' ON CONFLICT DO NOTHING'; } + #[\Override] + protected static function sqlLimitAll(): string { + // https://www.postgresql.org/docs/current/queries-limit.html + return 'ALL'; + } + #[\Override] public static function sqlRandom(): string { return 'RANDOM()'; diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php index c2470f0d6..d46b48f27 100644 --- a/app/Models/EntryDAOSQLite.php +++ b/app/Models/EntryDAOSQLite.php @@ -28,6 +28,12 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { return str_replace('INSERT INTO ', 'INSERT OR IGNORE INTO ', $sql); } + #[\Override] + protected static function sqlLimitAll(): string { + // https://sqlite.org/lang_select.html#the_limit_clause + return '-1'; + } + #[\Override] public static function sqlRandom(): string { return 'RANDOM()'; -- cgit v1.2.3