Duplicate entry on cache_pagesection on reloading twice
If I reload a page in the frontend twice or two or more persons access the same page at almost the same time, an SQL error in tabe cache_pagesection is show.
I have defined in TypoScript, that the output should not be cached at all:
config.no_cache = 1
On enabling cache by "no_cache=0" the error disappears.
See the attached HTML file to get the detailed error (sqlDebug) message.
(issue imported from #M4581)
#1 Updated by Oliver Hader almost 13 years ago
If a site is reloaded twiche, 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 this happens, t3lib_TStemplate thinks, that the record does not exist and makes an INSERT query.
So, I created a patch for this, that uses mysql_info() to determine if a INSERT query is necessary. See the attached file 0004581.patch
#2 Updated by Michael Stucki almost 13 years ago
notice that mysql_info was first introduced in PHP 4.3.0, and I am not sure if the decision was already made if older versions should still be supported or not. See core list for details.
However, as it seems, usage of mysql_info() is inevitable to fix this: | 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.
#10 Updated by Oliver Hader almost 13 years ago
Christian, the DELETE/INSERT queries were replaced by a UPDATE query on proceeding towards 4.1.x. So 4.0.x has the old DELETE/INSERT variant.
Did you test this issue on TYPO3 4.1-beta3?
REPLACE could also be a possibility but I don't know how this behaves on other DBMS like PostgreSQL or Oracle and if it's thus cross-database-independent.
#11 Updated by Christian Zehaczek almost 13 years ago
REPLACE was just a fix for my local source connected to mysql 4.1 and i think it won't be part of 4.1beta or later. If the UPDATE query was fixed there by using sql_info(), good, but it didn't help me on 4.0.2 either.
What may be interesting for Michael is that i also replaced the 'cache_pages' DELETE/INSERT behaviour in class.tslib_fe, you should check this in 4.1beta (also using UPDATE now?). Maybe sql_info() may avoid another 'duplicate key' bug there too.
#12 Updated by Michael Stucki almost 13 years ago
Fixed in 4.1. For an explanation of the final change, see http://lists.netfielders.de/pipermail/typo3-team-core/2007-January/006926.html and later mails in this thread.