DB Error: insufficient permissions

[brod17]

Hi,

I have ran into a strange problem after moving my CIVI installation to new test domain. All CIVI functions are working correctly except adding/modifying a contact which gives the above error. Looking at the debug it is still using the old database user from the original location, and not the new database user.
My entry in CIVI settings file is mysql://wordpress_2b:<password>@localhost/wordpress_2B?new_link=true where wordpress_2b is the SQL user and wordpress_2B is the new database.

I have confirmed this file is being used by CIVI in that changing the password to an incorrect one causes CIVI to fail.

As a test I did a fresh install of CIVI to wordpress_2B and was able to create/modify users using the SQL user wordpress_2b, however after importing the live database the error reappeared.

I have done a full search through phpmyadmin of the imported database and can find no reference to the wordpress_1 SQL user that it is still trying to use.

Have followed the wiki instructions for moving CIVI and reset the backend settings/cleared caches etc.

Any help much appreciated.

Rod

[Donald Lobo]

1. not seeing the error with wordpress_1 in it

2. does your db user have trigger / stored procedure permissions?

lobo

[brod17]

Hi,

Thanks for getting back to me.
The user wordpress_1  definately has trigger permissions as everything works correctly when I use a fresh CIVI install and a blank database still using the same wordpress_2b SQL user and wordpress_2B database.
I only get the error when I import my live database into wordpress_2B, with no other changes.

The exact debug error I see is:

Database Error Code: TRIGGER command denied to user 'wordpress_1'@'%' for table 'civicrm_contact', 1142
Additional Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> -26
    [message] => DB Error: insufficient permissions
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email , do_not_phone , do_not_mail , do_not_sms , do_not_trade , is_opt_out , external_identifier , sort_name , display_name , nick_name , preferred_communication_method , preferred_language , preferred_mail_format , hash , source , first_name , middle_name , last_name , prefix_id , suffix_id , email_greeting_id , email_greeting_custom , postal_greeting_id , postal_greeting_custom , addressee_id , addressee_custom , job_title , gender_id , birth_date , is_deceased ) VALUES ('Individual' ,  NULL ,  0 ,  0 ,  0 ,  0 ,  0 ,  0 ,  NULL , 'tes, test' , 'test tes' ,  NULL , '' , 'en_GB' , 'Both' , 'ee16ea145ff8417ea77c4693d00ef9e8' ,  NULL , 'test' ,  NULL , 'tes' ,  NULL ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  NULL ,  NULL ,  NULL ,  0 )  [nativecode=1142 ** TRIGGER command denied to user 'wordpress_1'@'%' for table 'civicrm_contact']
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email , do_not_phone , do_not_mail , do_not_sms , do_not_trade , is_opt_out , external_identifier , sort_name , display_name , nick_name , preferred_communication_method , preferred_language , preferred_mail_format , hash , source , first_name , middle_name , last_name , prefix_id , suffix_id , email_greeting_id , email_greeting_custom , postal_greeting_id , postal_greeting_custom , addressee_id , addressee_custom , job_title , gender_id , birth_date , is_deceased ) VALUES ('Individual' ,  NULL ,  0 ,  0 ,  0 ,  0 ,  0 ,  0 ,  NULL , 'tes, test' , 'test tes' ,  NULL , '' , 'en_GB' , 'Both' , 'ee16ea145ff8417ea77c4693d00ef9e8' ,  NULL , 'test' ,  NULL , 'tes' ,  NULL ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  NULL ,  NULL ,  NULL ,  0 )  [nativecode=1142 ** TRIGGER command denied to user 'wordpress_1'@'%' for table 'civicrm_contact']
    [to_string] => [db_error: message="DB Error: insufficient permissions" code=-26 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email , do_not_phone , do_not_mail , do_not_sms , do_not_trade , is_opt_out , external_identifier , sort_name , display_name , nick_name , preferred_communication_method , preferred_language , preferred_mail_format , hash , source , first_name , middle_name , last_name , prefix_id , suffix_id , email_greeting_id , email_greeting_custom , postal_greeting_id , postal_greeting_custom , addressee_id , addressee_custom , job_title , gender_id , birth_date , is_deceased ) VALUES ('Individual' ,  NULL ,  0 ,  0 ,  0 ,  0 ,  0 ,  0 ,  NULL , 'tes, test' , 'test tes' ,  NULL , '' , 'en_GB' , 'Both' , 'ee16ea145ff8417ea77c4693d00ef9e8' ,  NULL , 'test' ,  NULL , 'tes' ,  NULL ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  NULL ,  NULL ,  NULL ,  0 )  [nativecode=1142 ** TRIGGER command denied to user 'wordpress_1'@'%' for table 'civicrm_contact']"]
)

As you can see it is still using wordpress_1 as the SQL user not wordpress_2b.

Regards,

Rod

ps: Is a test system so I can email login username/password if needed.



[Donald Lobo]

1. might be easier to use the same DB user/pass on all machines

2. alternatively, you can take a backup without the triggers and get civi to rebuild the triggers from the admin menu

lobo

[brod17]

Cheers Donald, your tip to backup the database skipping the triggers solved the problem.

Thanks once again for your help.