spotweb: Unable to finish upgrade-db.php Cannot drop index 'idx_commentsfull_1': needed in a foreign key constraint

@Rouzax, if you have a file changelog in your spotweb root folder, then you are on Develop branch, if not, you are on Master.

just checked against db9f97351aa6b8ef808063f06d506a8f12831a6a and can confirm that the issue is solved.

Alright, so i managed get my own environment setup,

PHP 7.2.18 (cli) (built: Apr 30 2019 23:32:39) ( ZTS MSVC15 (Visual C++ 2017) x64 ) Copyright © 1997-2018 The PHP Group Zend Engine v3.2.0, Copyright © 1998-2018 Zend Technologies

Server version: 8.0.17 MySQL

Using the Develop branch:

Install went fine, then i did a upgrade-db.php

Updating schema..(pdo_mysql)
        Validating spots(messageid)
        Validating spots(poster)
        Validating spots(title)
        Validating spots(tag)
        Validating spots(category)
        Validating spots(subcata)
        Validating spots(subcatb)
        Validating spots(subcatc)
        Validating spots(subcatd)
        Validating spots(subcatz)
        Validating spots(stamp)
        Validating spots(reversestamp)
        Validating spots(filesize)
        Validating spots(moderated)
        Validating spots(commentcount)
        Validating spots(spotrating)
        Validating spots(reportcount)
        Validating spots(spotterid)
        Validating spots(editstamp)
        Validating spots(editor)
        Validating spotsfull(messageid)
        Validating spotsfull(verified)
        Validating spotsfull(usersignature)
        Validating spotsfull(userkey)
        Validating spotsfull(xmlsignature)
        Validating spotsfull(fullxml)
        Validating usenetstate(infotype)
        Validating usenetstate(curarticlenr)
        Validating usenetstate(curmessageid)
        Validating usenetstate(lastretrieved)
        Validating usenetstate(nowrunning)
        Validating commentsxover(messageid)
        Validating commentsxover(nntpref)
        Validating commentsxover(spotrating)
        Validating commentsxover(moderated)
        Validating commentsxover(stamp)
        Validating reportsxover(messageid)
        Validating reportsxover(fromhdr)
        Validating reportsxover(keyword)
        Validating reportsxover(nntpref)
        Validating spotstatelist(messageid)
        Validating spotstatelist(ouruserid)
        Validating spotstatelist(download)
        Validating spotstatelist(watch)
        Validating spotstatelist(seen)
        Validating commentsfull(messageid)
        Validating commentsfull(fromhdr)
        Validating commentsfull(stamp)
        Validating commentsfull(usersignature)
        Validating commentsfull(userkey)
        Validating commentsfull(spotterid)
        Validating commentsfull(hashcash)
        Validating commentsfull(body)
        Validating commentsfull(verified)
        Validating commentsfull(avatar)
        Validating settings(name)
        Validating settings(value)
        Validating settings(serialized)
        Validating commentsposted(ouruserid)
        Validating commentsposted(messageid)
        Validating commentsposted(inreplyto)
        Validating commentsposted(randompart)
        Validating commentsposted(rating)
        Validating commentsposted(body)
        Validating commentsposted(stamp)
        Validating spotsposted(messageid)
        Validating spotsposted(ouruserid)
        Validating spotsposted(stamp)
        Validating spotsposted(title)
        Validating spotsposted(tag)
        Validating spotsposted(category)
        Validating spotsposted(subcats)
        Validating spotsposted(filesize)
        Validating spotsposted(fullxml)
        Validating reportsposted(ouruserid)
        Validating reportsposted(messageid)
        Validating reportsposted(inreplyto)
        Validating reportsposted(randompart)
        Validating reportsposted(body)
        Validating reportsposted(stamp)
        Validating usersettings(userid)
        Validating usersettings(privatekey)
        Validating usersettings(publickey)
        Validating usersettings(avatar)
        Validating usersettings(otherprefs)
        Validating users(username)
        Validating users(firstname)
        Validating users(passhash)
        Validating users(lastname)
        Validating users(mail)
        Validating users(apikey)
        Validating users(lastlogin)
        Validating users(lastvisit)
        Validating users(lastread)
        Validating users(lastapiusage)
        Validating users(deleted)
        Validating sessions(sessionid)
        Validating sessions(userid)
        Validating sessions(hitcount)
        Validating sessions(lasthit)
        Validating sessions(ipaddr)
        Validating sessions(devicetype)
        Validating securitygroups(name)
        Validating grouppermissions(groupid)
        Validating grouppermissions(permissionid)
        Validating grouppermissions(objectid)
        Validating grouppermissions(deny)
        Validating usergroups(userid)
        Validating usergroups(groupid)
        Validating usergroups(prio)
        Validating notifications(userid)
        Validating notifications(stamp)
        Validating notifications(objectid)
        Validating notifications(type)
        Validating notifications(title)
        Validating notifications(body)
        Validating notifications(sent)
        Validating filters(userid)
        Validating filters(filtertype)
        Validating filters(title)
        Validating filters(icon)
        Validating filters(torder)
        Validating filters(tparent)
        Validating filters(tree)
        Validating filters(valuelist)
        Validating filters(sorton)
        Validating filters(sortorder)
        Validating filters(enablenotify)
        Validating filtercounts(userid)
        Validating filtercounts(filterhash)
        Validating filtercounts(currentspotcount)
        Validating filtercounts(lastvisitspotcount)
        Validating filtercounts(lastupdate)
        Validating spotteridblacklist(spotterid)
        Validating spotteridblacklist(ouruserid)
        Validating spotteridblacklist(idtype)
        Validating spotteridblacklist(origin)
        Validating spotteridblacklist(doubled)
        Validating cache(resourceid)
        Validating cache(cachetype)
        Validating cache(stamp)
        Validating cache(metadata)
        Validating cache(ttl)
        Validating moderatedringbuffer(messageid)
        Validating permaudit(stamp)
        Validating permaudit(userid)
        Validating permaudit(permissionid)
        Validating permaudit(objectid)
        Validating permaudit(result)
        Validating permaudit(ipaddr)
        Validating index idx_spots_1

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_abs.php on line 227
                Dropping index idx_spots_1
                Adding index idx_spots_1
        Validating index idx_spots_2

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_abs.php on line 227
                Dropping index idx_spots_2
                Adding index idx_spots_2
        Validating index idx_spots_3

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_abs.php on line 227
                Dropping index idx_spots_3
                Adding index idx_spots_3
        Validating index idx_spots_4

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_abs.php on line 227
                Dropping index idx_spots_4
                Adding index idx_spots_4
        Validating index idx_spots_5

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_abs.php on line 227
                Dropping index idx_spots_5
                Adding index idx_spots_5
        Validating FTS idx_fts_spots

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_abs.php on line 266

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_mysql.php on line 109
                Adding FTS idx_fts_spots

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_mysql.php on line 122

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_mysql.php on line 122

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_mysql.php on line 122
        Validating index idx_usenetstate_1

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_abs.php on line 227
                Dropping index idx_usenetstate_1
                Adding index idx_usenetstate_1
        Validating index idx_spotsfull_1

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_abs.php on line 227
                Dropping index idx_spotsfull_1
                Adding index idx_spotsfull_1
        Validating index idx_commentsfull_1

Notice: Undefined index: column_name in C:\wamp64\www\develop\lib\dbstruct\SpotStruct_abs.php on line 227
                Dropping index idx_commentsfull_1


SpotWeb crashed

Database schema or settings upgrade failed:
   HY000: 1553: Cannot drop index 'idx_commentsfull_1': needed in a foreign key constraint

Ok, hmm that’s bad, i need to overthink that.

It doesn’t even come up to this point: https://github.com/spotweb/spotweb/blob/a67945d27d5fd2742b6149ca34fc1ad3876bf971/lib/dbstruct/SpotStruct_abs.php#L274

So the line where it goes bad is: https://github.com/spotweb/spotweb/blob/a67945d27d5fd2742b6149ca34fc1ad3876bf971/lib/dbstruct/SpotStruct_abs.php#L124

Checks if a index structure is the same as the requested one. Recreats if not
	function validateIndex($idxname, $type, $tablename, $colList) {
		echo "\tValidating index " . $idxname . PHP_EOL;

	if (!$this->compareIndex($idxname, $type, $tablename, $colList)) {
			# Drop the index
			if ($this->indexExists($idxname, $tablename)) {
				echo "\t\tDropping index " . $idxname . PHP_EOL;
			   ----> $this->dropIndex($idxname, $tablename); <----
			} # if

			
			echo "\t\tAdding index " . $idxname . PHP_EOL;
			
			# and recreate the index
			$this->addIndex($idxname, $type, $tablename, $colList);
		} # if
	} # validateIndex

As you can see above it’s validating index, then tries to drop the index, but it’s not allowed, so we have to come up with a way to ditch the foreign key before dropping the index.

I’m running out of time today so i will try to pick-up tomorrow.

Rebuilding the indexes takes time

It’s a big database

Also I compared your table with mine and both are identical from this output

SELECT TABLE_NAME, CONSTRAINT_NAME FROM information_schema.key_column_usage
WHERE TABLE_SCHEMA = "spotweb"