GODDAMN UTF-8 / PHP

About 25 hours spent in two days on figuring out this problem with my PHP myAdmin app. Let's break it down. A shared hosting plan eliminated support for earlier versions of PHP. I thought I was ready for this, having carefully updated all the old mySQL calls, functions, etc. What I had not counted on was the default_charset change in 5.6 and beyond. It was previously Latin1 and then it went to UTF-8 (which is the right direction, of course). BUT, my database was full of double-encoded records spanning over a decade. I tried all the tricks I could find, frantically dumping and importing, and trying to convert the affected tables. Internet sleuthing reveals that UTF-8 encoding problems have clearly infuriated many developers. But, I did manage to create a quick fix...

Problem: mySQL database double encoded everything

Long term solution: Fix double encoded strings in database

Quick solution: explicitly revert to the OLD encoding that was in place prior to change in 5.6

Now, fixing the database is going to take some time. I had no luck with quick conversions, dumps, imports, etc. But, I was able to restore normal functionality to the app with a relatively small change:

 mysqli_query($my_connection,"SET CHARACTER SET 'Latin1'");

This is placed right after the connection to the database is made. Results? The garbled data became ungarbled for display. Of course, I had to include this line for every call to the database, which involved updating 20+ files. But it was the quick fix I needed in the moment.

Raspberry Pi Alarm Update : 3 years later

The Raspberry Pi home alarm conversion has been running non-stop for over three years. By my calculation, I have saved $1230 to date! I was paying $30/month for 2G radio monitoring, plus $50+ a year to the local police department for emergency response.

The hardware has not changed at all-- I've only added a small backup UPS for the system, which also powers the router and modem. I have made some minor changes to the code, which I am including below.

Error handling : I was (and still am) somewhat new to Python, so my original code did not gracefully handle errors. An inability to contact my personal server would often crash the script. I rewrote the "pingmysite" function as follows:

def pingmysite():
--try:
----response = requests.get('https://server.com/alarm.php?mypiekey=798793475ukjsghhdfghdfghwghtkjhkjsfrtg', timeout = 1)
----if response.status_code == requests.codes.ok:
-----print ("site pinged successfully")

--except: # catch * all exceptions
----e = sys.exc_info()[0]
----print ( "Error: %s " % e )
----no_server()
--time.sleep(25)

System health : It occurred to me that at some point the small boat light that has been dimly glowing for three years may eventually burn out, and I would be none the wiser. This led me to write the "emergency_dark" function to complement the original "emergency" function. This changed the checklight function as well:

def checklight():
--data = bus.read_i2c_block_data(0x39, 0x0C | 0x80, 2)
--data1 = bus.read_i2c_block_data(0x39, 0x0E | 0x80, 2)
--ch0 = data[1] * 256 + data[0]
--ch1 = data1[1] * 256 + data1[0]
--print ("Full Spectrum(IR + Visible) :%d lux" %ch0)
--print ("Infrared Value :%d lux" %ch1)
--print ("New Visible Value :%d lux" %(ch0 - ch1))
--luxvar = ch0 - ch1
--if luxvar > 25:
----print ("too damn bright!")
----emergency()
--if luxvar < 2:
----print ("too dark! no power or burnt out light bulb!")
----emergency_dark()
--time.sleep(5)
--onthehour()

E-mail woes : My script connects to a mono-purpose Gmail account, which only works with the "less secure" features activated in the advanced settings. Google started to force these accounts into "more secure" mode if they weren't being used on a regular basis. This led me to add a "daily_report" function, that simply sends an e-mail to me once a day with the message "Your Pi server is online".

def daily_report():
--#for the e-mail server to stay active with Google
--server = smtplib.SMTP('smtp.gmail.com', 587)
--server.starttls()
--server.login("----------@gmail.com", "------------------------")
--msg = "Good news. Your Pi server is online."
--server.sendmail("----------@gmail.com", "----------@gmail.com", msg)
--server.quit()
--time.sleep(120)

Remote Server : It has happened a couple of times that my remote server has been unavailable to the script. It tries to reach it, but the site is down for some reason, and I wanted to know about this in a timely fashion. I wrote this function that is called from the earlier "pingmysite" function.

def no_server():
--#make sure my personal server is up and running
--server = smtplib.SMTP('smtp.gmail.com', 587)
--server.starttls()
--server.login("----------@gmail.com", "------------------------")
--msg = "Warning : Unable to connect to remote server. Make sure your website is online."
--server.sendmail("----------@gmail.com", "----------@gmail.com", msg)
--server.quit()
--time.sleep(120)

Concluding Thoughts : It has been over three years of completely reliable operation, and I regularly test the system to make sure everything is working. With the better error handling and system health reports, I am even more satisfied with this DIY project.

Damn Windows 10 Clone

Challenge that shouldn't have been a challenge: Clone a conventional hard drive onto a new SSD.

I first tried to accomplish this through the operating system's tools alone, since there is a way to (theoretically) mirror two drives. But for whatever reason, Windows 10 kept objecting to my efforts to engage in this process. This may have been due to corrupt sectors on the original hard drive. I turned to the third party market with Macrium Free, and this tool seemed to be an intuitive solution to making a true clone. In fact, once I understood the interface (and repaired the source drive through CHKDSK multiple times), everything seemed to be smooth sailing. But, upon boot this mysterious error:

A required device isn’t connected or cannot be accessed with codes 0xc000000e

Ok, that's fine, no worries, since Macrium has a USB boot tool program to fix pesky MBR records. But when running it, I got stopped repeatedly because the keyboard/mouse became inactive. Stopped working entirely. I don't blame Macrium here, since the same thing happened with Windows PE recovery tools. It could very well be the problem was the BIOS or the Dell Hardware, or whatever.

So the solution here was to create a bootable Windows 10 full installation USB drive, and during setup, click the tiny "repair" link. I actually had to run the repair utility twice, but then the SSD booted up just as I had hoped.

TLDR; Windows 10 clone to SSD total nightmare, used Macrium for clone, ended up needing full Windows 10 installation USB drive to access functional repair tools.

mySQL inserts just stopped inserting (PHP)

Was just recently alerted to the fact the student grades were being calculated, but not inserted into a mysql database. I didn't have error reporting turned on, so that was my first step. I added the "or die" line to the query.

mysql_query($my_new_query) or die(mysql_error()."<br>Red alert, score not saved!");

This got me some good information, namely the following alert:

Incorrect integer value: '' for column 'uniquekey' at row 1

$my_new_query = "INSERT INTO grading_table VALUES ('$name','$score', ' ')";

The above code worked for a long time-- I just submitted an empty string to the mySQL database, and it did it's auto-increment auto-magically. Something must have recently changed with my shared hosting provider, because this no longer worked. Look what does though:

$my_new_query = "INSERT INTO grading_table VALUES ('$name','$score', NULL)";

Explicitly defining the NULL seems to make the new database happy and my scores are being saved again.

This is an old program, being retired soon, which is why I haven't updated the mysql_query PHP command to a more modern one.

Server 500 error on Chrome, not on Firefox

Recently had to deal with a perplexing issue in which my PHP script was causing a Internal Server Error, and the message seemed to be browser-specific which made no sense to me at all. Turns out has to do with error reporting. For debugging purposes, the first part of all of my pages reads like this:

//Comment the below off to turn off error warnings
//error_reporting(0);

I always leave it in the file, and I always comment it out. Strangely enough, it sufficed to remove the comment slashes to eliminate the server message. It now reads

error_reporting(0);

and everything is right again. This is especially strange because this error just popped up one day after my site having worked fine for three years with no hiccups. Hopefully it helps someone else out there since I was pulling my hear and teeth out in frustration.

Column count doesn’t match value count at row 1

This error was making me crazy with my PHP / mySQL project !  No matter what I seemed to do, the error was the same: Column count doesn't match value count at row 1

I was trying to insert some values into a mySQL database, and I was first have problems with the following code:

$mysql_query = "INSERT INTO reports VALUES ('$data1', '$data2', '$data3', '$data4', '$data6', '$user_ID', '')";

After a lot of headaches, I realized that my database had more rows then the query I was using. I forgot to list $data5 in the query. (I've changed the variable names to more clearly illustrate my mistake)

But then I had the following query string and was STILL getting the same error:

$mysql_query = "INSERT INTO reports VALUES ('$data1', '$data2', '$data3', '$data4', '$data5'  '$data6', '$user_ID', '')";

I kept banging my head against the fridge when I realized that my query string was missing a comma between $data5 and $data6. Once I put the comma in, there were no worries.

This problem was especially frustrating, because the database wouldn't insert the data and there was no warning message. To see a mySQL PHP error message, you need to explicitly ask for one by including the following:

echo "Error message = ".mysql_error();

Having added the above code, I could finally start to identify why the INSERT command wasn't working. What a pain. Of course, this isn't the preferred method to insert data anyway into a mySQL database, but maybe understanding the error message will help you.