MySQL - Can 'INSERT INTO - ON DUPLICATE KEY UPDATE' cause data loss

Question

I have a caching script that requests a bunch of data from a SOAP API using PHP (Cron job every 5 minutes). This script requests and stores a customer id and name.

The table that the API information is stored in has 3 columns:

'id' = int, Primary_key
'name' = varchar(255)
'paying' = bool

There is around 10 (in 80) customers with the bool paying set to true. However, every once in a while the all customer's paying columns revert to 0.

So... Can the following query cause the paying column to change under any circumstances?

INSERT INTO customer(`id`, `name`) VALUES ('$escapedId','$escapedName') ON DUPLICATE KEY UPDATE `name`='$escapedName'

Do you have a unique index on the customer table in your database? or any other index? — kejsu
– kejsu, Commented May 9, 2016 at 7:57
@kejsu The id column is the primary key, the columns id and name are provided by the API. — kgongonowdoe
– kgongonowdoe, Commented May 9, 2016 at 7:59

Your Common Sense · Accepted Answer · 2016-05-09 08:23:38Z

1

This query couldn't change the 'paying' field state.

Therefore, most likely the reason is some other code that is either setting the value to 0, or just deleting all records.

answered May 9, 2016 at 8:23

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - Can 'INSERT INTO - ON DUPLICATE KEY UPDATE' cause data loss

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related