Database refetch failed; row with signature 'xxx' does not exist!

[podolinek]

• bug report? yes
• feature request? no
• version: 2.4.4.

Description

Hello guys, we found bug in method related() with combination with count(). If I need uncached column on cached result, nette database not recognize different in query for caching related(), if you call count() before foreach loop.. Without call count() it looks ok.

It creates InvalidStateException "Database refetch failed; row with signature '3' does not exist!"

In the shortest piece of code for explaining...

class HomepagePresenter extends BasePresenter {

	/** @var @persistent */
	public $test;

	/** @var DocumentModel @inject */
	public $document;

	public function renderDefault() {
		$source = $this->document->findAll()->where('document.name IS NOT NULL');

		if ($this->test === true) {
			$source->where(':document_item.is_visible', 1);
		}

		foreach ($source as $row) {
			$items = $row->related('document_item');
			if ($items->count()) {//if this is commented, everything is ok
				foreach ($items as $item) {
					if ($this->test === true) {
						$item['is_visible'];
					}
				}
			}
		}
	}

	public function handleChangeState() {
		$this->test = true;
	}
}

In added sandbox with base logic you can try this. How to install and how to reproduce error in readme.md.

[EdaCZ]

I am also receiving about 30 bugreports every day because of this bug... :-(

My simple way to reproduce:

DB:

CREATE TABLE `Photo` (
  `number` int(4) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;

INSERT INTO `Photo` (`number`) VALUES (1), (2), (3);

CREATE TABLE `PhotoNonPublic` (
  `number` int(4) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`number`),
  CONSTRAINT `PhotoNonPublic_ibfk_1` FOREIGN KEY (`number`) REFERENCES `Photo` (`number`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;

INSERT INTO `PhotoNonPublic` (`number`) VALUES (2), (3);

index.php:

$journal = new Nette\Caching\Storages\SQLiteJournal(TEMP_DIR . '/cache/journal.s3db');
$cacheStorage = new Nette\Caching\Storages\FileStorage(TEMP_DIR . '/cache', $journal);
$connection = new Nette\Database\Connection('mysql:host=db_database;dbname=test', 'root', 'root', null);
$structure = new Nette\Database\Structure($connection, $cacheStorage);
$conventions = new Nette\Database\Conventions\DiscoveredConventions($structure);
$db = new Nette\Database\Context($connection, $structure, $conventions, $cacheStorage);

if (isset($_GET['published'])) {
	$where = '(:PhotoNonPublic.number IS NULL)';
} else {
	$where = '(:PhotoNonPublic.number IS NOT NULL)';
}

$result = $db->table('Photo')->where($where);

foreach ($result as $photoRow) {
	$related = $photoRow->related('PhotoNonPublic');

	if ($related->count() != 0) {
		$related->fetch()->toArray();
	}
}

First call the script with GET parameter published=1.
And then call it without that parameter.

You get error: Database refetch failed; row with signature '3' does not exist!

I am using version 2.4.4 too.

[EdaCZ]

@dg Hi, are you planning to look on this issue? Are those reprosteps above sufficient for you? Thanks.

[dg]

Unfortunately no, I am not the author and I do not know how it works.

[jiripudil]

Just for the record, we're facing a similar issue and I've tracked down where it comes from:

$user = $this->userRepository->findBy(['activateToken' => $token]);
// SELECT `id`, `isActive`, `email` FROM `admin_user` WHERE (`activateToken` = ?) ORDER BY `admin_user`.`id` LIMIT 1

$this->userActivator->activate($user);
// UPDATE `admin_user` SET `isActive`=1, `activateToken`=NULL WHERE (`id` = 2)

$identity = $this->identityFactory->createIdentity($user);
// SELECT * FROM `admin_user` WHERE (`activateToken` = 'test') AND (`admin_user`.`id` IN (2)) ORDER BY `admin_user`.`id`

The problem is that $user's associated Selection's SqlBuilder still contains the activateToken = ? condition which is no longer true. This happens within a single request and only when the cache is cold.

I hope this report helps whomever finds the courage to dig into the internals. Also, if it helps anybody facing the issue, we've worked around this by refetching the user explicitly after the activation:

$this->userActivator->activate($user);
$user = $this->userRepository->get($user->id);
// SELECT * FROM `admin_user` WHERE `admin_user`.`id` = 2

$identity = $this->identityFactory->createIdentity($user);
// no refetch needed

[EdaCZ]

@jiripudil Could you please simlify you code to use only calls of Nette/Database code?
I dont understand what is the problem in your use case.
It is ok that the condition is preserved on Selection until you clean it.
What code is exactly in createIdentity and where is the problem?

[jiripudil]

createIdentity just does something like this:

return new Identity($user->id, [
    'language' => $user->language, // <-- it fails here
]);

It accesses columns of $user that have not been fetched yet, which triggers the refetch. The refetch, however, is done with the preserved condition (activateToken = ...), but the condition is no longer true, because the database row has been updated in the activator. Hence, row with signature '2' does not exist.

[EdaCZ]

Oh, now I see. Thanks.

[genesiscz]

Is this getting a fix?