headless raspberry pi - changing VNC desktop resolution

In order to set up a headless raspberry pi with VNC, we can first follow this - https://raspberrytips.com/raspberry-pi-headless-setup/

I used the Raspberry Pi Imager method - under Advanced Options in Pi Imager.

Then, as suggested in this video for an older version, I could connect to the VNC desktop on the Pi and change the resolution using

Start -> Preferences -> Screen configuration

Then Layout Menu -> Screens -> NOOP1 -> Resolution -> 1920x1080

Raspberry Pi 4 - setting static IP and checking CPU temp

How to Set a Static IP Address on Raspberry Pi
https://www.tomshardware.com/how-to/static-ip-raspberry-pi
(did it via vnc, had to install realvnc client as tightvnc client did not support)

How to check CPU temperature on Raspberry Pi - Linux Tutorials - Learn Linux Configuration
https://linuxconfig.org/how-to-check-cpu-temperature-on-raspberry-pi

ffmpeg 25 fps to 24 fps

Due to this issue with OCVWarp and frame sequences, needed to change the fps for a video from 25 fps to 24 fps.

We can do it with re-encoding using Avisynth, under 

Video -> Filters -> Transform -> Change FPS

 - useful if we want to change the codec -

or without re-encoding with ffmpeg with a command similar to the one in my earlier post,

ffmpeg -itsscale 0.834167501 -i input-25fps.mp4 -vcodec copy output-24fps.mp4

mp3 encoding for dot net application

We got a request to install NAudio and NAudio.Lame on the production server of one of our dot net apps. But the prod server runs Linux, and NAudio uses DirectSound, hence is Windows-only. The developers were informed, and they decided to use ffmpeg instead. We used /etc/environment to add the env variable FFMPEG_PATH they wanted to create, using whereis to find the location of the executable.

Notes:

https://www.nuget.org/packages/NAudio/
dotnet add package NAudio --version 2.2.1
run inside project folder, or else error
Could not find any project in `/home/ubuntu/`.

as the relevant user in the directory also, same error.

https://stackoverflow.com/questions/56062228/error-dotnet-could-not-find-any-project-in-c-when-running-dotnet-add

But dotnet add package--help indicates that this would just add a reference to the nuget package to the project.

So, anyway nuget would need to be installed.

sudo apt install nuget
and then ...
Oh!
https://github.com/naudio/NAudio/issues/1077
NAudio does not have linux support due to directsound dependency.

rclone and RCloneBrowser for uploading 30 GB files to Google Drive

As noted earlier, rclone is the preferred solution to transfer large files to and from Google Drive.

A point to note is that mounting and then copying seems to result in errors for Google Drive if transfer times are greater than an hour or two, so we should preferably use just rclone copy or copyto.

In the case of a Windows machine, here were my steps:

choco install rclone
rclone config
(to create the gdrive remote)
(choose everything as the defaults.)
rclone lsd gdrivemydrivename:
(to list directories)
rclone copy myfilename.mp4 gdrivemydrivename:mydirname --progress

Unfortunately, RCloneBrowser doesn't show bandwidth or time to completion with the latest rclone. So, the commandline, or browser UI might be better.

chocolatey install

Many open-source projects can be easily installed on Windows using chocolatey - and it's scriptable, too. Installing chocolatey itself took a few steps.

I should have done with this,

https://docs.chocolatey.org/en-us/choco/setup/#installing-chocolatey-cli

but I ended up doing the powershell install,

https://docs.chocolatey.org/en-us/choco/setup/#install-with-powershellexe

So, running powershell as administrator, checking powershell version with

$PSVersionTable

(it was v5, so chocolatey is supported) and then  

Get-ExecutionPolicy

returned Restricted, so then ran 

Set-ExecutionPolicy AllSigned

and then the install script with

Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1'))

Opening a cmd window (as administrator) after this, and 

choco install ffmpeg 

worked fine.

VNC scraping server

Looking to reinstall some sort of VNC on an old Lenovo Thinkpad (where the built-in keyboard has issues with some crucial keys like <space> and <enter>) - found this useful thread about installing Tiger VNC scraping server, for making screen 0 available - so that works well with cinnamon desktop, unlike other VNC solutions - 

https://forums.linuxmint.com/viewtopic.php?t=351208

 sudo apt-get install tigervnc-scraping-server

We need to use the -localhost no option also, if not tunnelling over SSH, as mentioned in the last part of that thread. So, the way I've implemented is to create a startvnc.sh with the following command,

x0vncserver -PasswordFile=/home/user/.vnc/passwd -localhost no

where the pw file was created by running

vncpasswd

Then, in Linux Mint, there is the "Startup Applications" Menu item, where we can add this script startvnc.sh with an appropriate delay if required - I've put in a 5 second delay.

booting from secondary SSD - /dev/sdb

At first, installed on the secondary drive (SSD), and Linux Mint automatically wrote grub to the SSD. But when booted, grub was loaded from the primary drive. First, I tried update-grub which worked - the new install was also seen. But when I reformatted the primary hard disk, a grub rescue> prompt was seen. In my case, I did not really need to do the grub rescue - instead, I needed to go into the bios by pressing enter on a USB keyboard during boot (since the built-in keyboard enter key was broken), then Startup --> Boot --> make the SSD the primary boot device, then save and reboot.

hugo and caddy

via https://rclone.org/drive/

https://caddyserver.com/features - Interesting reverse-proxy server.

https://gohugo.io/getting-started/quick-start/ - Interesting static site generator using markup.

deployment-target in cordova moodle app config.xml file

Initially, I thought the 

<preference name="deployment-target" value="13.0" />

in the moodle app's config.xml file referred to Android version, so it should be updated to 14.0 for the SDK target 34 upgrade.

But as pointed out by @dpalou, this refers to iOS version, nothing to do with Android. 

Checking for 5G coverage

Following this video, could find that there is 5G coverage for Airtel at my location, using the Ookla Speedtest app, by going to the Maps button at the bottom of the home screen.

node version change in building customized Moodle app

Node version change, as we can see from this line in package.json for the moodle app,

so, changed this line in our build workflow.

Moodle Mysql database CPU usage at 200%

Originally, the issue raised was that an ad-hoc query was taking a long time to run. But checking the server, found that mysqld was using 200% CPU.

Checking why the database usage is so high, I tried to check which queries are taking more time on the db - 

mysql - mysqld performance high cpu and memory usage - Stack Overflow

sql - How do I enable the MySQL slow query log? - Stack Overflow

Found that some ad hoc tasks are the culprit.

# Time: 2024-07-08T08:07:05.163875Z

# User@Host: db_admin[db_admin] @  [::1]  Id:  8699

# Query_time: 7.503725  Lock_time: 0.000050 Rows_sent: 3  Rows_examined: 384225

SET timestamp=1720426017;

SELECT classname FROM task_adhoc WHERE nextruntime < '1720426017' GROUP BY classname;

# Time: 2024-07-08T08:07:05.193117Z

# User@Host: db_admin[db_admin] @  [::1]  Id:  8551

# Query_time: 5.819065  Lock_time: 0.000049 Rows_sent: 3  Rows_examined: 384225

SET timestamp=1720426019;

SELECT classname FROM task_adhoc WHERE nextruntime < '1720426019' GROUP BY classname;

 

Checking under tasks,

https://ourserver.org/admin/tool/task/runningtasks.php

I see that an ad-hoc task of course_delete_modules has been running for 5+ hours

This is what is causing the db to become unresponsive.

From this page,

https://ourserver.org/admin/category.php?category=taskconfig

I see that the task is not supposed to take so long. Max lifetime is supposed to be 30 minutes.

 

Rebooting didn't help.

I had to go to the database and delete the relevant row from the adhoc_tasks table in the database.

So that task is now gone from the "Tasks running now" screen. 

But other tasks are running, which is normal. What is not normal is the extremely high CPU usage of the mysql database on the database server.  

The CPU 200% issues seems to be related to adhoc tasks being run by mod_forum

The task_adhoc table for this instance is filled with entries created by

\mod_forum\task\send_user_notifications

tasks - the other moodle instances don't have these.

 

From

https://moodle.org/mod/forum/discuss.php?d=440932

checked

mdl_forum_queue table.

Found 45 rows, starting July 2, just like the adhoc

timemodified 1719909620

Could not find the relevant forum posts, so blindly deleted. 

Currently the cpu usage of mysql database has come down to < 1% - for which the following were done:

• deleted the mod_forum notification ad hoc tasks from task_adhoc table - around 385,000 rows! dating from July 2
• deleted the vv_forum_queue notification queue - around 45 rows, dating from July 2.

I'm leaving this for now, since I'm not sure what exactly we can do to prevent this sort of behaviour in future.

 

 

optimizing SQL code with help from ChatGPT

I had asked PB for help with optimizing the SQL code at
https://github.com/hn-88/ad-hoc-moodle-database-queries/blob/main/Feedback%20for%20all%20courses.sql

since it would time out for anything more than just a few lines of results, taking more than half an hour and slowing down the database. 

His response was to ask ChatGPT.

ChatGPT gives the following suggestion:

1. Reduce Subqueries: Instead of running multiple subqueries for each feedback item, we join the feedback_value and feedback_item tables multiple times to get the necessary values.
2. Conditional Aggregation: We use MAX(CASE WHEN ...) to fetch the appropriate values for each feedback item type.
3. Grouping: We group by the relevant fields to ensure distinct results.

This approach reduces the number of scans and joins, potentially speeding up the query execution.

SQL STATEMENT

___________________________________________________

SELECT
    FROM_UNIXTIME(fc.timemodified) AS Time,
    fc.userid,
    u.idnumber,
    u.username,
    u.firstname,
    u.lastname,
    u.email,
    u.institution,
    fv.value AS "Email entered",
    c.fullname AS BSS,
    MAX(CASE WHEN fi2.name LIKE '%Chapter%' AND fi2.typ = 'textfield' THEN fv2.value ELSE NULL END) AS "Chapter name",
    MAX(CASE WHEN fi3.name LIKE '%Main%Script%' THEN fv3.value ELSE NULL END) AS "MainScript",
    MAX(CASE WHEN fi4.name LIKE '%Inquisit%uestion%' THEN fv4.value ELSE NULL END) AS "IQ",
    MAX(CASE WHEN fi5.name LIKE '%Suggested%ctivity%' THEN fv5.value ELSE NULL END) AS "SA",
    MAX(CASE WHEN fi6.name LIKE '%Assessment%' THEN fv6.value ELSE NULL END) AS "QA",
    MAX(CASE WHEN fi7.name LIKE '%Day%day%elevan%' THEN fv7.value ELSE NULL END) AS "DD",
    MAX(CASE WHEN fi8.name LIKE '%Value%ontent%gained.' THEN fv8.value ELSE NULL END) AS "VC",
    MAX(CASE WHEN fi9.name LIKE '%Interest%side%' THEN fv9.value ELSE NULL END) AS "IA"
FROM
    {feedback_value} fv
    LEFT JOIN {feedback_completed} fc ON fc.id = fv.completed
    LEFT JOIN {user} u ON fc.userid = u.id
    LEFT JOIN {feedback_item} fi ON fi.id = fv.item
    LEFT JOIN {feedback} f ON f.id = fi.feedback
    LEFT JOIN {course} c ON c.id = f.course
    LEFT JOIN {feedback_value} fv2 ON fc.id = fv2.completed
    LEFT JOIN {feedback_item} fi2 ON fi2.id = fv2.item
    LEFT JOIN {feedback_value} fv3 ON fc.id = fv3.completed
    LEFT JOIN {feedback_item} fi3 ON fi3.id = fv3.item
    LEFT JOIN {feedback_value} fv4 ON fc.id = fv4.completed
    LEFT JOIN {feedback_item} fi4 ON fi4.id = fv4.item
    LEFT JOIN {feedback_value} fv5 ON fc.id = fv5.completed
    LEFT JOIN {feedback_item} fi5 ON fi5.id = fv5.item
    LEFT JOIN {feedback_value} fv6 ON fc.id = fv6.completed
    LEFT JOIN {feedback_item} fi6 ON fi6.id = fv6.item
    LEFT JOIN {feedback_value} fv7 ON fc.id = fv7.completed
    LEFT JOIN {feedback_item} fi7 ON fi7.id = fv7.item
    LEFT JOIN {feedback_value} fv8 ON fc.id = fv8.completed
    LEFT JOIN {feedback_item} fi8 ON fi8.id = fv8.item
    LEFT JOIN {feedback_value} fv9 ON fc.id = fv9.completed
    LEFT JOIN {feedback_item} fi9 ON fi9.id = fv9.item
WHERE
    fc.timemodified > :fromdate
    AND fc.timemodified < :todate
    AND u.idnumber LIKE :paramidnumber
    AND fi.name LIKE '%Email%'
GROUP BY
    FROM_UNIXTIME(fc.timemodified),
    fc.userid,
    u.idnumber,
    u.username,
    u.firstname,
    u.lastname,
    u.email,
    u.institution,
    fv.value,
    c.fullname

___________________________________________________

Please check if this suggestion helps.

My response to this was, after trying out the code suggested by ChatGPT and then modifying it:

The code shown [above] did not work - my earlier code was producing 5 rows in around 5-10 minutes, the chatgpt code ran for 30 minutes without any result. Looking at the code, there are some possible mistakes like the lines like 
LEFT JOIN {feedback_value} fv3 ON fc.id = fv3.completed

Corrected the code as
c.fullname AS BSS,
    MAX(CASE WHEN fi2.name LIKE '%Chapter%' AND fi2.typ = 'textfield' THEN fv2.value ELSE NULL END) AS "Chapter name",
    MAX(CASE WHEN fi2.name LIKE '%Main%Script%' THEN fv2.value ELSE NULL END) AS "MainScript",
    MAX(CASE WHEN fi2.name LIKE '%Inquisit%uestion%' THEN fv2.value ELSE NULL END) AS "IQ",
    MAX(CASE WHEN fi2.name LIKE '%Suggested%ctivity%' THEN fv2.value ELSE NULL END) AS "SA", etc etc

instead of fi3, fi4, fi5, fi6 etc, and now the code runs much faster. 6 rows in 6 seconds.

https://github.com/hn-88/ad-hoc-moodle-database-queries/blob/main/Feedback%20for%20all%20courses%20revised.sql

Thanks once again. :)

(Some references I looked up, but could not find immediate solutions to my problem:

How to fix MySQL high CPU usage (bobcares.com)

mysql - mysqld performance high cpu and memory usage - Stack Overflow

sql - How do I enable the MySQL slow query log? - Stack Overflow

)

link from div to open in new window

We wanted a "click here" image link for one of our websites, but the image was actually a background in CSS and not a simple img html tag. So, basically we needed to make the entire div into a clickable link. Following these,

https://stackoverflow.com/questions/7185044/change-the-mouse-cursor-on-mouse-over-to-anchor-like-style

https://stackoverflow.com/questions/5141910/javascript-location-href-to-open-in-new-window-tab

https://stackoverflow.com/questions/8524470/click-anywhere-on-div-instead-of-directly-on-link

what we used was something like this:

<div class="our-class-name" style="cursor: pointer" onclick="window.open('ourpage.html', '_blank');">

Microsoft Teams for new users

One of our institutions wanted to add a large number of new users to Microsoft Teams. Initial plan was to create email ids in Google Workspace and then add them as Guest users in Microsoft Admin console. But then, it would be much easier for the users, and also retain all the functionality of sharing meeting id via email etc if the users were created within Microsoft Admin console instead. So, finally, that was the way it was done. 

https://learn.microsoft.com/en-us/microsoft-365/enterprise/add-several-users-at-the-same-time?view=o365-worldwide

https://learn.microsoft.com/en-us/purview/bulk-import-external-contacts

https://learn.microsoft.com/en-us/exchange/mail-flow-best-practices/use-directory-based-edge-blocking

 

https://techcommunity.microsoft.com/t5/microsoft-teams/allow-ms-teams-via-conditional-access-but-block-other-o365/m-p/2622619