search

oroinc / platform

Main OroPlatform package with core functionality.
Other
627 stars 351 forks source link

4.1.7 Upgrade fails to run migrations on Amazon's RDS #1036

Open jimohalloran opened 4 years ago

jimohalloran commented 4 years ago

Summary
I've got a OroCRM installation running 4.1.5, which has just been upgraded to 4.1.7. This worked fine locally in a docker environment running MySQL 5.7.28. When I roll the same code out to my staging environment (running MySQL 5.7.25 hosted on Amazon's RDS) the migrations fail with the following error message:

` The application meets all mandatory requirements
Process migrations...

Oro\Bundle\EntityExtendBundle\Migration\LoadEntityConfigStateMigration
Oro\Bundle\ScopeBundle\Migrations\Schema\v1_1\AddRowHashColumn
ERROR: An exception occurred while executing 'DROP TRIGGER IF EXISTS or
o_scope_fill_row_hash_trigger;
CREATE TRIGGER oro_scope_fill_row_hash_trigger BEFORE INSERT ON oro_scope
FOR EACH ROW
BEGIN
SET NEW.row_hash = MD5(CONCAT(COALESCE(NEW.localization_id, '0'), ':
', COALESCE(NEW.organization_id, '0'), ':', COALESCE(NEW.user_id, '0'))
) ;
END;':

SQLSTATE[HY000]: General error: 1419 You do not have the SUPER privilege an
d binary logging is enabled (you might want to use the less safe logbin
trust_function_creators variable)
`

Steps to reproduce
Run migrations on a MySQL instance hosted on RDS. RDS does not and can not grant SUPER privileges.

On both my local environment and RDS, the migrations are run as a user which has all rights to the specific database in which OroCRM is installed. The following SQL was used to create the user:

GRANT ALL ON `orocrm`.* TO `orocrm`@`%` IDENTIFIED BY 'AReallyLongAndRandomPasswordHere';

These users have had no issues previously running migrations or accessing the OroCRM application.

Actual Result Running oro:platform:update fails with the error message above when migrations are run on a database instance hosted on RDS.

Expected Result
Migrations should not fail.

Details about your environment

Additional information

RDS can not grant the SUPER privilege, so features in Oro Platform that require the privilege are unusable. Additionally, the upgrade documentation for Oro platform do not mention the additional priveleges required when upgrading between 4.1.5 and 4.1.7.

Complete output from the failed deployment is below, but the important part appears above:

  The command "sudo -u apache /usr/bin/php /var/www/vhosts/staging/releases/7  
  9/oro/bin/console oro:platform:update --no-interaction --env prod --force -  
  -timeout=600" failed.                                                        

  Exit Code: 1 (General error)                                                 

  Host Name: staging                                                           

  ================                                                             
  Check system requirements                                                    
  +---------+----------------------------------------------------------------  
  -----------------+                                                           
  | Check   | Optional recommendations                                         
                   |                                                           
  +---------+----------------------------------------------------------------  
  -----------------+                                                           
  | WARNING | Disable Phar extension to reduce the risk of PHP unserializatio  
  n vulnerability. |                                                           
  +---------+----------------------------------------------------------------  
  -----------------+                                                           
  The application meets all mandatory requirements                             
  Process migrations...                                                        
    > Oro\Bundle\EntityExtendBundle\Migration\LoadEntityConfigStateMigration   
    > Oro\Bundle\ScopeBundle\Migrations\Schema\v1_1\AddRowHashColumn           
      ERROR: An exception occurred while executing 'DROP TRIGGER IF EXISTS or  
  o_scope_fill_row_hash_trigger;                                               
  CREATE TRIGGER oro_scope_fill_row_hash_trigger BEFORE INSERT ON oro_scope    
  FOR EACH ROW                                                                 
   BEGIN                                                                       
      SET NEW.row_hash =  MD5(CONCAT(COALESCE(NEW.`localization_id`, '0'), ':  
  ', COALESCE(NEW.`organization_id`, '0'), ':', COALESCE(NEW.`user_id`, '0'))  
  ) ;                                                                          
   END;':                                                                      

  SQLSTATE[HY000]: General error: 1419 You do not have the SUPER privilege an  
  d binary logging is enabled (you *might* want to use the less safe log_bin_  
  trust_function_creators variable)                                            
    > Oro\Bundle\ScopeBundle\Migrations\Schema\v1_1\AddRowHashUniqueIndex - s  
  kipped                                                                       
    > Oro\Bundle\AttachmentBundle\Migrations\Schema\v1_8_1\OroAttachmentBundl  
  e - skipped                                                                  
    > Oro\Bundle\MessageQueueBundle\Migrations\Schema\v1_8_1\MessageQueueBund  
  le - skipped                                                                 
    > Oro\Bundle\CalendarBundle\Migrations\Schema\v1_21\UpdateCalendarEventFi  
  elds - skipped                                                               
    > Oro\Bundle\CalendarBundle\Migrations\Schema\v1_21\UpdateCalendarEventIn  
  dexes - skipped                                                              
    > Oro\Bundle\AccountBundle\Migrations\Schema\v1_14_1\UpdateAccountEntityC  
  onfig - skipped                                                              
    > Oro\Bundle\OAuth2ServerBundle\Migrations\Schema\v1_4\AddAuthCodeTable -  
   skipped                                                                     
    > Oro\Bundle\MigrationBundle\Migration\UpdateBundleVersionMigration        
    > Oro\Bundle\EntityExtendBundle\Migration\RefreshExtendCacheMigration - s  
  kipped                                                                       
    > Oro\Bundle\EntityConfigBundle\Migration\UpdateEntityConfigMigration - s  
  kipped                                                                       
    > Oro\Bundle\EntitySerializedFieldsBundle\Migration\SerializedDataMigrati  
  on - skipped                                                                 
    > Oro\Bundle\EntityExtendBundle\Migration\UpdateExtendConfigMigration - s  
  kipped                                                                       
    > Oro\Bundle\ActivityContactBundle\Migration\ActivityContactMigration - s  
  kipped                                                                       
    > Oro\Bundle\ActivityListBundle\Migration\ActivityListMigration - skipped  
    > Oro\Bundle\EntityExtendBundle\Migration\UpdateExtendIndicesMigration -   
  skipped                                                                      
    > Oro\Bundle\EntityConfigBundle\Migration\WarmUpEntityConfigCacheMigratio  
  n - skipped                                                                  
    > Oro\Bundle\ScopeBundle\Migration\Schema\UpdateScopeRowHashColumn - skip  
  ped                                                                          

  In MigrationExecutor.php line 116:                                           

    Failed migrations: Oro\Bundle\ScopeBundle\Migrations\Schema\v1_1\AddRowHa  
  sh                                                                           
    Column.                                                                    

  oro:migration:load [--force] [--dry-run] [--show-queries] [--bundles [BUNDL  
  ES]] [--exclude [EXCLUDE]] [--timeout [TIMEOUT]] [-h|--help] [-q|--quiet] [  
  -v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interacti  
  on] [-e|--env ENV] [--no-debug] [--disabled-listeners DISABLED-LISTENERS] [  
  --current-user CURRENT-USER] [--current-organization CURRENT-ORGANIZATION]   
  [--] <command>
anyt commented 4 years ago

Hi @jimohalloran,

The upgrade guide and the documentation will be updated soon, thanks for pointing it's out.

The privilege to use triggers is the new requirement. You have to enable it for your database user. For RDS please see https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/

jimohalloran commented 4 years ago

Just to be 100% clear here, log_bin_trust_function_creators isn't a privilege required to run triggers as you suggest. It's a feature designed to prevent developers from running non-deterministic functions that could break replication, causing replicated data to be different from the master. Ref: Stored Program Binary Logging Whether that's relevant on RDS, and whether that can be mitigated with a different binlog_format setting ('MIXED` seems like it'd do it) isn't clear. But either way, an undocumented requirement on MySQL configuration that changes part way through an LTS cycle isn't cool.

In terms of documentation, this change was in 4.1.6 from what I can tell. 4.1.6 was released 2 months ago (2nd of July, it's not 2nd of September). Surely it's not unreasonable to expect that it takes less than 2 months to update the upgrade guide? Updating it should be part of the release process.

anyt commented 4 years ago

4.1.6 was released 2 months ago

Yes, this is definitely our fault. We update the documentation a few times a week. The above change was not just described properly. We have the requirement for all tasks to update the documentation, but guys just forget about it, sometimes it happens. We'll fix it.