Migration issues going from 2.0.14 (!) to 3.3

Comments

• R_J Admin

  April 2020

  In such a case I would always recommend to set up a brand new forum and then point the database of that new installation to the old database. Running utility/structure in the new forum should convert the database to what is needed.

  0
• martz New

  April 2020 edited April 2020

  Hi R_J, thanks for answering.

  OK, I've just installed 3.3 and pointed the config at the old database. Unfortunately the same error occurs:

  
  

  Fatal Error in Gdn_MySQLStructure._defineColumn();
  The specified data type (int unsigned) is not accepted for the MySQL database.
  The error occurred on or near: /var/www/html/package/library/database/class.mysqlstructure.php
  
  756:       }
  
  757:     }
  
  758: 
  
  759:     if (!in_array($column->Type, $validColumnTypes)) {
  
  760:       throw new Exception(sprintf(t('The specified data type (%1$s) is not accepted for the MySQL database.'), $column->Type));
  
  761:     }
  
  762: 
  
  763:     $return = "`{$column->Name}` ";
  
  764: 
  
  Backtrace:
  
  [/var/www/html/package/library/database/class.mysqlstructure.php:571] Gdn_MySQLStructure->_defineColumn();
  
  [/var/www/html/package/library/database/class.databasestructure.php:507] Gdn_MySQLStructure->_modify();
  
  [/var/www/html/package/applications/dashboard/settings/structure.php:792] Gdn_DatabaseStructure->set();
  
  [/var/www/html/package/applications/dashboard/models/class.updatemodel.php:698] PHP::include();
  
  [/var/www/html/package/applications/dashboard/models/class.updatemodel.php:660] UpdateModel->runStructureInternal();
  
  [/var/www/html/package/applications/dashboard/controllers/class.utilitycontroller.php:292] UpdateModel->runStructure();
  
  [/var/www/html/package/applications/dashboard/controllers/class.utilitycontroller.php:330] UtilityController->legacyUpdate();
  
  [/var/www/html/package/library/core/class.dispatcher.php:872] UtilityController->update();
  
  [/var/www/html/package/library/core/class.dispatcher.php:289] Gdn_Dispatcher->dispatchController();
  
  [/var/www/html/package/index.php:29] Gdn_Dispatcher->dispatch();
  
  Need Help?
  
  If you are a user of this website, you can report this message to a website administrator.
  
  If you are an administrator of this website, you can get help at the Vanilla Community Forums.
  Additional information for support personnel:
  
    Application: Vanilla
    Application Version: 3.3
    PHP Version: 7.3.16
    Operating System: Linux
    Server Software: Apache/2.4.41 (Fedora)
    User Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:74.0) Gecko/20100101 Firefox/74.0
    Request Uri: /package/utility/update
    Controller: Gdn_MySQLStructure
    Method: _defineColumn
  
  MySQL server version: mysqld 8.0.19 MySQL Community Server
  mysqlnd 5.0.12-dev - 20150407  
  
  

  
  

  0
• R_J Admin

  April 2020

  :-(

  Okay, then it will be more work. The most interesting part in the above (I have edited it to make it more readable) s this:

  The specified data type (int unsigned) is not accepted for the MySQL database.
  

  One or more of the columns in your database are of type "int unsigned" when they shouldn't be. I would suggest to dump the scheme of your database and do a fulltext search. Check why those columns are there and try to convert them to signed integers.

  This is somewhat hacky, but I assume some plugins have created those columns and in the end you might not even need them at all.

  0
• martz New

  April 2020

  Hi RJ,

  Thanks! In the time begin I've increased time out settings on Apache and was able to see what goes wrong with the first update try. Before I could not see it because of the 504 error that was returned by Apache. The unsigned int error is shown when I try for a second time.

  It tells something else, maybe an unexpected legacy value is stopping the update process?

  Fatal Error in PHP.gdn_ErrorHandler();

  Unknown column 'p.Garden.Email.Manage' in 'field list'

  The error occurred on or near: /var/www/html/package/library/database/class.database.php

  
  

  417:         if (!$message) {

  
  

  418:           $message = $ex->getMessage();

  
  

  419:         }

  
  

  420: 

  
  

  421:         trigger_error($message, E_USER_ERROR);

  
  

  422:       }

  
  

  423: 

  
  

  424:     }

  
  

  425: 

  
  

  Backtrace:

  
  

  /var/www/html/package/library/database/class.database.phpPHP::gdn_ErrorHandler();

  
  

  [/var/www/html/package/library/database/class.database.php:421] PHP::trigger_error();

  
  

  [/var/www/html/package/library/database/class.sqldriver.php:1707] Gdn_Database->query();

  
  

  [/var/www/html/package/library/database/class.sqldriver.php:664] Gdn_SQLDriver->query();

  
  

  [/var/www/html/package/applications/dashboard/models/class.permissionmodel.php:543] Gdn_SQLDriver->get();

  
  

  [/var/www/html/package/applications/dashboard/models/class.usermodel.php:5057] PermissionModel->cachePermissions();

  
  

  [/var/www/html/package/library/core/class.session.php:499] UserModel->getPermissions();

  
  

  [/var/www/html/package/applications/dashboard/models/class.updatemodel.php:703] Gdn_Session->start();

  
  

  [/var/www/html/package/applications/dashboard/models/class.updatemodel.php:660] UpdateModel->runStructureInternal();

  
  

  [/var/www/html/package/applications/dashboard/controllers/class.utilitycontroller.php:292] UpdateModel->runStructure();

  
  

  [/var/www/html/package/applications/dashboard/controllers/class.utilitycontroller.php:330] UtilityController->legacyUpdate();

  
  

  [/var/www/html/package/library/core/class.dispatcher.php:872] UtilityController->update();

  
  

  [/var/www/html/package/library/core/class.dispatcher.php:289] Gdn_Dispatcher->dispatchController();

  
  

  [/var/www/html/package/index.php:29] Gdn_Dispatcher->dispatch();

  
  

  Variables in local scope:

  
  

  [sql] 'select MAX(p.`PermissionID`) as `PermissionID`, MAX(p.`Garden.Email.Manage`) as `Garden.Email.Manage`, MAX(p.`Garden.Settings.Manage`) as `Garden.Settings.Manage`, MAX(p.`Garden.Routes.Manage`) as `Garden.Routes.Manage`, MAX(p.`Garden.Messages.Manage`) as `Garden.Messages.Manage`, MAX(p.`Garden.Applications.Manage`) as `Garden.Applications.Manage`, MAX(p.`Garden.Plugins.Manage`) as `Garden.Plugins.Manage`, MAX(p.`Garden.Themes.Manage`) as `Garden.Themes.Manage`, MAX(p.`Garden.SignIn.Allow`) as `Garden.SignIn.Allow`, MAX(p.`Garden.Registration.Manage`) as `Garden.Registration.Manage`, MAX(p.`Garden.Applicants.Manage`) as `Garden.Applicants.Manage`, MAX(p.`Garden.Roles.Manage`) as `Garden.Roles.Manage`, MAX(p.`Garden.Users.Add`) as `Garden.Users.Add`, MAX(p.`Garden.Users.Edit`) as `Garden.Users.Edit`, MAX(p.`Garden.Users.Delete`) as `Garden.Users.Delete`, MAX(p.`Garden.Users.Approve`) as `Garden.Users.Approve`, MAX(p.`Garden.Activity.Delete`) as `Garden.Activity.Delete`, MAX(p.`Garden.Activity.View`) as `Garden.Activity.View`, MAX(p.`Garden.Profiles.View`) as `Garden.Profiles.View`, MAX(p.`Vanilla.Settings.Manage`) as `Vanilla.Settings.Manage`, MAX(p.`Vanilla.Categories.Manage`) as `Vanilla.Categories.Manage`, MAX(p.`Vanilla.Spam.Manage`) as `Vanilla.Spam.Manage`, MAX(p.`Vanilla.Discussions.View`) as `Vanilla.Discussions.View`, MAX(p.`Vanilla.Discussions.Add`) as `Vanilla.Discussions.Add`, MAX(p.`Vanilla.Discussions.Edit`) as `Vanilla.Discussions.Edit`, MAX(p.`Vanilla.Discussions.Announce`) as `Vanilla.Discussions.Announce`, MAX(p.`Vanilla.Discussions.Sink`) as `Vanilla.Discussions.Sink`, MAX(p.`Vanilla.Discussions.Close`) as `Vanilla.Discussions.Close`, MAX(p.`Vanilla.Discussions.Delete`) as `Vanilla.Discussions.Delete`, MAX(p.`Vanilla.Comments.Add`) as `Vanilla.Comments.Add`, MAX(p.`Vanilla.Comments.Edit`) as `Vanilla.Comments.Edit`, MAX(p.`Vanilla.Comments.Delete`) as `Vanilla.Comments.Delete`, MAX(p.`Plugins.Debugger.View`) as `Plugins.Debugger.View`, MAX(p.`Plugins.Debugger.Manage`) as `Plugins.Debugger.Manage`, MAX(p.`Plugins.Pockets.Manage`) as `Plugins.Pockets.Manage`, p.JunctionTable as `JunctionTable`, p.JunctionColumn as `JunctionColumn`, p.JunctionID as `JunctionID`

  from `GDN_Permission` `p`

  where `p`.`RoleID` = :pRoleID

  group by `p`.`JunctionTable`, `p`.`JunctionColumn`, `p`.`JunctionID`'

  
  

  [inputParameters] array (

   ':pRoleID' => 2,

  )

  
  

  [options] array (

   'Type' => 'select',

   'Slave' => NULL,

   'ReturnType' => 'DataSet',

  )

  
  

  [returnType] 'DataSet'

  
  

  [tries] 2

  
  

  [try] 0

  
  

  [pDO] array (

  )

  
  

  [pDOStatement] false

  
  

  [state] '42S22'

  
  

  [code] 1054

  
  

  [message] 'Unknown column \'p.Garden.Email.Manage\' in \'field list\''

  
  

  [ex] array (

  )

  
  

  Need Help?

  
  

  If you are a user of this website, you can report this message to a website administrator.

  
  

  If you are an administrator of this website, you can get help at the Vanilla Community Forums.

  Additional information for support personnel:

  
  

    Application: Vanilla

    Application Version: 3.3

    PHP Version: 7.3.16

    Operating System: Linux

    Server Software: Apache/2.4.41 (Fedora)

    User Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:74.0) Gecko/20100101 Firefox/74.0

    Request Uri: /package/utility/update

    Controller: PHP

    Method: gdn_ErrorHandler

  0
• martz New

  April 2020

  Additional logging:

  03-Apr-2020 12:45:13 UTC] PHP Warning: Error running structure: Unknown column 'p.Garden.Email.Manage' in 'field list' in /var/www/html/package/applications/dashboard/models/class.updatemodel.php on line 706

  [03-Apr-2020 12:45:13 UTC] PHP Fatal error: Unknown column 'p.Garden.Email.Manage' in 'field list' in /var/www/html/package/library/database/class.database.php on line 421

  0
• R_J Admin

  April 2020

  The Garden.Email.Manage is a permission. Just a shot in the dark: the permission is missing but required for running /utility/structure. Create it manually by copying a column in the GDN_Permission table and name it "Garden.Email.Manage". It would be best to copy a column which has restrictive permissions so not to allow too much

  0
• martz New

  April 2020

  Update succeeded!

  You pointed me in the right direction. Before running update I dropped all the 'legacy' columns in the Permission table. I determined what the legacy columns were by comparing them to a 'Vanilla' 3.3 install.

  The 3.3 forum has all the content and seems to work fine!

  I could not run structure as the admin password is not accepted somehow. Also images are be broken, but I think someone else solved that and posted about it. Will look into that and I will try to somehow reset the admin user password.

  Thanks!

  
  

  p.s. this is the script that I used

  ALTER TABLE `vanilladb`.`GDN_Permission` 

  DROP COLUMN `Plugins.Pockets.Manage`,

  DROP COLUMN `Plugins.Debugger.Manage`,

  DROP COLUMN `Plugins.Debugger.View`,

  DROP COLUMN `Vanilla.Spam.Manage`,

  DROP COLUMN `Vanilla.Categories.Manage`,

  DROP COLUMN `Vanilla.Settings.Manage`,

  DROP COLUMN `Garden.Roles.Manage`,

  DROP COLUMN `Garden.Applicants.Manage`,

  DROP COLUMN `Garden.Registration.Manage`,

  DROP COLUMN `Garden.Themes.Manage`,

  DROP COLUMN `Garden.Plugins.Manage`,

  DROP COLUMN `Garden.Applications.Manage`,

  DROP COLUMN `Garden.Messages.Manage`,

  DROP COLUMN `Garden.Routes.Manage`,

  DROP COLUMN `Garden.Email.Manage`;

  2
• Kailash New

  October 2021

  I am importing data from SMF 2.0.18 version and when I attempt to import in Vanilla, I get following error:

  There was an error while trying to create the Media table (The specified data type (smallint unsigned) is not accepted for the MySQL database.).

  How can I fix it? Importing data is stopped at "Define Tables" steps.

  Thanks,

  Kailash

  0
• timanderson New

  January 2022

  See https://github.com/vanilla/vanilla/issues/11150

  Tim

  0