TYPO3 Core

• TypoScript: config.no_cache=1 or [PAGE].config.no_cache=1
• Extension plugin using cHash (useCacheHash) which is possibly wrong
• $GLOBALS['TSFE']->no_cache=1 or $GLOBALS['TSFE']->set_no_cache() in an extension plugin

I have a plugin which has """$this->pi_USER_INT_obj=1""" and
"""
t3lib_extMgm::addPItoST43($_EXTKEY,'pi1/class.user_X_pi1.php','_pi1','list_type',0);
"""
in ext_localconf.php.

But AFAICS it's not wrong: the extension is meant to provide several search forms and therefor is quite dynamic.
If I change the plugin to become a USER object instead of USER_int, the error occurs only on the first loading of the frameset after the cache has been cleared (as expected).

Please have a look to bug-id #16751 to know what's going on at your system.

If a page is reloaded twice, on the first hit, the new data is written to DB, on the second hit, obviously the same data would be updated in DB - but if no data is changed on an UPDATE query, sql_affected_rows returns zero. And if sql_affected_rows is zero, t3lib_TStemplate::start will use again an INSERT query with the same data that already exists on the database.

For disposal in frames this means: Frames are almost rendered at the same time. Thus, the first frame writes data to the storage correctly and the second frame does the same again and brings up the error.

The solution would be a method which determines if there is already a record in cache_pagesection. Additional SELECTs should be avoided, but if there is no other possibility we have to take the SELECT.

You can work around this bug using a TypoScript hack:

Example:
[globalVar = TSFE:type = 0]
[globalVar = TSFE:type = 3]
[globalVar = TSFE:type = 5]
[global]

So, put for each page typeNum a condition to your global TypoScript setup. If you're rendering a page of a given type (e.g. "5") this will change the "match" flag written to cache_pagesection. Thus it's individual but holds some rendundancy.

This is just a work-around until the bug is fixed! No TypoScript to remain forever in your site - just for the moment... ;-)

I'v got the same problem with typo3 4.1.1. It's realy anyoing...

@Oliver: I don't have one of the things you listed above. It's happening on pages without any special extension, and for god sake we don't use no_cache ;-)

Please someone explain exactly how to reproduce this problem. It seems to be hard to reproduce, so I ask you identify whatever is triggering the error...

No thing would be easier like describing like it comes along.
set config.debug=1
set sqlDebug in Installtool, and fe.debug, and be sure that you are able to view debug output (devIPmask etc..).
Clear all your caches.
reload your page in a clean (not logged in) browser window. If nothing happens first time, just reload a second time, or a third time. Be fast! ;) To me it seems, like the faster you're reloading, the more often it happens.
Not nice while developing ... ;)

And YES I am the paranoid-crazy reloadmaniac! :-)

Happens on all my 4.1.1 projects from time to time. (not every time, but lets say 4-6 times a day).

This problem is NOT related to framesets or anything like that. it seems to be just a bug in typo3 caching mechanism.
with innodb the same as with myisam.

And once the page is cached correctly, the bug isn't appearing any more, until caches are cleared again (with the two buttons in left menu of BE).

SOLUTION:
If you want to avoid this error messages on your live sites, just disable all debugging options in installtool and typoscript. (just as a temporary solution meant! ;) )

@Clemens Riccabona: That's not a real solution, that just hides the problem. Most Users don't recognize this error because they don't enable sql debug, but as developer you enable it.

It's not ok to just hide such problems/errors, it's just a solution to hide it from the customer.

that's why i posted that it is meant as a 'temporary solution'.

BTW IMHO every live system, no matter if it is built in php, c++, c# or anything else should run without debugging options as they are mostly consuming kind of resources.

The problem is here : (class.t3lib_tstemplate.php, line 365)

$GLOBALS['TYPO3_DB']->exec_UPDATEquery('cache_pagesection', 'page_id=' . intval($GLOBALS['TSFE']->id) . ' AND mpvar_hash=' . $mpvar_hash, $dbFields);
if ($GLOBALS['TYPO3_DB']->sql_affected_rows() == 0) {
$dbFields['page_id'] = intval($GLOBALS['TSFE']->id);
$dbFields['mpvar_hash'] = $mpvar_hash;
$GLOBALS['TYPO3_DB']->exec_INSERTquery('cache_pagesection', $dbFields);
}

To prevent an INSERT error, an UPDATE is done and the sql_affected_rows is checked to detect if the row did not exists (and have to be inserted)

But mysql_affected_rows can return 0 even if the row did exist :
Quote from php.net :

When using UPDATE, MySQL will not update columns where the new value
is the same as the old value. This creates the possibility that
mysql_affected_rows() may not actually equal the number of rows matched,
only the number of rows that were literally affected by the query.

So that's the explanation :)
Mayby a SELECT should be done to detect if the row already exists and then process an UPDATE or an INSERT ?

@Popy no-lastname-given

You could just insert my mail, so the solution is also presented ;-)
if (!$this->simulationHiddenOrTime && !$GLOBALS['TSFE']->no_cache) { // Only save currentPageData, if we're not simulating by hidden/starttime/endtime
$dbFields = array(
'content' => serialize($cc),
'tstamp' => $GLOBALS['EXEC_TIME']
);
$mpvar_hash = t3lib_div::md5int($GLOBALS['TSFE']->MP);
$existingCacheRecords = $GLOBALS['TYPO3_DB']->exec_SELECTquery('mpvar_hash, page_id', 'cache_pagesection', 'page_id=' . intval($GLOBALS['TSFE']->id) . ' AND mpvar_hash=' . $mpvar_hash);
if($GLOBALS['TYPO3_DB']->sql_num_rows($existingCacheRecords) > 0){
$GLOBALS['TYPO3_DB']->exec_UPDATEquery('cache_pagesection', 'page_id=' . intval($GLOBALS['TSFE']->id) . ' AND mpvar_hash=' . $mpvar_hash, $dbFields);
} else {
$dbFields['page_id'] = intval($GLOBALS['TSFE']->id);
$dbFields['mpvar_hash'] = $mpvar_hash;
$GLOBALS['TYPO3_DB']->exec_INSERTquery('cache_pagesection', $dbFields);
}
}

Of course, but I did not read it

But why do you select the rows instead of selecting a count(*) ??

Hi all,

your investigation is right, we can't use mysql_affected_rows() for that. There is a function called mysql_info() which provides the info we need, however this is solution again works only with mysql and therefore conflicts with the DBAL.

So the quick workaround is to adapt mysql_info() for that while the longterm solution is part of a rewrite which I have done in class.t3lib_tstemplate.php. Stay tuned for that...

- michael

Why should we use mysql_info ? Jonas Dübi 's solution will work (even if it can be optimized !) and don't need any DBAL adaptation !

see http://bugs.typo3.org/view.php?id=4581#20715
Additional SELECT queries should be avoided what is possible with Michael's rewriting of tstemplate.

There's a bug because somebody did not want to make a SELECT query...

And why avoid SELECT query ? If the core team want to optimize Typo3 and sql queries, It should be great tu use sql_free result before trying to avoid SELECT queries...

Popy, feel free to search for missing sql_free_results and to create a new bug issue with your patch. Thanks in advance!

Really ?
But, will the reports be read and the modification commited ?

Calm down please, dude! Have a look outside the window, and enjoy the nice weather. :-)

After all we don't want to collect workaround solutions since this doesn't solve the overall problem of having an improvable cache management. My resources are limited, so I spend them where I think it makes most sense!

Until then, you are free to add an additional select query on your local system if you think this makes sense.

- michael

I totaly agree with michi, this is a realy core core core function which has to be changed very carefully.

If the core team is rewrithing this part, and if the problem is also solved within this new version I'm happy.

Everyone who don't wanna wait, can easily adapt my change. You could also do a extension which xclasses this function just for a temporary solution which doesn't touch the core.

Hi,

i think this issue has to be continued. I have this problem really often, also by being not as fast - depends on the count of users on the site.

may be i have another Idea:
using the INSERT ... ON DUPLICATE KEY UPDATE ... Syntax.

I know this is a MySQL one, but may be there are synonym syntax for other DB-engines

Info: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

push

I need a solution for this issue.
We have a system with replication of 2 MySQL (Master/slave)
Now we run in the problems with replication

070911 13:09:10 [ERROR] Slave: Error 'Duplicate entry '0b2926e9693aeebd0c43647009044756' for key 1' on query. Default database: 'hallox01'. Query: 'INSERT INTO cache_hash
(
hash,
content,
ident,
tstamp
) VALUES (
'0b2926e9693aeebd0c43647009044756',
'a:20:{s:7:\"config.\";a:17:{s:9:\"extTarget\";s:6:\"_blank\";s:4:\"stat\";i:1;s:16:\"stat_typeNumList\";s:3:\"0,1\";s:21:\"cache_clearAtMidnight\";s:1:\"1\";s:25:\"spamProtectEmailAddresses\";s:1:\"2\";s:33:\"spamProtectEmailAddresses_atSubst\";s:4:\"(at)\";s:20:\"disablePrefixComment\";s:1:\"1\";s:8:\"linkVars\";s:1:\"L\";s:16:\"sys_language_uid\";s:1:\"0\";s:8:\"language\";s:2:\"de\";s:10:\"locale_all\";s:5:\"de_DE\";s:14:\"pageTitleFirst\";s:1:\"1\";s:7:\"doctype\";s:11:\"xhtml_trans\";s:11:\"xmlprologue\";s:4:\"none\";s:15:\"htmlTag_langKey\";s:2:\"de\";s:23:\"simulateStaticDocuments\";s:1:\"0\";s:7:\"config.\";a:2:{s:4:\"stat\";s:1:\"1\";s:10:\"stat_mysql\";s:1:\"1\";}}s:12:\"includeLibs.\";a:21:{s:21:\"tx_lumogooglemaps_pi1\";s:64:\"typo3conf/
070911 13:09:10 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'akne02.000007' position 15958669

So we run in big problems having two different versions of the DB.
I will patch this on this installation, but for next version a patch should be available.

i patched my system by this lines and it works:

$update_content=$GLOBALS['TYPO3_DB']->quoteStr(serialize($cc),'cache_pagesection');
$GLOBALS['TYPO3_DB']->sql_query("
INSERT INTO cache_pagesection (page_id,mpvar_hash)
VALUES (".intval($GLOBALS['TSFE']->id).",$mpvar_hash)
ON DUPLICATE KEY UPDATE
content='$update_content',tstamp=".$GLOBALS['EXEC_TIME']."
");

REPLACE query should work fine too :)

This one gets really annoying when using dbal. Will this be fixed in 4.2?

btw - same error happens sometimes in
t3lib_pageSelect->storeHash

I don't understand that becaus there are 2 queries: delete and insert.
Normally it shouldn't happen, but what i got is

ERROR Duplicate entry '03f58fcec121a5659d41c361df12caea' for key 1
lastBuiltQuery INSERT INTO cache_hash
(
hash,
content,
ident,
tstamp
) VALUES (
'03f58fcec121a5659d41c361df12caea',
'a:20:{s:7:\"config.\";a:17:{s:9:\"extTarget\";s:6:\"_blank\";s:4:\"an>< ... snip',
'TS TEMPLATE',
'1191256420'
)
debug_backtrace tslib_fe->getConfigArray // t3lib_TStemplate->start // t3lib_pageSelect->storeHash // t3lib_DB->exec_INSERTquery // t3lib_DB->debug

Anyway while discussing, Michael said to prevent second query, but here it's done with 2 queries, and the content-packages are heavy weight ;-)

//edit
trying with the same technique as above i got the error
"Got a packet bigger than 'max_allowed_packet' bytes"

Steffen did you try to include the tstamp field to the update query? i.e. into $dbFields?

Hi Nikolas, i tried this one:

$GLOBALS['TYPO3_DB']->sql_query("
INSERT INTO cache_hash (hash,content,ident,tstamp)
VALUES ('$hash','$data','$ident',".time().")
ON DUPLICATE KEY UPDATE
content='$data',ident='$ident',tstamp=".time()."
");

Steffen i thought about the other way round :
$dbFields['tstamp'] = time();
$GLOBALS['TYPO3_DB']->exec_UPDATEquery('cache_pagesection', 'page_id=' . intval($GLOBALS['TSFE']->id) . ' AND mpvar_hash=' . $mpvar_hash, $dbFields);
if ($GLOBALS['TYPO3_DB']->sql_affected_rows() == 0) {
$dbFields['page_id'] = intval($GLOBALS['TSFE']->id);
$dbFields['mpvar_hash'] = $mpvar_hash;
$GLOBALS['TYPO3_DB']->exec_INSERTquery('cache_pagesection', $dbFields);
}
That should prevent the sql_affected_rows() from returning 0 in the case the record already exists but no changes have been applied. Since the tstamp changes.

i did some further testings: setting the tstamp to time doesnt work. I set tstamp to a random value rand(1,1000000) for testing purpose and that seems to work ...
i ll do some further inverstigation.

Hi,

ok the timestamp is way to weak and results in duplicate entrys since its unit is "seconds" so if you fire 2 requests within one second this error will occure.
So there several passable ways to solve this issue:
1. Add a select to determine whether a records exists or not.
2. Change update/insert query to replace query
3. Change tstamp to microseconds
4. Add another unidque identifier field like md5(uniqid(rand(), true)) in order to make the sql_affected_rows approach work.

Cheers,
Nikolas

we have to investigate both:
1) in t3lib_tstemplate -> start
2) in t3lib_pageSelect -> storeHash

Both can throw the exeption.

I think best solution would be to create a "REPLACE"-Wrapper in t3lib_db and use this in both cases.
As to be read in dev-list, Ries is also working on native PG and Oracle solution , so this could be a solution at the core DB-class, and DBAL could also use a mapping for it.

As i saw in 2) there was an error though hash is a unique and a key - may be that's only a timing problem, but by doing that in one query it should be no problem. Also we don't have any native MySQL-SQL in core, only in the dbClass.

hmm - this is kind of a nightmare.

in t3lib_pageSelect -> storeHash

i tried first the INSERT...ON DUPLICATE...
I get error because "Got a packet bigger than 'max_allowed_packet' bytes"

ok, then i tried replace
$GLOBALS['TYPO3_DB']->sql_query("
REPLACE INTO cache_hash (hash,content,ident,tstamp)
VALUES ('$hash','$data','$ident',".time().")
");

I get the same SQL-Error as before:

t3lib_DB::exec_INSERTquery
ERROR Duplicate entry '97f53a7de4f0e723bd9baae3aae52646' for key 1
lastBuiltQuery INSERT INTO cache_hash
(
hash,
content,
ident,
tstamp
) VALUES (
...

Anyway this makes no sense because it happens in a ajax updater of a USER_INT-Object.

I'm stumped with it ...

Steffen:

The replace shouldnt throw an duplicate key entry. Perhaps it happens on several places?

yes, this is absolutely what i thought, but error msg is clearly:

debug_backtrace tslib_fe->getConfigArray // t3lib_TStemplate->start // t3lib_pageSelect->storeHash // t3lib_DB->exec_INSERTquery // t3lib_DB->debug

and there i replaced the 2 sql with the above one ...

Steffen:
t3lib_TStemplate->start:
$GLOBALS['TYPO3_DB']->sql_query("
REPLACE INTO cache_pagesection (page_id, mpvar_hash, content ,tstamp)
VALUES ('".intval($GLOBALS['TSFE']->id)."', '$mpvar_hash', '".$dbFields['content']."', ".$dbFields['tstamp'].")
");

works for me. But as you mentioned above the update/insert thing has to be replaced at several places.

i just wonder if there is even a need to let the update/insert decission to the database at all.
Since it can be done by comparing: $this->currentPageData and $cc at least for the t3lib_TStemplate->start: thing.

thx Nikolas,
in tstemplate i had the INSERT...ON DUPLICATE... but this seemed to throw the error (unbelievable but true)

Now i have in both places the REPLACE-Syntax and it seems to work

And you're right, there is no comparison. Maybe it could be done very simple, because all is collected in $cc as an array, so IMHO the comparison has to be like this:

if(md5(serialize($GLOBALS['TSFE']->all)) != md5(serialize($cc))) {...}

but i'm not sure if $GLOBALS['TSFE']->all holds the right data.

Steffen: yes right imho it can be done with simple comparison but i am not that much into it.
But fireing that query just when it is really needed would result in
slighly better performance.

But as far as i can see insert is only needed if
t3lib_TStemplate::getCurrentPageData ( )
http://doxygen.frozenkiwi.com/typo3/html/db/dfa/class_8t3lib__tstemplate_8php-source.html

Returns "none".
Otherwise an update should be sufficient. Perhaps improved by some intelligent comparison.

Please check if this patch fixes your problems.

Hi Michael,

i got an error applying this patch
"An unknown line type was found in line 12"

What could be the reason?

Hi Steffen,

that's probably because i edited the patch in an editor, and it stripped the blanks in empty lines. I uploaded a new patch which applies fine against Trunk of today.

- michael

By the way: Today I will leave for a long holiday trip and won't be back before end of this year.
I had a phone call with Oliver Hader yesterday who is aware of the things I have changed, so he will try to bring this change into TYPO3 depending that you confirm that it helps...

Have a nice time!
- michael

ok, have a nice holiday (wow, such long, i want that too :-) )

Michael:

The patch fluently applies against 4.2.0alpha1 and seems to resolve this issue. :)
But: An insert statetement is only needed if getCurrentPageData returns false right? So atm the decission wether a update/insert is needed is made on isCached.
Doenst it makes sense to performe a more well defined decission ? I.e. avoide fireing 2 querys in case getCurrentPageData==false?

i applied the patch and it doesn't helped me:

ERROR Duplicate entry '151-222419149' for key 1
lastBuiltQuery INSERT INTO cache_pagesection ...

sry ;-)

Bug is also in 4.1.3.

for me it worked to apply the patch and "not force" the caching at all i.e. remove all lines containing config.no_cache = 0 or 1
see http://lists.netfielders.de/pipermail/typo3-team-core/2007-January/006926.html

the problem occured for me when rendering a direct_mail newsletter and because it renders the plain text version and the html version at the same time, the bug always came up in the html mails that were sent!

What's the status with this patch?

Hi Ingmar!
before I left for holiday I showed this to Oliver Hader who wanted to have a further look at it. If he could not yet, then please commit it as experimental and let me do the cleanup when I am back again.

Regards, michael

Fixed in TYPO3 4.2