snork.ca: Nginx + Plogger + MariaDB

Nginx + Plogger + MariaDB 2016-07-10

I have been using PostImg for a while now to host images for me. Their site is pretty simple, responsive and images do not seem to be subject to any expiry or size restrictions. However, they recently made some changes to their web site, including more intrusive ads and more of them. So I dumped them.

My dog eating a tree.

I had used Plogger in the past, but had dumped it mostly because it can take up a fair bit of space, and gets a lot of spammer hits (there isn't really native support for captcha or anything similar). I checked out a few free web hosting sites and image hosting sites but nothing really seemed to be quite what I was looking for. Eventually I ended up downloading Plogger again and it seems to be working pretty much as I remember it.

One problem though, was that I had some issues making it work this time, mostly because my web server has changed over the years and the author of Plogger has not updated it in a long time. There are a few forks of Plogger on github, but none that really seem like a currently maintained application. So I decided to stick with plain old Plogger rather than a new fork, and here's how I got it going on my server. Hopefully this will prove useful to someone else in the same situation.

The first hitch was that I had changed from Apache to Nginx... which means I needed to make sure that php files are handled by adding something like this to my Nginx configs.

nginx.conf location ~ \.php$ {
   try_files $uri =404;
      fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
      include fastcgi_params;
      fastcgi_pass unix:/var/run/php5-fpm.sock;
}

Now to setup Plogger, the download wasn't a problem, but the installer was. I unzipped the archive and put it on my server as described in the documentation, but when I ran the install.php I had a couple of problems which were mostly related to using MariaDB. Now according to the MariaDB crapipedia page:

It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.
[...]
MariaDB intends to maintain high compatibility with MySQL, ensuring a "drop-in" replacement capability with library binary equivalency and exact matching with MySQL APIs and commands.

As it turns out, I am not so sure that the commands are exactly the same. I had trouble using the same old MySQL commands I had previously used to create empty databases and users, while assigning permissions to them at the same time. When installing plogger I kept getting error messages telling me that it could not connect to the database (or just a blank page sometimes), so I found this article which had a working syntax like this:

CREATE USER plogger@localhost IDENTIFIED BY "your_password_here";
create database plogger;
GRANT ALL ON plogger.* TO plogger@localhost;
FLUSH PRIVILEGES;

Once I had the database and user setup properly, I was still having trouble finishing off the install. The next step would provide me with a download link for a plog-config.php and told me to save the file in my Plogger install directory. I did this and the the following error message barfed up:

string(186) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Type=MyISAM DEFAULT CHARACTER SET UTF8' at line 6" string(186) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Type=MyISAM DEFAULT CHARACTER SET UTF8' at line 8" string(65) "Too big precision 14 specified for 'date_modified'. Maximum is 6." string(187) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Type=MyISAM DEFAULT CHARACTER SET UTF8' at line 12" string(187) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Type=MyISAM DEFAULT CHARACTER SET UTF8' at line 29" string(186) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Type=MyISAM DEFAULT CHARACTER SET UTF8' at line 6"

Looks awful doesn't it? Don't worry, the solution isn't that bad. I found this post at the Plogger forums from almost 5 years ago which has the answer. The first step is to open your plog-admin\includes\install-functions.php file in a text editor and replace all eight instances of

Type=MyISAM

with

Engine=MyISAM

and then there is a problem with the date lines (rows 376 and 377) which say

`date_modified` timestamp(14) NOT NULL,
`date_submitted` timestamp(14) NOT NULL,

They need to be changed to

`date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`date_submitted` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

Then just save the file and continue on with the installation as normal. If you trust that I have not made any malicious modifications you can download and use my modified Plogger install archive which has the modifications described above already done. My zip file has no changes other than what is described here, and can be checked against my PGP key using this ascii signature here.