TYPO3 Core

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

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.

Just changed some comments on the patch file.

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.

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.

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.

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...

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.

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

Thank you anyway.

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.

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.

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