Migrating to mysql2 package

[scripting]

We're going to try to migrate FeedLand to use the mysql2 package. The hope is the product will perform better, and it will fit in with some improvements they're making on the VIP system at Automattic.

This is the first thread we've started here on FeedLand as an open source product.

But this is the right place to talk about how we want to approach this.

[scripting]

Initial thoughts

1. If we knew it was going to work, and not just for feedland.com but also for feedland.org and feedland.social, and other users' installations, and other apps that use davesql, then what I'd do is change davesql's dependencies section in package.json, and the feedland package.json (see note below), and the require statement at the head of davesql, and commence testing. But I don't know what we'd do if it didn't work and we need to back out of the change for some reason.

2. The idea that I'm toying with is to have davesql work with both packages, and have it be an option to choose one package or the other. So we could just do the switch on feedland.com, do lots of testing, then do a burn-in, and if everything was ok after say a month or two, we could optionally update the other projects. This would be the most conservative and safest approach, plan for a gradual change.

I think this part is my responsibility, because I have the overall picture of how the pieces fit together.

Why we need to include the dependency in the package.json at the top level

This was something that came up when I was working initially with Chris Zarate, so we keep all the packages all the sub-packages use updated with required version numbers in the top level package.json. I'm not 100% clear why this is needed, but we do it and it seems to work. :smile:

What I don't want to do

I don't want to start changing things with the assumption that we're fully committed to this change before we have a chance to test. The concern is that we're doing something so out of the ordinary in the SQL code in feedlanddatabase and feedland (I forgot earlier that there is SQL code there too) that it can't work with the new mysql package. At that point this may become an unacceptably large project. All the time I spend doing this, we're losing ground in the UI stuff, because it will take time to start it up again. I know people don't understand why it is this way, but that doesn't change the fact that it is this way.

What would be optimal

Set up a place to test that does not involve updating packages in NPM, and run feedland.com from that while doing the testing. But this doesn't seem likely. I do have things like that worked out in other contexts, scripts that manually update files in the node_modules folder. This makes fast iteration possible. But I don't have a clear enough idea how the VIP architecture works to begin to contemplate setting up something like this. So basically all the changes/updates have to flow through the global NPM database.

Conclusion

The smoothest way forward is the one I outline above in the second item under Initial thoughts. Change davesql so it uses both packages and it's a config option for the app to choose which one it will use for a particular application.

Comments welcome.

[fmfernandes]

Hi @scripting,

I've read through your notes above and specially the What would be optimal section, there is a solution for that! We can tell NPM to use a package only available locally on the filesystem without going through the NPM database. Here's how to do it (and how I done it locally with my FeedLand instance):

I’ve created a custom package based on davesql, for now it's on my filesystem and I named the folder custom-davesql. I copied over the contents from the original davesql to that folder but changed the following:

• in that package’s package.json file I changed the dependency from mysql to mysql2 and also changed the require statement (this line) to const mysql = require ("mysql2");.

Then on my FeedLand project's package.json file I added an override section so it uses the custom-davesql instead of the original one:

"overrides": {
  "davesql": "file:custom-davesql"
}

That makes all the packages that have a dependency on davesql to use my custom-davesql instead.

Run npm install and my instance of FeedLand is running locally with the updated mysql package.

I can do that same process on our staging VIP environment without the need for you to release a new version of davesql. What do you think about this approach?

[scripting]

@fmfernandes -- i've already made the changes to the distributed version of davesql. It's v0.6.0.

it takes a new option, flUseMySql2 -- defaults false.

so if you add to your config.json file

"flUseMySql2": true

You will if it works be using the new package.

package.json updates

At least feedland and feedlanddatabase will have to be updated. I can't do that right now, other things to do, and not sure what else, probably davesql's package.json has to change too to specify the new version.

The actual code

I haven't updated the repos, so there's the new code so it's very clear how to invoke the new package.

function start (options, callback) {
    for (var x in options) { //9/22/23 by DW
        config [x] = options [x];
        }
    if (config.millisecsBetwQueueRuns === undefined) { //12/28/20 by DW
        config.millisecsBetwQueueRuns = 100;
        }
    if (config.flQueueAllRequests === undefined) { //1/9/23 by DW
        config.flQueueAllRequests = false;
        }
    if (config.flUseMySql2 === undefined) { //4/12/24 by DW
        config.flUseMySql2 = false;
        }

    console.log ("davesql.start: using " + ((config.flUseMySql2) ? "mysql2" : "mysql") + "."); //4/12/24 by DW
    mysql = (config.flUseMySql2) ? require ("mysql2") : require ("mysql"); //4/12/24 by DW

    theSqlConnectionPool = mysql.createPool (options);

    startQueryQueue ();

    if (callback !== undefined) {
        callback ();
        }
    }

[fmfernandes]

Hey @scripting, it's not clear to me, do you want us to update the config.json file to use the new package on feedland.com or on our staging server first?

[scripting]

@fmfernandes -- I'm just helping out. I don't know what the next step is. And I can't take this on with all the other things I'm doing.

[scripting]

I gave it a try on feedland.social

I tried enabling the flUseMySql2 option on feedland.social, 19 hours ago, and it seems to be running solid since then.

So I made the version changes to the package.json files, released them, as described below, and installed these versions on feedland.social. It's now a fully up to date server, and running mysql2.

How to

The flUseMySql2 option is part of the database structure, alongside options like flLogQueries and flQueueAllRequests, in config.json. It defaults to false, so you only have to specify it if you want to use mysql2.

The new versions are: feedland == 0.6.75, feedlanddatabase == 0.7.51, davesql == 0.6.0.

I updated all the package.json files to reflect the new versions, however this may not be all that that's required. If there are problems, consult with a package.json expert, and please say what you had to do here, so I can correct the originals.

Notes

There are some warnings when it boots up about the fact that we pass options to mysql2 that it doesn't know anything about, saying it's just a warning now but later it will be an error. At some point there will need to be an update to specially prepare the options object to its liking.

I don't know what to tell you about how to run it. If you're feeling conservative and want to be sure our users get a good experience, set up a staging server to try it out on, and do a reasonable amount of testing. Just running FeedLand on its own will do a fair amount with the database.

feedland.social is on Digital Ocean, running in its own droplet, so I am able to install any version of Node there. This was important because it the new mysql package uses features that are not found in earlier versions of Node. Not sure what the cutoff is. I'm running Node 18 on the server feedland.social is running on.

feedland.social is running news.scripting.com which gets a fair amount of read-only traffic and the blogroll on scripting.com, and I use it for all my feed reading, to give you and idea of what's being tested database-wise on that server.

[fmfernandes]

Hey @scripting, 👋🏻

I think I missed this part:

The flUseMySql2 option is part of the database structure, alongside options like flLogQueries and flQueueAllRequests, in config.json.

So, when I added it to config.json for feedland.com, I added it to the root, instead of in the database object. I think this is why I didn't notice this issue on feedland.com.

But I'd like to let you know that we've updated feedland, feedlanddatabase and davesql on feedland.com and fully transioned to using mysql2. I confirmed this reading the logs when the application started:

davesql.start: using mysql2.

After that, I tried the /getuserprefs endpoint and didn't get any errors.

These are the full versions we're using on feedland.com right now:

• daveqsl: 0.6.3.
• feedland: 0.6.79.
• feedlanddatabase: 0.7.54.
• mysql2: 3.10.2.

Thanks!

[scripting]

@fmfernandes -- thanks for the update.