5 ms·

I like the fact that MySQL is so light on features that it forces you to put more of the burden on your application. With richer RDBMSes like Oracle, MSSQL, an

I like the fact that MySQL is so light on features that it forces you to put more of the burden on your application. With richer RDBMSes like Oracle, MSSQL, and Postgres, people tend to throw too many responsibilities on the DB--which is not so good in the one part of the system that depends on global locks. That, and the article is kind of a half-truth when it comes to the MySQL warts. A big one he missed is the utf8_unicode vs utf8mb4_unicode fiasco, which is still a thing to the best of my knowledge...

sitharus 9y ago
I'm not sure what you're getting at. Good RDBMSs do not have any global locks, table level at most, and can easily handle thousands of reads and writes per second on modern hardware while maintaining isolation between transactions. Once you approach the limits of an RDBMS you're in really large application territory and very few of us will be working for a Facebook or Google.
- jstewartmobile 9y ago
  I take it you have never had to work on a legacy Oracle solution with triggers and PL/SQL scattered about everywhere. I'm just saying I like separation of concerns.
  - sitharus 9y ago
    You can make a mess out of any development system, and a knee-jerk reaction to throw out RDBMS guarantees is as sensible as ignoring Javascript because legacy jQuery made the UI really hard to understand and debug. I've worked on some large apps and almost always had separation of concerns - the database is ultimately responsible for data integrity. It will validate foreign keys, data ranges and types, but does not implement write business logic. This takes advantage of the amazing work put in to query planners but avoids most of the magic that triggers cause.
    - jstewartmobile 9y ago
      Where did I say throw out RDBMS? MySQL does fk/pk restriction, transactions, field enforcement (contrary to article), and all of the other good ACID stuff just fine. I'm complaining about things like PL/SQL letting you send an email inside a trigger (http://www.orafaq.com/wiki/Send_mail_from_PL/SQL http://www.orafaq.com/wiki/Send_mail_from_PL/SQL), or Postgres's XML processing features. I don't think that belongs in the DB. It's easy to say "don't do that" to a small team, but when you get to the corporate shops that do Oracle, that becomes a much harder thing to enforce.
      - snuxoll 9y ago
        There can be more to data integrity than foreign keys and transactions. We have an application to track and distribute work to employees, there can only be one active work item per item type on an account - we use MSSQL for this and originally enforced this with a CHECK constraint, which ultimately failed, we switched to a partial index (CREATE UNIQUE INDEX ON activities(account_id, activity_type) WHERE activity_state = 0) which works flawlessly. There is absolutely no sane way to implement this at the application layer, and even then we have additional applications and administrative work that may access this database directly so we can't rely on the application enforcing data integrity anyway. MySQL doesn't even support CHECK constraints, nor does it have partial indexes - while even SQL Server has supported partial (filtered) indexes since SQL Server 2008 (nearly a decade after PostgreSQL implemented them, but hey, still at least a decade before any of the MySQL family will have them). Should you put application logic in your database? No, probably not. But you should always make sure your database prevents ANY invalid data from being put into it, your little application won't be the only one touching that database forever in all likelihood.
        Mouse47 9y ago
        >CREATE UNIQUE INDEX (filtered) Ha...that's a bit of a hack! I've had to do a similar trick for a unique constraint on columns where we didn't care if the value was null. SQL Server (for some reason) considers null a value in this case; in other words, there could only be one null value in the table for purposes of a unique constraint. E.g: create unique index on (col) where col is not null
  - matwood 9y ago
    You can write unmaintainable code anywhere in your stack. The problem I routinely see with approaches similar to yours is the app code ends up poorly recreating functionality that the rdbms was built to do.
    - jstewartmobile 9y ago
      MySQL is an RDBMS. What is my approach? I just don't think sending emails and parsing XML should run in the database.
pmontra 9y ago
The data should be handled by the database, especially when there might be many different applications accessing it. Think of microservices, maybe written in different languages and frameworks, plus some devops running queries by hand on the db. The ability to stop modifications inside the db is the ultimate safeguard against data corruption. If you really value your data you should also implement validations with CHECK statements and not only in the application. Think about the database as another microservice worth its own logic.
- concede_pluto 9y ago
  This. You don't want to reimplement all your validation in each of your applications (plural!), and you're in for a nasty surprise if you think your entire organization has only one application using your database now and forever.
  - pmontra 9y ago
    Actually you probably should. Front end (JS SPA) for the convenience of users. Back end API (any web framework) for the convenience of developers, the users of the API. The database to make sure nobody can corrupt the data. In my experience the validations in the database almost never go past foreign keys, null fields and bounds on numeric data. Anecdote. I remember when the consensus of Rails developers was not to use foreign keys: Rails didn't have a way to define them so they probably didn't matter, right? I laughed and added them to every application of mine. Rails has foreign keys now, maybe since version 3. There are some gems to generate CHECK statements from validations.
- jstewartmobile 9y ago
  You are correcting something I never said. MySQL has primary/foreign key checking, field validation, transactions, etc. I have nothing against RDBMSes. I like them! I'm talking about not doing shit like XML parsing (https://www.postgresql.org/docs/9.6/static/functions-xml.html https://www.postgresql.org/docs/9.6/static/functions-xml.htm...), or LDAP queries (https://docs.oracle.com/cd/B10501_01/network.920/a96577/dbmsldap.htm https://docs.oracle.com/cd/B10501_01/network.920/a96577/dbms...) in the database. I don't think those things belong there. Serves me right for leaving a blank on HN! People here just love to fill those with 'herp derp'.

sitharus 9y ago

I'm not sure what you're getting at. Good RDBMSs do not have any global locks, table level at most, and can easily handle thousands of reads and writes per second on modern hardware while maintaining isolation between transactions. Once you approach the limits of an RDBMS you're in really large application territory and very few of us will be working for a Facebook or Google.

jstewartmobile 9y ago
I take it you have never had to work on a legacy Oracle solution with triggers and PL/SQL scattered about everywhere. I'm just saying I like separation of concerns.
- sitharus 9y ago
  You can make a mess out of any development system, and a knee-jerk reaction to throw out RDBMS guarantees is as sensible as ignoring Javascript because legacy jQuery made the UI really hard to understand and debug. I've worked on some large apps and almost always had separation of concerns - the database is ultimately responsible for data integrity. It will validate foreign keys, data ranges and types, but does not implement write business logic. This takes advantage of the amazing work put in to query planners but avoids most of the magic that triggers cause.
  - jstewartmobile 9y ago
    Where did I say throw out RDBMS? MySQL does fk/pk restriction, transactions, field enforcement (contrary to article), and all of the other good ACID stuff just fine. I'm complaining about things like PL/SQL letting you send an email inside a trigger (http://www.orafaq.com/wiki/Send_mail_from_PL/SQL http://www.orafaq.com/wiki/Send_mail_from_PL/SQL), or Postgres's XML processing features. I don't think that belongs in the DB. It's easy to say "don't do that" to a small team, but when you get to the corporate shops that do Oracle, that becomes a much harder thing to enforce.
    - snuxoll 9y ago
      There can be more to data integrity than foreign keys and transactions. We have an application to track and distribute work to employees, there can only be one active work item per item type on an account - we use MSSQL for this and originally enforced this with a CHECK constraint, which ultimately failed, we switched to a partial index (CREATE UNIQUE INDEX ON activities(account_id, activity_type) WHERE activity_state = 0) which works flawlessly. There is absolutely no sane way to implement this at the application layer, and even then we have additional applications and administrative work that may access this database directly so we can't rely on the application enforcing data integrity anyway. MySQL doesn't even support CHECK constraints, nor does it have partial indexes - while even SQL Server has supported partial (filtered) indexes since SQL Server 2008 (nearly a decade after PostgreSQL implemented them, but hey, still at least a decade before any of the MySQL family will have them). Should you put application logic in your database? No, probably not. But you should always make sure your database prevents ANY invalid data from being put into it, your little application won't be the only one touching that database forever in all likelihood.
      - Mouse47 9y ago
        >CREATE UNIQUE INDEX (filtered) Ha...that's a bit of a hack! I've had to do a similar trick for a unique constraint on columns where we didn't care if the value was null. SQL Server (for some reason) considers null a value in this case; in other words, there could only be one null value in the table for purposes of a unique constraint. E.g: create unique index on (col) where col is not null
- matwood 9y ago
  You can write unmaintainable code anywhere in your stack. The problem I routinely see with approaches similar to yours is the app code ends up poorly recreating functionality that the rdbms was built to do.
  - jstewartmobile 9y ago
    MySQL is an RDBMS. What is my approach? I just don't think sending emails and parsing XML should run in the database.

pmontra 9y ago

The data should be handled by the database, especially when there might be many different applications accessing it. Think of microservices, maybe written in different languages and frameworks, plus some devops running queries by hand on the db. The ability to stop modifications inside the db is the ultimate safeguard against data corruption. If you really value your data you should also implement validations with CHECK statements and not only in the application. Think about the database as another microservice worth its own logic.

concede_pluto 9y ago
This. You don't want to reimplement all your validation in each of your applications (plural!), and you're in for a nasty surprise if you think your entire organization has only one application using your database now and forever.
- pmontra 9y ago
  Actually you probably should. Front end (JS SPA) for the convenience of users. Back end API (any web framework) for the convenience of developers, the users of the API. The database to make sure nobody can corrupt the data. In my experience the validations in the database almost never go past foreign keys, null fields and bounds on numeric data. Anecdote. I remember when the consensus of Rails developers was not to use foreign keys: Rails didn't have a way to define them so they probably didn't matter, right? I laughed and added them to every application of mine. Rails has foreign keys now, maybe since version 3. There are some gems to generate CHECK statements from validations.
jstewartmobile 9y ago
You are correcting something I never said. MySQL has primary/foreign key checking, field validation, transactions, etc. I have nothing against RDBMSes. I like them! I'm talking about not doing shit like XML parsing (https://www.postgresql.org/docs/9.6/static/functions-xml.html https://www.postgresql.org/docs/9.6/static/functions-xml.htm...), or LDAP queries (https://docs.oracle.com/cd/B10501_01/network.920/a96577/dbmsldap.htm https://docs.oracle.com/cd/B10501_01/network.920/a96577/dbms...) in the database. I don't think those things belong there. Serves me right for leaving a blank on HN! People here just love to fill those with 'herp derp'.