What are Relational Databases?

Good Morning from my Robotics Lab! This is Shadow_8472 and today I am targeting Nextcloud for major milestone completion. Let’s get started.

Previously on my Nextcloud saga, I finally managed to run a pod with both Nextcloud and MariaDB in their own containers and get them to talk. The words they exchanged were along the lines of “Access Denied.” I also have a script to add a temporary third container for database command line access.

My next immediate job is learning enough MySQL to diagnose Nextcloud’s refusal to create an admin account. I found a few commands to try and learned that the database I expected to be there on container creation didn’t appear to be. Container logs didn’t report any errors. I need some more background research, even if that’s the only thing I do this week.

Most important things first: what is the relationship between MariaDB and MySQL? I’ve been treating the former as if it were an implementation or distribution of the later – like the difference between Debian and Linux. But according to MariaDB’s site, they’re a fork that avoids “vendor-lock” while maintaining protocol compatibility with MySQL [1]. So MySQL help should still work for MariaDB on a “close enough” basis, sort of like how Debian solutions may still work for Ubuntu systems. When available: use a matching guide.

Contrary to what I said in my closing words last time I handled Nextcloud, commands being written in all caps is only convention – the software is usually a lot more forgiving. SELECT equals sELeCT which equals select and SelEct as well as the other 60 possible combinations.

MariaDB is what is called a “relational database.” Fancy phrase to me, but here goes an explanation. Data is information. Information can be organized into tables for later retrieval. Zoom out one level, and now tables with information themselves become data that interacts with information in other tables. MariaDB can track these “relations.”

Takeaway

Needless to say, relational databases gets messy fast. Considering how my database is meant to be locked up tight within a pod on a server that blocks direct access, a containerized webUI I can expose will do nicely. MariaDB’s website has a lengthy list of graphical clients, but only phpMyAdmin showed up as having this feature when I used “find on page” [2]. Importantly, it also shows up in Docker.io as a Docker Official Image, which I can run on Podman.

Final Question

As of posting, I’m planning on spending a week or few each for learning phpMyAdmin and refining my MariaDB understanding. Otherwise, if someone can answer what my problem is that Nextcloud isn’t finding the expected database. The following is a command from my pod creation script.

podman create \
        --pod NextcloudRedLaptop \
        --name MariaDBContainer \
        -v MariaDBVolume:/var/lib/mysql:Z \
        -e MARIADB_ROOT_PASSWORD="<gibberish1>" \
        -e MARIADB_DATABASE=NextcloudDB \
        -e MARIADB_USER=nextcloudDbUser \
        -e MARIADB_PASSWORD="Gibberish2" \
        --restart on-failure \
        docker.io/library/mariadb:latest

And here is what I think is the important output from MariaDB:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.010 sec)

If I understand what I need correctly, I should have had one show up titled “NextcloudDB” on container creation.

And so, my final question is this: Why doesn’t it work? Am I even looking in the right spot?

If I oversimplified or got something wrong about relational databases, be sure to tell me all about it in the comments!

Works Cited

[1] MariaDB, “MariaDB vs. MySQL The difference between choice and vendor lock-in,” MariaDB, [Online]. Available: https://mariadb.com/database-topics/mariadb-vs-mysql/. [Accessed Sept. 18, 2023].

[2] MariaDB, “Graphical and Enhanced Clients,” MariaDB, [Online]. Available: https://mariadb.com/kb/en/graphical-and-enhanced-clients/. [Accessed Sept. 18, 2023].

I Survived Self-Hosting a Wiki With Podman!

Good Morning from my Robotics Lab! This is Shadow_8472, and today I am setting up not one, but two personal wikis on my home network. Let’s get started!

A Personal Wiki

Wikis are the reference material of choice for the casual researcher in this day and age. The content of the subject encyclopedia is turbocharged by the power of the hyperlink when compared to a volume/page reference that can take minutes to weeks to “load” depending on circumstantial accessibility. Community contributions allow for information to be updated in a timely manner, while built-in version control helps admins quickly repair sabotage.

This technology can easily be deployed to a closed-off environment for personal, group, or enterprise use. I know I could use one to organize my role play games, and my sister is after one to help with her writing. My goal for this month’s large project is to get both these wikis operational within our home network.

Wiki Planning

The first list of open source wiki software listed Wiki.js as supporting deployment on Docker. If my Rocky Linux 8 experience with ButtonMash has taught me anything, it’s that OCI containers are good for easy cleanup of botched installations, though challenges can arise when using Podman instead of Docker.

I spent a day studying Wiki.js off and on. My basic understanding is that you need three things for a wiki: the web server, the database, and the wiki software itself. I already understand a bit about the relationship between website and web server. Database vs. website is a similar relationship to website vs. browser. It is an independent process that serves data a website garnishes before presenting to a browser. It’s even possible for multiple websites to share a common database. While Wiki.js currently supports a few SQL (Structured Query Language) databases, PostgreSQL is the only database they will support in future versions.

Sparing a thought for my photo trunk project, I believe a wiki has potential for distribution once we learn more about how to use one. ButtonMash is configured to use the scanner though on its Debian install and not the Rocky 8 one, so I’ll need a different machine to host. GoldenOakLibry comes to mind as its primary function is to host and serve files.

My First Operable Prototype Wiki

GoldenOakLibry is a Network Attached Storage (NAS) by Synology running a custom version of Linux called Disk Station Manager. I found MediaWiki, the wiki software powering Wikipedia.org, in its package manager and chose the easy route. I did not know what I was doing for the most part – just that I was glad I re-enabled the old Vaultwarden container to store new passwords I made as I passed through Password Purgatory: database, wiki root, wiki admin (a user), database user – and then I had to make names for them all before I understood what each one did or how many more were needed.

There was a slight snag when the wiki wanted the database password and I wanted a new one, but someone blissfully using the same password for everything wouldn’t have noticed. A less tech savvy individual wouldn’t have thought to try looking for where to copy the wiki’s configuration file via command line. Once I figured that out though, I landed on a fresh wiki.

The snag that caught me was the mission-critical “What You See is What You Get” editor. Whenever I tried saving changes I made with it, it returned “[<RANDOM HEX NUMBER>] Caught exception of type Error.” A help topic on MediaWiki.org [1] reported fixing his wiki by installing a package called php7-zlib. This package is not in the Synology-approved repository, and I found no other package managers I’m familiar with when I connected over SSH. That’s… understandable, I suppose. The product is aimed at homes and small business too small for dedicated IT, after all.

An Alternative MediaWiki Host

A couple weeks ago, I had the misfortune of breaking one of the hooks securing the bezel around my laptop’s screen. Without it, I have to be extremely careful opening and closing the lid. I’m in the market for a new laptop, but in the meantime, the machine’s mind as it were is intact and I can’t use it for computing just anywhere anymore.

I learned a lot on my first successful prototype wiki. Database-website distinction and multi-site databases come to mind as relevant to my use-case. I’m imagining a system where I run each website in an OCI container with Podman on my laptop, then they go to a database on GoldenOakLibry for content.

…Podman isn’t in the Debian 10 repositories. There is a way to install it that involves a lot of hububaloo, but https://pkgs.org/ says it is on Debian 11, and I’ve had the computer upgrade bug as of late. My recent experience upgrading Mint primed me to locate a tutorial and upgrade to Debian 10. The process was the same (Timeshift, shift repositories, upgrade), just a bit less automated [2]. I took the opportunity to clean up after a failed project or two that involved repositories, but I think I ran into issues with Lutris’s repository GPG key (it updated later so I’m not sure). I’m leaving it for now.

The packages podman, cockpit, and cockpit-podman went on easily. Getting a static IP for the laptop was another story. Its official position within the house is under the TV, out of range of any free Ethernet cables we have laying about. After a few hours trying to understand how its Wi-Fi is even connected, I chose to move it next to ButtonMash and configure a static IP that way.

I started and enabled Cockpit with systemctl. It complained without a proper config file, but a browser on another computer made it to laptop’s Cockpit login screen. I told ButtonMash to link Cockpits, and it gave me a command I’ve been looking for for years.

ssh-keyscan -t ecdsa-sha2-nistp256 localhost | ssh-keygen -lf -

Admittedly, this only hints at a formula, but I saved it to a special directory on GoldenOakLibry anyway.

My Second Operable Prototype Wiki

With a more malleable host than GoldenOakLibry that wasn’t ButtonMash, I scrapped what I could of my first setup and started over. MediaWiki lists four packages as dependencies, and I removed three of them that related directly to serving web pages. MariaDB 10 stayed because I know for sure that it is compatible.

Unlike my experience with Rocky Linux 8, Podman on Debian 11 did not come with any unqualified registries configured, so I was getting fast searches with no results when pulling an image in Cockpit. I took a break for Sabbath, even though I felt I could keep the progress coming. When I got back, I about immediately found a tutorial that recommended a couple Red Hat container registries to add in addition to docker.io [3]. I spotted registry.centos.org in ButtonMash’s registries.conf; with the warnings in the file headers about who you trust, I removed it over the slim chance it gets compromised in the future. Worst case scenerio: I have to re-add it later.

Acquiring docker.io’s official image was easy next to telling MariaDB to let it in. I spent around seven hours inching through assorted tutorials tangential to setting up MediaWiki in a Podman container with [important keywords here:] remote access to a MariaDB database on a Synology device. It was slow, I could have written a post about just this paragraph, but I learned enough to understand the provided instructions (key tutorial: [4]). I braved Vim to write a needed config file and learned about MySQL database CLI client to make a pseudo-root account. And of course this was after locking things down to the static IP addresses I set up earlier.

Once MediaWiki was happy with its access to MariaDB, setup was similar to my first time, though I paid a little closer attention this time around and included all the editors, the mistake that send me on this side quest in the first place. The containerized setup will still come in handy, so it was not all for nothing. As a final, problematic sendoff, MediaWiki’s setup file, LocalSettings.php, remembers the port number it was installed to: future wiki installation attempts will happen in the containers they’re meant to run in, not some baseline I’ll be keeping around.

It was cause for celebration when I made the first edit and it stuck.

Project Notes

Given the right circumstances, I would have to say it’s possible for about anyone to bumble his or her way into a working self-hosted wiki on a Synology NAS, as I sort of did. Don’t get me wrong: even this is not an impatient beginner’s project! This week I learned that databases stand alongside websites, not inside them – a very important distinction for a sysadmin to know.

I’ve seen the Cockpit functionality to switch hosts since first installing Rocky 8 on ButtonMash. It was a pleasant surprise to find it worked over SSH and had a ready command for generating SSH host key fingerprints. DSM sadly does not have that functionality.

My opinion of Synology’s DSM began strong after a slow start, but it’s been fading. Stray one command outside their intended use case and it has DON’T TOUCH THAT! signs waiting everywhere. It’s still production grade, and that I can respect. I just won’t be asking for a similar system in the future.

The database password was extremely difficult to get right. No errors were ever thrown when entering 100+ character jibberish from Bitwarden, but 79 appears to be the maximum MySQL can swallow.

Takeaway

My progress this project does not represent a production-ready environment. I fully expect to have to tweak things before I have each wiki sequestered to its own user while still running happily. Website administration will be a whole other matter to conquer, but that is an exercise for another week.

Final Question

What kind of information might you organize with a wiki?

Works Cited

[1] Winel10, “Caught exception of type Error when saving changes in VisualEditor,”MediaWiki.org, Feb. 4, 2019 and June 8, 2022. [Online]. Available: https://www.mediawiki.org/wiki/Topic:Uuk96xjvh0ukaci2. [Accessed June 27, 2022].

[2] AM, “How to upgrade to Debian 11 from Debian 10,” AtechTown.com, 2022. [Online]. Available: https://www.atechtown.com/upgrade-debian-10-to-debian-11/. [Accessed June 27, 2022].

[3] J. Arthur, “How to Install Podman on Debian 11,” LinOxide.com, Sept. 20, 2021. [Online]. Available: https://linoxide.com/install-podman-on-debian/. [Accessed June 27, 2022].

[4] TechNotes “How to run Mariadb in Docker and Connect Remotely,”YouTube.com, Dec. 15, 2020. [Online]. Available: https://youtu.be/OabTOPOU2RU. [Accessed June 27, 2022].