CiviCRM Community Forums (archive)

Try this. It's from a dump I did recently:

DROP TABLE IF EXISTS `civicrm_acl`;
CREATE TABLE `civicrm_acl` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique table ID',
  `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain owns this contact',
  `name` varchar(64) collate utf8_unicode_ci default NULL COMMENT 'ACL Name.',
  `deny` tinyint(4) NOT NULL default '0' COMMENT 'Is this ACL entry Allow  (0) or Deny (1) ?',
  `entity_table` varchar(64) collate utf8_unicode_ci NOT NULL COMMENT 'Table of the object possessing this ACL entry (Contact, Group, or ACL Group)',
  `entity_id` int(10) unsigned default NULL COMMENT 'ID of the object possessing this ACL',
  `operation` enum('All','View','Edit','Create','Delete','Grant','Revoke') collate utf8_unicode_ci NOT NULL COMMENT 'What operation does this ACL entry control?',
  `object_table` varchar(64) collate utf8_unicode_ci default NULL COMMENT 'The table of the object controlled by this ACL entry',
  `object_id` int(10) unsigned default NULL COMMENT 'The ID of the object controlled by this ACL entry',
  `acl_table` varchar(64) collate utf8_unicode_ci default NULL COMMENT 'If this is a grant/revoke entry, what table are we granting?',
  `acl_id` int(10) unsigned default NULL COMMENT 'ID of the ACL or ACL group being granted/revoked',
  `is_active` tinyint(4) default NULL COMMENT 'Is this property active?',
  PRIMARY KEY  (`id`),
  KEY `index_acl_id` (`acl_id`),
  KEY `FK_civicrm_acl_domain_id` (`domain_id`),
  CONSTRAINT `FK_civicrm_acl_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `civicrm_acl`
--

/*!40000 ALTER TABLE `civicrm_acl` DISABLE KEYS */;
/*!40000 ALTER TABLE `civicrm_acl` ENABLE KEYS */;


It adds the foreign key constraints, and has a few other minor differences. Are you using phpmyadmin?

-Brian

I'm understanding that you're moving your Joomla installation and DB from your local/testing server to the production server. And that the SQL error was received during import on the production server. So I would try running the SQL snippet from phpmyadmin on that server. You can save it as a text file and import on the Import tab, or may be able to enter through the query tab (depending on what version is running).

Ok, let's back up a bit. The problem is that CiviCRM isn't installing correctly on your remote server. And the installation process broke down when trying to run the SQL script that builds the CiviCRM tables. I was reading the description of your problem very superficially -- that the problem was just with the acl table. But I suspect none of the CiviCRM tables were created -- the acl table being the first table, that's where the breakdown happened, but it was not limited to that one table. Can you get into phpmyadmin on your remote and confirm? All the CiviCRM tables begin with civicrm_

If that's the case, the problem may be permissioning related on your server. The installation of CiviCRM crashes because it lacks the authorization (within Joomla) to run the sql scripts.

I have never personally experienced this problem, but I remember reading about this happening on some shared hosting environments. I probably read about the problem on the Joomla forums. To see if this is what's happening, try installing another extension in Joomla on your production server. The problem shouldn't be unique to CiviCRM -- it would likely affect any extension that has to create and populate a table on your server.

As I recall (memory fuzzy here), the issue is that your Joomla admin login somehow doesn't correspond to the admin-level access required by your hosting provider. In other words, your host is not recognizing your Joomla admin as authorized to run an SQL create table script on the MySQL database.

I'm bouncing around a bit, and shooting from the hip --
Did you install Joomla using Fantastico, or some other script installation tool built-in to your host's control panel? If so, did they ask you to create a MySQL username during installation? If you installed Joomla manually (uploading and unzipping the install file; navigating to that location through your browser and walking through the Joomla installation process), you would have needed to either install using the root user, or create a MySQL user/pwd separately and reference that in the installation process. In either case, use your host control panel (or phpmyadmin) to look at your MySQL users, and make sure whichever one that is used by Joomla has sufficient MySQL privileges. That may be what's causing the problem.

If you confirm that the problem is not unique to CiviCRM, but affects other extensions, and the MySQL user privileges are at sufficient level that they should be able to create tables, then try doing a search on the Joomla forums for this issue. It has to do with a disconnect between your Joomla user and MySQL user privileges.

Lastly, as a workaround, you could do the following --
1) Upload the components/com_civicrm and administrator/components/com_civicrm folders from your local installation to the remote host.
2) Export your Joomla db from your local installation using phpmyadmin. Create the database in phpmyadmin on your host and import this file into it. If you don't want all your Joomla tables affected -- just the CiviCRM tables -- you will need to open the file in a text editor and delete the jos_ tables. You may also need to do a find/replace in that file (with a text editor) to make sure the database name matches the database name on your host. Most hosts have restrictions on the database name, prefacing each name with the username of your account with them.
3) Update the civicrm.settings.php file in both of the above folders (in #1). There are two of these files, one in each directory.

This "should" allow you to get CiviCRM up and running on your host. However, it doesn't address the real issue, which is lack of permission to run SQL via Joomla. That's going to affect other extensions, and probably be a pain in the butt moving forward with your site development.

Hope this helps.
-Brian

I use Windows, so I'm on WAMP. And it's fine for multi-site development (I probably have ten sites currently setup on it). But I know nothing about the Mac equivalent.
-Brian