Installer fails with Cannot execute CREATE TABLE civicrm_acl_cache

Question

First, thanks to anyone who takes a look at an issue I can't get to the bottom of regarding a Civi 5.33.2 install.

The issue: When running the installer I get the following error:

Civi\Setup\Exception\SqlException: Cannot execute CREATE TABLE civicrm_acl_cache ( id int unsigned NOT NULL

There is more to the error but I believe I am stuck on permission issue. I seem to be missing a GRANT privilege but I can't find which one I'd need.

Installing CiviCRM 5.33.2 under Drupal 7.78. I have a separate Civi MySQL 5.7 database (running under Centos 8) from my Drupal database but I use the same user to access both databases. I've created the civicrm database as follows:

CREATE DATABASE civicrm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, TRIGGER, CREATE ROUTINE, ALTER ROUTINE ON civicrm.* TO 'accessuser'@'localhost' IDENTIFIED BY 'somepassword';

I have the CREATE privilege so it's odd to be told I can't CREATE a table. Any ideas?

Thanks

Answer 1

(Might not be an answer but posting as answer since this won't fit in a comment.)

See https://docs.civicrm.org/installation/en/latest/general/requirements/#mysql-permissions. You don't have REFERENCES permission.

What's the full error if you run the create table manually? It looks like this:

CREATE TABLE `civicrm_acl_cache` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
     `contact_id` int unsigned    COMMENT 'Foreign Key to Contact',
     `acl_id` int unsigned NOT NULL   COMMENT 'Foreign Key to ACL',
     `modified_date` timestamp NULL   COMMENT 'When was this cache entry last modified' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_contact_id`(
        contact_id
  )
  ,     INDEX `index_acl_id`(
        acl_id
  )
  ,     INDEX `index_modified_date`(
        modified_date
  )
  
,          CONSTRAINT FK_civicrm_acl_cache_acl_id FOREIGN KEY (`acl_id`) REFERENCES `civicrm_acl`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC  ;