Looks great, thanks!
But I don't quite understand what the problem with mysql.initialDatabases is. Can you please explain?

@JohnAZoidberg as per https://nixos.org/nixos/options.html#services.mysql.initialdatabases:

List of database names and their initial schemas that should be used to create databases on the first startup of MySQL.

Because you want to create it again if it goes missing after a while?
That makes sense indeed and probably is more useful in many cases than initialDatabases.

@JohnAZoidberg I think you're missing the part I emphasized. I emphasized first. As in only the first time mysql starts up. Not the second. Or third. Just the first.

For example, if mysql is already installed and running on your system, then you install zoneminder the database won't be provisioned as desired.

The initialDatabases option is moreso for end users I would imagine, and shouldn't really be used in nixos itself that often (if ever). The same is true for the initialScript option.

Ahh, I do understand what you mean now!

I'd also apply this:

--- i/nixos/modules/services/misc/zoneminder.nix
+++ w/nixos/modules/services/misc/zoneminder.nix
@@ -147,6 +147,7 @@ in {
           default = "zmuser";
           description = ''
             Username for accessing the database.
+            Not used if <literal>createLocally</literal> is set.
           '';
         };
 
@@ -155,6 +156,7 @@ in {
           default = "zmpass";
           description = ''
             Username for accessing the database.
+            Not used if <literal>createLocally</literal> is set.
           '';
         };
       };
@@ -208,7 +210,7 @@ in {
         package = lib.mkDefault pkgs.mariadb;
         ensureDatabases = [ cfg.database.name ];
         ensureUsers = [{
-          name = cfg.database.username;
+          name = user;
           ensurePermissions = { "${cfg.database.name}.*" = "ALL PRIVILEGES"; };
         }];
       };

Because when used locally the Linux user is used for socket authentication as seen in this line:

     ZM_DB_USER=${if cfg.database.createLocally then user else cfg.database.username}

Also the nginx config validator issues a warning on location /cache { that there should be a trailing slash.
You, @aanderse seem to have a lot of experience with nginx. Could adding a slash there have negative consequences?

I built and am running these changes and it looks okay. I have never really fully used this service so I'm not sure if everything is working. I just discovered the issue while trying to make it run for a friend.

The service might be broken on unstable but I fixed that in #63151.
To circumvent that you can use nixpkgs.config.allowBroken = true; because the package is marked as broken but all the sources are in the cache ;)

You, @aanderse seem to have a lot of experience with nginx. Could adding a slash there have negative consequences?

@JohnAZoidberg You must be confusing me with someone else. I have never used nginx before.

List of database names and their initial schemas that should be used to create databases on the first startup of MySQL.

Nicely caught. Wouldn't it be awesome if the mysql module actually did something similar to what you did manually? Cough, cough...

By the way, I ripped out ZM some time ago, so I'm not able to test it, but maybe a nixos test for this would be good?

Wouldn't it be awesome if the mysql module actually did something similar to what you did manually? Cough, cough...

That would be great, but possibly not as useful as one might guess. I notice that most applications these days are pretty good at being database aware because they use some form of the migration concept. Often times an application knows whether it has a table structure or not and will create it if not. Exceptions I've worked on recently, where the application is unable to determine whether it should create a database structure or not... zabbix and mediawiki. I can list about ~6 other services I have worked on recently that were database aware enough to create their own table structure.

By the way, I ripped out ZM some time ago, so I'm not able to test it, but maybe a nixos test for this would be good?

Complete hijack to this PR, but what did you replace it with? I have a video camera I'm about to install and looking for opinions 😄
@JohnAZoidberg this puts testing solely on you! Comments added as requested.

what did you replace it with?

MotionEye.

https://gist.github.com/f9000a258afb8b73a6a427475cef9116

Far less moving parts and a way more sane architecture.

@peterhoeg I would say at this point this PR more than resolves #63152 so if you're happy please feel free to merge 😄
A quick test might be in order just to make sure the service still boots, but I'll leave that up to you and @JohnAZoidberg to decide.

A quick test might be in order

You know, if only there was some kind of framework that would allow us to run tests against nixos.... ;-)

Thanks @JohnAZoidberg and @peterhoeg!