Database migration error ERROR 3780 (HY000)

Troubleshooting
1

Hello,

few weeks I upgraded to Mattermost 8.1. Now I upgraded to 9.0. And then I wanted to migrate to a new server. After I created a mysqldump I imported the database to the new server.

But then I will get this error:

ERROR 3780 (HY000) at line 1582: Referencing column ‘PolicyId’ and referenced column ‘Id’ in foreign key constraint ‘FK_RetentionPoliciesChannels_RetentionPolic ies’ are incompatible.

Then I deleted this from my mysqldump:

--
-- Table structure for table `RetentionIdsForDeletion`
--

DROP TABLE IF EXISTS `RetentionIdsForDeletion`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `RetentionIdsForDeletion` (
  `Id` varchar(26) NOT NULL,
  `TableName` varchar(64) DEFAULT NULL,
  `Ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`Ids`)),
  PRIMARY KEY (`Id`),
  KEY `idx_retentionidsfordeletion_tablename` (`TableName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `RetentionIdsForDeletion`
--

LOCK TABLES `RetentionIdsForDeletion` WRITE;
/*!40000 ALTER TABLE `RetentionIdsForDeletion` DISABLE KEYS */;
/*!40000 ALTER TABLE `RetentionIdsForDeletion` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `RetentionPolicies`
--

DROP TABLE IF EXISTS `RetentionPolicies`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `RetentionPolicies` (
  `Id` varchar(26) NOT NULL,
  `DisplayName` varchar(64) DEFAULT NULL,
  `PostDuration` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `IDX_RetentionPolicies_DisplayName` (`DisplayName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `RetentionPolicies`
--

LOCK TABLES `RetentionPolicies` WRITE;
/*!40000 ALTER TABLE `RetentionPolicies` DISABLE KEYS */;
/*!40000 ALTER TABLE `RetentionPolicies` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `RetentionPoliciesChannels`
--

DROP TABLE IF EXISTS `RetentionPoliciesChannels`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `RetentionPoliciesChannels` (
  `PolicyId` varchar(26) DEFAULT NULL,
  `ChannelId` varchar(26) NOT NULL,
  PRIMARY KEY (`ChannelId`),
  KEY `IDX_RetentionPoliciesChannels_PolicyId` (`PolicyId`),
  CONSTRAINT `FK_RetentionPoliciesChannels_RetentionPolicies` FOREIGN KEY (`PolicyId`) REFERENCES `RetentionPolicies` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `RetentionPoliciesChannels`
--

LOCK TABLES `RetentionPoliciesChannels` WRITE;
/*!40000 ALTER TABLE `RetentionPoliciesChannels` DISABLE KEYS */;
/*!40000 ALTER TABLE `RetentionPoliciesChannels` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `RetentionPoliciesTeams`
--

DROP TABLE IF EXISTS `RetentionPoliciesTeams`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `RetentionPoliciesTeams` (
  `PolicyId` varchar(26) DEFAULT NULL,
  `TeamId` varchar(26) NOT NULL,
  PRIMARY KEY (`TeamId`),
  KEY `IDX_RetentionPoliciesTeams_PolicyId` (`PolicyId`),
  CONSTRAINT `FK_RetentionPoliciesTeams_RetentionPolicies` FOREIGN KEY (`PolicyId`) REFERENCES `RetentionPolicies` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `RetentionPoliciesTeams`
--

LOCK TABLES `RetentionPoliciesTeams` WRITE;
/*!40000 ALTER TABLE `RetentionPoliciesTeams` DISABLE KEYS */;
/*!40000 ALTER TABLE `RetentionPoliciesTeams` ENABLE KEYS */;
UNLOCK TABLES;

And then the import was working. But I am not sure, if this is fine. Maybe I will have problems in the future, because I removed the mentioned data?

Maybe someone can help and tell me a real fix for this error. :confused:

And I think I will use Mattermost Omnibus on the new server. But then I need to convert my mysql db to a postgresql db. And then I need to import this postgresql db to the database which was created automatically from the Omnibus installation. Do you think this is possible?

I really want to create a fresh installation, but we can’t lose all our information, its not possible. Also the link of the team contains still the old company name, so bad. And I can not change it also… I can change it in the database, but maybe this will destroy something? So bad. :frowning:

Thanks and greetings!

2

Ok, I adjusted my mysql database a bit and then I converted the database to postgresql. And then I noticed the data of the omnibus installation is in the public schema. So I moved the imported database tables to the public schema. And then I also imported the /data folder. And then everything seems working fine. But now I miss messages. I think I lost them while the conversion to postgresql. Because in the mysql database everything is still there.

I also got this error:

ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "current_timestamp(6)"
QUERY: CREATE TABLE mattermost.focalboard_blocks
(
  id          varchar(36) not null,
  insert_at   timestamptz not null default 'current_timestamp(6)',
  parent_id   varchar(36) default NULL,
  schema      bigint default NULL,
  type        text default NULL,
  title       text default NULL,
  fields      text default NULL,
  create_at   bigint default NULL,
  update_at   bigint default NULL,
  delete_at   bigint default NULL,
  root_id     varchar(36) default NULL,
  modified_by varchar(36) not null,
  channel_id  varchar(36) not null,
  created_by  varchar(36) not null,
  board_id    varchar(36) default NULL
);

Then I removed all focalboard tables, because there was no data anyway.

And I got an error regarding the “92 add_createat_to_teammembers” in the table “db_migrations”. Anything with a key… whatever. I deleted this row and it worked.

But I think I do not use omnibus. And when I change back to the normal version, I will use mysql again I think. Because with the conversation I lost messages.

I already wasted 14 hours with this. I hate it. :smiley: And the community seems not that active, which is not so helpful. Hope I will finish it now in 3 hours… argh.

3

Not anything specific to add regarding your problem, but you could try the Community Chat Server and see if anyone can help you there.