Monthly Archives: November 2013

Interrupted WordPress Update Leads to Crash

I’ve been busy lately with various things, and haven’t paid much attention to my blog.  I keep planning to make a post, but never get around to it for some reason.  I finally decided that I had something to share, and noted that there was an update for WordPress that I could install.  I clicked the “install” button, and waited as things began to happen.

 

Once they started to happen, they never stopped.  My site never finished updating, and I was now left with a blog that didn’t display anything at all-just a blank, white screen.

After waiting a long time (hours), I decided that I’d probably broken things completely.  I searched for an answer, thinking this must be fairly common, but didn’t find anything that was useful.  I finally contact my hosting expert, who also publishes this great food-critic blog.  I was able to get FTP access to my site after a couple of password resets, and I was able to get to the files for my WordPress blog.  The first thing I did was look at the error messages that php had recorded.  Here’s what I found:

[14-Nov-2013 01:35:31 UTC] PHP Fatal error: Call to a member function reset_postdata() on a non-object in /myhostingrootpath/www.corneroftherooftop.com/directorytofiles/query.php on line 118

(Note that I did change the path information slightly so as to not give all the internal details of my hosting server.)

This didn’t really help me that much, but a internet search on this error wasn’t really all that promising.  I then downloaded and installed the latest version of WordPress using FTP, but still I got nothing but a blank screen upon entering the web address.  I then decided to look at my wp-config.php file.  I had previously noted from the timestamp associated with the files that this one hadn’t changed when I had attempted the update from within WordPress.  I also logged in to my host user management site and discovered what my problem was.

The address for the mysql server that my WordPress site was using was different in the wp-config.php file and what the host noted was to be used on its website.  This probably resulted from an upgrade to a newer version of mysql on the hosts server farm, and when I attempted to update WordPress the old mysql address became obsolete.   I changed the address in the wp-config.php file to match the new mysql server url, and off we went. The line specifically that I had to change was similar to this one:

define('DB_HOST', 'theactaullwebaddressfortheMysqlserver');

I had to undo a few other things that I had tried in the interim, such as moving all of my content via FTP to my computer to see if content specifically was the problem, which it wasn’t.  I also turned debugging mode on which resulted in some notifications but didn’t really help.

To summarize, the mysql host changed for some reason, most likely being the new version of WordPress and an updated version of Mysql from my hosting platform.  After changing to the new mysql server address, my problems went away.

I hope this will be of help if anyone stumbles into the same problem that I did.

How to calculate change and percent change over a time interval in mysql

I have a mysql application that I use to simulate various types of trading and trading strategies.  One of the tables generated is a table with the value of the simulated portfolio and the date with an entry for each day.  I wanted to use a mysql query to generate monthly, quarterly, and annual returns based on the account history table.  While this sounds fairly straightforward, it did become more involved that I thought, as I had to use variables in mysql queries, as well as date aggregration.  In addition, I needed to have the data pulled from the last day of the period instead of the first.

Here’s that I was able to come up with for annual data.  The account history table has two fields that are used:  Date, and Total_Equity with an entry for each day.

SELECT b.`yyyy`, CONCAT('$', FORMAT(b.Open_Equity,2)) as Open_Equity, 
	concat('$',FORMAT(delta, 2)) as delta,
    concat(round(delta_p*100,2),'%') as delta_p 
FROM   (SELECT *,
          (Open_Equity - @pequity) as delta,
          (Open_Equity - @pequity)/@pequity as delta_p,
          (@pequity:= Open_Equity)
        FROM (SELECT (EXTRACT(YEAR FROM `Date`)) as `yyyy`, 
			(SUBSTRING_INDEX(GROUP_CONCAT(CAST(`Open_Equity` AS CHAR) ORDER BY `Date` DESC), ',', 1 )) AS `Open_Equity`
			FROM account_history GROUP BY `yyyy` ORDER BY `yyyy` DESC) p
			CROSS JOIN
			(SELECT @pequity:= NULL) as a
       ORDER BY `yyyy` ) as b
ORDER by `yyyy` ASC

I’m not certain how ofter this sort of thing might come up, but I hope it will save someone some time and help with coding in mysql.

 

Here is some sample output:

yyyy   	Open_Equity	delta	        delta_p
2000	$79,794.00	NULL	        NULL
2001	$92,361.50	$12,567.50	15.75%
2002	$127,137.12	$34,775.62	37.65%
2003	$168,950.25	$41,813.13	32.89%
2004	$177,490.75	$8,540.50	5.06%
2005	$184,199.50	$6,708.75	3.78%
2006	$223,008.17	$38,808.67	21.07%
2007	$289,139.60	$66,131.43	29.65%
2008	$296,956.64	$7,817.04	2.70%
2009	$387,388.70	$90,432.06	30.45%
2010	$552,576.95	$165,188.25	42.64%
2011	$671,374.49	$118,797.54	21.50%
2012	$614,951.48	$-56,423.01	-8.40%
2013	$671,146.02	$56,194.54	9.14%