F**k companies who's apps use MySQL/MariaDB tables of the table engine MEMORY.

Seriously.

That engine *sucks* to work with as an admin. It's such a huge pain in the ass having to always dump the whole DB instead of taking a snapshot.

And if the replica restarts... Poof. Replication breaks. Cuz all the memory tables are suddenly empty!

Fml. Fmfl. Ugh.

In what world is it even remotely acceptable to use volatile memory to store persistent data?!

In all my years, I've never met a project using mysql using the memory engine.

Who's bright idea was that?

@C0D4 nether have I.

@C0D4 I've seen it used for caching. Since the data in SQL tables is usually normalised, there are often cases where you need to denormalise (join or project) the data in order to perform certain queries. Instead of performing denormalisation every time you run a query, it might be faster and less memory-consuming to denormalise your data once, store denormalised data to an in-memory table, and then query that table instead.

I've never seen this either. Are they actually aware they're doing it? I've only been in companies where people used phpmyadmin to create tables without really caring about what happened afterwards

This meant a lot of tables with MyISAM engine, no indexes, no foreign keys and latin_swedish1 as collation and charset.

@hitko I can see it's use for caching, but I would question the need for MySQL to deal with it vs the likes of memcache.

I suppose one not being familiar with memcache could probably lean to mysql instead of it's already the DBS in play.

But, yea I'd still question it

@nitnip phpmyadmin ftw! Until you get pwned.

I had a dev once when we moved mysql behind a remote server and they wanted phpmyadmin, they weren't versed in anything else at the time.

Even tried to sneak the damn thing in through a deployment on the application side, god bless github and massive commits 😆

@C0D4 It is pretty handy, yeah. But it's rarely well safeguarded. A few years back, I remember there were a lot of websites that had their environment file accessible as plain text because of badly configured .htaccess

You could just google "filetype:.env" and get all the .env files google had unwittingly indexed.

@C0D4 Let's say you have a table of persons and a table of significant dates (birthday, date of death, graduation date, wedding date, etc.). If you want to list all persons whose graduation date is within some date range, you have to do something like

SELECT person.* FROM (SELECT date.person FROM date WHERE date.type LIKE 'graduation date' AND date.date > @RangeFrom AND date.date < @RangeTo) as filtered INNER JOIN person ON filtered.person = person.id;

With memcache you have to make a separate cache entry for each (RangeFrom, RangeTo), which doesn't make sense unless you're querying the same range over and over. Instead, you can make an in-memory table containing person + graduation date, which you can then quickly query for an arbitrary date range.

@cuddlyogre I'm upgrading a fleet of legacy MySQL servers where the customer used memory tables for their performance benefits.

Not sure how much that still applies today, but it's a pain in the ass to work with.

Already have a bootstrapped and working multi-source replica (Did I mention they have like... 8 of those legacy SQL servers?), and now, I finally managed to bootstrap a replica of that replica, as the legacy servers are planned to be disconnected soon.

Lets hope none of the servers reboots in the meantime, or bye bye replication... -_-"

@hitko To be honest, why would you create a sub query for that ?

The data is normalized... Just use a normal left join and you can get the same result (Or inner join if you don't want those with no dates)

SELECT person.* FROM person LEFT JOIN date ON date.person = person.id [...]

F**k companies who use relational database. It's a legacy tech and acid is a lie

@Grumm I just find it more readable and logical that way, i.e. "I need to find dates matching some condition, and then resolve persons for those dates" rather than "I need to find all pairs of person and date where date matches some condition". With simple queries like this one it makes no difference since the execution plan ends up the same either way, but with more complex ones it can result in significant performance gains if you filter the data before performing a join.

@PAKA I prefer relational dbs. Honestly the big problem is... people don't know how to design a good database schema anymore.

Shit ain't normalized.

Shit ain't indexed.

Nobody touches the DBMS's configuration because they just prefer using a fancy ORM.

Nothing is planned.

People don't know how to make good queries and let's not even get into sharding.

@nitnip team relational db here too.

Fuck all those noSQL crap where you can just dump data in and hope you can do something with it.

There is a reason why fb uses noSQL to store all the tracked data.

But also SAP or Dynamics AX are using relational databases. Big systems where data actually matters.

Just as a hint: read the documentation.

@Ubbe I mention you here because you mention changes.

Be VERY careful. That's not what MEMORY engine was designed for.

https://dev.mysql.com/doc/refman/...

For the full version.

The short version: Memory or previously Heap was a somewhat necessity as InnoDB wasn't mature enough (1995 plus I think, long time ago)... MyISAM and it's myriad of problems plus the MySQL optimizer made this an easy choice.

You basically "circumvented" the problem of aggregation. Note that this explicitly only applies to a "write from one client, read after" pattern.

Memory engine isn't made for heavy inserts - you will be better off with InnoDB and MVCC. Table locking can lead to severe performance degradation, especially if updates happen parallel - memory table will shit itself.

Easiest example is complex search: You have a complex search which spans across several tables, needs multiple queries to execute etc. "Back in the old days" you just created a MEMORY table for each search (hence no parallelity) and filled the table with the necessary search results and kept the database context alive for e.g. pagination. Yup. Hundreds of memory tables. Cause it was the 199+ and many users was a rarity...

Nowadays MEMORY use case is pretty niche.

You loose all benefits of an transactional storage engine, parallelity is unpractical (and possibly bug ridden) and you can shoot yourself very fast in the foot (e.g. excessive resource usage).

Let those things die. They're a relict of the past... Most often they hide a lot of problems which need to be addressed - because if MEMORY engine stops working, porting away from it will be a major PITA. Mostly because it's whole concept is anti to what an relational database is.

@Ubbe my mistake, i thought you had a heavy write pattern.

*Oopsie*