database

database copied to clipboard

• 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.

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.

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

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

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

@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?

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.

Oh, now I see. Thanks.

Is this getting a fix?

It should be fixed by #207

This is happening again, for different reason. Care to take a look?

https://github.com/nette/database/issues/230

Happend to me with nette/database v2.4.8. Should I prepare some example? Does anyone care?

An example would be fine. Does it happen with 3.0?

Happend to me with nette/database v2.4.8. Should I prepare some example? Does anyone care?

Everything fixed for me after upgrading to 3.0.2

@dg I finally managed to reproduce this issue on 7.1.11 and nette database v2.4.8 (and v3.0.1 later). To reproduce: start with composer create-project nette/web-project:2.* replace HomepagePresenter.php with one attached to this post, import test.sql to your db. You will need to set show in test table to 1 after each run. Delete cache and visit Homepage:default, comment continue; statement and visit that page again, you should now have

Nette\InvalidStateException Database refetch failed; row with signature '1' does not exist!

when you refresh that page again, error is gone (even with show set to 1). So to reproduce again uncomment continue, set show=1, delete cache...

I hope that make sense. Is there anything else I should add?

test.zip

I managed to reproduce this issue with nette/database v3.0.6 with method described in my last post. Can we reopen this issue?