[V3.3] PM error post-upgrade - how to fix utility/structure output?

Timmooo

Hi there,

Yesterday I completed an upgrade / server transfer for a forum that I'm hosting for some friends. We moved from Vanilla 2.1.9 (using PHP5.6) to Vanilla 3.3 (using PHP7.4). I have managed to get most things up and running and most things are working okay, but I am running into some issues around PMs working. They are throwing an error message that I believe is related to this utility/structure output I'm getting.

Is someone able to advise how I can fix these as the built in script doesn't seem to change these?

alter table `GDN_UserAuthenticationNonce`
-- [Existing: `Timestamp` timestamp not null default current_timestamp, New: `Timestamp` timestamp not null]
change `Timestamp` `Timestamp` timestamp not null;

alter table `GDN_UserAuthenticationToken`
-- [Existing: `Timestamp` timestamp not null default current_timestamp, New: `Timestamp` timestamp not null]
change `Timestamp` `Timestamp` timestamp not null;

update `GDN_User` `User`
set `Permissions` = ''
where `Permissions` <> '';

update `GDN_User` `User`
set `Permissions` = '[]'
where `UserID` = '2';

alter table `GDN_Conversation`
add `Type` varchar(10) null after `ConversationID`,
add `ForeignID` varchar(40) null after `Type`,
-- [Existing: `Subject` varchar(100) null, New: `Subject` varchar(255) null]
change `Subject` `Subject` varchar(255) null,
-- [Existing: `Contributors` varchar(255) not null, New: `Contributors` varchar(255) null]
change `Contributors` `Contributors` varchar(255) null,
-- [Existing: `InsertIPAddress` varchar(39) null, New: `InsertIPAddress` varbinary(16) null]
change `InsertIPAddress` `InsertIPAddress` varbinary(16) null,
-- [Existing: `UpdateUserID` int null, New: `UpdateUserID` int not null]
change `UpdateUserID` `UpdateUserID` int not null,
-- [Existing: `DateUpdated` datetime null, New: `DateUpdated` datetime not null]
change `DateUpdated` `DateUpdated` datetime not null,
-- [Existing: `UpdateIPAddress` varchar(39) null, New: `UpdateIPAddress` varbinary(16) null]
change `UpdateIPAddress` `UpdateIPAddress` varbinary(16) null,
add `CountParticipants` int not null default 0 after `CountMessages`,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;

alter table `GDN_Conversation`
add index IX_Conversation_Type (`Type`);

alter table `GDN_UserConversation`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;

update `GDN_Conversation` `c`
set `c`.`CountParticipants` = (select count(uc.ConversationID) from GDN_UserConversation uc where uc.ConversationID = c.ConversationID and uc.Deleted = 0);

alter table `GDN_ConversationMessage`
-- [Existing: `InsertIPAddress` varchar(39) null, New: `InsertIPAddress` varbinary(16) null]
change `InsertIPAddress` `InsertIPAddress` varbinary(16) null,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;

insert `GDN_UserMeta` 
(`UserID`, `Name`, `Value`) 
values ('0', 'stubcontent.record.locale', 'en');

I only have limited sql experience so figured I'd check here before I just start playing around with the database directly.

Cheers,

~Tim

R_J

Weird that utility structure doesn't work. I would try to go to the /dashboard/settings/applications and disable the Conversations addon and re-enable it

Timmooo

https://open.vanillaforums.com/discussion/comment/260667#Comment_260667

Well, turning it off does remove the errors from the utility/structure !

alter table `GDN_UserAuthenticationNonce`
-- [Existing: `Timestamp` timestamp not null default current_timestamp, New: `Timestamp` timestamp not null]
change `Timestamp` `Timestamp` timestamp not null;

alter table `GDN_UserAuthenticationToken`
-- [Existing: `Timestamp` timestamp not null default current_timestamp, New: `Timestamp` timestamp not null]
change `Timestamp` `Timestamp` timestamp not null;

update `GDN_User` `User`
set `Permissions` = ''
where `Permissions` <> '';

insert `GDN_UserMeta` 
(`UserID`, `Name`, `Value`) 
values ('0', 'stubcontent.record.locale', 'en');

Unfortunately it also doesn't turn back on. When trying to re-enable the Conversations application it gives the following error:

Data truncated for column 'UpdateUserID' at row 1

Any suggestions on how to fix this now?

Cheers,

~Tim

Timmooo

Okay, I managed to figure this one out. The messages around GDN_Conversation were around the default system DM that was sent to me when I first set up the old forums. I just had to change a bunch of fields from null to an appropriate value (as per the original messages).

Looking at the remaining errors, I've had a bit of a look but am not sure on a few things.

• GDC_UserAuthentication - both of these tables are emply, I tried changing the default value for timestamp from CURRENT_TIMESTAMP() to NULL but this just changed the error but gave the same change it wanted.
• GDN_User - This seems to be saying wipe all the permissions from everyone? But presumable the admin/system roles shouldn't be changed to ''.
• GDN_UserMeta - this field already exists so idk there...

R_J

This is poorly designed : those messages will never disappear and there is no hint about that anywhere...