Row does not contain primary id column data after deleting cache
genesiscz opened this issue · 14 comments
Version: "nette/database": "~3.0",
After deleting cache, I open a page and everything is just fine, I see queries to refresh the database cache. The next reload gives me an error "Row does not contain primary id column data". The row giving me this error is $details = $race->related("race_details")->where("active", 1);. The callstack is call to ActiveRow::related $groupedSelection = $this->table->getReferencingTable($key, $throughColumn, $this[$this->table->getPrimary()]);, offsetGet 236: return $this->__get($column);, accessColumn, refreshData, getSignature and then getPrimary which throws an 90: throw new Nette\InvalidStateException("Row does not contain primary $primary column data.");.
It worked before upgrading Nette Database and I have no idea why does this happen. The request giving this error does no queries to refresh the cache and another page refresh gives me the correct page without errors without refreshing the cache data.
What am I missing?
Can you create failing test?
TLDR: look at later posts with working minimal example stated
I didn't manage to reproduce it standalone. It is working quite well, but I have no idea why.
Currently, in my failing code it does these queries the first load (without cache)
162956 Query SELECT * FROM `races` WHERE (`phase_numeric` != 3) AND (`activity_sector_day_id` = 4931)
162956 Query SELECT * FROM `event_races` WHERE (`phase_numeric` != 3) AND (`activity_sector_day_id` = 4931)
And then at the second load
162960 Query SELECT * FROM `races` WHERE (`phase_numeric` != 3) AND (`activity_sector_day_id` = 4931)
162960 Query SELECT `event_id` FROM `event_races` WHERE (`phase_numeric` != 3) AND (`activity_sector_day_id` = 4931) <-- this query happens on foreach($races as $race)
162960 Query SELECT * FROM `event_races` WHERE (`phase_numeric` != 3) AND (`activity_sector_day_id` = 4931) <-- this query happens on the related call
If i try to create a failing minimal example like this
$container = require __DIR__ . '/../app/bootstrap.php';
$db = $container->getService("database");
$rows = $db->table("event_races")->where("phase_numeric != ?", 3)->where("activity_sector_day_id", 4931);
foreach($rows as $race) {
try {
$details = $race->related("race_details")->where("active", 1);
} catch(Exception $e) {
dump($race);
die();
}
foreach($details as $detail) {
dump($detail);
}
}
it does these queries the second load
SELECT `id` FROM `event_races` WHERE (`phase_numeric` != 3) AND (`activity_sector_day_id` = 4931)
SELECT `id`, `event_race_id` FROM `event_race_details` WHERE (`event_race_details`.`event_race_id` IN (3364)) AND (`active` = 1)
If i try this in my original code
foreach($races as $race) {
dump($race->toArray());
}it works properly. Looks like the related selection uses the old data, because when I do try this:
foreach ($races as $race) {
try {
$details = $race->related("race_details")->where("active", 1);
foreach($details as $detail) {
dump($detail->toArray());
}
} catch(Exception $e) {
dump($race);
die();
}
}
it catches the exception and it gives me this
Nette\Database\Table\ActiveRow
table private =>
data private =>
event_id => 763
dataRefreshed private => false
Sorry if It's still unclear, I will try my best to collaborate with the solution, I am still not sure about the problem yet.
And I just figured out the minimal failing example
$container = require __DIR__ . '/../app/bootstrap.php';
$db = $container->getService("database");
$rows = $db->table("event_races")->where("phase_numeric != ?", 3)->where("activity_sector_day_id", 4931);
foreach($rows as $race) {
try {
$details = $race->related("race_details")->where("active", 1);
} catch(Exception $e) {
dump($race);
die("Exception");
}
$race = (object)($race->toArray() + [
"event" => $race->event, // this is the line
]);
dump("ok");
}If I don't include the line "event" => $race->event, everything works properly. If I do include it, first load is okay, second fails and every other is just fine. What is the matter here?
If I change the $race to this
$race = (object)(/*$race->toArray() + */[
"event" => $race->event,
]);it also works (doesn't fail)
If i change it to
$race = (object)($race->toArray()/* + [
"event" => $race->event,
]*/);it is also without fail.
Even created a fully working (failing) example
SQL:
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
DROP TABLE IF EXISTS `A_event`;
CREATE TABLE `A_event` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
INSERT INTO `A_event` (`id`) VALUES
(1);
DROP TABLE IF EXISTS `A_race`;
CREATE TABLE `A_race` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`A_event_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `A_event_id` (`A_event_id`),
CONSTRAINT `A_race_ibfk_1` FOREIGN KEY (`A_event_id`) REFERENCES `A_event` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
INSERT INTO `A_race` (`id`, `A_event_id`) VALUES
(1, 1);
DROP TABLE IF EXISTS `A_race_detail`;
CREATE TABLE `A_race_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a_id` int(11) DEFAULT NULL,
`active` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `a_id` (`a_id`),
CONSTRAINT `A_race_detail_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `A_race` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
INSERT INTO `A_race_detail` (`id`, `a_id`, `active`) VALUES
(1, 1, 1);
index.php:
$container = require __DIR__ . '/../app/bootstrap.php';
$db = $container->getService("database");
$rows = $db->table("A_race");
foreach($rows as $race) {
try {
$details = $race->related("A_race_detail")->where("active", 1);
} catch(Exception $e) {
dump($race);
die("Exception");
}
$race = (object)($race->toArray() + [
"event" => $race->A_event,
]);
dump("ok");
}
Has anyone progress with solution of this issue? I have exactly the same problem in my app....
My workarround is to select all columns like this:
$details = $race->related("A_race_detail")->select("A_race_detail.*")->where("active",1);
But this is nothing pretty and wanted in application :-(
@TomasHalasz @genesiscz I think that David made himself clear - we need to create failing test first and then we can elaborate on solution for this bug. It's useless to add here another mention of him :-)
I tried to reproduce your example, @genesiscz, but that failing example is unfortunately "not working" for me.
I tried it in tests - but it didn't fail. I used the same trick as in issue 187 for repeated run. But it just passed.
Then I tried it also in nette/sandbox - I just run that SQL from your latest post from 17tr April and put this into index.php:
$container = App\Bootstrap::boot()->createContainer();
$db = $container->getByType(Context::class);
$rows = $db->table("A_race");
foreach ($rows as $race) {
try {
$details = $race->related("A_race_detail")->where("active", 1);
} catch (Exception $e) {
dump($race);
die("Exception");
}
$race = (object) ($race->toArray() + [
"event" => $race->A_event,
]);
dump("ok");
}And it works fine, it's not failing even after multiple reloads and deleting the case. So I don't know what to do next...
I really tried, but it works on my machine.
Please try to download fresh sandbox and try to simulate it again. Maybe you'll find something else what is important for replication of this bug.
Thanks!
(I am also user of Nette/Database and I would be glad if we managed to fix this.)
@EdaCZ Can we contact each other privately, somehow resolve the problem and elaborate it here? Contact me on admin [at] phpcode.eu
The problem is probably in some of the older versions of Nette. I am using these versions in composer.json
"php": ">= 7.1.0",
"nette/application": "~3.0",
"nette/bootstrap": "~3.0",
"nette/caching": "~3.0",
"nette/database": "~3.0",
"nette/di": "~3.0",
"nette/finder": "~2.5",
"nette/forms": "~3.0",
"nette/http": "~3.0",
"nette/mail": "~3.0",
"nette/robot-loader": "~3.0",
"nette/safe-stream": "~2.4",
"nette/security": "~3.0",
"nette/utils": "~3.0",
"latte/latte": "~2.5",
"tracy/tracy": "^2.6"
@genesiscz Which exact version of Nette\Database do you have in your project, where you can reproduce this bug? (Take a look into composer.lock...)
"version": "dev-master",
"time": "2019-03-26T14:20:10+00:00"
Mm, that explains a lot. I guess I will have to upgrade once more.
@genesiscz Yeah, I would definitely recommend upgrading to latest version. And if it's still problematic, just let us know and we can elaborate on it :-)
@genesiscz I'v just updated on nette/database v3.0.2 and issue is gone. Now it seems that everything is ok.
Thanks for the info Tomas, I would love to know what the issue was caused by as it is bugging me for pretty long time. If you have any idea which commit fixed it, it would be really appreciated