News for hackers. Stuff that matters.
Re:MySQL 5.7.6 is out by taf2 on Tuesday March 10, 2015 @09:57AM (#9177714)
So much MySQL hate these days... I should try Postgres out but here's why I still like and trust MySQL or MariaDB/Percona...

  1. It's reliable/durable

  2. I know the tools (mysql command line is friendly to me)

  3. It's performance is predictable.  99% of issues with performance are easily solved with the right index

  4. I don't find myself needing many SQL features beyond the basics.
Also, I'm a happy user of other tools to compliant mysql. Including redis and elasticsearch. Both of these tools IMO compliant MySQL.

I'm no fan of Oracle by any means but at least MySQL is still an open source database. Perhaps when I have free time before the next startup I'll give Postgres a good look. For now I'll probably try it out with AWS redshift...

Re:MySQL 5.7.6 is out by pilif on Tuesday March 10, 2015 @10:09AM (#9177763)
>1. It's reliable/durable

I would personally contest that. I've seen MySQL corrupt tables before and the insanely unhelpful defaults which make MySQL accept and alter invalid data really don't strike me as neither reliable nor durable.

Your other points are valid reasons for sticking with MySQL though. If you're happy and the tool does what you need, that's fine.

But once you've seen what you can do as you gain access to the more advanced SQL features and once you've burned by MySQL not starting up because of data corruption, then you might want to investigate other options.

Postgres happens to be a very good alternative with good tools, predictable performance, very advanced SQL features, but also really good reliability and durability.

Re:MySQL 5.7.6 is out by kyrra on Tuesday March 10, 2015 @10:20AM (#9177813)
Corruption issues are fairly true with MyISAM engine. InnoDB did a lot to help reliability of tables (it can auto-correct them in cases where you had a hard poweroff or other related issues). No more needing to manually run table scans to detect corruption.

What "advanced SQL features" do other DB products have that you cannot do with MySQL?

Re:MySQL 5.7.6 is out by pilif on Tuesday March 10, 2015 @10:32AM (#9177884)
CTEs, range types, window functions, custom aggregates, functional indexes, conditional indexes, proper utf-8 support and once you bring Postgres with its unique features into the mix: kick-ass JSON support (including the usage of indexes), array support (any my best friend array_agg())

Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware)

Now I can totally accept that this might have been a one-time fluke, but I personally really have trouble trusting a DBMS once it has lost data due to causes other than user-error or hardware faults.

Re:MySQL 5.7.6 is out by kbenson on Tuesday March 10, 2015 @11:19AM (#9178174)
It's not pretty, but you can get the equivalent of range types using variables[1].

Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware)

It's not safe until it's backed up. I don't care if it's a $100k oracle installation, It's not safe until it's backed up. Even then it's suspect...

1: http://stackoverflow.com/questions/17147920/postgres-window-...

Re:MySQL 5.7.6 is out by ldng on Tuesday March 10, 2015 @12:31PM (#9178632)
I would add, as Dimitri Fontaine says : it is not safe until backup is tested and automatically recoverable !

https://fosdem.org/2015/schedule/event/youd_better_have_test...

Re:MySQL 5.7.6 is out by porker on Tuesday March 10, 2015 @11:30AM (#9178227)
> CTEs

Coming from a MySQL background I didn't understand the fuss over CTEs ...until I worked with a SQL Server guy. That completely opened my eyes to how _sane_ things could be, vs my MySQL work-arounds.

Made me wish I'd chosen a Microsoft route!

Re:MySQL 5.7.6 is out by JohnBooty on Tuesday March 10, 2015 @11:58AM (#9178415)
Yeah, once you've used CTEs it's impossible to go back. Same with window functions.

Good news is that Postgres has great CTE support and it was ahead of MSSQL in window functions last I checked!

Re:MySQL 5.7.6 is out by meritt on Tuesday March 10, 2015 @11:28AM (#9178218)
As a long-time and fairly technical MySQL user and only light experience with PostgreSQL, can you suggest the best reading material (in addition to the user manual) on the subject?

Comparatively, for MySQL, my recommendation would be 'High Performance MySQL'.

Re:MySQL 5.7.6 is out by parfe on Tuesday March 10, 2015 @11:50AM (#9178366)
The postgres manual. Seriously. It's all you need to forget mysql existed.

http://www.postgresql.org/docs/9.4/interactive/index.html

Re:MySQL 5.7.6 is out by neverminder on Tuesday March 10, 2015 @10:36AM (#9177909)
The list is long (if compared to Postgres particularly), but this is a rough summary: http://www.sql-workbench.net/dbms_comparison.html
Re:MySQL 5.7.6 is out by NhanH on Tuesday March 10, 2015 @10:48AM (#9177969)
Very minor thing, but funny enough, I was working with MySQL just earlier and need to use my one in a blue moon quota of Full Join ... turned out MySQL doesn't have it :( (yes, I know how to emulate it with union).
Re:MySQL 5.7.6 is out by morgo on Tuesday March 10, 2015 @10:19AM (#9177808)
In the context of this post (MySQL 5.7) it does not accept invalid data :) The default sql mode has changed.
Re:MySQL 5.7.6 is out by equoid on Tuesday March 10, 2015 @11:54AM (#9178392)
"The default sql mode has changed."

So long as it possible to redefine the mode at will on a per connection basis or on startup then the integrity of data held within MySQL cannot be guaranteed.

Re:MySQL 5.7.6 is out by morgo on Tuesday March 10, 2015 @12:34PM (#9178665)
Being able to change it on a per connection basis is required for earlier applications. If we use Wordpress as an example, upon initial connection it will set the SQL mode to what it is compatible with (magento, drupal, others also do similar).

Integrity can also not be upheld if applications lie. i.e. I always enter the same incorrect birthday if an application asks me for this, but I don't think they have a reason for it.

Re:MySQL 5.7.6 is out by tsuraan on Tuesday March 10, 2015 @11:12AM (#9178125)
So, MySQL, as of 5.7, no longer accepts dates with year 0, and it also stopped converting unrecognized date strings into the date 00-00-0000? That would be a very positive change.
Re:MySQL 5.7.6 is out by morgo on Tuesday March 10, 2015 @11:37AM (#9178278)
Yes. See slide 43 for examples of statements that now produce errors by default: http://www.slideshare.net/morgo/upcoming-changes-in-mysql-57

This will break some applications that are upgrading. I have a whitelist/blacklist suggestion on how to transition on my blog: http://www.tocker.ca/2014/09/01/suggestions-for-transitionin...

Re:MySQL 5.7.6 is out by FireBeyond on Tuesday March 10, 2015 @11:31AM (#9178235)
It has "not supported" them for a long time. But it wasn't the default, due to backward compatibility (of course the decision that lead to this in the first place is no less suspect).
Re:MySQL 5.7.6 is out by IndianAstronaut on Tuesday March 10, 2015 @12:21PM (#9178554)
I have had terrible luck with MySQL on my macbook. The db gets corrupted often and I lose my data on a regular basis.
Re:MySQL 5.7.6 is out by FireBeyond on Tuesday March 10, 2015 @11:32AM (#9178247)
Though "you shouldn't have to", my first recommendation for anyone setting up a MySQL installation is to go to the Percona Configuration Wizard and build a much more appropriate configuration file (with strict and enforcing defaults, better performance and a whole bunch of other things).
Re:MySQL 5.7.6 is out by JohnBooty on Tuesday March 10, 2015 @11:48AM (#9178358)

  > 3. It's performance is predictable.  99% of issues 
  > with performance are easily solved with the right index
This is one of my least favorite things about MySQL. The "explain" feature is much less informative than that offered by MSSQL or Postgres, so figuring out what indexes or tuning to apply is harder than it needs to be.

And then when you do have everything indexed correctly, MySQL is significantly dumber (edit: looks like I'm significantly dumber! see correction below) about using those indexes than MSSQL or Postgres.

For example: those two can do index-only SELECTS. If table "foo" has columns A, B, C, D, E, and F and I've created an index on A and B, why can't "select A,B from foo" be served directly from the index? Postgres and MSSQL do it, and it's a huge performance boost in those two.

Postgres also lets you do some amazing things with partial indexes, indexes on computed values, etc.

MySQL might not be horrible here, but out of the 3 major relational databases I've used, it's clearly the weakest at this so it's funny to hear it touted as a strength.

Edit: Good news - I was wrong; MySQL does support index-only selects (covering indexes). User morgo also pointed out the improved JSON format for explain as well.

Re:MySQL 5.7.6 is out by morgo on Tuesday March 10, 2015 @12:14PM (#9178517)
If you do not like explain, may I suggest using `EXPLAIN FORMAT=JSON` - it is new to MySQL 5.6, and contains additional meta data. There is also optimizer trace, which provides significantly more meta data. Some good examples in these slides: http://www.slideshare.net/oysteing/how-to-analyze-and-tune-s...

I believe your example might be http://mysql-nordic.blogspot.ca/2015/01/with-latest-verions-... - Fixed in 5.7.5.

There has been a lot of work in improving the optimizer recently. One project that I find particularly interesting, is the focus on improving the cost model: http://mysqlserverteam.com/the-mysql-optimizer-cost-model-pr...

Re:MySQL 5.7.6 is out by JohnBooty on Tuesday March 10, 2015 @12:56PM (#9178811)
That's great to see. Thank you for the correction. I did not know that information was available. I wonder if there are others like me who didn't know this because they've been doing the plain-vanilla EXPLAIN like always.
Re:MySQL 5.7.6 is out by sevenproxies on Tuesday March 10, 2015 @12:17PM (#9178534)
>why can't "select A,B from foo" be served directly from the index

mysql supports that with covering indexes.

https://books.google.co.uk/books?id=IagfgRiKWd4C&lpg=PA178&v...

Re:MySQL 5.7.6 is out by JohnBooty on Tuesday March 10, 2015 @12:53PM (#9178790)
Wow, that's great news. Thanks for the correction!
Re:MySQL 5.7.6 is out by morgo on Tuesday March 10, 2015 @10:18AM (#9177799)
Under your point (3), it is important to note that this has been a major focus area of MySQL. I have two comments to make on it:

1) Peak throughput is not as important as consistent throughput (i.e. less variance between response times). This makes it a great fit for front-facing web apps, and we have Percona/Facebook/Google in particular to thank for their contributions in improving this.

2) Often users do not get the performance they are entitled to, because they lack the visibility. MySQL 5.6 and above (and particularly mysql 5.7) have performance_schema to be able to track down and diagnose issues. Memory/transactions/stages/replication/prepared statements/statements/mutexes.. these things are all instrumented by Performance schema. Just today I saw a new query written to show a breakdown of latency per schema/per statement type: http://dba.stackexchange.com/questions/94723/getting-databas...

Re:MySQL 5.7.6 is out by kidmenot on Tuesday March 10, 2015 @11:05AM (#9178089)
>4. I don't find myself needing many SQL features beyond the basics.

That's probably why you still like it. As it stands, MySQL has so many shortcomings it's not even funny.

Version 5.7 is the first one to support multiple triggers per event. Beyond that, and just to name a few, you can't have subselects in views, you don't have CTEs (good luck storing hierarchical data in an adjacency list), no materialized views, CHECK constraints are parsed but ignored.

I'm glad it works for you, but the minute you need to do something slightly more complex, MySQL gets in your way.

I hope the situation will improve, though.

Re:MySQL 5.7.6 is out by Buttons840 on Tuesday March 10, 2015 @12:27PM (#9178595)
There have been several times in my career where I have missed full SQL support in MySQL databases other people have setup and left for me to maintain. Choosing MySQL isn't the worst thing one can do when building a new system, but just because the initial designers don't intend to use advanced SQL features doesn't mean future use cases wont benefit from full SQL support.
Re:MySQL 5.7.6 is out by kidmenot on Wednesday March 11, 2015 @05:57AM (#9183344)
Indeed. Just these days the lack of materialized view has been giving me troubles. It would make a certain process about 50 times faster without touching code at all, and it would take me all of three queries.

Granted, the real problem is a poor design of the system, but materialized views would enable me to improve what others have left behind.

Re:MySQL 5.7.6 is out by dschiptsov on Tuesday March 10, 2015 @01:37PM (#9179176)
Try this. Download mysql sources. Install clang 3.6. Try to compile with all sanitizers. Last time I did it (it was percona code) it found a leak somewhere in SQL parsing code. No kidding.

It is my habit from old days to compile everything.

Re:MySQL 5.7.6 is out by sarciszewski on Tuesday March 10, 2015 @10:42AM (#9177947)
I made the switch from MySQL to PostgreSQL last year. The learning curve between the two is small and you'll probably find yourself liking PG more.

Even if you don't, give it a chance.

Re:MySQL 5.7.6 is out by IgorPartola on Tuesday March 10, 2015 @10:38AM (#9177917)
MySQL is a fine DB for most cases. There are some specific features it lacks that do make it annoying (and make Postgres a better choice):

1. Indexes are too simplistic. You can't index the output of a function.

2. InnoDB lacks full text search. MyISAM has it, but MyISAM is bad since it doesn't support transactions.

3. Unicode support by default does not support all Unicode characters. That's right, even though it says UTF-8, not everything is supported, and you have to specifically enable support for some character subsets for your DB.

4. MySQL replication is... how should I put it? Delicate. There is no integrity checking, and since by default it's just replaying an SQL log you can easily get inconsistencies between the master and the slave. There are lots of ways to confuse replication, such as `INSERT INTO my_table (foo) VALUES (RAND())`. There are no built-in tools for integrity checking the slave, and the third party tools that exist have to resort to some really crazy things, like re-inserting tables. I could go on about replication, and its issues for a while, but I want to move on to the other points.

5. No transactional DDL. This really sucks when using with something like Django migrations.

6. No point in time backups. You either use mysqldump with a transaction (you aren't using MyISAM, right?), or you stop the database server.

7. Logs suck. No, really, have you tried debugging an issue with your queries, deadlocks, configuration errors, etc.? MySQL's server logs (not query logs), are not very verbose, and what the do write is mostly useless.

8. Row level locking semantics are at times doing unexpected things. Last time I used MySQL for complex real time write-heavy stuff, I actually moved to advisory locks instead (the support for which is not exactly great in MySQL and could be expanded). This invites contention, deadlocks, and other nastiness where it isn't necessary.

9. No IPv6 support.

10. Can't index Archive engine tables.

11. Can't partition tables by any arbitrary value. It has to be only specific types which makes it too restrictive.

12. GIS support is not really there. Lots of basic features aren't supported.

13. No materialized views. You can simulate it with triggers, but that's not nearly as convenient.

14. No async drivers.

Note that these are very specific features. If you don't use them, good for you. No reason to switch away from MySQL just because. It has some advantages over Postgres as well:

1. Simple user management.

2. Simple database management. No schemas, objects, etc., just tables and views here.

3. INSERT IGNORE and REPLACE! You don't need to create triggers for these.

4. Decent performance out of the box, and lots of knobs to turn when tuning.

5. Generally, doesn't require a ton of setup time. Install it, create user and DB and code away (Postgres and lots of others have this too, but it is a good feature).

6. Large amount of community brain share, so you won't be braving a new world here.

So, basically, go ahead and use it if it works, but I'd say Postgres deserves a try too and your 4 points apply to it equally as well.

Re:MySQL 5.7.6 is out by morgo on Tuesday March 10, 2015 @11:02AM (#9178056)
1. We're working on something in this area: http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/

2. InnoDB has fulltext from MySQL 5.6+

3. I agree utf8mb4 should be renamed "utf8". When this was introduced it was given a new name to support downgrades, but the feature does exist :)

4. RAND() is actually deterministic (via other meta data written to the binlog). In any case, MySQL also supports Row-based replication and it is the proposed default for 5.7.

5. I would like to have this feature too :) It is important to note that few databases have this though.

6. MyISAM is no longer the default. PITR works fine with InnoDB.

7. The logging verbosity can be changed. In MySQL 5.7 there is a new server logging API which makes sure the output format is consistent. For some of the meta data you are suggesting, the better place to retrieve this is performance_schema.

8. You are probably talking about gap locks etc. These are required for statement-based replication, with Row-based they are not required.

10. I would suggest that ARCHIVE engine is of limited use-cases.

11. Please see http://mysqlserverteam.com/the-mysql-5-7-6-milestone-release... (search "innodb native partitioning"). Allowing more arbitrary functions would most likely require global secondary indexes. 5.7.6 contains an important change to move in that direction.

12. GIS support is improved dramatically in 5.7, including InnoDB support. Please take another look :)

14. I agree on this one. Async drives is becoming very important :)

RE: Point #4 in your second list, MySQL 5.6 contains much better configuration out of the box: http://www.tocker.ca/2013/09/10/improving-mysqls-default-con...

Re:MySQL 5.7.6 is out by IgorPartola on Tuesday March 10, 2015 @11:18AM (#9178166)
Thanks. I'll add one more and maybe this is already in the works: last I checked you couldn't rebuilt an index of an InnoDB table without re-inserting all the rows into a table. This meant that adding an index to a huge table was very slow.

I didn't know about a lot of these improvements, and am happy that they are happening. I moved away from MySQL around 2012-2013 because of the above reasons, so glad that things are improving.

Re:MySQL 5.7.6 is out by morgo on Tuesday March 10, 2015 @11:25AM (#9178209)
MySQL 5.6 supports Online DDL. Most operations are online, you can see a list at: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-o...

(The table 14.5 column "Allows Concurrent DML?" is the interesting one.)

Re:MySQL 5.7.6 is out by IgorPartola on Tuesday March 10, 2015 @11:34AM (#9178265)
Awesome, thanks. I am actually really happy about this.
Re:MySQL 5.7.6 is out by brendoncrawford on Tuesday March 10, 2015 @10:11AM (#9177774)
Redshift is great for some use cases, and completely unsuitable for others. If testing the waters with Postgres is your objective, Redshift will not be a good representation.
Re:MySQL 5.7.6 is out by benmorris on Tuesday March 10, 2015 @10:10AM (#9177769)
I have a similar opinion. Not to mention I have a many clients/projects already built on MySql databases. Moving to a different DB is expensive and unnecessary.
Re:MySQL 5.7.6 is out by Thaxll on Tuesday March 10, 2015 @10:05AM (#9177748)
Well it's HN, outside of Postgres / Redis everything sucks. And yes you're right on MySQL it's reliable. Honest question here, is there an equivalent of Percona tools for Postregres?