Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Upgrade from 2.2.1 to 2.8.3 - 504
I'm trying to upgrade to the latest version and keep getting 504 errors when I attempt to do the database upgrades. I followed all the instructions as well turned off all plugins. I have the default theme set.
I turned on the debugger and got this:
Unknown column 'c.CountCategories' in 'field list'
The error occurred on or near: /home/gypsypicking/djangobooks.com/forum/library/database/class.database.php
403: // If we get here then the pdo statement prepared properly.
404: break;
405:
406: } catch (Gdn_UserException $uex) {
407: trigger_error($uex->getMessage(), E_USER_ERROR);
408: } catch (Exception $ex) {
409: list($state, $code, $message) = $pDO->errorInfo();
410:
411: // If the error code is consistent with a disconnect, attempt to retry
Backtrace:
/home/gypsypicking/djangobooks.com/forum/library/database/class.database.phpPHP::gdn_ErrorHandler(); [/home/gypsypicking/djangobooks.com/forum/library/database/class.database.php:407] PHP::trigger_error(); [/home/gypsypicking/djangobooks.com/forum/applications/vanilla/models/class.categorymodel.php:3443] Gdn_Database->query(); [/home/gypsypicking/djangobooks.com/forum/applications/vanilla/settings/structure.php:107] CategoryModel->recalculateTree(); [/home/gypsypicking/djangobooks.com/forum/applications/dashboard/models/class.updatemodel.php:681] PHP::include(); [/home/gypsypicking/djangobooks.com/forum/applications/dashboard/controllers/class.utilitycontroller.php:237] UpdateModel->runStructure(); [/home/gypsypicking/djangobooks.com/forum/applications/dashboard/controllers/class.utilitycontroller.php:207] UtilityController->runStructure(); [/home/gypsypicking/djangobooks.com/forum/library/core/class.dispatcher.php:862] UtilityController->structure(); [/home/gypsypicking/djangobooks.com/forum/library/core/class.dispatcher.php:279] Gdn_Dispatcher->dispatchController(); [/home/gypsypicking/djangobooks.com/forum/index.php:29] Gdn_Dispatcher->dispatch();
0
Comments
You have to be logged in as the admin during the update and visit www.djangobooks.com/forum/utility/structure
I was logged in as the admin and did go to www.djangobooks.com/forum/utility/structure
That’s where I saw the debug info posted above. It seems that 2.8.3 expects the GDN_Category table to have a field named c.CountCategories. But 2.2.1 doesn’t use that field. Shouldn’t the updated just add it?
I searched the 3 core structure.php listed, only found CountCategories - not c.CountCategories.
Here's more of the error message. It looks like the updated is trying to update c.CountCategories and failing:
[sql] 'update GDN_Category c join ( select ParentCategoryID, count(ParentCategoryID) as CountCategories from GDN_Category group by ParentCategoryID ) c2 on c.CategoryID = c2.ParentCategoryID set c.CountCategories = c2.CountCategories, c.Depth = 0;' [inputParameters] NULL [options] array ( ) [returnType] NULL [tries] 2 [try] 0 [pDO] array ( ) [pDOStatement] false [state] '42S22' [code] 1054 [message] 'Unknown column \'c.CountCategories\' in \'field list\'' [uex] array ( )I tried a fresh install of 2.8.3 and then changed the config to point to my 2.2.1 database. It will display the posts from the old database but if I try forum/utility/update nothing happens. Any ideas how I can upgrade to 2.8.3? I'm totally stuck.
Thanks,
Michael
I tried this instead forum/index.php?p=/utility/update and it tried to update but I got the same 504 error and the debugger said the same thing: Unknown column 'c.CountCategories' in 'field list'
Any ideas on how to fix this issue?
In sql you can give short aliases so that you can use
alias.fieldname. In this case the GDN_Category table has the alias "c".That's a very good approach. After doing that you still need to run /utility/structure and also copy over the uploads folder, but updating that way is the cleanest approach
@R_J Thanks...so do you have any idea why the updater is failing to add the c.CountCategories field to my GDN_Category database? Is there some MYSQL security setting I should check? I didn't have any problems setting up a fresh install which created a GDN_Category table with the CountCategories field. I just can't get the updater to convert my 2.3 database over to the 2.8 format.
No, I cannot tell. I might be if I could see your database structure. But haven't you updated already by installing a fresh version?
Okay, drank some alcohol and haven't read your comment enough, sorry.
You have clearly stated that you cannot update. So even a fresh install fails utility/structure after you tweaked database settings?
Correct, I installed a clean version of 2.8.3 and then changed the config to point to the old 2.2.1 database and then tried to run the updater. I still got the same c.CountCategories error.
Could you please post a dump of your database schema. Only the structure, not the data.
Here is the schema for the GDN_Category table:
Field Type Null Key Default Extra
CategoryID int(11) NO PRI auto_increment
ParentCategoryID int(11) YES "" ""
TreeLeft int(11) YES "" ""
TreeRight int(11) YES "" ""
Depth int(11) YES "" ""
CountDiscussions int(11) NO "" 0 ""
CountComments int(11) NO "" 0 ""
DateMarkedRead datetime YES "" ""
AllowDiscussions tinyint(4) NO "" 1 ""
Archived tinyint(4) NO "" 0 ""
Name varchar(255) NO "" ""
UrlCode varchar(255) YES "" ""
Description varchar(500) YES "" ""
Sort int(11) YES "" ""
CssClass varchar(50) YES "" ""
Photo varchar(255) YES "" ""
PermissionCategoryID int(11) NO "" -1 ""
PointsCategoryID int(11) NO "" 0 ""
HideAllDiscussions tinyint(4) NO "" 0 ""
DisplayAs enum('Categories','Discussions','Heading','Default') NO "" Default ""
InsertUserID int(11) NO MUL ""
UpdateUserID int(11) YES "" ""
DateInserted datetime NO "" ""
DateUpdated datetime NO "" ""
LastCommentID int(11) YES "" ""
LastDiscussionID int(11) YES "" ""
LastDateInserted datetime YES "" ""
AllowedDiscussionTypes varchar(255) YES "" ""
DefaultDiscussionType varchar(10) YES "" ""
AllowFileUploads tinyint(4) NO "" 1 ""
Very strange that this column is missing. I assume you left out some version where this has been introduced.
In order to proceed I would suggest changing a core file slightly: open up /applications/vanilla/settings/structure.php and search for
if ($CategoryExists) { CategoryModel::instance()->rebuildTree(); CategoryModel::instance()->recalculateTree(); unset($CategoryModel); }It should be at line 105. Alter the first line of that part like that:
if ($CategoryExists && $CountCategoriesExists) { CategoryModel::instance()->rebuildTree(); CategoryModel::instance()->recalculateTree(); unset($CategoryModel); }After that run /utility/structure multiple times! Make sure your GDN_Category has the column CountCategories after that procedure.
The change is not harmful: you do not have to revert the changes in structure.php. And once that column is created, you will never face the problem again.
@R_J I tried your suggestion and it did get me past the c.CountCategories error. Now I'm getting a 500 error:
An error occurred while attempting to connect to the database
SQLSTATE[HY000] [2002] Connection refusedThe error occurred on or near: /home/gypsypicking/djangobooks.com/forum_test/library/database/class.database.php168: if ($timeout) { 169: throw new Exception(errorMessage('Timeout while connecting to the database', $this->ClassName, 'Connection', $ex->getMessage()), 504); 170: } 171: 172: throw new Exception(errorMessage('An error occurred while attempting to connect to the database', $this->ClassName, 'Connection', $ex->getMessage()), 500); 173: } 174: 175: return $pDO; 176: }Sorry for the delay. If everything else fails, directly alter your database. Use PHPMyAdmin or any similar tool and run the following command against your database:
ALTER TABLE GDN_Category ADD COLUMN `CountCategories` int(11) NOT NULL DEFAULT '0';@R_J the previous modification to the structure.php file seemed to have fixed the CountCategories issue. The problem now just seems to be a general inability of the updater script to run at all. I just get 504 errors. I even tried checking utility/structure on my 2.2 install and it suggested some database updates which I tried to run. Even those timed out, so I think there is some problem with database. I'm talking to my webhost and hoping they can find the issue.
When I go to utility/structure and scan I get this result:
alter table `GDN_Role`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_User`
-- [Existing: `LastIPAddress` varchar(15) null, New: `LastIPAddress` varbinary(16) null]
change `LastIPAddress` `LastIPAddress` varbinary(16) null,
-- [Existing: `InsertIPAddress` varchar(15) null, New: `InsertIPAddress` varbinary(16) null]
change `InsertIPAddress` `InsertIPAddress` varbinary(16) null,
-- [Existing: `UpdateIPAddress` varchar(15) null, New: `UpdateIPAddress` varbinary(16) null]
change `UpdateIPAddress` `UpdateIPAddress` varbinary(16) null,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserRole`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserMeta`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserPoints`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserAuthentication`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserAuthenticationProvider`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserAuthenticationNonce`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserAuthenticationToken`
-- [Existing: `ProviderKey` varchar(64) not null, New: `ProviderKey` varchar(50) not null]
change `ProviderKey` `ProviderKey` varchar(50) not null,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Session`
-- [Existing: `Attributes` mediumtext null, New: `Attributes` text null]
change `Attributes` `Attributes` text null;
alter table `GDN_Session`
add index IX_Session_DateExpires (`DateExpires`);
alter table `GDN_AnalyticsLocal`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Permission`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Permission`
add `Garden.Tokens.Add` tinyint not null default 0 after `Garden.Community.Manage`,
add `Garden.Uploads.Add` tinyint not null default 0 after `Garden.Tokens.Add`,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
update `GDN_Permission` `Permission`
set `Garden.Tokens.Add` = `Garden.Settings.Manage`,
`Garden.Uploads.Add` = '3'
where `RoleID` = '0'
and `JunctionTable` is null
and `JunctionColumn` is null;
update `GDN_Permission` `Permission`
set `Garden.Tokens.Add` = `Garden.Settings.Manage`,
`Garden.Uploads.Add` = 1
where `RoleID` <> '0'
and `JunctionTable` is null;
update `GDN_User` `User`
set `Permissions` = ''
where `Permissions` <> '';
alter table `GDN_Invitation`
add `DateAccepted` datetime null after `AcceptedUserID`,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_ActivityType`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Activity`
-- [Existing: `InsertIPAddress` varchar(15) 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;
alter table `GDN_ActivityComment`
-- [Existing: `InsertIPAddress` varchar(15) 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;
alter table `GDN_Message`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Tag`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Permission` change column `Plugins.Tagging.Add` `Vanilla.Tagging.Add` tinyint not null default 0;
alter table `GDN_Log`
-- [Existing: `RecordType` enum('Discussion','Comment','User','Registration','Activity','ActivityComment','Configuration','Group') not null, New: `RecordType` enum('Discussion','Comment','User','Registration','Activity','ActivityComment','Configuration','Group','Event') not null]
change `RecordType` `RecordType` enum('Discussion','Comment','User','Registration','Activity','ActivityComment','Configuration','Group','Event') not null,
-- [Existing: `RecordIPAddress` varchar(15) null, New: `RecordIPAddress` varbinary(16) null]
change `RecordIPAddress` `RecordIPAddress` varbinary(16) null,
-- [Existing: `InsertIPAddress` varchar(15) 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;
alter table `GDN_Regarding`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Ban`
-- [Existing: `InsertIPAddress` varchar(15) null, New: `InsertIPAddress` varbinary(16) null]
change `InsertIPAddress` `InsertIPAddress` varbinary(16) null,
-- [Existing: `UpdateIPAddress` varchar(15) null, New: `UpdateIPAddress` varbinary(16) null]
change `UpdateIPAddress` `UpdateIPAddress` varbinary(16) null,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Spammer`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Media`
add `Active` tinyint not null default 1 after `Size`,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Media`
add index IX_Media_InsertUserID (`InsertUserID`);
alter table `GDN_UserMerge`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserMergeItem`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Attachment`
-- [Existing: `InsertIPAddress` varchar(64) not null, New: `InsertIPAddress` varbinary(16) not null]
change `InsertIPAddress` `InsertIPAddress` varbinary(16) not null,
-- [Existing: `UpdateIPAddress` varchar(15) null, New: `UpdateIPAddress` varbinary(16) null]
change `UpdateIPAddress` `UpdateIPAddress` varbinary(16) null,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
create table `GDN_contentDraft` (
`draftID` int not null auto_increment,
`recordType` varchar(64) not null,
`recordID` int null,
`parentRecordID` int null,
`attributes` mediumtext not null,
`insertUserID` int not null,
`dateInserted` datetime not null,
`updateUserID` int not null,
`dateUpdated` datetime not null,
primary key (`draftID`),
index `IX_contentDraft_recordType` (`recordType`),
index `IX_contentDraft_insertUserID` (`insertUserID`),
index `IX_contentDraft_record` (`recordType`, `recordID`),
index `IX_contentDraft_parentRecord` (`recordType`, `parentRecordID`)
) engine=innodb default character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Category`
-- [Existing: `Depth` int null, New: `Depth` int not null default 0]
change `Depth` `Depth` int not null default 0,
add `CountCategories` int not null default 0 after `Depth`,
add `CountAllDiscussions` int not null default 0 after `CountDiscussions`,
add `CountAllComments` int not null default 0 after `CountComments`,
add `LastCategoryID` int not null default 0 after `CountAllComments`,
add `CanDelete` tinyint not null default 1 after `Archived`,
-- [Existing: `DisplayAs` enum('Categories','Discussions','Heading','Default') not null default 'Default', New: `DisplayAs` enum('Categories','Discussions','Flat','Heading','Default') not null default 'Discussions']
change `DisplayAs` `DisplayAs` enum('Categories','Discussions','Flat','Heading','Default') not null default 'Discussions',
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Category`
add key FK_Category_ParentCategoryID (`ParentCategoryID`);
alter table `GDN_Discussion`
-- [Existing: `InsertIPAddress` varchar(15) null, New: `InsertIPAddress` varbinary(16) null]
change `InsertIPAddress` `InsertIPAddress` varbinary(16) null,
-- [Existing: `UpdateIPAddress` varchar(15) null, New: `UpdateIPAddress` varbinary(16) null]
change `UpdateIPAddress` `UpdateIPAddress` varbinary(16) null,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserCategory`
add `Followed` tinyint not null default 0 after `DateMarkedRead`,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserDiscussion`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Comment`
-- [Existing: `InsertIPAddress` varchar(15) null, New: `InsertIPAddress` varbinary(16) null]
change `InsertIPAddress` `InsertIPAddress` varbinary(16) null,
-- [Existing: `UpdateIPAddress` varchar(15) null, New: `UpdateIPAddress` varbinary(16) null]
change `UpdateIPAddress` `UpdateIPAddress` varbinary(16) null,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserComment`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_User`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Draft`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Permission`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Permission`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_TagDiscussion`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Tag`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Conversation`
-- [Existing: `Contributors` varchar(255) not null, New: `Contributors` varchar(255) null]
change `Contributors` `Contributors` varchar(255) null,
-- [Existing: `InsertIPAddress` varchar(15) null, New: `InsertIPAddress` varbinary(16) null]
change `InsertIPAddress` `InsertIPAddress` varbinary(16) null,
-- [Existing: `UpdateIPAddress` varchar(15) null, New: `UpdateIPAddress` varbinary(16) null]
change `UpdateIPAddress` `UpdateIPAddress` varbinary(16) null,
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_UserConversation`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_ConversationMessage`
-- [Existing: `InsertIPAddress` varchar(15) 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;
alter table `GDN_User`
character set utf8mb4 collate utf8mb4_unicode_ci,
convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table `GDN_Permission`
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');
But if I run the script it just times out. I tried testing it with a fresh database and Vanilla will perform structure updates on it. So there's something about the working database I'm trying to update that is causing the script to time out. Any ideas? There are no error messages other than 504, even with the debuger on.
Go through them one by one. What you see is pure SQL so you can copy it and paste it in your favourite SQL tool.
SQL commands end with ";". I would guess that the slowest queries are those that concern Discussion, Comment and User table. So I would run them one by one and make small groups of the remaining commands