Missing DB Fields (Unknown Column)

How to avoid and fix problems caused by making too big a jump in MODX versions.


The best practice with MODX, is to upgrade your site(s) whenever a new version comes out. I like to wait a little bit in case there are problems with the new version, but seldom more than a week. Sometimes, though, upgrading a site you don't work on very often can get away from you. Although it's tempting to upgrade straight to the newest version, that can lead to trouble down the road. This is because MODX periodically adds new columns to certain database tables. In the most recent versions of MODX, it's assumed that those new table columns have already been added and you can end up with PHP errors when MODX tries to access the missing columns.

Upgrading through all the intermediate upgraded is always a safe solution, though it's usually fine just to upgrade to the intermediate versions that end in .0 (e.g., 2.3.0, 2.4.0, 2.5.0). Don't skip over those versions — important database changes are made in those versions.

The UpgradeMODX extra makes it a lot easier than it used to be. In case you already made too big a jump in versions, in this article we'll look at the symptoms of the problem and see a way to fix it without reverting the site and going through all the intermediate upgrades.


MODX logo

Symptoms

Since Version 2.2.0, I only know of six fields added to the database:

  • The name field in the modx_context table
  • The rank field in the modx_categories table
  • The createdon field in the modx_users table
  • The active field in the modx_transport_providers table
  • The priority field in the modx_transport_providers table
  • The properties field in the modx_transport_providers table
  • The createdon field in the modx_users table (added in 2.5.0)

The last one above is particularly insidious because if that field is missing, it can prevent you from logging in to the MODX Manager.

If you skip versions when you upgrade MODX, those fields can end up missing. The upgrade will go fine. The Manager may function well for you until you do something that triggers code that accesses one of those fields. It may be a while, but eventually, you'll see an error like one of the following in the MODX error log or on the screen:

Unknown column 'modContext.name' in 'where clause'
Unknown column 'modCategory.rank' in 'where clause'
Unknown column 'modUser.createdon' in 'where clause'
Unknown column 'modTransportProvider.active' in 'where clause'
Unknown column 'modTransportProvider.priority' in 'where clause'
Unknown column 'modUser.createdon' in 'where clause'

The errors may take a slightly different form if the code causes a PHP Fatal error. In some cases, you could be looking at the White Screen of Death, or unable to escape the login screen, but the errors should still appear in the MODX Error log.


Solutions

One solution is to revert your site from a backup to a point before the upgrade, then perform all the intermediate upgrades to get to the current version. I haven't tested this, but you should be able to skip the minor upgrades and install the first release of each major version -- 2.0.0, 2.1.0, 2.2.0, 2.3.0, 2.4.0, 2.5.0.x, etc.

If you use the UpgradeMODX extra, you can set the &versionsToShow property to a higher number (the default is 5) so the versions will go back far enough for you, after clearing the cache (you may have to manually delete the files in the core/cache directory.

If, for some reason, UpgradeMODX doesn't work for you, or you prefer to do your upgrades the old-fashioned way, the previous versions of MODX can be found here.

Another approach, if you're comfortable doing it, is simply to add the missing fields to the database in PhpMyAdmin or the equivalent. The examples below assume that your table prefix is modx_. If you have a different prefix, you'll see that in the table names instead of modx_.

To add the columns at a host that uses cPanel, follow these steps:


  • Launch PhpMyAdmin in cPanel
  • Select your MODX database
  • Click on the appropriate table (see below)
  • Click on the Structure tab
  • Make sure the column is not already there
  • At the bottom of the grid, look for the Add 1 column row
  • Click on the "Go" button at the right side of that line
  • Enter the values from below, leaving unmentioned fields blank
  • Click on the "Save" button

Be careful. Make sure you're in the correct table. Two of the tables you want are modx_context and modx_categories. There is also a modx_access_context table and a modx_access_category table — be sure you *don't* change any tables with access in their names.


Values for the new Fields

Add name column to the modx_context table (make a note of the collation for the key field):

   Table: modx_context
   Name: name
   Type: VARCHAR
   Length/Values: 255
   Default: NULL
   Collation: Same as <code>key</code> field
   Null Checkbox: checked

After clicking on the "Save" button, you can add a name in the value field for each context. You can use the same value as the one in the key field, or use a longer, more descriptive name. The name is only used in the context grid in the Manager — MODX never uses it for anything else.


Add rank column to the modx_categories table:

   Table: modx_categories
   Name: rank
   Type: INT
   Length/Values: 11
   Default: Select 'as defined' and put 0 in the box that appears
   Null Checkbox: unchecked

After clicking on the "Save" button, when you click on the "Browse" tab, you should see a value of 0 in the rank field for all categories. If you want to re-order the categories as displayed in the Manager, you can add your own rank number (lower numbers will be higher up in the display). If you leave the rank as 0 the categories will be displayed in alphabetical order.


Add createdon column to the modx_users table:

   Table: modx_users
   Name: createdon
   Type: INT
   Length/Values: 20
   Default: Select 'as defined' and put 0 in the box that appears
   Null Checkbox: unchecked

Add active column to the modx_transport_providers table:

   Table: modx_transport_providers
   Name: active
   Type: tinyint
   Length/Values: 1
   Default: Select 'as defined' and put 0 in the box that appears
   Null Checkbox: unchecked

Add priority column to the modx_transport_providers table:

   Table: modx_transport_providers
   Name: priority
   Type: tinyint
   Length/Values: 4
   Default: Select 'as defined' and put 10 in the box that appears
   Null Checkbox: unchecked

Add properties column to the modx_transport_providers table:

   Table: modx_transport_providers
   Name: properties
   Type: mediumtext
   Null Checkbox: unchecked
   Collation: Same as description field

Add createdon column to the modx_users table:

   Table: modx_users
   Name: createdon
   Type: int
   Length: 20
   Null Checkbox: unchecked
   Default: Select 'as defined' and put 0 in the box that appears

Here's an example of the SQL code for fixing the modx_context table on the SQL tab in PhpMyAdmin. Thanks to Jon Ewing (siteadvice):

        ALTER TABLE modx_context ADD name VARCHAR(255) NOT NULL AFTER rank;
    

Once you've made sure those fields are present in the database, the Unknown column errors should go away.

 

Looking for high-quality, MODX-friendly hosting? As of May 2016, Bob's Guides is hosted at A2 hosting. (More information in the box below.)



Comments (2)

  1. MarkJan 10, 2017 at 11:01 AM

    Thanks Bob, this was a lifesaver after getting a
    Fatal error: Call to a member function checkPolicy() on a non-object in /var/sites/d/xxxx/public_html/core/model/modx/modx.class.php on line 1881
    whilst upgrading an old site.

    I was also having the issue with your upgrade snippet about the Github requests exceeded - obviously caused by your snippet being too popular on certain hosts!!. Managed to add my own token, but misread a post from your instructions thinking (wrongly) that you meant change the System Settings for the snippet, not realizing until I saw someone else's post that you meant changing the default properties tab. Would it not be easier to create system settings that could be updated rather than 'hacking' the snippet?

    anyhow just a thought but thanks again for this post!

  2. Bob RayMar 23, 2017 at 11:26 PM

    Sorry for the delay. I missed your comment when it was posted.

    Thanks for the kind words.

    Traditionally, System Settings are meant for information that needs to be available across the site, but that tradition seems to be weakening. I hesitate to change that on existing extras for fear of breaking things for people who use them. The sensible workaround is a property set, which won't be altered by upgrades.


Please login to comment.

  (Login)