Fixed: Database Columns Missing After Upgrading

(@lancemonotone)

If you are receiving error messages after upgrading, it’s because not all of the necessary database table columns are added during the upgrade.

To add all tables, replace the if{} block at wp-content/plugins/adrotate/adrotate-setup.php:609 with the code below.

Unfortunately, because you’ve probably already updated, you’ll also need to set your Adrotate database version back to something like 60 in wp_options/adrotate_db_version so these changes can take effect. a:2:{s:7:"current";i:60;s:8:"previous";i:60;}

Subsequent upgrades to the plugin will overwrite these changes, but it won’t matter because the tables only need to be fixed once.

// Database: 	69
// AdRotate:	5.9.2
if($adrotate_db_version['current'] < 69) {
		adrotate_add_column("{$wpdb->prefix}adrotate_groups", 'cat_par', 'tinyint(2) NOT NULL default \'0\' AFTER cat_loc');
		adrotate_add_column("{$wpdb->prefix}adrotate_groups", 'page_par', 'tinyint(2) NOT NULL default \'0\' AFTER page_loc');
		adrotate_add_column("{$wpdb->prefix}adrotate_groups", 'woo_cat', 'longtext NOT NULL AFTER page_par');
		adrotate_add_column("{$wpdb->prefix}adrotate_groups", 'woo_loc', 'tinyint(1) NOT NULL default \'0\' AFTER woo_cat');
		adrotate_add_column("{$wpdb->prefix}adrotate_groups", 'bbpress', 'longtext NOT NULL AFTER woo_loc');
		adrotate_add_column("{$wpdb->prefix}adrotate_groups", 'bbpress_loc', 'tinyint(1) NOT NULL default \'0\' AFTER bbpress');
		adrotate_add_column("{$wpdb->prefix}adrotate_groups", 'mobile', 'tinyint(1) NOT NULL default \'0\' AFTER bbpress_loc');
		adrotate_add_column("{$wpdb->prefix}adrotate_groups", 'align', 'tinyint(1) NOT NULL default \'0\' AFTER wrapper_after');

		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'spread', 'char(1) NOT NULL default \'N\' AFTER maximpressions');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'spread_all', 'char(1) NOT NULL default \'N\' AFTER spread');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'daystarttime', 'char(4) NOT NULL default \'0000\' AFTER spread_all');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'daystoptime', 'char(4) NOT NULL default \'0000\' AFTER daystarttime');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'day_mon', 'char(1) NOT NULL default \'Y\' AFTER daystoptime');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'day_tue', 'char(1) NOT NULL default \'Y\' AFTER day_mon');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'day_wed', 'char(1) NOT NULL default \'Y\' AFTER day_tue');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'day_thu', 'char(1) NOT NULL default \'Y\' AFTER day_wed');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'day_fri', 'char(1) NOT NULL default \'Y\' AFTER day_thu');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'day_sat', 'char(1) NOT NULL default \'Y\' AFTER day_fri');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'day_sun', 'char(1) NOT NULL default \'Y\' AFTER day_sat');
		adrotate_add_column("{$wpdb->prefix}adrotate_schedule", 'autodelete', 'char(1) NOT NULL default \'N\' AFTER day_sun');

		adrotate_add_column("{$wpdb->prefix}adrotate", 'desktop', 'char(1) NOT NULL default \'Y\' AFTER show_everyone');
		adrotate_add_column("{$wpdb->prefix}adrotate", 'mobile', 'char(1) NOT NULL default \'Y\' AFTER desktop');
		adrotate_add_column("{$wpdb->prefix}adrotate", 'tablet', 'char(1) NOT NULL default \'Y\' AFTER mobile');
		adrotate_add_column("{$wpdb->prefix}adrotate", 'os_ios', 'char(1) NOT NULL default \'Y\' AFTER tablet');
		adrotate_add_column("{$wpdb->prefix}adrotate", 'os_android', 'char(1) NOT NULL default \'Y\' AFTER os_ios');
		adrotate_add_column("{$wpdb->prefix}adrotate", 'os_other', 'char(1) NOT NULL default \'Y\' AFTER os_android');
		adrotate_add_column("{$wpdb->prefix}adrotate", 'budget', 'char(1) NOT NULL default \'Y\' AFTER autodelete');
}

This topic was modified 2 years, 10 months ago by lancemonotone.
This topic was modified 2 years, 10 months ago by lancemonotone.

Viewing 11 replies - 1 through 11 (of 11 total)

Plugin Author Arnan
(@adegans)

2 years, 10 months ago

But if you do the update right, the tables are updated properly.
You can also run the updater from Settings > Maintenance.

Thread Starter lancemonotone
(@lancemonotone)

2 years, 10 months ago

I’m not sure how I did the update ‘wrong’. I clicked the update link like every other plugin. I also tried to manually update as you suggested. But those columns are not included in the upgrade function as far as I can tell. Perhaps you could provide the location? It seems other people are having the same issue and I’m just trying to help them out.

Plugin Author Arnan
(@adegans)

2 years, 10 months ago

Some of those columns have been added years ago. Some are quiet recent. All are part of the installation script and update function as far as I can tell.

Thread Starter lancemonotone
(@lancemonotone)

2 years, 10 months ago

Perhaps our plugin version was older than when you added the columns. They are not in the upgrade function as far as I can tell, and they weren’t added when I updated, thus my post. Be well.

Plugin Author Arnan
(@adegans)

2 years, 10 months ago

All of these columns either are part of standard AdRotate or have been later added through a update script.
But you do you…

Thread Starter lancemonotone
(@lancemonotone)

2 years, 10 months ago

It’s weird that you’re picking a fight over this and blaming me for a bug in your product when I’m just trying to help others (and you).

The columns are indeed created when the plugin is first activated if the tables do not already exist. However, if the tables already exist, the columns are not added during upgrade.

For example, adrotate_groups.page_par is created in adrotate_setup.php:404, but it is not added to the table during an upgrade if the table already exists. If this is incorrect, please point us to the file and line where it is added.

Also, the version (5.11) of the plugin available for download on your website is behind the version (5.12.2) in the WP plugin directory.

Plugin Author Arnan
(@adegans)

2 years, 10 months ago

I’m not picking a fight, nor am I blaming you for anything. Stop twisting words or interpretations.

All I’m saying is that the columns you add are either standard in AdRotate or have been added later through an upgrade script. Argue all you want about that but you can read the code for yourself and see all the relevant columns being added over the course of many years.

If you were using a version OLDER than what’s supported in the upgrade script that’s not my fault or mistake and indeed then some columns may have been missing. Simply because the update routine didn’t exist back then. But those versions are so old they’re not considered compatible or relevant and you really should just uninstall the plugin and start fresh with the new version as that will be much faster and easier – For most people anyway.

But good for you that you managed to get it to work and that your code fixed an issue for you 🙃

Plugin Author Arnan
(@adegans)

2 years, 10 months ago

I’ll look at the 5.11 version and see what’s up with that, that should be 5.12.2. Thanks for letting me know.

Thread Starter lancemonotone
(@lancemonotone)

2 years, 10 months ago

Look buddy, this is the real world. I was hired to help my client upgrade their site. They had your plugin installed and dozens of ads already configured. Perhaps the plugin was older than your upgrade script allows, but uninstalling and starting over was not an option. I feel compelled to point out that if there was a cutoff, a good plugin developer would let users know that their version was too old to upgrade instead of letting them do it and breaking their site.

Whatever the previous version was, whether it was before or after your imaginary cutoff, upgrading to the latest version did break the site because none of those columns were added during the upgrade. In order to restore the site without wiping out all my client’s previous work, I had to add the missing columns. If you don’t like it, too bad so sad, but that’s what I had to do and I stand firmly by my statements above. My fix was very simple and may help others who run into the same problem I did.

I stupidly thought I might even help you. But instead of incorporating my code or ignoring my post altogether, you keep telling me I’m wrong, which reflects poorly on you and your products. If my client wanted to go Pro or use another of your products, I would tell them nope. I can only imagine what a nightmare support tickets must be.

But you do you… 🙄

Plugin Author Arnan
(@adegans)

2 years, 10 months ago

Well, clearly this is going nowhere.
You have your fix which works fine.

I have my upgrade routine which also works fine. Unless you do something non-standard, just like updating from Windows 98 to 10 is unlikely to work.

Just so you now, overall my customers and users are happy with my efforts. And I’m sure your efforts will be appreciated by the few that need it too.

And let’s just leave it at that.
Have a good day.
SpyCamCentral
(@spycamcentral)

2 years, 8 months ago
@lancemonotone thanks for providing the code, it helped me get started on fixing this. Had an older version of Adrotate on multiple sites and updating to new version caused same database errors as you had.

Running the plugin’s updater manually in Adrotate’s Settings->Maintenance to upgrade database didn’t add all missing fields and I still had to add some manually.

Anyway, here’s SQL code to add missing tables in case anyone else has similar issues in the future:
```
ALTER TABLE wp_dbprefix_adrotate_schedule
ADD COLUMN daystarttime CHAR(4) NOT NULL DEFAULT '0000' AFTER spread_all,
ADD COLUMN daystoptime CHAR(4) NOT NULL DEFAULT '0000' AFTER daystarttime,
ADD COLUMN day_mon CHAR(1) NOT NULL DEFAULT 'Y' AFTER daystoptime,
ADD COLUMN day_tue CHAR(1) NOT NULL DEFAULT 'Y' AFTER day_mon,
ADD COLUMN day_wed CHAR(1) NOT NULL DEFAULT 'Y' AFTER day_tue,
ADD COLUMN day_thu CHAR(1) NOT NULL DEFAULT 'Y' AFTER day_wed,
ADD COLUMN day_fri CHAR(1) NOT NULL DEFAULT 'Y' AFTER day_thu,
ADD COLUMN day_sat CHAR(1) NOT NULL DEFAULT 'Y' AFTER day_fri,
ADD COLUMN day_sun CHAR(1) NOT NULL DEFAULT 'Y' AFTER day_sat,
ADD COLUMN autodelete CHAR(1) NOT NULL DEFAULT 'N' AFTER day_sun;

ALTER TABLE wp_dbprefix_adrotate
ADD COLUMN desktop CHAR(1) NOT NULL DEFAULT 'Y' AFTER show_everyone,
ADD COLUMN tablet CHAR(1) NOT NULL DEFAULT 'Y' AFTER mobile,
ADD COLUMN os_ios CHAR(1) NOT NULL DEFAULT 'Y' AFTER tablet,
ADD COLUMN os_android CHAR(1) NOT NULL DEFAULT 'Y' AFTER os_ios,
ADD COLUMN os_other CHAR(1) NOT NULL DEFAULT 'Y' AFTER os_android;
```
Don’t forget to change “dbprefix” to your database prefix!

Viewing 11 replies - 1 through 11 (of 11 total)

The topic ‘Fixed: Database Columns Missing After Upgrading’ is closed to new replies.