Integrity constraint violation with encrypted array

@JussiMannisto It's also what I think.

But it's strange because the field is very simple.

datasource_credentials	longtext	utf8mb4_bin		Yes	NULL	

If I remove the encrypted:array casting, it works.

@vincent15000 Can you show the factory code and the Company table schema? There must be something else going on. If you removed the encrypted:array casting and tried to save an array to the column, you should get a different exception.

@JussiMannisto I switched the collation from utf8mb4_bin to utf8mb4_unicode_ci and it works.

Thank you ;).

@vincent15000 That's a collation setting, not an encoding. I still don't understand what was going on. But I'm glad it works, I guess ¯_(ツ)_/¯.

@JussiMannisto Yes effectively it's the collation.

I don't know what happened ... but utf8mb4_bin is case sensitive, whereas utf8mb4_unicode_ci is not.

No tested with other case insensitive collations to be sure it was a case problem.

@JussiMannisto Wow ... no it doesn't work ... I changed manually the collation and it worked only once.

I refreshed the database with the "right" collation and it doesn't work anymore.

I tried just now something else.

I have changed back the collation to utf8mb4_bin and it works.

Very very strange, like if the collation change action is responsible for this to work.

@vincent15000 Did you drop or truncate any tables between these changes? If you did, that would make sense, since this error comes from a FK or unique key violation. The 1st insert on an empty table could work while the 2nd causes a violation.

Can you show the Company table schema or migration?

@JussiMannisto No ... only one unique line stored in this table. And the error is precisely focused on the JSON (LONGTEXT) field.

Schema::create('companies', function (Blueprint $table) {
    $table->ulid('id')->primary();
    $table->string('name')->index();
    $table->json('address')->nullable();
    $table->string('phone')->nullable();
    $table->string('email')->nullable();
    $table->string('tax_identification_number')->nullable();
    $table->enum('datasource', ['pipedrive', 'googlesheet'])->nullable();
    $table->json('datasource_credentials')->nullable();
    $table->boolean('datasource_credentials_validated')->default(false);
    $table->boolean('banned')->default(false);
    $table->timestamps();
});

id Primaire	char(26)	utf8mb4_unicode_ci		Non	Aucun(e)			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	2	name Index	varchar(255)	utf8mb4_unicode_ci		Non	Aucun(e)			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	3	address	longtext	utf8mb4_bin		Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	4	phone	varchar(255)	utf8mb4_unicode_ci		Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	5	email	varchar(255)	utf8mb4_unicode_ci		Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	6	tax_identification_number	varchar(255)	utf8mb4_unicode_ci		Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	7	datasource	enum('pipedrive', 'googlesheet')	utf8mb4_unicode_ci		Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	8	datasource_credentials	longtext	utf8mb4_bin		Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	9	datasource_credentials_validated	tinyint(1)			Non	0			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	10	banned	tinyint(1)			Non	0			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	11	created_at	timestamp			Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	12	updated_at	timestamp			Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	13	stripe_id Index	varchar(255)	utf8mb4_unicode_ci		Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	14	pm_type	varchar(255)	utf8mb4_unicode_ci		Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	15	pm_last_four	varchar(4)	utf8mb4_unicode_ci		Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus
	16	trial_ends_at	timestamp			Oui	NULL			Modifier Modifier	Supprimer Supprimer	
Plus Plus

@JussiMannisto And here is the complete error.

SQLSTATE[23000]: Integrity constraint violation: 4025 CONSTRAINT companies.datasource_credentials failed for comizio.companies (Connection: mariadb, SQL: insert into companies (name, datasource, datasource_credentials, datasource_credentials_validated, id, updated_at, created_at) values (rerum illo, pipedrive, eyJpdiI6IlFLL29FQk10bHUxelN1NDhCazQ0anc9PSIsInZhbHVlIjoiL2h2Z0xLU2RWclZuUmVVU2NsZ0xGQ1JPWFUvQWp0NDFzVkZsakQzaG1MR0ZCank0SXVTVDVnNThjVWdPVlJheW9vUVJ6SU9XZDJ1UDQ5VkhyV2k0VTRJQUhLSXIyUE1tMkJhOU43N0pIZlAyVzhENWN5MVh0czYyRlRSTjl5cUNqMy9ucmpxR2JJNjY1NFJoQUdkc0sxQmI1VnFIMHQ1K3hWOE0yWlBMemlFSk1uQVNFakxaODN0NythN2NaTWVncU4wQnRvYTJkbGpHc0xMN3paY0pjTU0vMCtqSktQOUp6U1BJdWhhV2d2OFBUaEIxWEppUFZsNldEYTFsK1BPZXFQZ3FvY2pqRW9PLzBudWZVdFJRRWc9PSIsIm1hYyI6ImMyMzc5YzA0NDMwYWQ4NDJiYjZlMThkNmYyOTE3ZmZhNmUxZWFkMmE4ZDNhYThhYTJlODZkNjgzMGM3NWYyNDUiLCJ0YWciOiIifQ==, 1, 01jv5de69vz3y1z0z96tkkaq9y, 2025-05-13 18:10:16, 2025-05-13 18:10:16))

@vincent15000 Now I see what's wrong. You have a ULID primary key, but it's not assigned when you're trying to insert the row. Presumably the first insert creates an empty string as the PK, and the 2nd insert fails because it tries to do the same.

ULIDs can't be auto-generated on the DB side, they must be set by the app. Eloquent does this automatically if your model uses the HasUlids trait. Is that missing from your Company model, or are you trying to create the record without Eloquent?

@JussiMannisto No it's not missing from the model, it uses the HasUlids trait.

And I use Eloquent to create the model and the id is present in the query, have a look at the last line of the error.

class Company extends Model
{
    use HasFactory, HasUlids, Billable;

    protected $fillable = [
        'name',
        'address',
        'phone',
        'preferred_locales',
        'tax_identification_number',
        'datasource',
        'datasource_credentials',
        'datasource_credentials_validated',
        'banned',
    ];

    /**
     * The attributes that should be hidden for serialization.
     *
     * @var array<int, string>
     */
    protected $hidden = [
        'datasource',
        'datasource_credentials',
    ];

    protected function casts(): array
    {
        return [
            'address' => 'array',
            'preferred_locales' => 'array',
            'datasource' => DatasourceEnum::class,
            'datasource_credentials' => 'encrypted:array',
            'datasource_credentials_validated' => 'boolean',
            'banned' => 'boolean',
        ];
    }
}

@vincent15000 Oh right, I just assumed ID would be the first column in the statement.

@JussiMannisto Do you have any other ideas ?

@vincent15000 I'm a bit confused since there are no FK or Unique constraints on the column, yet you get a constraint violation. Maybe there's some JSON-related CHECK constraint on the column, since you defined it as JSON in the migration? I don't think Laravel adds one automatically, but you should still check it. Call this in the database and tell me what you see:

SELECT * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS;

If there's some JSON rule, then that's the issue. By the way, the column type shouldn't be JSON since you're storing an encrypted string there, not JSON.

That still wouldn't explain why the first insert works with encryption. But let's see the output first.

@JussiMannisto Ok ... here is the result.

I have a constraint on the datasource_credentials field : json_valid(datasource_credentials).

With the encryption, it's not a valid JSON anymore.

So it would probably be a good idea that I create the field as a simple text field from the migration.

@JussiMannisto Just tried and yes it works.

Thank you very much ;).

@vincent15000 That explains it. I didn't know that Laravel automatically adds a CHECK constraint for JSON columns. Good to know.

@JussiMannisto Yes, good to know, and it's also a good thing that Laravel adds automatically this constraint.