"belongsToMany" lock timeout with different database connections/tables in mysql

[JaredPage]

So I have 2 models in my Laravel project. Let's call them Model A, Model B (I've renamed them from my real project names). I am trying to have a MultiSelect field in Model A for selecting a few values of Model B. Seems straightforward, but the challenge is that Model A & Model B tables are in different databases (i.e. different config/database.php connection stanza each). Connection & Database name are sort of conflated in MySql since it seems you can only have one database per connection anyway.

Firstly, I am able to successfully use the normal/default Laravel/Eloquent relationships within Nova correctly and attach many instances of Model B to Model A completely fine. I obviously want to use MultiSelect because the default select interface in Nova (when there are lots of results) is hilariously slow and users think it has glitched out.

The behaviour I am seeing with MultiSelect is that when trying to create a new record of Model A (and subsequently trying to select one or many instances of Model B in the MultiSelect), I get a error:

SQL STATE [HY000]: General Error: 1205 Lock wait timeout exceed; try restarting transaction (SQL: insert into 'database_for_a'.'a_table' ('a_id', 'b_id') values (2, 17))

Every combination of creating the record without using MultiSelect (as mentioned - including using the default belongsToMany relationship interface in Nova) works completely fine. It does appear it is the package itself causing this.

When editing a record, the package works completely fine, and I can add and remove instances of Model B from Model A fine, and - get this weirdness - I can even remove all entries, save the Model, exit and come back in and add new Model B instances using MultiSelect fine. It seems it's only when initially creating the Model A entry that the error happens.

This is what I have so far that makes the most sense to me:

Model A

public function model_b_relationship(): \Illuminate\Database\Eloquent\Relations\BelongsToMany
{
return $this
->setConnection(env('DB_CONNECTION', DatabaseConfig::$DEFAULT_MODEL_A_CONNECTION))
->belongsToMany(ModelB::class, DB::connection(env('DB_CONNECTION', DatabaseConfig::$DEFAULT_MODEL_A_CONNECTION))>getDatabaseName().".a_b_table", "model_a_id", "model_b_id");
}

Nova Multiselect::make("Choose Model B", "model_b_relationship")->belongsToMany(ModelB::class,false)

Databases Here is a simplified view of the databases:

A_Table (Connection 1)	B_Table (Connection 2)	A_B_Table (Connection 1)
id	id	id
name	name	a_id
desc	desc	b_id

I've tried many things (including using setConnection on the Modal A relationship function, but unable to get it to work the first time with this MultiSelect package, and I'm hoping someone on here can help me out, and confirm this is a bug :)

Thanks! PHP v8.1 Laravel 9.X Nova 4.X

[JaredPage]

@Tarpsvo is there any updates here on where the changes would be for this to work?

[JaredPage]

I found that my issue was actually caused by referencing two connections in a 'morphedByMany' method - as described here: https://github.com/laravel/framework/issues/23413.

Curiously, I will say that this still is not an issue with the standard MorphedByMany::make laravel library. I'm not sure fundamentally how they are different, but essentially I traced it all the way back to /Illuminate/Database/Query/Builder -> insert(), and found that it hangs for some combination of bindings but not all. I think it's all down to chance or timing with the PDO.