Installation fails with foreign key constraint error

[kolbyjack]

I'm trying to set up an semaphore docker stack using the docker-compose file from #148 (comment), but when I run docker-compose up, the container fails with

Error 1005: Can't create table `semaphore`.`task__output` (errno: 150 "Foreign key constraint is incorrectly formed")

[kolbyjack]

After updating my stack to use mysql:5.6 instead of mariadb:10, it started working.

[fiftin]

Thank you. I think it is a bug. I will try reproduce it.

[atroxes]

Experienced the same error when trying to do a regular installation on Rocky Linux 8.4.

Problem appeared when using "MariaDB-server-10.6.4-1.el8.x86_64" from the official MariaDB CentOS repository.

Seeing @kolbyjack had succeeded using MySQL 5.6 I figured it might be a new feature/change/deprecation in MariaDB and I tried going back to "mariadb-server-3:10.3.28-1.module+el8.4.0+427+adf35707.x86_64" from the Rocky Linux repos. Also made sure to remove the MariaDB data directory contents before switching to 10.3.28.

Error disappeared and Semaphore server is now starting up without issue.

[nbkgroup]

Same error on Ubuntu 20.04.3 LTS using 10.6.4+maria~focal.

Running DB Migrations..
Checking DB migrations
Creating migrations table
Executing migration v0.0.0 (at 2021-09-01 13:03:25.666791794 +0300 MSK m=+26.535953079)...
 [12/0]9]
Executing migration v1.0.0 (at 2021-09-01 13:03:25.805213194 +0300 MSK m=+26.674374502)...
 [6/200]WARN[0026] 
 ERR! Query: create table `task__output`
(
    task_id int not null
        references task
            on delete cascade,
    task varchar(255) not null,
    time datetime not null,
    output longtext not null
)

 
Rolling back v1.0.0 (time: 2021-09-01 13:03:25.884467679 +0300 MSK m=+26.753628992)...
Rollback SQL does not exist.

Database migrations failed!
 Error 1005: Can't create table `semaphore`.`task__output` (errno: 150 "Foreign key constraint is incorrectly formed")

[HuTaeh]

Same error on Debian 11.0 using 10.5.11-MariaDB

Running DB Migrations..
Checking DB migrations
Creating migrations table
Executing migration v0.0.0 (at 2021-09-14 11:48:51.809117054 +0200 CEST m=+375.572080452)...
 [12/0]9]
Executing migration v1.0.0 (at 2021-09-14 11:48:52.028492988 +0200 CEST m=+375.791456387)...
 [6/200]WARN[0375] 
 ERR! Query: create table `task__output`
(
    task_id int not null
        references task
            on delete cascade,
    task varchar(255) not null,
    time datetime not null,
    output longtext not null
)

 
Rolling back v1.0.0 (time: 2021-09-14 11:48:52.14387883 +0200 CEST m=+375.906842248)...
Rollback SQL does not exist.

[fiftin]

Thank you. I will check MariaDB

[ziptx]

Troubleshooting detail:

Environment:  NAME="AlmaLinux" / VERSION="8.4 (Electric Cheetah)" / ID="almalinux" / ID_LIKE="rhel centos fedora" / VERSION_ID="8.4"

What Works:
semaphore 2.7.16 setup with MariaDB 10.4

Completed a mysqldump of 10.4.21-MariaDB after a fresh setup from semaphore_2.7.16_linux_amd64.rpm.  [Dump script below]
Applied the sqldump to a fresh install of 10.6.4-MariaDB ... NO ERRORs.   

The failure seems to be related to the actual migration script itself ... near v1.0.0 as documented by HuTaeh.

The bug surfaces with the use of MariaDB 10.6+ during 'setup'.

==

-- MariaDB dump 10.19  Distrib 10.4.21-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: semaphore
-- ------------------------------------------------------
-- Server version       10.4.21-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `access_key`
--

DROP TABLE IF EXISTS `access_key`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `access_key` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `type` varchar(255) NOT NULL,
 `project_id` int(11) DEFAULT NULL,
 `secret` text DEFAULT NULL,
 `removed` tinyint(1) DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `project_id` (`project_id`),
 CONSTRAINT `access_key_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `event`
--

DROP TABLE IF EXISTS `event`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event` (
 `project_id` int(11) DEFAULT NULL,
 `object_id` int(11) DEFAULT NULL,
 `object_type` varchar(20) DEFAULT '',
 `description` text DEFAULT NULL,
 `created` datetime NOT NULL,
 `user_id` int(11) DEFAULT NULL,
 KEY `project_id` (`project_id`),
 KEY `object_id` (`object_id`),
 KEY `created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `migrations`
--

DROP TABLE IF EXISTS `migrations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `migrations` (
 `version` varchar(255) NOT NULL,
 `upgraded_date` datetime DEFAULT NULL,
 `notes` text DEFAULT NULL,
 PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `project`
--

DROP TABLE IF EXISTS `project`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `created` datetime NOT NULL,
 `name` varchar(255) NOT NULL,
 `alert` tinyint(1) NOT NULL DEFAULT 0,
 `alert_chat` varchar(10) DEFAULT '',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `project__environment`
--

DROP TABLE IF EXISTS `project__environment`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project__environment` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `project_id` int(11) NOT NULL,
 `password` varchar(255) DEFAULT NULL,
 `json` longtext NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `removed` tinyint(1) DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `project_id` (`project_id`),
 CONSTRAINT `project__environment_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `project__inventory`
--

DROP TABLE IF EXISTS `project__inventory`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project__inventory` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `project_id` int(11) NOT NULL,
 `type` varchar(255) NOT NULL,
 `inventory` longtext NOT NULL,
 `ssh_key_id` int(11) DEFAULT NULL,
 `name` varchar(255) DEFAULT NULL,
 `removed` tinyint(1) DEFAULT 0,
 `become_key_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `project_id` (`project_id`),
 CONSTRAINT `project__inventory_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `project__repository`
--

DROP TABLE IF EXISTS `project__repository`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project__repository` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `project_id` int(11) NOT NULL,
 `git_url` text NOT NULL,
 `ssh_key_id` int(11) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `removed` tinyint(1) DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `project_id` (`project_id`),
 KEY `ssh_key_id` (`ssh_key_id`),
 CONSTRAINT `project__repository_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE,
 CONSTRAINT `project__repository_ibfk_2` FOREIGN KEY (`ssh_key_id`) REFERENCES `access_key` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `project__schedule`
--

DROP TABLE IF EXISTS `project__schedule`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project__schedule` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `template_id` int(11) DEFAULT NULL,
 `project_id` int(11) NOT NULL,
 `cron_format` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `project__template`
--

DROP TABLE IF EXISTS `project__template`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project__template` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `project_id` int(11) NOT NULL,
 `inventory_id` int(11) NOT NULL,
 `repository_id` int(11) NOT NULL,
 `environment_id` int(11) DEFAULT NULL,
 `playbook` varchar(255) NOT NULL,
 `arguments` text DEFAULT NULL,
 `override_args` tinyint(1) NOT NULL DEFAULT 0,
 `alias` varchar(100) DEFAULT NULL,
 `description` longtext DEFAULT NULL,
 `removed` tinyint(1) NOT NULL DEFAULT 0,
 `vault_pass_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `project_id` (`project_id`),
 KEY `inventory_id` (`inventory_id`),
 KEY `repository_id` (`repository_id`),
 KEY `environment_id` (`environment_id`),
 CONSTRAINT `project__template_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE,
 CONSTRAINT `project__template_ibfk_3` FOREIGN KEY (`inventory_id`) REFERENCES `project__inventory` (`id`),
 CONSTRAINT `project__template_ibfk_4` FOREIGN KEY (`repository_id`) REFERENCES `project__repository` (`id`),
 CONSTRAINT `project__template_ibfk_5` FOREIGN KEY (`environment_id`) REFERENCES `project__environment` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `project__user`
--

DROP TABLE IF EXISTS `project__user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project__user` (
 `project_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `admin` tinyint(1) NOT NULL DEFAULT 0,
 UNIQUE KEY `project_id` (`project_id`,`user_id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `project__user_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE,
 CONSTRAINT `project__user_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `session`
--

DROP TABLE IF EXISTS `session`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `session` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `created` datetime NOT NULL,
 `last_active` datetime NOT NULL,
 `ip` varchar(39) NOT NULL DEFAULT '',
 `user_agent` text NOT NULL,
 `expired` tinyint(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `expired` (`expired`),
 KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `task`
--

DROP TABLE IF EXISTS `task`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `task` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `template_id` int(11) NOT NULL,
 `status` varchar(255) NOT NULL,
 `playbook` varchar(255) NOT NULL,
 `environment` longtext DEFAULT NULL,
 `debug` tinyint(1) NOT NULL DEFAULT 0,
 `created` datetime DEFAULT NULL,
 `start` datetime DEFAULT NULL,
 `end` datetime DEFAULT NULL,
 `user_id` int(11) DEFAULT NULL,
 `dry_run` tinyint(1) NOT NULL DEFAULT 0,
 `arguments` text DEFAULT NULL,
 `project_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `template_id` (`template_id`),
 CONSTRAINT `task_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `project__template` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `task__output`
--

DROP TABLE IF EXISTS `task__output`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `task__output` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `task_id` int(11) NOT NULL,
 `task` varchar(255) NOT NULL,
 `time` datetime NOT NULL,
 `output` longtext NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `created` datetime NOT NULL,
 `username` varchar(255) NOT NULL,
 `name` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `password` varchar(255) NOT NULL,
 `alert` tinyint(1) NOT NULL DEFAULT 0,
 `external` tinyint(1) NOT NULL DEFAULT 0,
 `admin` tinyint(1) NOT NULL DEFAULT 1,
 PRIMARY KEY (`id`),
 UNIQUE KEY `username` (`username`),
 UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user__token`
--

DROP TABLE IF EXISTS `user__token`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user__token` (
 `id` varchar(44) NOT NULL,
 `created` datetime NOT NULL,
 `expired` tinyint(1) NOT NULL DEFAULT 0,
 `user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

==EDIT==
For those using this dump script to get 10.6 working ... you'll need this data in the 'migrations' table:

-- Dumping data for table migrations

LOCK TABLES migrations WRITE;
/*!40000 ALTER TABLE migrations DISABLE KEYS /;
INSERT INTO migrations VALUES ('0.0.0','2021-09-15 16:53:25',NULL),('1.0.0','2021-09-15 16:53:26',NULL),('1.2.0','2021-09-15 16:53:26',NULL),('1.3.0','2021-09-15 16:53:26',NULL),('1.4.0','2021-09-15 16:53:26',NULL),('1.5.0','2021-09-15 16:53:26',NULL),('1.6.0','2021-09-15 16:53:26',NULL),('1.7.0','2021-09-15 16:53:26',NULL),('1.8.0','2021-09-15 16:53:26',NULL),('1.9.0','2021-09-15 16:53:26',NULL),('2.2.1','2021-09-15 16:53:26',NULL),('2.3.0','2021-09-15 16:53:26',NULL),('2.3.1','2021-09-15 16:53:26',NULL),('2.3.2','2021-09-15 16:53:26',NULL),('2.4.0','2021-09-15 16:53:26',NULL),('2.5.0','2021-09-15 16:53:26',NULL),('2.5.2','2021-09-15 16:53:26',NULL),('2.7.1','2021-09-15 16:53:26',NULL),('2.7.10','2021-09-15 16:53:26',NULL),('2.7.12','2021-09-15 16:53:26',NULL),('2.7.13','2021-09-15 16:53:26',NULL),('2.7.4','2021-09-15 16:53:26',NULL),('2.7.6','2021-09-15 16:53:26',NULL),('2.7.8','2021-09-15 16:53:26',NULL),('2.7.9','2021-09-15 16:53:26',NULL);
/!40000 ALTER TABLE migrations ENABLE KEYS */;
UNLOCK TABLES;

[fiftin]

Fixed, Meriadb supported.

[janhelwich]

@fiftin can you let us know how it was fixed?