CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied
Pages: [1]

Author Topic: Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied  (Read 4605 times)

nihow

  • Guest
Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied
April 08, 2009, 06:44:12 am
Hi all,

I'm moving a production site to a live server, and I'm currently trying to import the MySQL DB from the production site into the new DB via PHPMyAdmin. I get the following error though:

Code: [Select]
Error
SQL query:
--
-- Triggers `civicrm_contact`
--
DROP TRIGGER IF EXISTS `civicrm`.`civicrm_contact_before_insert` ;
MySQL said: #1227 - Access denied; you need the SUPER privilege for this operation
My SQL skills are pretty basic. Can anyone assist me to get past this point? The production server is a CPanel server, but I'm happy to work in MySQL from the command line.

Regards,
Nick Howlett

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied
April 08, 2009, 08:23:22 am

I assume u used the internationalization features on your test site. To use these feature you need TRIGGER support (and hence the super privileges) which u dont seem to have on your live server.

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

nihow

  • Guest
Re: Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied
April 08, 2009, 10:42:59 pm
Quote from: Donald Lobo on April 08, 2009, 08:23:22 am

I assume u used the internationalization features on your test site. To use these feature you need TRIGGER support (and hence the super privileges) which u dont seem to have on your live server.

lobo


Yes I do. The production site is on a VPS under my control. As someone with little experience with MySQL, I'm not sure how to get implement TRIGGER support or grant SUPER privileges, and the info I've googled so far hasn't helped me. Any pointers?

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied
April 09, 2009, 02:47:13 pm
Since none of us can know everything - Google is a good friend :-)

i googled this:
site: mysql.com how to grant super privilege

and got this:
http://dev.mysql.com/doc/refman/5.1/en/grant.html

... should help get you going.
Protect your investment in CiviCRM by  becoming a Member!

nihow

  • Guest
Re: Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied
April 09, 2009, 04:59:40 pm
Quote from: Dave Greenberg on April 09, 2009, 02:47:13 pm
Since none of us can know everything - Google is a good friend :-)

i googled this:
site: mysql.com how to grant super privilege

and got this:
http://dev.mysql.com/doc/refman/5.1/en/grant.html

... should help get you going.

What about upgrading MySQL from 5.0.67 to the latest stable build of 5.1? I read that it is possible to GRANT CREATE TRIGGER?

nihow

  • Guest
Re: Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied
April 09, 2009, 05:19:57 pm
Can I make a suggestion, Dave and Donald?

It might be a good idea to more widely advertise the full requirements, including the MySQL privileges needed, more prominently in the documentation and at the download page, perhaps even requesting people to check if their host supports all of CiviCRM's requirements. I imagine that many shared hosts will either not let you install an InnoDB database, or grant SUPER privileges.

That will prevent angst and timewasting by CiviCRM users such as myself, with limited SQL skills. Not everyone has access to the kind of resources that are probably needed - dedicated developer, hosting, $$ to pay a developer etc.

As someone working in the developing world, this is my experience, at least.

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied
April 09, 2009, 06:13:37 pm

1. check http://civicrm.org/aboutcivicrm (this comes up as the first link for civicrm requirements). I updated it to include the SUPER requirements for multi-language installs. If you think the requirements need to be in other places also, please let us know what pages. Note that the wiki is user editable, so please feel free to make those changes (or point to the above page)

2. The checkbox to enable multiple languages is NOT shown if the db user does not have SUPER privileges. While you had the privileges on your local machine you did not have it on your VPS.

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

nihow

  • Guest
Re: Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied
April 10, 2009, 11:31:42 pm
I think your change is an improvement. That's the first place I would look. It probably needs to be at http://civicrm.org/download and on the Sourceforge download page too.
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • Can't transfer CiviCRM DB to live site - MySQL error #1227 - Access denied

This forum was archived on 2017-11-26.