Update Error: PDOStatement: Can't DROP 'issue_backlog_user_id'; check that column/key exists

[BerChill]

Hi,
I know this is probably a probelem on my end but maybe someone could help me with that? After trying to update to the newest version I get the following message:

500 Internal Server Error

PDOStatement: Can't DROP 'issue_backlog_user_id'; check that column/key exists

Trying to revert to the previously installed version resulted in the same message. My phpMyAdmin shows the following:

I guess I did something wrong although the update process of "download->extract->overwrite" has always worked in the past. I was thinking if an open connection/session during the update could have caused this but that's just a wild guess.

I am thankfull for any hints.

[Alanaktion]

That key was introduced when the issue_backlog table was first added in the 16.02.04.1 update, so it should be there, but if it's not, you should be able to manually run the queries from db/16.11.25.sql without the issue_backlog_user_id line. Those queries will need to be run as a single transaction with one connection to work properly.

If your issue_backlog table is empty, you can simply drop and re-create it with these queries:

DROP TABLE `issue_backlog`;
CREATE TABLE `issue_backlog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sprint_id` int(10) unsigned DEFAULT NULL,
  `issues` blob NOT NULL,
  PRIMARY KEY (`id`),
  KEY `issue_backlog_sprint_id` (`sprint_id`),
  CONSTRAINT `issue_backlog_sprint_id` FOREIGN KEY (`sprint_id`) REFERENCES `sprint` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[BerChill]

Thank you for your quick reply. Unfortunately, I couldn't resolve the issue.

Since my issue_backlog table has no entries I first tried to execute the queries you gave me. The result is that I get the same error message upon logging in but this time with Can't DROP 'user_id';

So I restored my old database and tried to manually run the queries from db/16.11.25.sql without the following lines:
DROP INDEX issue_backlog_user_id and
DROP FOREIGN KEY issue_backlog_user_id

This gave me the following output:

# Merge group- and type-based backlog into single per-sprint lists
SET SESSION group_concat_max_len = 8192;# MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze).

CREATE TEMPORARY TABLE issue_backlog_converting
SELECT sprint_id, REPLACE(GROUP_CONCAT(issues), '],[', ',')
FROM issue_backlog
GROUP BY sprint_id;# MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze).

TRUNCATE issue_backlog;# MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze).

ALTER TABLE issue_backlog
  DROP COLUMN user_id,
  DROP COLUMN type_id,
  DROP INDEX issue_backlog_type_id,
  DROP FOREIGN KEY issue_backlog_type_id;# MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze).

INSERT INTO issue_backlog (sprint_id, issues)
SELECT * FROM issue_backlog_converting;# MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze).

DROP TEMPORARY TABLE issue_backlog_converting;# MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze).

UPDATE `config` SET `value` = '16.11.25' WHERE `attribute` = 'version';# 1 Datensatz betroffen.

MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze). means that the result returned was null (there are no entries) which is to be expected, I guess?

After that the login page loads properly but upon sending my credentials I get:
500 Internal Server Error PDOStatement: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Sorry for the long post. I hope you have another idea.

[Alanaktion]

I'm not sure why the SUPER privilege would be required for any of those queries... I'm thinking either the SET SESSION or CREATE TEMPORARY TABLE statements are the issue, although neither of those require SUPER under normal circumstances.

With the Can't DROP 'user_id'; error, you might be able to work around it by running these, which also updates the version in config to skip installing the update normally.

SET foreign_key_checks = 0;
DROP TABLE `issue_backlog`;
CREATE TABLE `issue_backlog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sprint_id` int(10) unsigned DEFAULT NULL,
  `issues` blob NOT NULL,
  PRIMARY KEY (`id`),
  KEY `issue_backlog_sprint_id` (`sprint_id`),
  CONSTRAINT `issue_backlog_sprint_id` FOREIGN KEY (`sprint_id`) REFERENCES `sprint` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
UPDATE `config` SET `value` = '16.11.25' WHERE `attribute` = 'version';

I'll try running this upgrade under a few different setups to see if I can figure out the cause.

[BerChill]

OK, I ran your code after restoring the DB to the original (meaning first problematic) version and running the statements that got me to Can't DROP 'user_id'. No luck 😞 ... I am now getting the SUPER privilege(s) error again.

Going back to the initial problem: I don't get why the issue_backlog_user_id key is missing. I have done several updates throughout the last year and that never caused a problem. The only thing I can think of is that it might have to do with a downgrade to v1.4.1 because I think I did that last to make sure all the production systems are on a stable release ... maybe not a good idea 😫

Could I not just add the missing column to the table manually so the update skript "sees" it?

[BerChill]

Update: I found an older version which was (clean) installed on a test server on the same host on September 7th, 2016 and has just a few projects and issues. Updated to 1.4.1 - no problems. Updated to 1.5.0 - no problems.

None of the versions had/have a issue_backlog_user_id attribute in the issue_backlog table. Am I looking at the wrong table?

I will look into how things behave after a downgrade tomorrow.

[Alanaktion]

It looks like the issue_backlog_user_id key was changed in the upgrade script after it was first committed. If you updated on February 4th-5th before I made that change, it may not have included that key correctly. Downgrading the code with an upgraded database is unsupported and could definitely cause some strange behavior, but in theory you use the inverse of the upgrade scripts in sql/ to revert the database as well, at least for the non-destructive upgrades.

I definitely recommend keeping a full backup before each upgrade. I'm also planning to make the upgrade process more stable so it won't run automatically, and so it will install all available upgrades in a single request instead of one per pageload, which could fix some issues like this.

[BerChill]

OK, after numerous attempts at fixing my problem I have to give up and do a clean install. Thank you for your continued support on this one, though. Maybe, once my SQL skills improve I will give it another shot (talking about months rather than weeks).
I don't really view this as a bug since it was probably caused by me trying to downgrade from a non-release to a release version which cannot be expected to work.