7 ms·

Dammit, MySQL

141 points by stephen 16y ago

mmt 16y ago
I only wish I were so lucky as to have been spoiled by Postgres for so long. My favorite quote from an experience DBA is "MySQL's biggest feature is that it's retarded." Sadly, as someone who actually likes technology and working with it, I can't possibly see that as a feature. DDL Doesn’t Respect Transactions My Oracle DBA friend tells me Oracle doesn't, either, not that this is any kind of excuse. Maybe with the first-class replication in the 9.0 release, it will happen sooner rather than later. I thought RDS wasn't so great on MySQL replication, either, or is that only if one wishes to do it off-AWS?
- briansmith 16y ago
  MS SQL Server and Oracle use CREATE SCHEMA and ALTER SCHEMA for DDL transactions. I think it is the ANSI/ISO standard way to do it. But, also "The CREATE SCHEMA command does not support Oracle extensions to the ANSI CREATE TABLE and CREATE VIEW commands (for example, the STORAGE clause)."
  - mmt 16y ago
    I'm certainly no expert on standard SQL, but I suspect that's academic. Since my knowledge of transactional DDL is PostgreSQL[1], which supports effectively everything, with no onerous locking, I now have the question: what's the difference? Was my DBA friend in error, or merely out of date? [1] first-hand knowledge, anyway. My 3rd-hand knowledge of MSSQL is that it's more or less Sybase, which does fully support DDL inside a transaction.
    - pradocchia 16y ago
      Yes, MSSQL does support transactional DDL, via locking rather than versioning. Not sure how PostresSQL implements it. Anyone? Row versioning would be more concurrency-friendly, and arguably more "right". I'd be interested if anyone took advantage of versioned transactional DDL to persist arbitrary data structures on the fly.
      - mmt 16y ago
        Postgres does it with versioning, as that's how everything is stored on disk. As I've seen DDL outside of a transaction take much longer than inside one, it may use locking there.
  - ora600 16y ago
    Can you link to documentation of ALTER SCHEMA for Oracle that does transactional DDL? I don't think it exists.
    - briansmith 16y ago
      You are right. I think ALTER SCHEMA might not be in standard SQL.
  - julius_geezer 16y ago
    Oracle DDL occurs as transaction per command. CREATE TABLE A (b number); -- one transaction; INSERT INTO a(b) VALUES(1); CREATE TABLE c (d number); -- another transaction, commits the insert.
- samuel 16y ago
  Oracle DDL doesn't respect transactions, but at least you can wrap it into an autonomous transaction so it won't commit your main transaction. Said that, it's a great feature. It allows you to deploy your stored procs transactionally, instead of the locking nightmare that Oracle's model may result into.
kingofspain 16y ago
Only the auto-updating timestamp thing has really got in my way in the past. Much as I liked Postgres when I used it, I have such easy access to MySQL and I so rarely encounter any problems that I kinda ignore the toy jibes. Maybe I'm not pushing it enough but if that's the case, why should I jump through hoops to set up something I won't be making good use of?
- Lewisham 16y ago
  It depends how valuable your data is to you. For many/most companies, data is incredibly valuable, and as the company grows, it gets more valuable over time. If you find the usability of MySQL better than Postgres, and you don't put a lot of value on your data, then choosing MySQL is fine. The author is right to point out that a lot of the complaints are really just "this doesn't have to be this way." You should really expect your database to enforce non-null, especially if it lets you specify it and doesn't warn you it's not going to do a damn thing about it. As I mentioned in my comment above, you can fix buggy applications, but you can't fix incorrect data.
  - kingofspain 16y ago
    Oh of course and this wass from a purely personal POV. Without trying to sound too flippant, my data can go to hell without me losing too much sleep - even if it never has in 10 or so years using MySQL. I keep decent backups and I know what behaviour to expect so personally, I'm fine with it. It's one of those 'educated' leaps of faith I take every day. If my data integrity was so very important, I'd pass the buck to someone who knew their beans and they could find & maintain me a safe db. I'm a competent amateur (hence MySQL), not a DBA :)
    - JoachimSchipper 16y ago
      Is this still true when considering silent corruption, instead of catastrophic failure? The latter is much easier to recover from...
      - 16y ago
jbooth 16y ago
Every one of his complaints is a practice I've constantly yelled at everyone around me not to use -- for my whole career. Application logic belongs in the application. Don't rely on a database to do it for you, or you're going to wind up with a giant ball of mud driven by side-effects.
- kingkilr 16y ago
  Don't rely on the database to not put null in a column marked not nulls? That's like saying don't rely on C to not put a struct in a char*. It's got nothing to do with application logic.
  - ars 16y ago
    He's wrong about that BTW. It doesn't put a NULL there, it puts zero for a number field, or a blank (0 length) string for a string field. Date fields also get 0 (00-00-0000). Edit: I get a downmod for this? The group think here is amazing. I suppose if I bash MySQL I'll get some upmods even if the bashing is incorrect?
    - dagheti 16y ago
      Inserting a default value is even worse than inserting NULL. Now you can't tell what data was wrongly inserted if you tried to clean up the mess later.
      - ars 16y ago
        What? Have you ever programmed SQL? Because what you wrote doesn't make sense. No data is inserted wrongly - you are simply leaving out a field. There is no mess. Just an unused field. Are you thinking it's like csv where if you leave out a column all the others are shifted? It's not like that. The standard says if you leave out a column that does not have a default the SQL should return an error. Instead MySQL puts in a default (but only if you tell it too in the configuration). Putting a NULL in a non-NULL field would be much worse.
ergo98 16y ago
What's the beef with the timestamp? I would expect, by default, that a timestamp reflects the last change to the row. Most systems use timestamps for optimistic locking/replication purposes.
- mbreese 16y ago
  Um... no. Shouldn't a timestamp reflect a specific point in time? And it shouldn't change unless there is an ON UPDATE condition set. That would be the correct thing to do according to the principle of least surprise. You could then set a column to auto-update, but it shouldn't be the default action. I'm fairly shocked that I haven't been hit with this "feature". Thankfully, I usually set a DEFAULT on timestamp columns.
  - ergo98 16y ago
    I primarily use the timestamp data type in SQL Server. Which is a data type that isn't actually a time (or date) at all, but instead is a incrementing database-wide value that is automatically updated on every row update.
  - randallsquared 16y ago
    In MySQL (and SQL Server, apparently), it reflects the specific point in time when this row was last updated, and neither database will let you have more than one such field in a table. The data type everyone here seems to want is "datetime", which behaves like you want a "timestamp" to behave.
    - JoachimSchipper 16y ago
      > neither [MySQL nor SQL server] ... will let you have more than one [timestamp] field in a table. Wait, what?
      - gxti 16y ago
        There can be only one because it exists solely to be the "last updated" field. There's no point in having more than one. In MySQL, it simply only updates the first -- so you get a sometimes magic field.
samstokes 16y ago
The last MySQL limitation to bite me was that time columns are truncated to whole-second granularity - no milliseconds or microseconds. http://bugs.mysql.com/bug.php?id=8523 http://bugs.mysql.com/bug.php?id=8523 If you want sub-second granularity, the advice is to store milliseconds-since-epoch as a BIGINT and have your application convert it to a time: http://feedblog.org/2007/05/26/why-doesnt-mysql-support-millisecond-datetime-resolution/ http://feedblog.org/2007/05/26/why-doesnt-mysql-support-mill...
- pbh 16y ago
  I had the same problem (and, really, 5 years?), but "solved" it at the time with ISO8601 strings. They sort, they're standard, they support microseconds, they're fairly common in web services. Slower than a reasonable native type though, obviously.
drblast 16y ago
The design philosophy differences between Postgres and MySQL should be a case study. When I first started using SQL databases, Postgres had a reputation for being a fully ACID compliant database that paid a performance penalty for it, and was hard to use. They made it "correct" as a top priority. MySQL had a reputation for being blazingly fast, even outperforming expensive commercial DB's in some areas. However, things like foreign keys were missing. There were debates in the MySQL community as to whether foreign keys were even necessary, since you could just do all that stuff in the application layer. That seemed reasonable to me at the time and MySQL was trivially easy to install at a time when Postgres was a huge pain, so I picked MySQL. I almost immediately got bitten by the lack of foreign keys, and although I didn't know it at the time, spent most of my time coding application logic that was a workaround for MySQL's lack of views. So I switched to Postgres, it solved many of my problems and have tried to use it whenever possible. Every release the MySQL people get closer to Postgres's features, but there's always something missing, and as of a few years ago at least, all of these features were dependent on what backend table type you were using, which became especially annoying if you didn't have control over the installation (cheap web hosting.) The funny thing is, MySQL's performance on microbenchmarks really meant nothing in more complex applications. A few years ago I was doing something more than a simple search that involved a few joins and intersections with MySQL; the query optimizer in MySQL seemed determined to pick the slowest possible path to return a result (intersection of a small set of rows with a huge one somehow would cause MySQL to check every row of the large set for a match). It ended up being 50 times faster to implement the logic in PHP and use two queries instead of one. So I switched to a host with Postgres access, and the identical query that took forever in MySQL was no problem for Postgres. After a few experiences like that, you begin to see the virtue in doing things right the first time and optimizing later. You also see the virtue in using a database system that takes the onus off of the application developer to implement logic that should be in the DB.
- deleted 16y ago
  [deleted]
- SkyMarshal 16y ago
  After a few experiences like that, you begin to see the virtue in doing things right the first time and optimizing later. You know what they say about premature optimization being the mother of all fuckups...
Calamitous 16y ago
Does Postgres have reliable, tolerably-easy-to-install-and-configure master/slave replication yet? That's the only thing that kept us off postgres last time we were looking, we definitely need a fast, reliable, read-only slave.
- stephen 16y ago
  I think what you're looking for is the marque feature of 9.0: http://developer.postgresql.org/pgdocs/postgres/release-9-0.html http://developer.postgresql.org/pgdocs/postgres/release-9-0....
  - Calamitous 16y ago
    Oh, man, sweet. :) Now to get the Ops guys on board...
    - mmt 16y ago
      Any "Ops guys" who don't find something like Slony tolerably easy to install might not be suited to your environment. I've found the "feature" that MySQL replication is integrated[1] to a huge problem, especially when faced with messes made by those who installed/implemented it because understanding something more complex (like the realities of database replication) would have been too high a barrier to entry. It also, of course, meant that competing solutions were rare, something that, in the case of MySQL, I do consider a benefit. [1] I hesitate to use the term "built in," since there's a giant piece missing: initial synchronization. They want me to do what?! A full dump and restore, before I even turn on the replication? That, as well as the "tip" of just copying files over always struck me as amazingly manual.
      - chrisbolt 16y ago
        The way we do initial synchronization with MyISAM: * Everything is on LVM * Run FLUSH TABLES WITH READ LOCK * Take an LVM snapshot then record binary log filename and position * Run UNLOCK TABLES * Mount snapshot, rsync to new server and bring up replication with recorded file and position Very fast and only locks tables for a short time.
sgt 16y ago
I see that many of you are discussing PostgreSQL. I have to admit that I'm already using PostgreSQL 9.0beta3 in production, and it's been rock stable so far.
MichaelGG 16y ago
MySQL: Visual Basic's "On Error Resume Next" for databases.
mmaunder 16y ago
You're creating the wrong impression here by calling MySQL a toy and citing a handful of user preference issues you have with arcane features. http://www.mysql.com/customers/ http://www.mysql.com/customers/ MySQL is used by the largest and most successful companies in the world. It is incredibly performant and stable in the most brutal environments. Who on earth expects transactions to be available when you're creating a schema? "drop table if exists" is a fine way to back-out if your deployment dies. Deferred key constraints? You should be spanked for using those in the first place.
- gfodor 16y ago
  This is a weak argument. Deferred key constraints and the other intra-transactional feature mentioned are hardly bad practice, since you're relying upon the database to cover your ass. Go figure, that's what a database is supposed to do. You should be free to do anything in a transaction you wish, it is, after all, not committed until you say so. The appeal to authority doesn't add any weight to the argument.
- kls 16y ago
  MySQL is used by the largest and most successful companies in the world. It is incredibly performant and stable in the most brutal environments. I hate that line, you know what most of these companies have in common? They where started by guys with only a few years in the field and a vision. Some of them not the best developers in the world, just guys with a great idea for a product and a passion. The other thing they have in common, is that they have spent millions if not more on rolling their own solutions to compensate for a technology decision they made a long time ago in a garage. This is the way it works, the new guys come in, they say well company X uses this, they build something on it because X is using it and they hit growing pains at a certain point. Twitter is experiencing those growing pains now, fortunately they seem to be throwing out the bad and trying new things. A good deal of companies become dogmatic about their choice and given their success just throw money at it to build compensating workarounds. MySQL was not chosen by those companies because of its merits, it was chosen because those companies started in a garage, and the technical selection was "what are the other guys using in their garage".
cosmok 16y ago
I wrote about how I ran into his complaint #1 here:http://www.trk7.com/blog/programming/innodb-mysql-implicit-autocommit/ http://www.trk7.com/blog/programming/innodb-mysql-implicit-a... . MySQL is quite annoying but, it seems to do the job for most of the projects that I have worked on.
ww520 16y ago
The only feature that tipped in MySQL's favor was easy replication. Now that Postgres has it too, there's no more reason to use it.
naturalethic 16y ago
Postgres has no unsigned 64 bit integer. :(
- anthonyb 16y ago
  No, but you can define your own: http://bytes.com/topic/postgresql/answers/423780-numeric-type-problems http://bytes.com/topic/postgresql/answers/423780-numeric-typ... Also note the issues with MySQL and it's large integers :) Although they might have fixed things since that post.
  - naturalethic 16y ago
    Thanks a ton for this.
c00p3r 16y ago
MySQL is the same thing as PHP - illusion of simplicity, and as a result - vast and active community and availability in any distro and on all cheapest hosting. While quality of the code and design decisions were seconded. Hype and community - that is why it was sold for a billion dollars.
dlsspy 16y ago
This didn't mention that check constraints are completely ignored. That one bit me pretty hard the last time I tried to use MySQL. For whatever reason, app developers kept getting longitude and latitude backwards. I wrote a check constraint that prevented that from being possible (in many cases), so if an app was doing it backwards, the DB would reject it. I ended up losing data (in dev -- which really meant I lost 45 minutes in having to restore it) in testing this. Then I read the docs. I expect things to do what I tell them too frequently.

141 points by stephen 16y ago

mmt 16y ago

I only wish I were so lucky as to have been spoiled by Postgres for so long. My favorite quote from an experience DBA is "MySQL's biggest feature is that it's retarded." Sadly, as someone who actually likes technology and working with it, I can't possibly see that as a feature. DDL Doesn’t Respect Transactions My Oracle DBA friend tells me Oracle doesn't, either, not that this is any kind of excuse. Maybe with the first-class replication in the 9.0 release, it will happen sooner rather than later. I thought RDS wasn't so great on MySQL replication, either, or is that only if one wishes to do it off-AWS?

briansmith 16y ago
MS SQL Server and Oracle use CREATE SCHEMA and ALTER SCHEMA for DDL transactions. I think it is the ANSI/ISO standard way to do it. But, also "The CREATE SCHEMA command does not support Oracle extensions to the ANSI CREATE TABLE and CREATE VIEW commands (for example, the STORAGE clause)."
- mmt 16y ago
  I'm certainly no expert on standard SQL, but I suspect that's academic. Since my knowledge of transactional DDL is PostgreSQL[1], which supports effectively everything, with no onerous locking, I now have the question: what's the difference? Was my DBA friend in error, or merely out of date? [1] first-hand knowledge, anyway. My 3rd-hand knowledge of MSSQL is that it's more or less Sybase, which does fully support DDL inside a transaction.
  - pradocchia 16y ago
    Yes, MSSQL does support transactional DDL, via locking rather than versioning. Not sure how PostresSQL implements it. Anyone? Row versioning would be more concurrency-friendly, and arguably more "right". I'd be interested if anyone took advantage of versioned transactional DDL to persist arbitrary data structures on the fly.
    - mmt 16y ago
      Postgres does it with versioning, as that's how everything is stored on disk. As I've seen DDL outside of a transaction take much longer than inside one, it may use locking there.
- ora600 16y ago
  Can you link to documentation of ALTER SCHEMA for Oracle that does transactional DDL? I don't think it exists.
  - briansmith 16y ago
    You are right. I think ALTER SCHEMA might not be in standard SQL.
- julius_geezer 16y ago
  Oracle DDL occurs as transaction per command. CREATE TABLE A (b number); -- one transaction; INSERT INTO a(b) VALUES(1); CREATE TABLE c (d number); -- another transaction, commits the insert.
samuel 16y ago
Oracle DDL doesn't respect transactions, but at least you can wrap it into an autonomous transaction so it won't commit your main transaction. Said that, it's a great feature. It allows you to deploy your stored procs transactionally, instead of the locking nightmare that Oracle's model may result into.

kingofspain 16y ago

Only the auto-updating timestamp thing has really got in my way in the past. Much as I liked Postgres when I used it, I have such easy access to MySQL and I so rarely encounter any problems that I kinda ignore the toy jibes. Maybe I'm not pushing it enough but if that's the case, why should I jump through hoops to set up something I won't be making good use of?

Lewisham 16y ago
It depends how valuable your data is to you. For many/most companies, data is incredibly valuable, and as the company grows, it gets more valuable over time. If you find the usability of MySQL better than Postgres, and you don't put a lot of value on your data, then choosing MySQL is fine. The author is right to point out that a lot of the complaints are really just "this doesn't have to be this way." You should really expect your database to enforce non-null, especially if it lets you specify it and doesn't warn you it's not going to do a damn thing about it. As I mentioned in my comment above, you can fix buggy applications, but you can't fix incorrect data.
- kingofspain 16y ago
  Oh of course and this wass from a purely personal POV. Without trying to sound too flippant, my data can go to hell without me losing too much sleep - even if it never has in 10 or so years using MySQL. I keep decent backups and I know what behaviour to expect so personally, I'm fine with it. It's one of those 'educated' leaps of faith I take every day. If my data integrity was so very important, I'd pass the buck to someone who knew their beans and they could find & maintain me a safe db. I'm a competent amateur (hence MySQL), not a DBA :)
  - JoachimSchipper 16y ago
    Is this still true when considering silent corruption, instead of catastrophic failure? The latter is much easier to recover from...
    - 16y ago

jbooth 16y ago

Every one of his complaints is a practice I've constantly yelled at everyone around me not to use -- for my whole career. Application logic belongs in the application. Don't rely on a database to do it for you, or you're going to wind up with a giant ball of mud driven by side-effects.

kingkilr 16y ago
Don't rely on the database to not put null in a column marked not nulls? That's like saying don't rely on C to not put a struct in a char*. It's got nothing to do with application logic.
- ars 16y ago
  He's wrong about that BTW. It doesn't put a NULL there, it puts zero for a number field, or a blank (0 length) string for a string field. Date fields also get 0 (00-00-0000). Edit: I get a downmod for this? The group think here is amazing. I suppose if I bash MySQL I'll get some upmods even if the bashing is incorrect?
  - dagheti 16y ago
    Inserting a default value is even worse than inserting NULL. Now you can't tell what data was wrongly inserted if you tried to clean up the mess later.
    - ars 16y ago
      What? Have you ever programmed SQL? Because what you wrote doesn't make sense. No data is inserted wrongly - you are simply leaving out a field. There is no mess. Just an unused field. Are you thinking it's like csv where if you leave out a column all the others are shifted? It's not like that. The standard says if you leave out a column that does not have a default the SQL should return an error. Instead MySQL puts in a default (but only if you tell it too in the configuration). Putting a NULL in a non-NULL field would be much worse.

ergo98 16y ago

What's the beef with the timestamp? I would expect, by default, that a timestamp reflects the last change to the row. Most systems use timestamps for optimistic locking/replication purposes.

mbreese 16y ago
Um... no. Shouldn't a timestamp reflect a specific point in time? And it shouldn't change unless there is an ON UPDATE condition set. That would be the correct thing to do according to the principle of least surprise. You could then set a column to auto-update, but it shouldn't be the default action. I'm fairly shocked that I haven't been hit with this "feature". Thankfully, I usually set a DEFAULT on timestamp columns.
- ergo98 16y ago
  I primarily use the timestamp data type in SQL Server. Which is a data type that isn't actually a time (or date) at all, but instead is a incrementing database-wide value that is automatically updated on every row update.
- randallsquared 16y ago
  In MySQL (and SQL Server, apparently), it reflects the specific point in time when this row was last updated, and neither database will let you have more than one such field in a table. The data type everyone here seems to want is "datetime", which behaves like you want a "timestamp" to behave.
  - JoachimSchipper 16y ago
    > neither [MySQL nor SQL server] ... will let you have more than one [timestamp] field in a table. Wait, what?
    - gxti 16y ago
      There can be only one because it exists solely to be the "last updated" field. There's no point in having more than one. In MySQL, it simply only updates the first -- so you get a sometimes magic field.

samstokes 16y ago

The last MySQL limitation to bite me was that time columns are truncated to whole-second granularity - no milliseconds or microseconds. http://bugs.mysql.com/bug.php?id=8523 http://bugs.mysql.com/bug.php?id=8523 If you want sub-second granularity, the advice is to store milliseconds-since-epoch as a BIGINT and have your application convert it to a time: http://feedblog.org/2007/05/26/why-doesnt-mysql-support-millisecond-datetime-resolution/ http://feedblog.org/2007/05/26/why-doesnt-mysql-support-mill...

pbh 16y ago
I had the same problem (and, really, 5 years?), but "solved" it at the time with ISO8601 strings. They sort, they're standard, they support microseconds, they're fairly common in web services. Slower than a reasonable native type though, obviously.

drblast 16y ago

The design philosophy differences between Postgres and MySQL should be a case study. When I first started using SQL databases, Postgres had a reputation for being a fully ACID compliant database that paid a performance penalty for it, and was hard to use. They made it "correct" as a top priority. MySQL had a reputation for being blazingly fast, even outperforming expensive commercial DB's in some areas. However, things like foreign keys were missing. There were debates in the MySQL community as to whether foreign keys were even necessary, since you could just do all that stuff in the application layer. That seemed reasonable to me at the time and MySQL was trivially easy to install at a time when Postgres was a huge pain, so I picked MySQL. I almost immediately got bitten by the lack of foreign keys, and although I didn't know it at the time, spent most of my time coding application logic that was a workaround for MySQL's lack of views. So I switched to Postgres, it solved many of my problems and have tried to use it whenever possible. Every release the MySQL people get closer to Postgres's features, but there's always something missing, and as of a few years ago at least, all of these features were dependent on what backend table type you were using, which became especially annoying if you didn't have control over the installation (cheap web hosting.) The funny thing is, MySQL's performance on microbenchmarks really meant nothing in more complex applications. A few years ago I was doing something more than a simple search that involved a few joins and intersections with MySQL; the query optimizer in MySQL seemed determined to pick the slowest possible path to return a result (intersection of a small set of rows with a huge one somehow would cause MySQL to check every row of the large set for a match). It ended up being 50 times faster to implement the logic in PHP and use two queries instead of one. So I switched to a host with Postgres access, and the identical query that took forever in MySQL was no problem for Postgres. After a few experiences like that, you begin to see the virtue in doing things right the first time and optimizing later. You also see the virtue in using a database system that takes the onus off of the application developer to implement logic that should be in the DB.

deleted 16y ago
[deleted]
SkyMarshal 16y ago
After a few experiences like that, you begin to see the virtue in doing things right the first time and optimizing later. You know what they say about premature optimization being the mother of all fuckups...

Calamitous 16y ago

Does Postgres have reliable, tolerably-easy-to-install-and-configure master/slave replication yet? That's the only thing that kept us off postgres last time we were looking, we definitely need a fast, reliable, read-only slave.

stephen 16y ago
I think what you're looking for is the marque feature of 9.0: http://developer.postgresql.org/pgdocs/postgres/release-9-0.html http://developer.postgresql.org/pgdocs/postgres/release-9-0....
- Calamitous 16y ago
  Oh, man, sweet. :) Now to get the Ops guys on board...
  - mmt 16y ago
    Any "Ops guys" who don't find something like Slony tolerably easy to install might not be suited to your environment. I've found the "feature" that MySQL replication is integrated[1] to a huge problem, especially when faced with messes made by those who installed/implemented it because understanding something more complex (like the realities of database replication) would have been too high a barrier to entry. It also, of course, meant that competing solutions were rare, something that, in the case of MySQL, I do consider a benefit. [1] I hesitate to use the term "built in," since there's a giant piece missing: initial synchronization. They want me to do what?! A full dump and restore, before I even turn on the replication? That, as well as the "tip" of just copying files over always struck me as amazingly manual.
    - chrisbolt 16y ago
      The way we do initial synchronization with MyISAM: * Everything is on LVM * Run FLUSH TABLES WITH READ LOCK * Take an LVM snapshot then record binary log filename and position * Run UNLOCK TABLES * Mount snapshot, rsync to new server and bring up replication with recorded file and position Very fast and only locks tables for a short time.

sgt 16y ago

I see that many of you are discussing PostgreSQL. I have to admit that I'm already using PostgreSQL 9.0beta3 in production, and it's been rock stable so far.

MichaelGG 16y ago

MySQL: Visual Basic's "On Error Resume Next" for databases.

mmaunder 16y ago

You're creating the wrong impression here by calling MySQL a toy and citing a handful of user preference issues you have with arcane features. http://www.mysql.com/customers/ http://www.mysql.com/customers/ MySQL is used by the largest and most successful companies in the world. It is incredibly performant and stable in the most brutal environments. Who on earth expects transactions to be available when you're creating a schema? "drop table if exists" is a fine way to back-out if your deployment dies. Deferred key constraints? You should be spanked for using those in the first place.

gfodor 16y ago
This is a weak argument. Deferred key constraints and the other intra-transactional feature mentioned are hardly bad practice, since you're relying upon the database to cover your ass. Go figure, that's what a database is supposed to do. You should be free to do anything in a transaction you wish, it is, after all, not committed until you say so. The appeal to authority doesn't add any weight to the argument.
kls 16y ago
MySQL is used by the largest and most successful companies in the world. It is incredibly performant and stable in the most brutal environments. I hate that line, you know what most of these companies have in common? They where started by guys with only a few years in the field and a vision. Some of them not the best developers in the world, just guys with a great idea for a product and a passion. The other thing they have in common, is that they have spent millions if not more on rolling their own solutions to compensate for a technology decision they made a long time ago in a garage. This is the way it works, the new guys come in, they say well company X uses this, they build something on it because X is using it and they hit growing pains at a certain point. Twitter is experiencing those growing pains now, fortunately they seem to be throwing out the bad and trying new things. A good deal of companies become dogmatic about their choice and given their success just throw money at it to build compensating workarounds. MySQL was not chosen by those companies because of its merits, it was chosen because those companies started in a garage, and the technical selection was "what are the other guys using in their garage".

cosmok 16y ago

I wrote about how I ran into his complaint #1 here:http://www.trk7.com/blog/programming/innodb-mysql-implicit-autocommit/ http://www.trk7.com/blog/programming/innodb-mysql-implicit-a... . MySQL is quite annoying but, it seems to do the job for most of the projects that I have worked on.

ww520 16y ago

The only feature that tipped in MySQL's favor was easy replication. Now that Postgres has it too, there's no more reason to use it.

naturalethic 16y ago

Postgres has no unsigned 64 bit integer. :(

anthonyb 16y ago
No, but you can define your own: http://bytes.com/topic/postgresql/answers/423780-numeric-type-problems http://bytes.com/topic/postgresql/answers/423780-numeric-typ... Also note the issues with MySQL and it's large integers :) Although they might have fixed things since that post.
- naturalethic 16y ago
  Thanks a ton for this.

c00p3r 16y ago

MySQL is the same thing as PHP - illusion of simplicity, and as a result - vast and active community and availability in any distro and on all cheapest hosting. While quality of the code and design decisions were seconded. Hype and community - that is why it was sold for a billion dollars.

dlsspy 16y ago

This didn't mention that check constraints are completely ignored. That one bit me pretty hard the last time I tried to use MySQL. For whatever reason, app developers kept getting longitude and latitude backwards. I wrote a check constraint that prevented that from being possible (in many cases), so if an app was doing it backwards, the DB would reject it. I ended up losing data (in dev -- which really meant I lost 45 minutes in having to restore it) in testing this. Then I read the docs. I expect things to do what I tell them too frequently.