Unique constraint error can't be extracted in MySQL 5.7

[supermx1]

Hello everyone, so if I check the field option "Unique - Value has to be unique" and I fill a form to test, and I enter a value that is not unique. It shows the error above, which is not descriptive. The field is not hidden. Secondly, I think it's not standard to display an error at the top of a page for UX reasons, especially for long forms. The user has to scroll to the top to see the errors, instead of highlighting the field(s) that have said errors or validation issues. Adding to it. When a field has validation errors the submit form button (green check button at the top) doesn't give any indication that there are errors on submission. It just stays the same and the user will just be left there clicking and clicking with no action or scroll effect or even warning animation.

Environment
Directus 9.0.0-RC.68
MySQL 8 (MAMP)
NPX
Chrome Version 90.0.4430.212 (Official Build) (x86_64)

[rijkvanzanten]

@emekatoby Did you create the unique constraint through Directus itself? MySQL doesn't return the affected column when a unique constraint fails, so we can't reliably extract that field name :C Directus tries reading it from the constraint name, but this only works when the constraint was made through Directus directly.

[rijkvanzanten]

Thank you for taking the time to submit this! Unfortunately, our team was not been able to reproduce it based on the information provided. Therefore, we will close this ticket for now, but will happily re-open it if new details are provided that allow us to reliably replicate the problem.

As a reminder:

• Provide as much detail as possible in the issue description
• Video screen captures and clear steps are very helpful
• A full SQL dump is the fastest way for us to test/resolve (can be sent privately)
• Make sure you are on the latest version of Directus
• Did you try turning it off and on again?

---

This is an automated response.

[supermx1]

@emekatoby Did you create the unique constraint through Directus itself? MySQL doesn't return the affected column when a unique constraint fails, so we can't reliably extract that field name :C Directus tries reading it from the constraint name, but this only works when the constraint was made through Directus directly.

I created all fields and unique constraint in directus. When i go to test it in the form section. It shows that error. I did not alter or move anything directly in the MySQL database

[rijkvanzanten]

@emekatoby Could you check what the unique index name is in the database directly? You should be able to use:

SELECT
	CONSTRAINT_NAME,
	TABLE_NAME
FROM
	information_schema.TABLE_CONSTRAINTS
WHERE
	CONSTRAINT_SCHEMA = 'directus' # << USE YOUR DIRECTUS DATABASE HERE
	AND CONSTRAINT_TYPE = 'UNIQUE';

[supermx1]

I checked it. Showed this

[supermx1]

github.mp4

video showing example. I used an entry already present in the DB "1234567890" in the unique field and it threw that same error. I have shut directus down and started it up again, same thing

[rijkvanzanten]

I tried setting up a reproduction of your issue locally on MySQL 8, but still not able to reproduce it 😭 :

Would you be able to share a database dump where this is happening, so I can reproduce the exact setup?

[supermx1]

Okay. Is there a private way I can send it to you? Plus what do you think the fix would be seeing that you can't reproduce it. Show I do a DB migrate up or down or do an NPM update for my directus instance?

[rijkvanzanten]

You can email it directly to me at rijk@directus.io 👍🏻

what do you think the fix would be seeing that you can't reproduce it.

It's most likely a change in the codebase, so a simple update would be enough

[supermx1]

sent. I am actually using the RC69 release. Which should be the latest.

[rijkvanzanten]

Seems to work as expected... 😵 (testing in MySQL 8.0.25)

What's the exact version of MySQL you're using in MAMP?

[supermx1]

Database server
Server: Localhost via UNIX socket
Server type: MySQL
Server connection: SSL is not being used Documentation
Server version: 5.7.32 - MySQL Community Server (GPL)
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)
Web server
Apache/2.4.46 (Unix) OpenSSL/1.0.2u PHP/7.4.12 mod_wsgi/3.5 Python/2.7.13 mod_fastcgi/mod_fastcgi-SNAP-0910052141 mod_perl/2.0.11 Perl/v5.30.1
Database client version: libmysql - mysqlnd 7.4.12
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
PHP version: 7.4.12

[rijkvanzanten]

Ohhhh so you're not on MySQL 8 😄 You're using MySQL 5.7.32. (Directus requires 5.7.8+, so you might run into some issues with JSON fields)

I think the db error format has changed between v5 and v8, which means our extraction logic doesn't work for this older version 👍🏻

[supermx1]

I just confirm it is a problem with MAMP or something. I have a production environment running on the same directus instance I copied into my windows server. I tried the same thing and it echoed the correct validation error.

Anyway, while we are here, I wanted to point out another error. The production server I am running has issues loading the logo images. I can see it in the dom, but it doesn't display. I cannot figure out why.

[rijkvanzanten]

@supermx1 Your MAMP MySQL version is different from your MySQL version on the production server 👍🏻

Anyway, while we are here, I wanted to point out another error. The production server I am running has issues loading the logo images. I can see it in the dom, but it doesn't display. I cannot figure out why.

Please open a new issue if that's a problem in Directus. It's impossible to track multiple issues in the same "issue" on GitHub 🙂

[supermx1]

Ok