Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
podolinek opened this issue Oct 11, 2017 · 14 comments
Closed

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

podolinek opened this issue Oct 11, 2017 · 14 comments

Comments

@podolinek
Copy link

@podolinek podolinek commented Oct 11, 2017

  • 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

This comment has been minimized.

Copy link
Contributor

@EdaCZ EdaCZ commented Sep 25, 2018

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

This comment has been minimized.

Copy link
Contributor

@EdaCZ EdaCZ commented Sep 26, 2018

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

@dg

This comment has been minimized.

Copy link
Member

@dg dg commented Sep 27, 2018

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

@jiripudil

This comment has been minimized.

Copy link

@jiripudil jiripudil commented Sep 27, 2018

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

This comment has been minimized.

Copy link
Contributor

@EdaCZ EdaCZ commented Sep 27, 2018

@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

This comment has been minimized.

Copy link

@jiripudil jiripudil commented Sep 28, 2018

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

This comment has been minimized.

Copy link
Contributor

@EdaCZ EdaCZ commented Sep 28, 2018

Oh, now I see. Thanks.

MartinMystikJonas added a commit to MartinMystikJonas/database that referenced this issue Oct 1, 2018
dg added a commit that referenced this issue Oct 1, 2018
…mptyResultSet is called (#187)(#207)
dg added a commit that referenced this issue Oct 1, 2018
…mptyResultSet is called (#187)(#207)
dg added a commit that referenced this issue Oct 1, 2018
…mptyResultSet is called (#187)(#207)
dg added a commit that referenced this issue Oct 1, 2018
…mptyResultSet is called (#187)(#207)
@genesiscz

This comment has been minimized.

Copy link

@genesiscz genesiscz commented Nov 13, 2018

Is this getting a fix?

@dg

This comment has been minimized.

Copy link
Member

@dg dg commented Nov 13, 2018

It should be fixed by #207

@dg dg closed this Nov 13, 2018
@genesiscz

This comment has been minimized.

Copy link

@genesiscz genesiscz commented May 26, 2019

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

#230

@ustpcz

This comment has been minimized.

Copy link

@ustpcz ustpcz commented Aug 14, 2019

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

@dg

This comment has been minimized.

Copy link
Member

@dg dg commented Aug 15, 2019

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

@genesiscz

This comment has been minimized.

Copy link

@genesiscz genesiscz commented Aug 19, 2019

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

@ustpcz

This comment has been minimized.

Copy link

@ustpcz ustpcz commented Aug 31, 2019

@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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
6 participants
You can’t perform that action at this time.