Bug #16751

Duplicate entry on cache_pagesection on reloading twice

Added by Oliver Hader almost 13 years ago. Updated over 12 years ago.

Status:
Closed
Priority:
Should have
Category:
Communication
Target version:
-
Start date:
2006-11-29
Due date:
% Done:

0%

TYPO3 Version:
4.1beta1
PHP Version:
5
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

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)

cache_error.htm View (4.6 KB) Administrator Admin, 2006-11-29 11:05

0004581.patch View (1.88 KB) Administrator Admin, 2006-12-15 12:11


Related issues

Related to TYPO3 Core - Bug #17088: "Duplicate entry" in cache_pagesection when using frames Closed 2007-03-07
Related to TYPO3 Core - Bug #17053: cache is not saved properly Closed 2007-02-28
Related to TYPO3 Core - Bug #18253: Duplicate entry on cache_pagesection on reloading twice Closed 2008-02-19
Duplicated by TYPO3 Core - Bug #17147: Duplicate entry on cache_pagesection Closed 2007-03-23

History

#1 Updated by Oliver Hader almost 13 years ago

According to http://www.php.net/manual/en/function.mysql-affected-rows.php:

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

Hi Oliver,

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.

#3 Updated by Oliver Hader almost 13 years ago

Just changed some comments on the patch file.

#4 Updated by Oliver Hader almost 13 years ago

Hi Michael,

an alternative would only be to make a SELECT query to determine if there is already a record, and decide on that to take UPDATE or INSERT afterwards.

#5 Updated by Michael Stucki almost 13 years ago

Let's avoid additional queries, if possible. I will read through these threads on the core list and if they already decided to drop PHP < 4.3 support, no further action will be needed.

#6 Updated by Christian Zehaczek almost 13 years ago

Hi Michael,

is there a way to patch this in 4.0.2 ?

We got heavy problems on our site based on this bug and the patch file cannot be applied due to different logic in ts_template.

I would be pleased if you could help me.

#7 Updated by Michael Stucki almost 13 years ago

Christian,
you can "patch" your files manually. Just open the patch in a text editor and see where the changes were made. Shouldn't be a big problem after all...

#8 Updated by Christian Zehaczek almost 13 years ago

Hi,

that's what i did but there is no UPDATE query at all. 4.0.2 just makes DELETE and INSERT afterwards. This sounds like the problem won't occur in 4.0.2, but it definitively does on heavy load.

#9 Updated by Christian Zehaczek almost 13 years ago

I solved it by adding REPLACE INTO support to t3lib_db and make use of it instead of separated DELETE/INSERT.

Thank you anyway.

#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

Hi Oliver,

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.

- michael

Also available in: Atom PDF