MySQL SSL connection

[scotthansonde]

I just did a new installation of my FeedLand instance. I'm trying out PlanetScale as my database provider (they seem to have a generous free plan 😃). They require the database connection be made over SSL, and I wasn't sure how to configure that in config.json. I eventually found the answer in the docs for the npm mysql package (used by davesql to connect to the database). I just needed to add an 'ssl' object to the database section of config.json

"database": {
        "host": "aws.connect.psdb.cloud",
        "port": 3306,
                 …
        "ssl": {
            "rejectUnauthorized": true
            }
        },

I'm not sure whether this is worth adding to the docs or not.

[scripting]

@scotthansonde -- good to hear you're starting up a fresh instance. ;-)

it'll be interesting to see if others have a comment on this...

[fmfernandes]

I'm not sure whether this is worth adding to the docs or not.

It sure is! And probably add a link to the MySQL docs as well to make it clear that any of those options can be used.

[scotthansonde]

In case anyone else wants to try PlanetScale's free plan with feedland, it won't work. It has a limit of 1 billion row reads per month, which sounds like a lot. However, my feedland instance with one user and only the suggested feeds ran up 1.7 billion row reads in 10 days.

I've now switched to a managed database at DigitalOcean for $15 a month. Just remember to set legacy password encryption for your feedland database user, that's required by the npm mysql package. I was tripped up by that for a little bit. 😃

[scripting]

I heard that there may be an issue in some of the SQL code in feedlanddatabase, there are queries that return hundreds of thousands of rows? Not sure. But it would be interesting to try to track this down and see if there isn't an error or misunderstanding. As I've said many times, I am a relative SQL newbie and could be doing something boneheaded. ;-)

Anyway -- thanks for the tip @scotthansonde. :-)

[scotthansonde]

I've now looked into this. The offending query is `select

• from items where guid = :guid and feedUrl = :feedUrl` in the isItemInDatabase function in feedlandDatabase. It gets called quite often. 😃

There's an index on feedUrl but not on guid, so the query has to read all rows with the same feedUrl to find the one with the right guid. I created a new index create index itemGuidUrl on items(guid, feedUrl). Now it reads only one row per query, so hopefully now it will only read tens of millions of rows in a month instead of billions. I'll leave it running a few days to see if any issues occur.

[scripting]

@fmfernandes -- check this out.

[scripting]

I added a note to the setup page, with a link back to this thread.

https://github.com/scripting/feedlandInstall/blob/main/docs/setup.md#mysql-setup

[scripting]

@scotthansonde @fmfernandes -- i installed the index recommended by Scott Hanson on feedland.org.

here's the worknote.

then after thinking about it for an instant i realized that the item table doesn't have a key and if it did this would be it.

this was a mistake.

[scripting]

BTW, isItemInDatabase is called a lot.

Every time we read a feed, we check if there are new items, or changes to an existing item.

For every item in a feed we're checking we call isItemInDatabase.

[scripting]

I think I should add this to the end of the declaration of the items table.

primary key (guid, feedUrl)

And remove the CREATE INDEX command I just added at the end.

What do you think?

[fmfernandes]

@scripting,

then after thinking about it for an instant i realized that the item table doesn't have a key and if it did this would be it.

I think it does have, it's defined here so ideally you should keep the new index.

[scripting]

@fmfernandes -- you're absolutely right.

i did that deliberately with much thought, and then proceeded to overlook it.

thank you. ;-)