Web servers and SQLITE Editors Rethink

ApacheFor some considerable time now I’ve been using Apache (web server) on my various SBCs to present a basic web page, run PHPLITEADMIN (local  SLQLite Database Manager and do other menial jobs. A LONG time ago I got rid of MySQL (which I had used on and off along with MS SQL on larger machines for a long time)  despite it’s undoubted qualities as MySQL tends to be not ideal for the likes of little Raspberry Pis with their limited resources and limited life SDs. Well, that was a start but now it is possibly time to move on from Apache to something a little lighter on it’s feet as well.

NGINXAntonio is busy having another so at setting up Caddy as an alternative web server, but he’s having a busy week and I could not help but give Nginx a go – I WILL play  with both that and Caddy in the very bear future and the winner will no doubt get into “the script”. Any web server for me has to run PHP, preferably v7 as I like to be up to date – warning - I’ve only tested this  on a Raspberry Pi 3 (2 will work) but as there was nothing specifically Raspbian-ish in the setup I’ve no doubt this would work elsewhere.

Nginx is a web server which can also be used as a reverse proxy, load balancer, mail proxy and HTTP cache. The software was created by Igor Sysoev and first publicly released in 2004. But for my needs, it is simply an easy to install and use, relatively lightweight web server.

After reading a small amount about Nginx, which apparently has a not insignificant share of the server market, I stumbled on this: https://howtoraspberrypi.com/install-nginx-raspbian-and-accelerate-your-raspberry-web-server/

Only a couple of things… following disabling Apache as recommended, I removed Apache with “sudo apt-get remove apache2” without the quotes of course and then “sudo apt-get autoremove”. That left some files in /etc/apache2 so I used “sudo rmdir –r /etc/apache2” but if you find all of that too permanent you could just leave Apache2 disabled – unless of course you don’t have it installed.

I followed the instructions above to the letter, stopping at the “Error 403” paragraph heading. Everything worked including my original PHPLITEADMIN and a simple html web page I had sitting there. The latter worked as you’d expect, the former only if I specified the index.php page by name. Well, that was no good so as ROOT I went into   /etc/nginx/sites-available – therein is only a text file called default – which has a line showing acceptable default page names including index.html and index.htm – I added index.php to that.

All was perfect but I’m used to PI user (in WinSCP etc) being able to see the files in the /var/www/html folder – so I made a minor change to make permissions for /var/www the same recursively – i.e. 755 – you may or may not wish to copy that if you have security concerns.

Now I was cooking – all my favourite PHP stuff was working just as before along with my default.html home page but without the weight of Apache.

Installation and initial (trivial) testing went so smoothly that I thought I’d also see if I could push my luck and find a PHP alternative to PHPLITEADMIN. After doing the rounds of what is available, I stumbled on MyWebSQL – and it is lovely. It handles MySQL, SQLITE and POSTgreSQL and comes with wonderful themes. Installation could be done by a blind drunk monkey.

This page was helpful.

I took the mywebsql folder out of the zip file here and put it into /var/www/html – _ then edited the config/servers.php file therein to add a username and password and point the editor to my sqlite db directory (not to any particular file).

That’s IT – it ALL WORKS.

MyWebSQL

and if you don’t like the colours – see INTERFACE - THEME at the top.

MyWebSQL

If only all installations were THIS easy. As some of you know, after a life steeped in electronics and computer software, I’ve been doing a damn good job of  keeping up to speed but late last year, at the ripe young age of 64, out of the blue, I had an ischemic stroke – and not a toy stroke either, but one of those cruel tricks of nature to teach me a lesson for getting good at what I do. I was out of commission for 3 months (in hospital) and although I’ve worked extremely hard to recover (and in the process, deliberately shed 30% of my body weight to help (as far as you can) ensure that was a one-off hit), getting my act back together has been (and continues to be) hard work, only in the last month have really got it together sufficiently to consider getting ambitious again with my home control kit. There is so much hard-to-follow (and often old and/or inaccurate) material out there that it came as a sheer delight to find material I could follow immediately without asking people lots of questions and in a matter of hours, not only install a working new web server and new SQLite manager on a Pi (I’m keeping PHPLiteAdmin as well – why not) but also document it in this blog.  I am of course immensely grateful to those who have helped me get this far back into the driving seat.

Lovely.

Facebooktwittergoogle_pluspinterestlinkedin

14 thoughts on “Web servers and SQLITE Editors Rethink

  1. Hi Pete

    Thank you for sharing your experiences with web servers, SQL and life, really appreciated all of it and glad to see you up for it again. May you continued to uncover, inform, guide, educate, delight and entertain us now that you are spared to be back in the driving seat for the next round! Life too short to struggle to long with stuff that does not meet expectations, so based on your experience will give this a go on my fresh 3B+ running Stretch with The Script, ready to explore.

    Blessings

    1. Update: I did give these a go by installing Nginx prior to running the script, removing only Apache install from the script and using it to do the rest of the heavy lifting. After fixing the 403 error everything seem to works just fine so far. MyWebSQL install was a breeze and indeed lovely. Adding these two options to The Script would be on my Christmas wish list.

  2. Peter maybe you can help me here (or someone else,) but I have run into the error "SQLite3 client library is not installed" when trying to log into MyWebSQL. After receiving the error, I did the usual 'sudo apt-get install sqlite3 libsqlite3-dev' and even rebooted my PC with no luck. Granted I also updated the config/servers.php file, and changed to point to a sqlite database folder and changed the user and password in the config file.

    A note here that I am running Ubuntu 18.04 server on a dedicated x64 PC and not a Raspberry Pi, but would think it all works out the same with the exception of no GPIO pins. After all, I successfully ran 'The Script' as well (on a prior installation.) However I have since reimaged this PC and have installed everything manually.

    Regards to, on your continued improvements with your health. I always look forward to your posts and eagerly try out new things (and sadly dwindle my wallet too.) 🙂

    1. this should solve:
      sudo apt-get install php7-sqlite3

      or search for the correct package:
      sudo apt-cache search sqlite3|grep php

      the script is NOT supported on ubuntu 18.04, and if my work on docker will soon work completely, it will NEVER be supported, as docker allows to be independent from the underlying OS version, given it just has a kernel that supports it...

        1. a linux box should be rebooted only on kernel update 🙂
          just restart the needed service, like:
          sudo systemctl restart nginx

          p.s.: if you don't know the service name, press TAB twice just after:
          sudo systemctl restart

    2. Hi Jerry, I got that same message about SQLite not installed but then a little drop down menu box appeared on the mywebsql log on screen that let me select the SQLite3 database. From then on it worked. I think you can alter the server config file so that only SQLite3 can be selected.

      I also noticed on Peter's screen shots that he is logged on as 'root' not 'pi'.

      1. Thanks for the info. However, the information provided by DrFragle and MrShark, on installing the PHP module worked for me.

        sudo apt-get install php7-sqlite3
        although I just used the general install to pull in the latest repository
        sudo apt-get install php-sqlite3

  3. Hi Peter, i hope you can help!
    Regarding the MyWebSQL, " page was helpful" does indeed take one to the web page and download for that product but the "Link" appears to take one to Db browser for SQLite, so not the correct software I assume.
    From the MyWebSQL site I downloaded the zip and extracted it and tried to install it in the var/www/html folder on my ubuntu 16.04 set up on a dual boot PC. I had previously installed your script which worked fine apart from issues with Node-RED which were resolved following post from you and Antonio.
    However when I tried to move the MyWebSQL folder from my windows download directory to my ubuntu set up using winSCP it failed saying access denied. I then tried mobaXterm, that crashed into an endless loop and could only be shutdown with task manager, so there is a bug in that software.
    My var/www/html file is in the root directory and that is why winSCP failed so I wondered, do you have winSCP or mobaXterm set to allow access to the root directory, if so could you tell me how that is done please.
    to the best of my knowledge everything was set up under ubuntu as per the 'script'

    1. copy via winscp into your home directory, then move via putty from there into the html folder, or try:

      sudo adduser pi www-data && sudo chmod g+rwx /var/www/html

      and retry via winscp, basic file and folder management in linux, tons of guides online...

      1. Antonio, thanks for the help and advice. Using winSCP I copied the extracted file to /home/pi and then used the sudo mv command to move it to /var/www/html.
        I had previously used the two sudo commands to change the access permissions.
        I am not running 'headless' at the moment so I made the changes direct using ubuntu terminal.
        I learn a little more about linux each day.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.