Freshrss: Document migration MySQL <-> SQLite <-> PostgreSQL

[gerhard-tinned]

Is there a way to migrate from sqlite to mysql? I have started testing with sqlite setting. But now i would like to switch to mysql. How can i do this?

Is there a possibility / setting / procedure to do this? If not, can you explain me the manual way of migration from sqlite to mysql?

Thanks

[aledeg]

There is no easy way to migrate.

What I suggest is to dump data in sql files like with mysqldump. I am not really familiar with sqlite, but I guess there is similar tools. At this step, you should dump table creation queries as well.
Then change the database configuration.
Then create the new database on mysql.
Then run the sql files generated at step one on the new database.

There is nothing to do with the config since it is stored in text files.

[marienfressinaud]

You can export your subscriptions (OPML) and favourites (JSON). Then, you can do a new installation (by adding an empty file ./data/do-install.txt) and choose MySQL database. Finally, import your feeds and favourite entries on your new installation.

That's the easiest procedure.

[aledeg]

@marienfressinaud when doing what you suggest, you lose all the articles that are not flagged as favorite.

[Alkarex]

Maybe this could help:

• http://www.sqlite.org/cvstrac/wiki?p=ConverterTools
• https://blog.bandinelli.net/index.php?post/2014/03/27/sqlite3-to-mysql

[gerhard-tinned]

Well, the first project is a dead one but it has a few links. I will try it. :)

[gerhard-tinned]

The second one is useless to me as i do not speak whatever that is (French?).

[Alkarex]

Most of the page is Python, the rest is French, yes :-)
https://translate.google.fr/translate?sl=fr&tl=en&js=y&prev=_t&hl=fr&ie=UTF-8&u=https%3A%2F%2Fblog.bandinelli.net%2Findex.php%3Fpost%2F2014%2F03%2F27%2Fsqlite3-to-mysql&edit-text=&act=url

[Alwaysin]

@gerhard-tinned did you manage to do it and are willing to share how you did?
@Alkarex is this something we want to include in the official documentation?

[gerhard-tinned]

No, sorry, I did not migrate. Did I mention that I am not able to read French??
I am still annoyed and stuck with sqlite.

[Alwaysin]

I've tried the script in the Python blog (not need to speak french for that :)) and it converted but I have troubles importing the database:

ERROR 1005 (HY000) at line 17: Can't create table 'freshrss.feed' (errno: 150)

Here is the converted code:

CREATE TABLE `feed` (
        `id` INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
        `url` varchar(511) NOT NULL,
        `category` SMALLINT DEFAULT 0,
        `name` varchar(255) NOT NULL,
        `website` varchar(255),
        `description` text,
        `lastUpdate` int(11) DEFAULT 0, -- Until year 2038
        `priority` tinyint(2) NOT NULL DEFAULT 10,
        `pathEntries` varchar(511) DEFAULT NULL,
        `httpAuth` varchar(511) DEFAULT NULL,
        `error` boolean DEFAULT 0,
        `keep_history` MEDIUMINT NOT NULL DEFAULT -2,
        `ttl` INT NOT NULL DEFAULT -2,
        `cache_nbEntries` int DEFAULT 0,
        `cache_nbUnreads` int DEFAULT 0,
        FOREIGN KEY (`category`) REFERENCES `category`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
        UNIQUE (`url`)
);

It looks like I'm missing something according to https://github.com/FreshRSS/FreshRSS/blob/dev/app/SQL/install.sql.pgsql.php#L29 so I added:

CREATE INDEX feed ON feed(name);
CREATE INDEX priority ON feed(priority);
CREATE INDEX keep_history ON feed(keep_history);

but still I have the same error.

Do you see a problem in there @Alkarex?

[Alkarex]

@Alwaysin You must ensure to create the table category first, then feed, then entry, to respect foreign keys.

[aledeg]

Or create foreign keys afterwards...

[Kaan88]

Has anyone successfuly did this?

[Alkarex]

@Kaan88 Yes, this is now a built-in feature. Available in the /dev branch
See https://github.com/FreshRSS/FreshRSS/blob/dev/cli/README.md and https://github.com/FreshRSS/FreshRSS/pull/2496
Additional testing welcome. Will be released with FreshRSS 1.15.0.
Volunteers for a little note in our documentation also welcome :-)
https://github.com/FreshRSS/FreshRSS/tree/dev/docs

[Kaan88]

That's great to hear.

[Kaan88]

Wow, it couldn't be more simple. I don't know if this is how it was meant to be done but it worked with no problem.

I have created a mysql database, changed database from sqlite to mysql in config.php and ran the import command. It took everything from my sqlite database and imported it.

I assume import looks at the config file for current database choice?

[Alkarex]

@Kaan88 Thanks for the feedback, and yes, you got it right 🙂
It is also possible to use the CLI to change database parameters instead of manually editing the config file (which is also fine)

[Alkarex]

@Kaan88 If you feel like writing a few lines somewhere in our documentation, that would be nice 😉

• Source https://github.com/FreshRSS/FreshRSS/tree/dev/docs/
• Result https://freshrss.github.io/FreshRSS/

[hrehfeld]

I'm running into an error, maybe you need to filter first or use a different INT type?

Old server:

./export-sqlite-for-user.php --user myuser --filename test.sqlite
FreshRSS exporting database to SQLite for user “myuser”…
Start SQL copy…
120501/385017vda1: warning, group block quota exceeded.
130701/385017vda1: warning, group block quota exceeded.
130801/385017vda1: warning, group block quota exceeded.
385017/385017
Result: success

New server (docker), running on postgresql:

$ sudo docker cp ./freshrss.sqlite freshrss-app:/tmp/
$ sudo docker exec freshrss-app /var/www/FreshRSS/cli/import-sqlite-for-user.php --user myuser --filename /tmp/freshrss.sqlite --force-overwrite
FreshRSS importing database from SQLite for user “myuser”…
Deleting SQL data for user “myuser”…
Start SQL copy…
261201/385017FreshRSS[42]: [myuser] [Fri, 13 Nov 2020 14:20:28 +0100] [error] --- SQL error addEntry: 22003: 7 ERROR:  value "97225228800" is out of range for type integer while adding entry in feed 323 with title: Switching to Hugo - Full Time
Error during SQLite copy of entries!
FreshRSS[42]: [myuser] [Fri, 13 Nov 2020 14:20:28 +0100] [error] --- Error during SQLite copy of entries!
If you would like to clear the user database first, use the option --force-overwrite

This is the corresponding entry; the offending number is in the date column.

INSERT INTO "main"."entry" ("id", "guid", "title", "author", "content", "link", "date", "lastSeen", "hash", "is_read", "is_favorite", "id_feed", "tags") VALUES ('1586437688147852', 'https://joshrollinswrites.com/help-desk-head-desk/switching-to-hugo/', 'Switching to Hugo - Full Time', '', '
<p>PayPal informed me that the host of my WordPress blog is charging me for another $80 or so for the next year. The very next day, tumblr <a href="https://tumblr.zendesk.com/hc/en-us/articles/231885248-Sensitive-content">decided to shoot itself in the head</a> (rather than the leg, if you ask me). It was good to be reminded to not trust cloud services for creative content - like my blog.</p>
<h2>The Issues With my Host</h2>
<p>My current host sucks. My WordPress blog continuously goes offline, so much so that I had to mute the alerts I got because they overwhelmed my inbox. My connection issues never got resolved.</p>
<p>Another major issue with that website was the lack of https (this is not my host fault, actually, it’s just yet another thing you need to buy) and the constant spam I got on the website. People would go to my link and find a totally different website trying to sell them something. That issue was not resolved either.</p>
<p>There were other technical issues, from WordPress not functioning right due to database corruptions (the host had an automatic set up process), to my domain not being resolved correctly. In the past, such issues would die out in the first month or two when I switch to a new host – but not this time. This time, as soon as I got the notification of renewal from PayPal, I immediately asked for cancellation.</p>
<h2>More Work to be Done</h2>
<p>There’s a lot more work to do on this website. I am still not happy with it, and I believe my WordPress blog overall looked better still. But it is up to me to make this place better with my own set of growing skills. CSS, ox-Hugo, Go, and of course, the quality (and quantity) what I write matter.</p>
<p>I’m also not entirely happy with the blog hosted on GitHub, which is now owned by Microsoft. It’s no difference than Tumblr being bought out by Yahoo. I need to move to an actual host with a more liberal point of view about sexual content. Unfortunately, in this world of ours, this means I’m looking for a host that is OK with Pornography. They have to include everything in one blanket statement so it’s easy to sort out in courts, in case someone gets offended. But I’m not going to go down that hole right now, there’s a whole lot of it, more than enough, on the internet right now with people freaking out about Tumblr.</p>
<p>So what’s the solution…?</p>
<p>I believe AWS (Amazon hosting service) might be it for now. I find it hard to believe they’d bother chasing me if I mention a sexual encounter or show a picture of a breast. Still, even AWS is not a safe haven. Hmmm.</p>
', 'https://joshrollinswrites.com/help-desk-head-desk/switching-to-hugo/', '97225228800', '1603746025', X'6a362fb8f166baf4a047eb92ec0f3cc0', '1', '0', '323', '');\

id | guid | title | author | content | link | date | lastSeen | hash | is_read | is_favorite | id_feed | tags
-- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | --
1586437688147852 | https://joshrollinswrites.com/help-desk-head-desk/switching-to-hugo/ | Switching to Hugo - Full Time |   | <p>PayPal informed me that the host of my WordPress blog is charging me for another $80 or so for the next year. The very next day, tumblr <a href="https://tumblr.zendesk.com/hc/en-us/articles/231885248-Sensitive-content">decided to shoot itself in the head</a> (rather than the leg, if you ask me). It was good to be reminded to not trust cloud services for creative content - like my blog.</p> <h2>The Issues With my Host</h2> <p>My current host sucks. My WordPress blog continuously goes offline, so much so that I had to mute the alerts I got because they overwhelmed my inbox. My connection issues never got resolved.</p> <p>Another major issue with that website was the lack of https (this is not my host fault, actually, it’s just yet another thing you need to buy) and the constant spam I got on the website. People would go to my link and find a totally different website trying to sell them something. That issue was not resolved either.</p> <p>There were other technical issues, from WordPress not functioning right due to database corruptions (the host had an automatic set up process), to my domain not being resolved correctly. In the past, such issues would die out in the first month or two when I switch to a new host – but not this time. This time, as soon as I got the notification of renewal from PayPal, I immediately asked for cancellation.</p> <h2>More Work to be Done</h2> <p>There’s a lot more work to do on this website. I am still not happy with it, and I believe my WordPress blog overall looked better still. But it is up to me to make this place better with my own set of growing skills. CSS, ox-Hugo, Go, and of course, the quality (and quantity) what I write matter.</p> <p>I’m also not entirely happy with the blog hosted on GitHub, which is now owned by Microsoft. It’s no difference than Tumblr being bought out by Yahoo. I need to move to an actual host with a more liberal point of view about sexual content. Unfortunately, in this world of ours, this means I’m looking for a host that is OK with Pornography. They have to include everything in one blanket statement so it’s easy to sort out in courts, in case someone gets offended. But I’m not going to go down that hole right now, there’s a whole lot of it, more than enough, on the internet right now with people freaking out about Tumblr.</p> <p>So what’s the solution…?</p> <p>I believe AWS (Amazon hosting service) might be it for now. I find it hard to believe they’d bother chasing me if I mention a sexual encounter or show a picture of a breast. Still, even AWS is not a safe haven. Hmmm.</p> | https://joshrollinswrites.com/help-desk-head-desk/switching-to-hugo/ | 97225228800 | 1603746025 |   | 1 | 0 | 323 |  

It seems that a bunch of posts from this blog have weird dates:

Maybe there's a sanitizer/filter missing on refresh/import?

[aledeg]

@Alkarex You've added the tag documentation on that issue. What needs to be done so we can close it?

[Alkarex]

@aledeg Just make sure that we somewhere in the doc mention this option to export to SQLite from any database, and import from SQLite to any database for instance for the purpose of changing database type, for changing server, for backup, or even for attempting to fix database corruptions.