SQL Keys in Depth • Hacker News Board

Re: SQL Keys in Depth

#81

Post by chrisconroy » Tue, Jan 02, 2018, 2:26 PM UTC

barrkel wrote: post #76

chrisconroy wrote: post #71

Earlier quoted context omitted.

auto_increment doesn't lock the table. When you use an auto_increment column, MySQL will grab the next int as it creates the insert write-ahead log message. Two concurrent transactions with T1 beginning first and T2 beginning second but actually committing out of order can thus have out of order ids. e.g. T2(id=10) T1(id=9) Also, note that this means auto_increment IDs are not continuous (read: a reader looking after…

I'm not talking about locking the table. I'm talking about locking a table resource. https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.htm... Quote: "While initializing a previously specified AUTO_INCREMENT column on a table, InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column. In accessing the auto-increment counter, InnoDB uses a specific AUTO-INC table lock mode w…

Gotcha. Thanks for the clarification!

Yes, even if you're using normal insert statements, it's good to limit the size of any one transaction to the extent you can for a host of reasons!

Re: SQL Keys in Depth

#82

Post by EdwardDiego » Tue, Jan 02, 2018, 2:26 PM UTC

tome wrote: post #40

hug wrote: post #21

Earlier quoted context omitted.

I mean, maybe I am, maybe I'm not. How many other languages from the '70s haven't been replaced in a domain where they should have been? And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-val…

> And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-value worth mentioning. Do you believe that all keyboard layouts are of indistinguishable practicality? That seems to be the inescapable c…

> Do you believe that all keyboard layouts are of indistinguishable practicality?

If you have evidence to the otherwise, a lot of us would be keen to read it.

Re: SQL Keys in Depth

#83

Post by onion2k » Tue, Jan 02, 2018, 2:28 PM UTC

yeukhon wrote: post #61

onion2k wrote: post #56

Earlier quoted context omitted.

If your system already assumes email must be unique... ...then you're not building a system that works with the way users work. Some (very small) percentage of your users will have problems. I first encountered this problem when I asked my parents to test my first startup. They shared an email account. When they signed up to things they just used the shared account. It was fine until they wanted separate accounts on…

> It was fine until they wanted separate accounts on the same service and it failed. When it comes to designing a system, I try my best to prioritize this way: security, privacy, and finally usability. A product with the best user-experience may have the most vulnerable design. I say this because we need to balance between security and UX. Pick some constraints which make sense to your system. If we have to accommoda…

They want a separate account for some specific reasons, I assume, so why not help them?

That's the point - they didn't want different email accounts. They wanted to share one. There will always be edge cases that don't conform to your expectations of how users should be doing things, and your applications probably need to work even in those less-than-ideal circumstances. If your app is going to be used by older people who might share an email address then I wouldn't make the account process require a unique address for every user.

I actually go a step further these days and try to make things that don't even require any email address. The less personal information required to create an account the better. So long as users are aware that they won't be able to recover an account if they forget their password there's no real need to ask for an email in the first place.

Re: SQL Keys in Depth

#84

Post by Jwarder » Tue, Jan 02, 2018, 2:29 PM UTC

pc86 wrote: post #72

onion2k wrote: post #26

Earlier quoted context omitted.

It's also worth noting that the same is true for email addresses. People share them, so two people can have the same address.

While technically accurate, this is non-standard to such a degree, and represents such a minority percentage of the population, that "don't do this" really is an appropriate response to the user.

I'm curious how often people share e-mail addresses. I used to see it a lot for people who used their ISP's mail system. Most people who are even slightly tech oriented have gmail, hotmail, etc these days, but now I can't help but wonder how many people just stick with the default ISP e-mail.

Re: SQL Keys in Depth

#85

Post by [deleted] » Tue, Jan 02, 2018, 2:34 PM UTC

barrkel wrote: post #76

chrisconroy wrote: post #71

Earlier quoted context omitted.

auto_increment doesn't lock the table. When you use an auto_increment column, MySQL will grab the next int as it creates the insert write-ahead log message. Two concurrent transactions with T1 beginning first and T2 beginning second but actually committing out of order can thus have out of order ids. e.g. T2(id=10) T1(id=9) Also, note that this means auto_increment IDs are not continuous (read: a reader looking after…

I'm not talking about locking the table. I'm talking about locking a table resource. https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.htm... Quote: "While initializing a previously specified AUTO_INCREMENT column on a table, InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column. In accessing the auto-increment counter, InnoDB uses a specific AUTO-INC table lock mode w…

[deleted]

Re: SQL Keys in Depth

#86

Post by Ericson2314 » Tue, Jan 02, 2018, 2:45 PM UTC

hug wrote: post #21

Terr_ wrote: post #19

Earlier quoted context omitted.

I think you're discounting network-effects and historical inertia. Even things which are functionally very simple to implement won't necessarily catch on, ex: "If you could make keyboard layouts more practical than QWERTY, then you would have done so by now."

I mean, maybe I am, maybe I'm not. How many other languages from the '70s haven't been replaced in a domain where they should have been? And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-val…

Sure SQL is pretty good as far as entrenched 70s techs go, but that's a pretty low bar to clear for design (separate from making something which people actually use).

My immediate follow-up post https://news.ycombinator.com/item?id=16050403 had some pretty concrete starting points. What more do you want to envision the entire language?

Re: SQL Keys in Depth

#87

Post by tome » Tue, Jan 02, 2018, 2:54 PM UTC

EdwardDiego wrote: post #82

tome wrote: post #40

Earlier quoted context omitted.

> And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-value worth mentioning. Do you believe that all keyboard layouts are of indistinguishable practicality? That seems to be the inescapable c…

> Do you believe that all keyboard layouts are of indistinguishable practicality? If you have evidence to the otherwise, a lot of us would be keen to read it.

I'm happy to take hug's claim that there is no such evidence at face value. What I'm really interested in, however, is whether the absence of such evidence causes him/her to believe that there is no difference.

Re: SQL Keys in Depth

#88

Post by Sean1708 » Tue, Jan 02, 2018, 2:55 PM UTC

laumars wrote: post #75

simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

That's not the only point of the article I really disagree with: > Here are some values that often work as natural keys: > login names This makes user names static. Which would annoy people if they change name (eg marriage, gender change, nationalization change (Chinese name vs English name)) or they just want to update their online handle. > email addresses Same problem as above. What happens if someone wants to cha…

>> login names

> This makes user names static.

Does it? I can understand why making it a foreign key would make it static, but why would making it a normal key make it static? It seems to me that making login names unique would be preferable, same with emails.

Re: SQL Keys in Depth

#89

Post by ProblemFactory » Tue, Jan 02, 2018, 2:58 PM UTC

dullgiulio wrote: post #80

ProblemFactory wrote: post #32

Earlier quoted context omitted.

UUIDs (and other mostly random IDs) also have the important advantage that they can be generated offline - in mobile apps, client-side javascript or elsewhere. With autoincrement keys, you need to either contact the database server for each data object you create, or implement complex logic to replace temporary IDs with database IDs later.

Nope, you can't. You still have to check for uniqueness on the server side before committing them, so you are back from the start. Always keep in mind that anything coming from the client cannot be trusted.

You can't blindly overwrite existing data based on the UUID, sure.

But I find that the security checks against "stealing UUIDs" are much simpler to implement than a system of composite primary keys (deviceid+autoincrement or userid+autoincrement) or some sort of temporary primary keys. If you see suspicious UUIDs, you can just discard the data.

Having the client generate the "final" IDs for objects is particularly convenient when the client can produce a complex graph of objects offline, for example an order + order items + comments + more. If using temporary IDs until first contact with server, all these links would have to be patched up when the objects get real server-side-assigned IDs.

Re: SQL Keys in Depth

#90

Post by tragomaskhalos » Tue, Jan 02, 2018, 2:59 PM UTC

foreigner wrote: post #33

At what scale does all this stuff start to actually matter? I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems. We use auto-incrementing integers as primary keys and try not to expose them externally. Indexes are added as necessary to enable specific queries. We don't enforce any other constraints (e.g. not-null or foreign keys) at the database level. ...…

I would certainly look to add not-null and uniqueness constraints, although you might find that by now your data is actually violating some of those constraints!

A lot of people take the view that the application code is sufficient proof against bad data getting into your database, but this ignores (a) bugs and (b) back-door data loading, which in most applications will almost certainly be happening at some point.

The data in your database is the crown jewels (the application that fronts it is by comparison a piece of crap that you can replace at any time), so put guards on that gate ...