Just FYI, back to the original question, it’s still possible to configure the latest Ghost version with sqlite and get it running on Fly.
Here’s the relevant parts of the config I’m using:
[env]
database__client = "sqlite3"
database__connection__filename = "content/data/ghost.db"
database__useNullAsDefault = "true"
database__debug = "false"
[mounts]
source="data"
destination="/var/lib/ghost/content"
I use this guide and deploy ghost with sqlite3:
How to Host a Ghost Blog for Free on Fly.io (blixtdev.com)
Can you help me to transfer my ghost blog from sqlite3 to MySQL8 . Right now my blog is running on sqlite3 on ghost 5.23.0 with a message that says I run my ghost blog on unsupported database in production. Its working ok, but I’m not sure what may happened if I still use sqlite3. Also, can you tell me how to backup and update ghost to newer released version? I’m not sure how to do that from Fly command-line tools.
Hey there, I hope, someone will answer still! Thank you so much for your tutorial, @Curiositry ! It worked almost for me, but I have trouble, connecting the database and the blog.
I get the following error:
2023-01-19T17:12:35.422 app[96cc2b97] fra [info] [2023-01-19 17:12:35] INFO Ghost is running in production...
2023-01-19T17:12:35.437 app[96cc2b97] fra [info] [2023-01-19 17:12:35] INFO Your site is now available on https://three-columns.fly.dev/
2023-01-19T17:12:35.439 app[96cc2b97] fra [info] [2023-01-19 17:12:35] INFO Ctrl+C to shut down
2023-01-19T17:12:35.441 app[96cc2b97] fra [info] [2023-01-19 17:12:35] INFO Ghost server started in 3.465s
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] [2023-01-19 17:12:35] ERROR connect ECONNREFUSED 127.0.0.1:3306
2023-01-19T17:12:35.927 app[96cc2b97] fra [info]
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] connect ECONNREFUSED 127.0.0.1:3306
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] "Unknown database error"
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] Error ID:
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] 500
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] Error Code:
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] ECONNREFUSED
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] ----------------------------------------
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] Error: connect ECONNREFUSED 127.0.0.1:3306
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] at /var/lib/ghost/versions/5.25.5/node_modules/knex-migrator/lib/database.js:57:19
2023-01-19T17:12:35.927 app[96cc2b97] fra [info] at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1278:16)
2023-01-19T17:12:35.927 app[96cc2b97] fra [info]
2023-01-19T17:12:35.943 app[96cc2b97] fra [info] [2023-01-19 17:12:35] WARN Ghost is shutting down
2023-01-19T17:12:35.946 app[96cc2b97] fra [info] [2023-01-19 17:12:35] WARN Ghost has shut down
2023-01-19T17:12:35.947 app[96cc2b97] fra [info] [2023-01-19 17:12:35] WARN Your site is now offline
2023-01-19T17:12:35.949 app[96cc2b97] fra [info] [2023-01-19 17:12:35] WARN Ghost was running for a few seconds
2023-01-19T17:12:36.102 app[96cc2b97] fra [info] Starting clean up.
I don’t have MySql installed locally, but that shouldn’t be a problem, right? I have only been coding and deploying for half a year. So if I didn’t add all the necessary details, just say the word.
Thanks in advance for any help!
Hi @n-co,
The first thing I notice is that you link to the SQLite version of my Ghost on Fly tutorial, but the logs say that it’s running in production mode, which requires the MySQL version.
So if you’re just experimenting, you could try running:
flyctl secrets set -a three-columns NODE_ENV=development
And see if that fixes it.
If you want a production blog, try the script from my newer Ghost + MySQL 8 tutorial and let me know if that works:
Hi @Curiositry! First of all, thanks for all the helpful tutorials.
I managed to get everything working within the last weeks, using your Ghost 5 + MySQL8 script. Setting up certificates, domain, app secrets and even figured out the “transactional mail” thing you described in this thread… then I started to write/blog and prepared for the launch. Today I decided to update to the latest Ghost version, from 5.26.3 to 5.30.1 - so here is what I did:
I ran a manual .json backup from ghost admin and additionally a zip backup via
flyctl ssh sftp shell -a APPNAME
Next step was the update:
fly deploy -a [APPNAME] was only working from the fly.toml directory
But unfortunately, I’m not able to access the site anymore (white screen)
Wether from the .com domain I set nor from the fly hostname appname.fly.dev
Fly dashboard says:
The applications are running: APPNAME 
; APPNAME-mysql
The Free builder (fly-builder-damp-haze-xyz) was created and is now in pause mode.
When checking the app information under Image details, it lists the Tag “5.30.1-alpine”. The update logs were without any special events. Then I ran flyctl doctor and everything seems working fine, but I’m still not able to access the site.
Any ideas? Your help is highly appreciated.
P.S. Later on, I also ran the mysql update - also fine
Hi @stefan1. Sorry to hear that. That’s not something I’ve experienced before. What is the output of fly logs -a APPNAME? Are there any errors there? Does it say Ghost crashed?
Also (since the appname is private) can you glean any hints of what might be wrong from the network + console tabs of your devtools when you visit appname.fly.dev?
Also, I vaguely remember that the order of which you update/restart is sometimes important, so if it happened after you updated the MySQL instance, you could try re-fly deploying the Ghost instance in case updating MySQL while Ghost was running snarled something up.
It’s strange, from the appname.fly.dev and also from the appname.com I receive the following from network console:
:status: 502
Via: 2 fly.io
Date: Fri, 20 Jan 2023 06:40:39 GMT
Server: Fly/e78c172f (2023-01-10)
fly-request-id: 01GQ6YE8MPQCM6T64C8CV7E8J0-fra
First I run the ghost update, then the app stopped working. Afterwards my idea was to update mysql with some errors due to a lack of memory, but finally if updated the app as well. The logs seems fine, but I will look into it again. Need to find the first logs after update, because I tried re-deploying several times in the meantime. Thanks for your help.
Hi @Curiositry , thank you so much for your answer! I linked to the wrong article… Sorry about that. I actually used the article & script, you linked to in your answer! The database site seems healthy: three-columns-mysql.fly.dev but the connection to three-columns.fly.devseems to fail with the error message above…
Thanks for your detailed reports @stefan1 & @n-co. I don’t see anything immediately suspicious, and won’t have time for in-depth debugging until after the weekend. But both are strange cases, and I’d like to figure out what went wrong.
One thing that would help: did both of you run the script from my tutorial exactly as written? Knowing what if anything is different about your config would help me reproduce the issues you’re running into.
@stefan1 The quick fix would probably be to spin up a fresh install, import what you exported from the admin panel, and then attach the old ghost_data volume to the new Ghost instance. I haven’t tried this exact method, but it would mean you don’t have to mess with dumping and importing the DB for the posts and such (which should all be in the export from the admin panel), or even restoring from your SFTP backup for images & files (which should all be on the ghost_data volume).
@n-co My first hunch is either that the DB info set via fly secrets is incorrect (or didn’t get set correctly), or that some small piece of the install script (silently) failed due to your operating system or a missing dependency. (Maybe try re-running those fly secrets commands, making sure the values and syntax is correct?) Did you notice any errors or anomalous output when you ran the command?
Tanks for your thoughts. I ran the script as written, with some minor changes, due to localization , e.g. changed quotation marks and region, removed comments
Last question before the weekend:
Would restoring a snapshot not be the easier to accomplish? (Never did that before)
Because otherwise I need to create certificates, adapt DNS settings and so on.
Ah, I see. It might work to restore from a snapshot, for the app instance, the DB instance, or both (depending on what you have other backups of, and what actually is causing the issue). I haven’t tried it (at least not successfully), so I can’t really advise though without more research. But that wouldn’t help if the issue is with the config, rather than what’s on the volumes.
@stefan1 My first hunch though is that this 502 bad gateway error is more likely related to Fly than your Ghost install, or may have been something that got messed up during the issues with fra region.
When it’s Ghost crashing, I usually get 500 internal server errors .
I fixed it by the following steps:
fly volumes create ghost_data --snapshot-id vs_Bg57zQGw..... -a <appname> -s 1fly volumes delete vol_x915gr.....
Chaka boom, it’s working with the multiple times deployed ghost version 5.30.1 
And now comes the fun part… when checking for completeness, I realised that a new Ghost version, 5.31, was released. Perfect for testing the update process again, so I ran fly deploy this time successfully.
@stefan1 Excellent to hear!
Maybe it got corrupted because it was updated when there were issues with the fra region? I can’t think of any other obvious explanation. It would be interesting to know what it was that god corrupted (ie, some specific file the would show up in a diff of the .zip backup? or the volume itself?) In any case, glad you got it sorted out!
Maybe the fra host problems might be the root cause… just curious.
Anyway, thanks for sharing your knowledge - highly appreciated!
Btw: Do you know what happens when pressing the upgrade link in the ghost admin area?
(I did not dare)
If only it was that easy! (Though fly deploy is close, when it works.) For me it redirects to Ghost Docs
Hey @Curiositry , soo this is the script, I used - I changed small things (explanations included):
# I hardcoded all names (appname, passwords) because the variables were not read
# (not even 'undefined', just empty like so:
# '-mysql' instead of 'three-columns-mysql')
# all my secrets seem to be set in the fly dashboard
# I used more specific images (ghost:5.31.0, mysql:8.0.32) because
# the versions in the original script were not found
bash << EOF
flyctl auth signup
mkdir ann-secondtry && cd ann-secondtry
echo | flyctl launch --name three-columns --image=ghost:5.31.0 --region fra --no-deploy
flyctl volumes create ghost_data --region fra --size 1 # <----- changed to FRA bc that's the region I need
sudo apt install sed
sed -i 's/internal_port = 8080/internal_port = 2368/g' fly.toml
cat >> fly.toml << BLOCK
[mounts]
source="ghost_data"
destination="/var/lib/ghost/content"
BLOCK
flyctl secrets set url=https://three-columns.fly.dev
mkdir ann-secondtry-mysql && cd ann-secondtry-mysql
echo | flyctl launch --name three-columns-mysql --image=mysql:8.0.32 --region fra --no-deploy
flyctl volumes create mysql_data --size 1 --region fra # <----- changed 'fly' to 'flyctl' bc. of a 'Command not found' error in the terminal
flyctl secrets set MYSQL_PASSWORD=three MYSQL_ROOT_PASSWORD=############### # <----- just exchanged the passwords for hashes for the forum post
flyctl secrets set database__client=mysql
flyctl secrets set database__connection__host=three-columns-mysql.internal -a three-columns
flyctl secrets set database__connection__user=ghost -a three-columns
flyctl secrets set database__connection__password=################ -a three-columns
flyctl secrets set database__connection__database=ghost -a three-columns
flyctl secrets set database__connection__port=3306 -a three-columns
flyctl secrets set NODE_ENV=production
cat > fly.toml << BLOCK2
app = "three-columns-mysql"
kill_signal = "SIGINT"
kill_timeout = 5
processes = []
[build]
image = "mysql:8"
[mounts]
source="mysql_data"
destination="/data"
[env]
MYSQL_DATABASE = "ghost"
MYSQL_USER = "ghost"
[experimental]
cmd = [
"--default-authentication-plugin",
"mysql_native_password",
"--datadir", "/data/mysql",
"--performance-schema=OFF",
"--innodb-buffer-pool-size", "64M"
]
BLOCK2
flyctl launch
cd ../
flyctl launch
EOF
→ These are the secrets set for three-columns.fly.dev
→ These are the secrets of three-columns-mysql.fly.dev
The thing is that I actually already have a running blog but it uses sqlite and I keep getting the warning that the website crashed and that I need to scale my memory to 2048. It IS still worknig (without raising the memory) but I am not sure for how long. The friend, for whom I build the blog, does not want to pay for memory, particularly if there are ways not to 
.
Thanks again!
Okay, I’ll get to this soon. One thing I notice is that in the TOML block it’s still just MySQL 8, which doesn’t match the image tag you use above. I have no idea if that would matter, though.
It’s also curious that it was also the fra region… have you tried it with sea or yyz?
It also looks like you’re not using the alpine image, which might be responsible for your memory requirements (and possibly the other problems?). I’ve only used the alpine image.