update or delete insert?

[jodunno]

Hello,

I am not a very good programmer and i really don't know professional methods to accomplish many taska. I've managed to build a login system for my website and it works. I've finally started revisiting my sql code to add new features and i noticed some files in mysql folder. One particular file is named after my user_settings table with .ibd extension. I looked at the file with Notepad++ and i see a sort of history of background images. I have a feature on my site which allows users to choose a background photo for the site. It seems that all of my selections are stored in this file like a history. I use the update sql command to write your new selection to the database. I now think that delete and insert is a better method to avoid this history.

How is this process  of changing field data normally done by pros? delete, then insert? update with a history is not good when changing passwords and usernames. I guess that i chose update because it worked. I thought that it deleted the current field data then added the new data. oops!

Thank you and stay healthy.

[requinix]

Don't worry about the IBD file. MySQL knows how to manage itself, you don't need to go second guessing it because of what you think you saw in Notepad.

The question you think you're asking is whether to use an UPDATE or a DELETE+INSERT, but the question you're actually asking is how you should manage uploaded files that can be replaced.

The answer to that is... well, it depends. There are two basic options:

1. Forget the previously uploaded file. You don't care about it. Take the new file and stick it wherever you want, update the database, and delete the old file. Gotta delete. Because if you forget about the old file then there's not much of a point to keeping the file itself around too.
2. Keep track of the previous file. You'd probably want a table that holds all the information for past and future uploads, and that's where you track them. For using those files, instead of storing the file information in whatever place, you reference the file in your upload information table. New image, new information row, and you update whatever place was affected. This lets you keep a history of everything, which probably isn't important for stuff like user avatars but is frighteningly important for stuff like monetary transactions.

"Okay, I've decided that I want to do <whichever option>. But what about my literal question? Should I update or delete and insert?"

Time to learn about an important concept in computing that disappointingly few programmers ever end up learning: atomicity. That's the noun version of "atomic", which means (in this case) that whatever operation you need to do can't be interrupted or broken in half or appear to anyone else as being anything less than one single action. Atomicity is important for stuff like files and databases because you basically never want to look at a file or data in the middle of some important operation.

Imagine your site is popular. Really popular. Facebook or Twitter popular. Constant traffic to your servers. Now imagine a user uploads a new image. When the code is ready, it needs to go off into the database to make whatever changes it needs to make so the user has the new image.

Say you go with DELETE and INSERT. Your code runs one query that DELETEs whatever, then another query that INSERTs. Sounds fine. Except remember how your site is always busy? It's quite possible someone was looking at your site at the moment in between those two queries. Since the DELETE has happened but not yet the INSERT, your code isn't going to find whatever data it needed to find and the user is going to get a bad experience. If that user was a CEO for a huge company that wanted to buy you out for lots of money, they might not do that now. A DELETE and INSERT is not atomic because there was that point in between the two queries. It was not "one single action".

Instead you go with UPDATE. The database does whatever it does, but the clever people who wrote the software for it already knew about stuff like atomicity. And they made their system guarantee that UPDATEs are atomic. One single action. If you do an UPDATE when that rich CEO looks at your site, the database has guaranteed to you that either (a) the CEO will see the old data because the update hasn't happened yet, or (b) they'll see the new data because the update has happened. There is no moment in between old and new for stuff to be broken.

[jodunno]

Hi Requinix,

it is a lovely informative post by you (as usual because i always enjoy your messages. I learn alot from you. I remember always that you helped me learn regex on my own and i always appreciate you.)

Still, i am having difficulty excusing the history. I don't like that user background photo preferences are stored like a history or cache. bg preferences should be private. I have tried to find a method that removes the entry (from idb file as well), thereby replacing it with a new value. I thought that update does it but apparently not. interesting. I'd hate to have a history of user names and passwords even hashed passwords (which i use hashed passwords).

i am not familiar with the term atomicity but i all ready think about this concept. worry is a better word. I know that i have to deal with delete at some point and i am not experienced enough to know correct methods. I figure that i would have to loop while error is not present or maximum tries/loops have been met. then store the original data before delete (via session variable at login). if delete successful then insert new value. if no error then operation complete. select new data and compare to new data post to verify no corruption. finished or rinse and repeat until max tries then restore old data and exit with error. I really don't know if this is correct procedure or not and it sounds alot like the built in transaction.

anyway, i tried replace and it works but it seems to me that it is like deleting then reinserting. I suppose that i could encrypt all of my data and stick with update. But i would like to know what you think of replace? here is my quick and dirty test code at the console:

create database testBed character set utf8mb4 collate utf8mb4_unicode_ci;
use testBed;
create table usersettings(id mediumint unsigned not null auto_increment, bgphoto varchar(32) default 'bgfactory', bgcategory varchar(3) default '0', primary key(id));
INSERT into usersettings SET bgcategory = 0;
INSERT into usersettings SET bgphoto = 'testinsert', bgcategory = 1;
describe usersettings;
SELECT bgphoto FROM usersettings WHERE id = 1;
SELECT * FROM usersettings;
REPLACE INTO usersettings SET id=1, bgphoto = 'namechanged'; 
SELECT * FROM usersettings;

what do you think about replace? do you suggest that i just encrypt and stick with update?

Thank you very much for your expertise. Please stay healthy, requinix.

[requinix]

5 hours ago, jodunno said:

I don't like that user background photo preferences are stored like a history or cache. bg preferences should be private.

Okay. Fine. So don't track history. And now that you've made a decision I call tell you that I wouldn't store a history for this either.

 

5 hours ago, jodunno said:

I have tried to find a method that removes the entry (from idb file as well),

Stop caring about that file.

 

5 hours ago, jodunno said:

i am not familiar with the term atomicity

I guess you missed the rather significant part of my post where I tried to explain what it is.

 

5 hours ago, jodunno said:

I figure that i would have to loop while error is not present or maximum tries/loops have been met.

Not sure what you're talking about but I'm 99% sure "loop while error is not present" is not the answer.

 

5 hours ago, jodunno said:

then store the original data before delete (via session variable at login). if delete successful then insert new value.

sigh

I'm done.

[jodunno]

Hi requinix,

No need to sigh, i'm sticking with update. Your opinion is good enough for me. Thank you for the lovely posts. You have been very helpful.

Best wishes to you and all members.

[gizmola]

1 hour ago, jodunno said:

Hi requinix,

No need to sigh, i'm sticking with update. Your opinion is good enough for me. Thank you for the lovely posts. You have been very helpful.

Best wishes to you and all members.

Hi jodunno.

I thought I'd chime in and say one thing about your original concern.

A relational database is meant to be a black box.  You never need to poke in the individual files the underlying database server creates or manipulates.  You only talk to your database and manipulate it via SQL.  A relational database management system like MySQL is inherently a client/server system where the server runs persistently and the client connects and talks to it via SQL.  

The .ibd file is literally the innodb file for the user_settings table.  Anything you do to that file would likely corrupt and destroy the table and its contents.  Innodb files are actually clustered btree indexes stored in primary key order.  You have root/master access to your data.  This gives you a way of looking at those files, but other people can't.  

[jodunno]

Hi gizmola,

Thank you for taking time to reply. I appreciate your expertise with this subject. I'm a bit like a programming sheriff: trust noone and nothing / suspect everything. I don't even trust my own code. i use a counter in my foreach loops to be certain that they cannot become infinite loops via tampering:

$count = 0; $maxentries = 100;
++$count; if ($count === $maxentries) { //i said 100 so why is this still going? exit now }

i have designed my site to detect as many errors as possible. if file exists, if function exists, if isset everything. I don't want to help a hacker wreck my site. No stepping stones from me. I look at everything and question everything. In this case, i was startled by the history of background image names in the idb file like a fingerprint.

Your explanation is most helpful to me. I am comfortable with this info. I've retained update as it is the best option. I can only do so much anyway. Atleast now i know more than i did yesterday.

Best wishes to you and all members. I hope that you have a pleasant day. Stay safe and healthy and make the most of life, my friends.