The last month or so, this blog has been acting up. Partially broken. Today, with the help of the Typosphere IRC channel, I discovered a fix and implemented it.
Settings Column Design Concerns
Over the years, the blog DB has been updated time and time again. Along the way, it appears that the software didn’t always behave properly. Some of the settings were stored in the same DB column. I know from working on DBs designed in this manner for clients that this seems like a good idea, initially, but over the long term, this is a failure waiting to happen. If it isn’t an outright failure, it certainly will become slower as the DB becomes larger.
Initially, I was unaware of this part of the DB design when seeking a solution. The first solution attempt below shows that ignorance.
First Fix Try
The first attempt at a fix was to use the Ruby on Rails db:migrate facility. It failed.
Here’s the code:
class InitPasswordForArticles < ActiveRecord::Migrationclass InitPasswordForArticles < ActiveRecord::Migration class Content < ActiveRecord::Base end class Article < Content end def self.up say "Initializes existing articles with a password" Article.find(:all).each do |art| if art.password.nil? or art.password.empty? art.password = "" art.save! end end end def self.down say "There is migration does absolutely nothing" end
It was believed that a per-article-password wasn’t properly initialized along the way. This turned out to not be the issue.
The Real Issue
That migration ran and nothing was fixed. In fact, it seemed to break more articles so they could not be viewed or edited. It was scary. Things were getting worse, not better.
I started looking for the password field that was causing the issue. After not finding that field, but having the migration code above which was provided by the core Typo Blog developer, I knew which DB table was involved.
I’m comfortable using a DB management tool, so I
- dumped the schema to a file
- looked at the columns
- performed a few queries to understand where the passwords were being stored.
Doing this, I found the column I needed to research more about was settings. I used this query to see more about it:
SELECT id, title, settings FROM contents WHERE id > 0;
Seeing that output for the 1450 or so articles plus a little testing based on the values seen inside the settings column, it became clear to me that along the way the values inside the settings column had been screwed up somehow. I was unable to figure out the specific settings held inside the column after looking at the DB and the code. Rather than fight it too much, I found the articles that were working as desired and copied those values in the settings column. This is the SQL UPDATE statement used:
UPDATE contents SET settings = '--- password: "" ';
The 3 lines are important. Merging them will make the resulting DB unusable by the software. This SQL fixed the issue for every article to where we can view, edit, and the category-based queries are working again. The key thing about this fix is that no password protected articles will remain. I don’t use passwords on articles, so that was not an issue at all.
It is nice to have access to all the articles again and for the sidebar Categories links to work again.