Yii2 MySQL remote SSL connection

'db' => [
	'class' => 'yii\db\Connection',
	'dsn' => 'mysql:host=REMOTE.HOST.MYSQL;dbname=remote_db_name',
	'username' => 'remote_db_user',
	'password' => 'remote_db_password',
	'charset' => 'utf8',
	'attributes' => [
		PDO::MYSQL_ATTR_SSL_KEY => dirname(dirname(__DIR__)) . '/common/config/ssl/client-key.pem',
		PDO::MYSQL_ATTR_SSL_CERT => dirname(dirname(__DIR__)) . '/common/config/ssl/client-cert.pem',                
		PDO::MYSQL_ATTR_SSL_CA => dirname(dirname(__DIR__)) . '/common/config/ssl/server-ca.pem',                
		],
],

This is assuming that the certs are within the common folder of an Yii2 advanced template project.

It’s probably also a good idea to chmod the pem files to 600 and the ssl directory to 700 so that only the user

Where are the pem files?

/var/lib/mysql/*.pem

List of LGA Postcodes impacted by COVID19 MELBOURNE lockdown

At the office we needed a list of Postcodes of suburbs affected by the second lockdown of Melbourne, Victoria.

Below is the list. While I believe that this is accurate, assume it’s released under the MIT license. It could have gaps. (Technically it’s licensed under the ‘Creative Commons Attribution 4.0 International (CC BY 4.0)’ license)

UPDATE 16/7/2020 – As pointed out by Michael the list below is missing 3135 & 3136. I’ve included them in the list below.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Data is from the VEC: https://discover.data.vic.gov.au/dataset/victorian-electors-by-locality-postcode-and-electorates
With a filter of LGA listed from:
https://www.dhhs.vic.gov.au/updates/coronavirus-covid-19/statement-premier

3083
3088
3085
3087
3095
3093
3094
3081
3084
3079
3187
3188
3186
3193
3192
3190
3191
3103
3126
3127
3147
3124
3146
3122
3123
3104
3101
3102
3012
3020
3021
3023
3026
3036
3033
3042
3038
3022
3043
3037
3981
3984
3978
3987
3809
3810
3980
3782
3807
3808
3781
3783
3159
3812
3815
3814
3816
3813
3977
3975
3177
3802
3806
3912
3803
3805
3804
3976
3156
3078
3070
3072
3071
3058
3073
3201
3198
3199
3200
3910
3911
3204
3165
3162
3145
3161
3185
3163
3183
3175
3171
3172
3174
3173
3018
3028
3025
3015
3016
3047
3061
3048
3060
3064
3062
3049
3428
3430
3427
3045
3063
3429
3059
3189
3196
3197
3169
3202
3167
3195
3194
3153
3155
3154
3152
3180
3179
3787
3178
3105
3108
3106
3107
3115
3111
3131
3109
3114
3134
3113
3019
3011
3032
3013
3008
3207
3006
3052
3053
3054
3002
3031
3000
3051
3003
3004
3141
3336
3335
3024
3029
3337
3338
3340
3664
3658
3762
3764
3444
3522
3758
3523
3660
3663
3435
3662
3521
3659
3666
3753
3756
3125
3148
3168
3150
3149
3170
3166
3040
3041
3039
3034
3046
3056
3057
3055
3068
3044
3926
3918
3919
3915
3920
3933
3913
3931
3930
3934
3936
3942
3939
3940
3929
3928
3938
3916
3944
3937
3941
3927
3943
3099
3097
3775
3759
3760
3761
3755
3089
3754
3090
3096
3091
3206
3184
3205
3182
3181
3143
3144
3142
3130
3128
3129
3151
3133
3132
3082
3757
3076
3752
3750
3075
3074
3751
3030
3027
3211
3067
3121
3066
3065
3137
3138
3777
3139
3799
3797
3723
3116
3770
3766
3140
3796
3795
3160
3786
3791
3793
3765
3767
3788
3789
3792
3785
3158
3135
3136

Filtered Excel Dataset can be downloaded below:
Victorian Electorates by Locality and Postcode Filtered for COVID19

Background: I was asked to compile a list of postcodes in lockdown in Melbourne, I started by first going through the list of Local Government Area, I got to the second one and decided that their had to be a better way. Thank goodness for the DATA VIC site.

Issue installing pyinstaller

Recently while running:

pip install pyinstaller

I encountered the following error:

Failed to build pyinstaller
Skipping wheel build for altgraph, due to binaries being disabled for it.
Skipping wheel build for pefile, due to binaries being disabled for it.
ERROR: Could not build wheels for pyinstaller which use PEP 517 and cannot be installed directly

It turns out that Windows Defender (the built in AV in Windows 10) was blocking runw.exe

The real error was a few lines above:

error: could not open 'PyInstaller\bootloader\Windows-32bit\runw.exe': Invalid argument

A quick unblock from Windows Defender and it worked.

Thanks to: https://github.com/pyinstaller/pyinstaller/issues/3813#issuecomment-575319940 for the hint.

Adding Headset to LG Phones

Recently I added some headsets (Jabra PRO 9450) to a range of LG LIP-8012D and LG LIP-8024D phones.

  1. The electronic hook switch plugs into the back of the handset, with the coloured strip (usually indicating pin 1) facing up. It’s best to remove the power, add the accessory, then plug it back in, otherwise, the phone won’t recognize the accessory.
    A second cable for audio is required from the headset port on the back of the phone to the base use of the headset. (only control signals are sent down the electronic hook switch cable)
  2. The handset requires programming to be told to use the handset and not the speakerphone.
    Trans PGM > 6 > 1.
    Select 0 for headset then Hold/Save.
  3. The handset requires programming to be told to ring in the headset:
    Trans PGM > 6 > 2
    Select 2 for headset or 3 for both, then Hold/Save
  4. That’s about it. Enjoy

Upgrading requests_toolbelt in Zato 2.0.8

After failing with pip and easy_install I came across this post again:

https://forum.zato.io/t/what-is-the-canonical-way-to-add-extra-python-packages-to-my-zato-server/580?source_topic_id=586

There is another way, that of updating versions.cfg and buildout.cfg files but if you are more familiar with pip than buildout then pip install package-name is perfectly fine.

So it turns out it is as easy as:

nano ~/current/versions.cfg
update the version from 0.2.0 to 0.8.0 then run
cd ~/current
buildout

And buildout will do it’s magic.

Sorting 404s in Nginx access Logs

The default Nginx logs can be combined and sorted to gather some useful stats.

  1. Make a working directory and copy current logs over (optional):
    mkdir ~/accesslogs
    sudo cp /var/log/nginx/access.log* ~/accesslogs
    cd ~/accesslogs
  2. Extract logs compressed by log rotate:
    ungzip *.gz
  3. Make a big combined log:
    sudo cat access.log* >> log.combined
  4. Parse the combined log and count the 404’s
    sudo awk '($9 ~ /404/)' log.combined | awk '{print $7}' | sort | uniq -c | sort -rn >> 404sC.txt

Let’s encrypt certbot – moving vps

Sometimes you need to move VPS’s, and you don’t want downtime with the SSL certs.

Well it turns out that you can easily run Let’s Encrypt in standalone mode and use DNS verification to do a once-off cert prior to moving the VPS. – Read the Certbot docs.

But once the migration has taken place, you may also want to return to the validation method that you had been using previously. Two options exist, either a) modify the config files by hand (strongly advised against) or to return to the certbot and get it to update the config itself.

For example here is how I generally get a SSL cert on a particular box:

certbot certonly --webroot -w=/var/www/letsencrypt -d [www.URL.com] -d [URL.com]

And here is how I update to the new config

sudo certbot certonly --cert-name [www.URL.com] --force-renewal -a webroot -w=/var/www/letsencrypt

Thanks to @schoen on this post: https://community.letsencrypt.org/t/how-to-change-certbot-verification-method/56735/3

Installing a Canon Image Runner Advance Printer on a Mac OS

For some reason installing the UFR II/UFRII based printer on Mac OS is strangely complex.

Here’s what to do:
1) Download the correct driver for the Canon Machine / Mac OS from the canon site (I install C2020’s a lot: https://www.canon.com.au/multifunction-devices/imagerunner-advance-c2020/support)
2) Install the Package
3) Within System Preferences > Printers > Add a New Printer.
4) Select ‘IP’ from the top tab. Fill the form in as follows:
Address: IP Address of the Printer
Protocol: Change to Line Printer Daemon
Queue: Insert ‘LP’ < this is the part I always forget. Name: A logical name for the user Location: A logical location for the user. Use: Select the Driver from the one that you just installed, in my case I selected 'Canon iR-ADV C2020/2030 Click Add. Canon Printer Mac OS X Settings

It now should now be setup to print.

If you want to setup the default settings on the printer, you need to use the CUPS web interface.
Visit: http://localhost:631/printers/ on the machine.
You’ll probably get a message about CUPS Web interface not being enabled, follow the prompts to open up ‘terminal’ and paste in
cupsctl WebInterface=yes

Then you’ll be able to access the printer UI.
From here you can select the printer, and change the default printer settings.

I always change from 2 sided printing to 1 sided, and from Color to B/W

There you go, installing a Canon Printer or Canon Photocopier on Mac OS X.