7 ms·

Don't hate me for it, but I'd like this for MySQL to postgres too. At least as a stepping stone. Use case: some of my SQL syntax depends on MySQL but I realize

Don't hate me for it, but I'd like this for MySQL to postgres too. At least as a stepping stone. Use case: some of my SQL syntax depends on MySQL but I realize I made a poor life choice and would rather have transactional DDL and a myriad of better features on postgres.

ziftface 6y ago
Something like this would solve a lot of my problems
- leesalminen 6y ago
  I’d considered migrating MySQL to Postgres on a 200 table production app more than once. Couldn’t find any good tooling at the time so I just sucked it up and lived with my life choices.
touisteur 6y ago
Can't you do it with a foreign data wrapper or the equivalent in mysql? Maybe keep your mysql-specific queries as views in mysql and call them from pg? Just spitballing, sorry, I love FDWs.
pletnes 6y ago
For SQL server, AWS can save their customers money by cutting license cost (to MS). MySQL is already free so I don’t see how they can benefit from such a project.
- etaioinshrdlu 6y ago
  I agree, but there might be a creative way to do it that increases customer happiness and still is a decent business.
- bbatha 6y ago
  They can go one better — they can fork the parsers out of both projects and run it against a common query planner/storage engine (aurora).
- ksec 6y ago
  >don’t see how they can benefit from such a project I thought there were bad blood between Oracle and Amazon?
  - jen20 6y ago
    In that case you'd expect to see it for Oracle proper instead of MySQL first, no?
    - rj0049 6y ago
      The Oracle wire protocol contains a (copyrighted) haiku, so no one can reverse-engineer the Oracle wire protocol without infringing on Oracle's copyright.
- jcriddle4 6y ago
  If you have several thousand stored procs and triggers then having to not rewrite all of that is actually likely over a million in savings.
robocat 6y ago
However if the translation layer is designed for consistency, like Babelfish, it would have to replicate many of the MySQL “faults” too... e.g. it would need to have the same non-transactional DDL so it acted “correctly”! And it would need to replicate the specialised data types like utf8mb3. To use PostgreSQL features (such as JSON datatype) would presumably need a separate data connection and transaction, because if it isn’t implemented by MySQL, then the syntax wouldn’t be supported by Babelfish. You would get some wins, but you don’t get a blend of the best of both.
- etaioinshrdlu 6y ago
  It would be nice if it could aim for doing the sane thing over exact MySQL compatibility. But I agree it would be a minefield...
linsomniac 6y ago
You might be able to get away with staying with MySQL or a variant. Years ago I did an "interview problem" to generate a report of some accounting data of a sample data set in MySQL. The details are foggy now, but I ended up doing it all using advanced SQL features that I thought only existed in PostgreSQL (you know, or Oracle), but whatever engine I ended up using had it as well. It was probably the latest Percona or Maria?
- Conlectus 6y ago
  MySQL has worse guarantees in several cases, though. For example, using `SERIALIZABLE` isolation means your queries can deadlock when run in parallel. Likewise, selecting a column not present in a GROUP BY leads to random values being returned.
  - johannes1234321 6y ago
    MySQL improved a lot in those places.
  - donatj 6y ago
    > Likewise, selecting a column not present in a GROUP BY leads to random values being returned. That hasn't been the default behavior for something like 5 years. Beyond that the values aren't "random" as much as "one of the values from the set" which can be a useful feature in cares where you want any of the values and don't want the performance hit that comes with using an aggregate function particularly on very very large sets.
The_rationalist 6y ago
Why not migrate to MariaDB? It's better than MySQL and has easy and great clustering contrary to Postgres
lutoma 6y ago
I would love that. There is so much mediocre but popular software out there that uses hard-coded MySQL queries instead of some abstraction layer. Having to use MySQL/MariaDB after usually dealing with PostgreSQL always feels like such a downgrade.
lukaseder 6y ago
Use https://www.jooq.org/translate/ https://www.jooq.org/translate/
- victor106 6y ago
  This is so awesome. Wish I found it earlier. Thanks for posting
  - lukaseder 6y ago
    Well, what were you looking for? We might need to improve our SEO...
gavinray 6y ago
pgloader does this (and much much more) in a single command: https://github.com/dimitri/pgloader https://github.com/dimitri/pgloader pgloader \ mysql://user:password@mysql:3306/database \ postgresql://postgres:root@localhost:5432/postgres This is a gem of a tool. Surprised nobody has mentioned it. Supports SQLite and MS-SQL too. https://pgloader.readthedocs.io/en/latest/ref/mysql.html# https://pgloader.readthedocs.io/en/latest/ref/mysql.html#
- acmecorps 6y ago
  Absolutely, absolute love pgloader! So painless and quite straightforward. Saved me lots of time migrating my production db. Highly, highly recommend if anyone wants to migrate from MySQL to PG!
brian_herman 6y ago
https://aws.amazon.com/about-aws/whats-new/2016/04/aws-schema-conversion-tool-now-supports-conversions-between-mysql-and-postgresql/ https://aws.amazon.com/about-aws/whats-new/2016/04/aws-schem...

ziftface 6y ago

Something like this would solve a lot of my problems

leesalminen 6y ago
I’d considered migrating MySQL to Postgres on a 200 table production app more than once. Couldn’t find any good tooling at the time so I just sucked it up and lived with my life choices.

touisteur 6y ago

Can't you do it with a foreign data wrapper or the equivalent in mysql? Maybe keep your mysql-specific queries as views in mysql and call them from pg? Just spitballing, sorry, I love FDWs.

pletnes 6y ago

For SQL server, AWS can save their customers money by cutting license cost (to MS). MySQL is already free so I don’t see how they can benefit from such a project.

etaioinshrdlu 6y ago
I agree, but there might be a creative way to do it that increases customer happiness and still is a decent business.
bbatha 6y ago
They can go one better — they can fork the parsers out of both projects and run it against a common query planner/storage engine (aurora).
ksec 6y ago
>don’t see how they can benefit from such a project I thought there were bad blood between Oracle and Amazon?
- jen20 6y ago
  In that case you'd expect to see it for Oracle proper instead of MySQL first, no?
  - rj0049 6y ago
    The Oracle wire protocol contains a (copyrighted) haiku, so no one can reverse-engineer the Oracle wire protocol without infringing on Oracle's copyright.
jcriddle4 6y ago
If you have several thousand stored procs and triggers then having to not rewrite all of that is actually likely over a million in savings.

robocat 6y ago

However if the translation layer is designed for consistency, like Babelfish, it would have to replicate many of the MySQL “faults” too... e.g. it would need to have the same non-transactional DDL so it acted “correctly”! And it would need to replicate the specialised data types like utf8mb3. To use PostgreSQL features (such as JSON datatype) would presumably need a separate data connection and transaction, because if it isn’t implemented by MySQL, then the syntax wouldn’t be supported by Babelfish. You would get some wins, but you don’t get a blend of the best of both.

etaioinshrdlu 6y ago
It would be nice if it could aim for doing the sane thing over exact MySQL compatibility. But I agree it would be a minefield...

linsomniac 6y ago

You might be able to get away with staying with MySQL or a variant. Years ago I did an "interview problem" to generate a report of some accounting data of a sample data set in MySQL. The details are foggy now, but I ended up doing it all using advanced SQL features that I thought only existed in PostgreSQL (you know, or Oracle), but whatever engine I ended up using had it as well. It was probably the latest Percona or Maria?

Conlectus 6y ago
MySQL has worse guarantees in several cases, though. For example, using `SERIALIZABLE` isolation means your queries can deadlock when run in parallel. Likewise, selecting a column not present in a GROUP BY leads to random values being returned.
- johannes1234321 6y ago
  MySQL improved a lot in those places.
- donatj 6y ago
  > Likewise, selecting a column not present in a GROUP BY leads to random values being returned. That hasn't been the default behavior for something like 5 years. Beyond that the values aren't "random" as much as "one of the values from the set" which can be a useful feature in cares where you want any of the values and don't want the performance hit that comes with using an aggregate function particularly on very very large sets.

The_rationalist 6y ago

Why not migrate to MariaDB? It's better than MySQL and has easy and great clustering contrary to Postgres

lutoma 6y ago

I would love that. There is so much mediocre but popular software out there that uses hard-coded MySQL queries instead of some abstraction layer. Having to use MySQL/MariaDB after usually dealing with PostgreSQL always feels like such a downgrade.

lukaseder 6y ago

Use https://www.jooq.org/translate/ https://www.jooq.org/translate/

victor106 6y ago
This is so awesome. Wish I found it earlier. Thanks for posting
- lukaseder 6y ago
  Well, what were you looking for? We might need to improve our SEO...

gavinray 6y ago

pgloader does this (and much much more) in a single command: https://github.com/dimitri/pgloader https://github.com/dimitri/pgloader pgloader \ mysql://user:password@mysql:3306/database \ postgresql://postgres:root@localhost:5432/postgres This is a gem of a tool. Surprised nobody has mentioned it. Supports SQLite and MS-SQL too. https://pgloader.readthedocs.io/en/latest/ref/mysql.html# https://pgloader.readthedocs.io/en/latest/ref/mysql.html#

acmecorps 6y ago
Absolutely, absolute love pgloader! So painless and quite straightforward. Saved me lots of time migrating my production db. Highly, highly recommend if anyone wants to migrate from MySQL to PG!

brian_herman 6y ago

https://aws.amazon.com/about-aws/whats-new/2016/04/aws-schema-conversion-tool-now-supports-conversions-between-mysql-and-postgresql/ https://aws.amazon.com/about-aws/whats-new/2016/04/aws-schem...