sql update same record no work

[Jakofff Parker]

Hi..

Hoping that someone can help me describe the following error that I have with f3.

I have an update that I execute directly with exec, however if I do update and the record matches the exec returns 0 modified registers, that is, it does not seem to update, but I do require that if I do it, I think it could be a problem of ttl but I can not solve it, someone has happened to him?

$resultSQL = $this->db->exec($sql, $fieldsValues, -1);

if ($resultSQL === FALSE) {

} else if($resultSQL >= 0) {

}

thanks

[ved]

Hi,

When asking for support, please post the code that's causing the issue along with a basic explanation of the issue.

Without code, it will be hard for anybody here to give you any support.

Cheers

On Saturday, June 29, 2019 at 12:00:28 AM UTC+1, Jakofff Parker wrote:

Hi..

Hoping that someone can help me describe the following error that I have with f3.

I have an update that I execute directly with exec, however if I do update and the record matches the exec returns 0 modified registers, that is, it does not seem to update, but I do require that if I do it, I think it could be a problem of ttl but I can not solve it, someone has happened to him?

thanks

[Jakofff Parker]

ready!

[ved]

Hi,

Thanks for posting some code, but it's still not clear what exactly is your issue.

Can you please clarify this sentence:

 however if I do update and the record matches the exec returns 0 modified registers, that is, it does not seem to update, but I do require that if I do it

You can also call $f3->db->log() to check for any query errors

[Jakofff Parker]

What is happening is that if I send an update on a record it makes it perfect, it returns the number of records that I updated, the problem is that if I send the same update again to the same record in a short period of time, it does not execute the update and it returns me that I update 0 records.

I think the problem has to do with the ttl but I can not identify it, it already adds -1 to the execute and it does not work.

I will try the logs to see if I find any errors

thanks.

[ved]

Hi,

I doubt this is an F3 issue and it will most likely be some SQL issue or with the code itself.

As for the cache, if you leave the $ttl parameter empty, then nothing will be cached. Also note that for caching to work you have to enable the CACHE framework variable.

When debugging strange issues it's always a good idea to set CACHE to false and make sure everything is working with the cache disabled.

Now, the information we have is still not enough to help you that much.

We're going to need to see the query and either the output from log() or from the database logs in order to see exactly what queries are getting executed so we can see if we can figure out what's going on.

Cheers,

[Jakofff Parker]

Now activate the db log, I attach the log that shows my app, where it is seen that the first time executes an update correctly because it has another value in a field, but in the second time to have the same value in that field no longer he does it

Wed, 03 Jul 2019 14:49:05 -0500 [::1] 6|GenericDAO::edit|DB Log(0.1ms) SELECT rv.id_pregunta_respuesta, rv.valor 

                        FROM respuestas_valor rv

                        WHERE rv.id_usuarios_encuestas = 1 

                        AND rv.id_pregunta_respuesta = 1

(0.1ms) UPDATE respuestas_valor

                                SET valor = 1

                                WHERE id_usuarios_encuestas = 1 

                                AND id_pregunta_respuesta = 1

Wed, 03 Jul 2019 14:49:05 -0500 [::1] 6|GenericDAO::edit|Query Time [ms]: 53

Wed, 03 Jul 2019 14:49:05 -0500 [::1] 6|GenericDAO::edit|no se actualizaron los datos

Wed, 03 Jul 2019 14:49:05 -0500 [::1] 6|GenericDAO::edit|resultSQL: 1

Wed, 03 Jul 2019 14:50:06 -0500 [::1] 6|GenericDAO::edit|DB Log(0.1ms) SELECT rv.id_pregunta_respuesta, rv.valor 

                        FROM respuestas_valor rv

                        WHERE rv.id_usuarios_encuestas = 1 

                        AND rv.id_pregunta_respuesta = 1

(0.1ms) UPDATE respuestas_valor

                                SET valor = 1

                                WHERE id_usuarios_encuestas = 1 

                                AND id_pregunta_respuesta = 1

Wed, 03 Jul 2019 14:50:06 -0500 [::1] 6|GenericDAO::edit|Query Time [ms]: 47

Wed, 03 Jul 2019 14:50:06 -0500 [::1] 6|GenericDAO::edit|no se actualizaron los datos

Wed, 03 Jul 2019 14:50:06 -0500 [::1] 6|GenericDAO::edit|resultSQL: 0

check resultSQL

thanks

[ved]

Hi,

So, when you run the update query the first time, you set valor=1. So, "valor" wasn't "1" before this first update, so the database changes and returns 1 changed value (which is correct and what you want)

Then, you run the same update again, set valor=1, but "valor" was already "1" from the previous update query, so you get no modified values. But your code is still dependent on getting a 1 here which is what you'll most likely have to change.

So, assuming what I said above is correct, and your database is MySQL, then this is the correct behavior according to MySQL's documentation, where it states:

If you set a column to the value it currently has, MySQL notices this and does not update it.

 (not sure about other databases, but I'm assuming most will behave the same for performance reasons)

Which means that everything is working correctly, in that only when the value gets actually updated, you get a 1 back.

So you should maybe change your code to not depend on the return value from that update, or maybe even find some other way to completely avoid running the same query twice.

A quick alternative fix: If you also create some sort of "modified" or "last_updated" column with a timestamp, and then always update it along with "valor", since the timestamp for the second update will be different from the first, then the database will really have to update the row, so you should get 1 on both updates.

Hope it helps, good luck

[Jakofff Parker]

Thank you!!! I had not thought that MySQL itself had that form that functionality. I remember doing the same with older versions and have no problem.

And the solution you comment makes sense, in fact I have it in other tables, but in this I do not need it, however now if I can do the update.

Postdata: Forget that the error is TTL !!

[ved]

Hi, 

No problem, glad we could help.

Have a nice day.

Cheers.