Troubleshooting Piwik database errors.

Posted by Orville Bennett on August 30, 2017
Read time: about 1 minute

Do you know what’s not fun? Troubleshooting database errors. Imagine you’ve set up piwik on a client’s server. You have everything installed, configured, and running. You’re testing out various features, you know, to make sure they work as advertised before handing off to the client. Not required, but it’s that little extra icing on top that makes your service just a touch above the rest.

Then you find that a feature isn’t working properly. And you have a deadline. That is quickly approaching. Sigh.

I had just such a problem “recently” and was unable to get any useful error info from the server’s logs. It was absolutely maddening, until I recalled something else I do before turning over a final project: I turn off all debug logging to the rendered webpages. I typically do this fairly early in the process so that I don’t have to worry about it later.

Having remembered that, I re-enabled php’s debug output, but was still not getting any useful debug info back. The reason, again, was me. In the nginx config I set up generic error pages to be served when a server-side error was encountered. In order to troubleshoot I had to disable generic pages first.

That required editing /usr/local/etc/nginx/nginx.conf and commenting out the line

error_page   500 502 503 504  /50x.html;

changing it to

#error_page   500 502 503 504  /50x.html;

This allowed me to see the Unknown column `use_12_hour_clock` on update from 2.15.0 to 2.15.1-b2 or later error.

This was a bug in piwik code that, thankfully, I didn’t have to solve myself. The solution had already been found. All that was required was to run the following code on the piwik database:

ALTER TABLE `piwik_user_language` ADD COLUMN `use_12_hour_clock` TINYINT(1) NOT NULL DEFAULT 0 AFTER `language`

You’d think that now I could be done with this project and hand it off. But you’d be wrong! More testing found more issues; this time when editing Goals which existed before Piwik v3:

Key "description" for array with keys "idsite, idgoal, name, match_attribute, pattern, pattern_type, case_sensitive, allow_multiple, revenue, deleted" does not exist.

The solution was to run this code on the database:

ALTER TABLE piwik_goal ADD description VARCHAR(255) NOT NULL DEFAULT '';

Finally, I finished verifying that things worked as expected, with the client none the wiser. Interesting thing isn’t it, the better I you are at your job the less recognition you get. It’s a good thing I actually like it that way. ;-)