Chad Don Cooper Projects and ponderings…

23Jan/180

Install PHP Unit on Windows

  • Download the latest phpunit.phar
  • Place it in a directory which is listed under PATH
  • Remove the .phar file extension.

Doing this rather than making a batch file gets it working in Git Bash.

Filed under: Uncategorized No Comments
17Nov/170

Automated install of MySQL on CentOS 7

Run the scirpt below using the first argument for the root user password.

#!/bin/bash

NEW_MYSQL_ROOT_PASSWORD=$1

rpm -Uvh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum install mysql-server expect -y
systemctl enable mysqld
systemctl start mysqld

TMP_MYSQL_ROOT_PASSWORD=$(grep -oP 'root@localhost: (.+)$' /var/log/mysqld.log)
TMP_MYSQL_ROOT_PASSWORD="${TMP_MYSQL_ROOT_PASSWORD/root@localhost: /}"

SECURE_MYSQL=$(expect -c "
set timeout 10
spawn mysql_secure_installation
expect \"Enter current password for root:\"
send \"$TMP_MYSQL_ROOT_PASSWORD\r\"
expect \"New password:\"
send \"$NEW_MYSQL_ROOT_PASSWORD\r\"
expect \"Re-enter new password:\"
send \"$NEW_MYSQL_ROOT_PASSWORD\r\"
expect \"Change the password for root\"
send \"y\r\"
expect \"New password:\"
send \"$NEW_MYSQL_ROOT_PASSWORD\r\"
expect \"Re-enter new password:\"
send \"$NEW_MYSQL_ROOT_PASSWORD\r\"
expect \"Remove anonymous users?\"
send \"y\r\"
expect \"Disallow root login remotely?\"
send \"y\r\"
expect \"Remove test database and access to it?\"
send \"y\r\"
expect \"Reload privilege tables now?\"
send \"y\r\"
expect eof
")

echo "$SECURE_MYSQL"

yum remove expect -y

Filed under: Uncategorized No Comments
24Jun/170

Kodi – Update SQL databases when files move

In a similar vain to updating my ripped TV show paths in Sickrage's database, I needed to do the same for Kodi.
I used this script to change shows beginning with certain letter ranges to a specific folder.


-- Update path table
UPDATE path SET strPath = REPLACE(strPath,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 1 (#-G)')
where strPath glob 'smb://server/Video/TV 1 (#-S)/[1-9A-Ga-g]*';

UPDATE path SET strPath = REPLACE(strPath,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 2 (H-S)')
where strPath glob 'smb://server/Video/TV 1 (#-S)/[H-Sh-s]*';

UPDATE path SET strPath = REPLACE(strPath,'smb://server/Video/TV 2 (T-Z)', 'smb://server/Video/TV 3 (T-Z)')
where strPath glob 'smb://server/Video/TV 2 (T-Z)/[T-Zt-z]*';

-- Update episode table
UPDATE episode SET c18 = REPLACE(c18,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 1 (#-G)')
where c18 glob 'smb://server/Video/TV 1 (#-S)/[1-9A-Ga-g]*';

UPDATE episode SET c18 = REPLACE(c18,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 2 (H-S)')
where c18 glob 'smb://server/Video/TV 1 (#-S)/[H-Sh-s]*';

UPDATE episode SET c18 = REPLACE(c18,'smb://server/Video/TV 2 (T-Z)', 'smb://server/Video/TV 3 (T-Z)')
where c18 glob 'smb://server/Video/TV 2 (T-Z)/[T-Zt-z]*';

-- Update art table
UPDATE art SET url = REPLACE(url,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 1 (#-G)')
where url glob 'smb://server/Video/TV 1 (#-S)/[1-9A-Ga-g]*';

UPDATE art SET url = REPLACE(url,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 2 (H-S)')
where url glob 'smb://server/Video/TV 1 (#-S)/[H-Sh-s]*';

UPDATE art SET url = REPLACE(url,'smb://server/Video/TV 2 (T-Z)', 'smb://server/Video/TV 3 (T-Z)')
where url glob 'smb://server/Video/TV 2 (T-Z)/[T-Zt-z]*';

-- Update tvshow
UPDATE tvshow SET c16 = REPLACE(c16,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 1 (#-G)')
where c16 glob 'smb://server/Video/TV 1 (#-S)/[1-9A-Ga-g]*';

UPDATE tvshow SET c16 = REPLACE(c16,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 2 (H-S)')
where c16 glob 'smb://server/Video/TV 1 (#-S)/[H-Sh-s]*';

UPDATE tvshow SET c16 = REPLACE(c16,'smb://server/Video/TV 2 (T-Z)', 'smb://server/Video/TV 3 (T-Z)')
where c16 glob 'smb://server/Video/TV 2 (T-Z)/[T-Zt-z]*';

-- Update files
UPDATE files SET strFilename = REPLACE(strFilename,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 1 (#-G)')
where strFilename glob 'smb://server/Video/TV 1 (#-S)/[1-9A-Ga-g]*';

UPDATE files SET strFilename = REPLACE(strFilename,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 2 (H-S)')
where strFilename glob 'smb://server/Video/TV 1 (#-S)/[H-Sh-s]*';

UPDATE files SET strFilename = REPLACE(strFilename,'smb://server/Video/TV 2 (T-Z)', 'smb://server/Video/TV 3 (T-Z)')
where strFilename glob 'smb://server/Video/TV 2 (T-Z)/[T-Zt-z]*';

22Jun/170

Sickrage – Update SQL database when files move

I currently have all my ripped TV shows spread across several drives in Windows. I use Sickrage to maintain an index of them as it produces media information which can be used by Kodi. Rather than using RAID, I have chosen to split the volumes by first letter of each show. I do this as I'd rather try recovery on a single NTFS drive/partition and I'm not in the mood to go ZFS anytime soon.
This does mean I sometimes have to shuffle around data between the drives which makes Sickrage and Kodi unhappy. Rather than go through updating paths manually however I tend to run the following scripts using SQLiteSpy after taking a backup of the db file.

Can view shows 0-9 and A-G using:
select * from tv_shows where show_name glob '[1-9A-Ga-g]*' order by show_name asc

You can move shows H-S from T:\ to U:\ with:
UPDATE tv_shows SET location = 'U:\' || SUBSTR(location,4) WHERE show_name GLOB '[H-Sh-s]*'

5Mar/160

Dreamcast Backup Checker

After searching about I couldn't find a quick and easy way to verify my Dreamcast GDI rips against the dumpcast dat files. Knocked up a quick C# console app which takes a directory argument where dumps are located (one per sub folder) and check the MD5 against a known hash. A txt file report is produced in the directory of the verification program when the program completes.

Binary release here, source code here. Thanks to dcemulation.org for hosting the dat files which I found here.

Filed under: Programs No Comments
28Feb/160

Z4 E85 GPS Firmware and Maps Update

Finally got around to updating the GPS in my E85 Z4. I have the MKIV DVD based system with a monochrome OLED display (which I actually like since it's so discrete) and was running with outdated maps from 2005.
The first part of the update involves flashing newer software to the system in the form of a new firmware. The latest official version V32, can be found here.
To perform the update:

  1. Burn this ISO to a CD-R
  2. Get into the car and leave the door open
  3. Insert the CD in place of the maps disc
  4. Turn the ignition to position 1
  5. Follow the on screen instructions as the software updates

The final part involves acquiring maps discs. The non-iDrive discs for this system are called High 2015. The set for Europe can be purchased at a cost of £179.99 from BMW themselves or you can have a look online for ISOs. For Europe there are two Discs each at 6GB. One for western Europe and one for east. You'll need dual layer discs, I used Verbatim DVD+R DL, I've not tried DVD-Rs.

Tagged as: , , , No Comments
6Feb/160

PHP 7 Apache x64 Upgrades

Updating to the latest releases of Apache and PHP 7, both of which are running in x64 mode was quite painless. A few things needed updating; scripts running the older mysql connector, scripts using pass by reference (=&) and phpVirtualBox. 64bit PHP, finally.

Filed under: Upgrades No Comments
4Feb/160

Avoiding TCP/IP Port Exhaustion

Having had lots of network problems with TIME_WAIT and "no client ports available", I finally got around to tweaking the TCP/IP pool settings on my Windows Server.
The following settings can be found in the registry at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
I used the following settings (DWORD32 Decimal):

  1. TcpTimedWaitDelay = 30
  2. MaxUserPort = 65534
  3. TcpNumConnections = 16777214
  4. TcpMaxDataRetransmissions = 5

I probably need to look at what's running in more detail at some point, but these settings have kept everything humming along for the time being.

Filed under: Fixes No Comments
23Jan/160

Erratic wipers

So the wipers on my Z4 had been misbehaving for a while, deciding to park at a random point on the windshield rather than at the bottom. I was kinda living with it until recently when they wouldn't stop at all or they'd stop and not start again without "rebooting" (:D) the car.

Anyhow I finally got around to fixing them, the issue was with grease inside the motor getting onto the copper contact plate. Cleaning the grease off the plate cures all the problems. Hardest part is removing the tray under the windscreen. This uses the usual BMW screws made from a plastic-cheese composite to hold it in place. Any screwdriver I used seems to shear the heads so if I was to do it again I'd buy spares to replace the ones I take out. BMW decided to use plastic clips to hold screws that themselves hold the tray down vertically too. Four of these clips snapped off, they were plastic welded on from what I could tell. I used gorilla glue to put these back after fixing the motor. One of the white screw also went AWOL for a while, I fished this out of a rubber drain on the right side of the engine bay. There are a couple of these rubber drains which have flaps at the end, I made sure these were all clear of rotting leaves before I put everything back together.

Filed under: Fixes, Problems No Comments
19Jan/160

771 – 775 Xeon CPU Mod

Through some late night surfing I recently discovered the socket 771 to 775 CPU mod. Provided you have a socket 775 Intel motherboard with a supported chipset, there's a good chance you can run a server class Xeon CPU in your humble old desktop motherboard.

Knowing this, curiosity got the better of me, not least since I had a salvaged EVGA nForce 790i Ultra SLI Motherboard sat in my garage which I've been meaning to test for ages anyway. The motherboard is pretty nice considering it's 8 years old. Triple SLI, DDR3, 1600MHZ FSB... I always felt having it collecting cobwebs.

One (albet expensive at £10) pre-flashed BIOS chip later I was ready to look at which CPU to buy. Now the fastest CPU, which actually demands a 1600MHz bus is the 3.4GHz 12MB Intel Xeon X5492, but those are running at £60+ due to the increased demand. Although the board is probably worth £100 working on eBay, I wasn't about to risk buying a CPU for an untested board. Instead I opted for an Intel Xeon X5460 3.16GHz 12MB cache with a FSB of 1333MHz, the same as the RAM I had. Given that it cost me £20 delivered from Korea, I plan on overclocking it as much as humanely possible using the 1600Mhz headroom.

The mod is pretty simple. You connect some of the pins on the underside of the CPU with a pre-made piece of tape and cut the tabs off the CPU socket allowing the chip to sit flush.
I've not had time to test yet as I need to source a cheap PSU for the build. Speaking of which I've also got an Ati 7850 with a blown mosfet and chipped resistor to fix, if that happens then I could have a nice little ghetto gaming rig.

Filed under: Fixes, Tweaks No Comments