Error with migrations:execute - Foreign key constraint is incorrectly formed

💻 Development app dev
, ,
1

Hello everyone :slight_smile:

I am coding a database table for my Workspace application.
This application is sort of a plug-in for Groupfolders application.

But, I need to create a database table which spaces list :

  • space_id (biginteger) * ;
  • groupfolder_id (biginteger) * ** ;
  • space_name (string) ** ;

:warning: ( * ) It’s the primary key ; ( ** ) it’s the foreign key constraint.

So, after reading the lib/Migration/Version102020Date20180806161449.php code (https://github.com/nextcloud/groupfolders/blob/stable21/lib/Migration/Version102020Date20180806161449.php) and this lib/Migration/Version501000Date20190927102434.php (groupfolders/lib/Migration/Version501000Date20190927102434.php at stable21 · nextcloud/groupfolders · GitHub) code from the Groupfolders application.

So, I coded that :

<?php

declare(strict_types=1);

namespace OCA\Workspace\Migration;

use Closure;
use OCP\DB\ISchemaWrapper;
use OCP\Migration\IOutput;
use OCP\Migration\SimpleMigrationStep;

/**
 * Auto-generated migration step: Please modify to your needs!
 */
class Version0000Date20210615125333 extends SimpleMigrationStep {

	/**
	 * @param IOutput $output
	 * @param Closure $schemaClosure The `\Closure` returns a `ISchemaWrapper`
	 * @param array $options
	 * @return null|ISchemaWrapper
	 */
	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {

		$schema = $schemaClosure();

		if ( $schema->hasTable('work_spaces') ) {
			$schema->dropTable('work_spaces');
		}

		$table = $schema->createTable('work_spaces');

		$table->addColumn('space_id', 'bigint', [
			'autoincrement' => true,
			'notnull' => true,
			'length' => 6
		]);

		$table->addColumn('groupfolder_id', 'bigint', [
			'autoincrement' => false,
			'notnull' => true,
			'length' => 6
		]);

		$table->addColumn('space_name', 'string', [
			'notnull' => true,
			'length' => 128,
		]);
		
		$table->changeColumn('space_name', [
			'notnull' => true,
			'length' => 4000
		]);

		$table->setPrimaryKey([
			'space_id',
			'groupfolder_id',
		]);

		$table->addForeignKeyConstraint($schema->getTable('group_folders'), ['groupfolder_id'], ['folder_id'], [], 'fk_groupfolder_id_work_spaces');
                // Trigger a bug
		// $table->addForeignKeyConstraint($schema->getTable('group_folders'), ['space_name'], ['mount_point'], [], 'fk_mount_point_work_spaces');

		return $schema;
	}
}

Okay, I ran with this command : nginx php ../../occ migrations:execute workspace 0000Date20210615125333.

And it worked !

MariaDB [my_database]> describe oc_work_spaces ;
+----------------+---------------+------+-----+---------+----------------+
| Field          | Type          | Null | Key | Default | Extra          |
+----------------+---------------+------+-----+---------+----------------+
| space_id       | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| groupfolder_id | bigint(20)    | NO   | PRI | NULL    |                |
| space_name     | varchar(4000) | NO   |     | NULL    |                |
+----------------+---------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)

But, I would like to define a second foreign key constraint that’s space_name.

So, I uncommented this line :

<?php

// code

		$table->addForeignKeyConstraint($schema->getTable('group_folders'), ['space_name'], ['mount_point'], [], 'fk_mount_point_work_spaces');

// code

I reran this command : nginx php ../../occ migrations:execute workspace 0000Date20210615125333 …

And Boom !
I got this error :

In ExceptionConverter.php line 114:
                                                                                                                                                   
  An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1005 Can't create table `my_database`.`oc_work_s  
  paces` (errno: 150 "Foreign key constraint is incorrectly formed")                                                                               
                                                                                                                                                   

In Exception.php line 26:
                                                                                                                                                   
  SQLSTATE[HY000]: General error: 1005 Can't create table `my_database`.`oc_work_spaces` (errno: 150 "Foreign key constraint is i  
  ncorrectly formed")                                                                                                                              
                                                                                                                                                   

In Connection.php line 82:
                                                                                                                                                   
  SQLSTATE[HY000]: General error: 1005 Can't create table `my_database`.`oc_work_spaces` (errno: 150 "Foreign key constraint is i  
  ncorrectly formed")                                                                                                                              
                                                                                                                                                   

migrations:execute <app> <version>

The error message is :

Foreign key constraint is incorrectly formed

I don’t understand why I get this error while I tooke this code into account ?

I thanks for your help ! :slight_smile:

2

Hi @nickvergessen and @Anna Larch :slight_smile:

I found the solution !
Well, I think… I didn’t do much a SQL :sweat_smile:

So, the solution is I change the name of my database join.

Before I coded this :

<?php

// ...

 $table->addForeignKeyConstraint($schema->getTable('group_folders'), ['space_name'], ['mount_point'], [], 'fk_mount_point_work_spaces');
        $table->addForeignKeyConstraint($schema->getTable('group_folders'), ['groupfolder_id'], ['folder_id'], [], 'fk_groupfolder_id_work_spaces');

// ...

Now :

<?php

//...

		$table->addForeignKeyConstraint($schema->getTable('group_folders'), ['space_name'], ['mount_point'], [], 'fk_gi_sn_work_spaces');
		$table->addForeignKeyConstraint($schema->getTable('group_folders'), ['groupfolder_id'], ['folder_id'], [], 'fk_gi_sn_work_spaces');

//...

And it worked ! :partying_face:

Did you mean @nickvergessen in Nextcloud’s talk ? :slightly_smiling_face:

I don’t know it is a best practice ?

3

I meant to just foreign_key the id here
and then in your actual code you:

SELECT …, gf.mount_point FROM oc_work_spaces ws LEFT JOIN oc_groupfolders gf ON (gf.folder_id = ws.groupfolder_id