Support MySQL

[David Thompson]

MediaGoblin currently does not support MySQL. It would be nice if it did.

[David Thompson]

Elrond has mentioned that there are some difficulties associated with MySQL such as the required VARCHAR size that SQLite or PostgreSQL do not require. Elrond shared this link in the IRC that should prove useful to make variants of queries specifically for MySQL.

​http://docs.sqlalchemy.org/en/latest/core/types.html#sqlalchemy.types.TypeDecorator.with_variant

[David Thompson]

Created a branch for testing MySQL support. It seems to work correctly so far. I haven't tested migration from MongoDB but starting from scratch with MySQL works.

​https://gitorious.org/~davethompson/mediagoblin/davethompsons-mediagoblin/commits/mysql

[Elrond]

The above mentioned branch has some good ideas. We possibly should start out and pick individual changes we like from this branch and give credit to David Thompson.

Well, this feels low priority given the number of people asking for mysql support...

[David Thompson]

The code in the branch is quite far behind master at this point. Maybe I could try to bring it up-to-date at some point as a proof of concept.

[Christopher Allan Webber]

This is a good sprint project *if* someone knows mysql.

[Elrond]

As I already pointed out: The code in the branch by David Thompson has some good ideas. And picking that individually might be a start.

[Danilo Tomasoni]

I added a patch that can be applied on top of the official master branch to include experimental mysql support. Code review is appreciated.

Thanks.

[Danilo Tomasoni]

mysql branch against master. Conflicts fixed.

[David Thompson]

Hey! Cool to see someone wanting this feature. I went back and looked at what I had written, and decided to re-do my work in another branch.

Below is a link to a branch that adds MySQL support in fewer lines of code and is up-to-date with master.

​https://gitorious.org/~davethompson/mediagoblin/davethompsons-mediagoblin

The main issue with MySQL support is that VARCHARs must have a length. So, I've created a variant of Unicode that defaults to a length of 255 characters for MySQL. This is just a default, and the length can be tweaked on a per column basis if needed.

[Danilo Tomasoni]

OMG. See this ​https://diasp.org/posts/2031680
this is gonna be a problem..

[David Thompson]

Don't worry, you can use MariaDB. :)

[David Thompson]

There is an issue with the branch as it is currently: Migrations are broken. Starting from an empty database works fine. Will fix soon.

[David Thompson]

Updated branch to fix issues with migrations and all of the included media types.

[Danilo Tomasoni]

Replying to dthompson:

Updated branch to fix issues with migrations and all of the included media types.

It worked for me, I successfully added the audio media type to my MG installation
with ./bin/gmg dbupdate

[rodney757]

Replying to dthompson:

Updated branch to fix issues with migrations and all of the included media types.

I see you have 2 mysql branches. Is the correct one 421-mysql?

[David Thompson]

Yes, 421-mysql is the correct branch.

[Danilo Tomasoni]

will this branch be included in some release?

[Christopher Allan Webber]

No comments on this in a while, my apologies.

I don't have time to pick up a database review; if someone else running MySQL is intersted in helping review this, I'd be interested in pulling it into master.

[Matt Molyneaux]

Doesn't look like the above branch has been kept updated. Changing to "accepted".

[Ben Sturmfels]

From a strategic point of view, I'm not sure I see the benefit of supporting MySQL. It's a significant amount of work up-front, plus has ongoing maintenance overhead to ensure future changes work for both database backends. Given the limited developer resources, I think the project needs to be clear about why this is a desirable goal.

The reason possible reason I can think of for supporting MySQL is if it encouraged more people to run MediaGoblin rather than using centralised, proprietary services. Is MySQL more popular than PostgreSQL? Probably yes, due to the "Wordpress effect". But in terms of ease of deployment, you can't put MediaGoblin on $5 a year shared hosting anyway, so whether it requires MySQL or PostgreSQL seems fairly moot - you don't interact significantly with either database anyway. In contrast, supporting SQLite3 does dramatically simplify the deployment process for small sites - no additional database server at all.

PostgreSQL seems to be widely preferred amongst the Python and Django community. It has better SQL standards support and useful features like transactions around schema modifications. I hear quite a bit about switching from MySQL to PostgreSQL, but I don't think I've heard anyone going the other way.

Since MediaGoblin is an application, rather than a library, I suggest being shamelessly opinionated and sticking to a single backend. (For what it's worth, I use both MySQL and PostgreSQL in production for my day job.)

Definitely interested to hear peoples' thoughts though - especially since I greatly respect dthompson's work. :)

[Ben Sturmfels]

There haven't been any responses to my enquiries here for 12 months, so I'll go ahead and close this as "wontfix" for now. Please don't hesitate to re-open if you think there's still a good case for concurrently supporting PostgreSQL and MySQL/MariaDB.