V8-archive: Error when updating wysiwyg field

[AleeeKoi]

Bug Report

Steps to Reproduce

1. Go to a collection
2. Click on an existing wysiwyg field
3. Edit something (or not the result doesn't change)
4. Click save

Expected Behavior

The field is updated

Actual Behavior

Field doen't update and and error occurs, see following log from the http response

Http response

{"error":{"code":404,"message":"Statement could not be executed (HY000 - 1025 - Error on rename of '.\/test_directus\/test' to '.\/test_directus\/#sql2-1-139' (errno: 168 \"Unknown (generic) error from engine\"))"}}

Technical Details

• Device: Desktop/Chrome
• OS: Windows 10
• Web Server: 2.4
• PHP Version: 7.3-fpm
• Database: MariaDB 10.4
• Directus Release Version: 8.5.4

[rijkvanzanten]

I've never seen this error before. Based on a quick google search, could it be that you have some foreign key constraints (manually) setup in the DB?

https://stackoverflow.com/questions/160233/what-does-mysql-error-1025-hy000-error-on-rename-of-foo-errorno-150-me

[AleeeKoi]

I don't think so, as you can see from the log the error happens on a test fresh installation.
Nothing was done before the wysiwyg creation (only the 1st initialization).
No relation or manually FK was added.
If helpful, my current db charset is utf8mb4 and utf8mb4_general_ci as collation.

[AleeeKoi]

Hello @rijkvanzanten
There are any updates?
Thank you

[rijkvanzanten]

No updates. I haven't been able to reproduce it. Do you have specific steps to reproduce it on a fresh machine, like a digitalocean droplet or docker container?

[AleeeKoi]

I have one update, today I have updated to 8.5.5 on two different machines.
On windows 10 the problem persist, on an OSX the problem occurs sometimes.
The 2 machines is using same docker container with following images:

• bitnami/mariadb:10.4
• php:7.3-apache-stretch
  On those machines I have other apps running (based on Laravel, wordpress, ecc.) and no problems occur.

This is the entire update request log:

Request info:

Request URL: http://directus.local/test/fields/test/aaaa
Request Method: PATCH
Status Code: 422 Unprocessable Entity
Remote Address: 127.0.0.1:80
Referrer Policy: no-referrer-when-downgrade

Request header:

Access-Control-Allow-Credentials: true
Access-Control-Allow-Origin: http://directus.local
Connection: Keep-Alive
Content-Length: 215
Content-Type: application/json
Date: Fri, 14 Feb 2020 15:17:17 GMT
Keep-Alive: timeout=5, max=78
Server: Apache/2.4.25 (Debian)
X-Powered-By: PHP/7.3.14

Response: header:

Accept: application/json, text/plain, */*
Accept-Encoding: gzip, deflate
Accept-Language: it-IT,it;q=0.9,en-US;q=0.8,en;q=0.7
Cache-Control: no-cache
Connection: keep-alive
Content-Length: 494
Content-Type: application/json;charset=UTF-8
Cookie: PHPSESSID=3dede67211bffdb7d0925a6a838bf2d4; directus-test-session=ewcNI5jaH6jA8BOJqKg9D2ADM5QytkpHl5w%3D%3A%3A240131f2a7bab4b974c16f59a9fc4376
Host: directus.local
Origin: http://directus.local
Pragma: no-cache
Referer: http://directus.local/admin/
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36
X-Directus-Project: test

Request body:

{
   "id":178,
   "sort":null,
   "field":"aaaa",
   "type":"string",
   "datatype":"TEXT",
   "interface":"wysiwyg",
   "default_value":"1",
   "options":{
      "tinymce_options":null,
      "toolbar":[
         "bold",
         "italic",
         "underline",
         "removeformat",
         "link",
         "bullist",
         "numlist",
         "blockquote",
         "h1",
         "h2",
         "h3",
         "image",
         "media",
         "hr",
         "code",
         "fullscreen"
      ],
      "custom_formats":null
   },
   "readonly":false,
   "required":false,
   "unique":false,
   "note":"",
   "hidden_detail":false,
   "hidden_browse":false,
   "primary_key":false,
   "validation":null,
   "width":"full",
   "translation":{

   }
}

Response body:

{
   "error":{
      "code":404,
      "message":"Statement could not be executed (HY000 - 1025 - Error on rename of '.\/test_directus\/test' to '.\/test_directus\/#sql2-85-4c' (errno: 168 \"Unknown (generic) error from engine\"))"
   }
}

Thank you

[h3ll0n1k]

@rijkvanzanten same problem here. Today i've tried updating a wysiwyg (adding a tinyMCE option) field in a collection, but directus hasn't updated field and show me an alert saying "trouble proccessing request. Try again after refreshing the page".
Below my log.

Thank you

```
[2020-02-21 12:37:32] api[2ronin].ERROR: Directus\Exception\SQLException: Statement could not be executed (42000 - 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(16777215) Null' at line 1) in /var/www/headless-cms-2ronin/src/endpoints/Fields.php:122
Stack trace:

0 [internal function]: Directus\Api\Routes\Fields->update

1 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(40): call_user_func

2 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/Route.php(281): Slim\Handlers\Strategies\RequestResponse->__invoke

3 /var/www/headless-cms-2ronin/src/core/Directus/Application/Http/Middleware/AbstractRateLimitMiddleware.php(34): Slim\Route->__invoke

4 [internal function]: Directus\Application\Http\Middleware\AbstractRateLimitMiddleware->__invoke

5 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array

6 [internal function]: Slim\DeferredCallable->__invoke

7 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func

8 /var/www/headless-cms-2ronin/src/core/Directus/Application/Http/Middleware/AuthenticationMiddleware.php(124): Slim\Route->Slim{closure}

9 [internal function]: Directus\Application\Http\Middleware\AuthenticationMiddleware->__invoke

10 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array

11 [internal function]: Slim\DeferredCallable->__invoke

12 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func

13 /var/www/headless-cms-2ronin/src/core/Directus/Application/Http/Middleware/TableGatewayMiddleware.php(25): Slim\Route->Slim{closure}

14 [internal function]: Directus\Application\Http\Middleware\TableGatewayMiddleware->__invoke

15 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array

16 [internal function]: Slim\DeferredCallable->__invoke

17 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func

18 /var/www/headless-cms-2ronin/src/core/Directus/Application/Http/Middleware/DatabaseMigrationMiddleware.php(15): Slim\Route->Slim{closure}

19 [internal function]: Directus\Application\Http\Middleware\DatabaseMigrationMiddleware->__invoke

20 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array

21 [internal function]: Slim\DeferredCallable->__invoke

22 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func

23 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\Route->Slim{closure}

24 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/Route.php(268): Slim\Route->callMiddlewareStack

25 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/App.php(503): Slim\Route->run

26 /var/www/headless-cms-2ronin/src/core/Directus/Application/Http/Middleware/AbstractRateLimitMiddleware.php(34): Slim\App->__invoke

27 [internal function]: Directus\Application\Http\Middleware\AbstractRateLimitMiddleware->__invoke

28 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array

29 [internal function]: Slim\DeferredCallable->__invoke

30 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func

31 /var/www/headless-cms-2ronin/vendor/directus/proxy-detection/src/ProxyDetectionMiddleware.php(30): Slim\App->Slim{closure}

32 /var/www/headless-cms-2ronin/src/core/Directus/Application/Http/Middleware/ProxyMiddleware.php(18): RKA\Middleware\ProxyDetectionMiddleware->__invoke

33 [internal function]: Directus\Application\Http\Middleware\ProxyMiddleware->__invoke

34 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array

35 [internal function]: Slim\DeferredCallable->__invoke

36 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func

37 /var/www/headless-cms-2ronin/vendor/akrabat/ip-address-middleware/src/IpAddress.php(113): Slim\App->Slim{closure}

38 [internal function]: RKA\Middleware\IpAddress->__invoke

39 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array

40 [internal function]: Slim\DeferredCallable->__invoke

41 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func

42 /var/www/headless-cms-2ronin/src/core/Directus/Application/Http/Middleware/CorsMiddleware.php(71): Slim\App->Slim{closure}

43 [internal function]: Directus\Application\Http\Middleware\CorsMiddleware->__invoke

44 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array

45 [internal function]: Slim\DeferredCallable->__invoke

46 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func

47 /var/www/headless-cms-2ronin/src/core/Directus/Application/Http/Middleware/ResponseCacheMiddleware.php(62): Slim\App->Slim{closure}

48 [internal function]: Directus\Application\Http\Middleware\ResponseCacheMiddleware->__invoke

49 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array

50 [internal function]: Slim\DeferredCallable->__invoke

51 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func

52 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\App->Slim{closure}

53 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/App.php(392): Slim\App->callMiddlewareStack

54 /var/www/headless-cms-2ronin/vendor/slim/slim/Slim/App.php(297): Slim\App->process

55 /var/www/headless-cms-2ronin/src/core/Directus/Application/Application.php(161): Slim\App->run

56 /var/www/headless-cms-2ronin/public/index.php(5): Directus\Application\Application->run [] []```

[AleeeKoi]

Hello @rijkvanzanten ,
After some investigations I found that there is a problem with the alter query made for updating the field of the wysiwyg.
The problem is generate by the function addNotNullConstraint.
Is you set the column length of a TEXT type it fail (in my case) and generate the exception.

In my case, the generated query is:
ALTER TABLE `test` MODIFY COLUMN `field_wysiwyg` TEXT(65535) Null;

Removing the part (65535) and leaving only TEXT the query works fine.

Today I have release a dicrectus app on a LAMP (working on mysql 5.7) hosted on digital ocean (so with a different environment from my first issue request) and the problem still exists.

So, there are any miss configurations with my mariadb/mysql or the function addNotNullConstraint need to be fixed removing length for types like (text, mediumtext, etc.)?

Update:

@rijkvanzanten Sorry.. My fault, the query is fine. There was a miss configuration in my docker container. The problem was related to the file sharing permissions.

Any way, now I get another problem. If I create a wysiwyg field with TEXT as mysql datatype all works fine. If I update this field (simply opening it and clicking save button) the update work but the datatype become "MEDIUMTEXT".

If I repeat editing the field (without changes) when I click save an error occurred:
Statement could not be executed (42000 - 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(4294967295) Null' at line 1)

If i try to update the field but reset the datatype to TEXT the update works without errors but in the database the field is stored as a LONGTEXT.

I'm not able to reset field (from the UI) to TEXT.

There is something not working?

Thank you

[hemratna]

@AleeeKoi
I fixed this issue with PR #1764.
Please check it and if issue is solved then close it.

[AleeeKoi]

Hi @hemratna !
yes! I saw that PR, I will try ASAP.
Thank you

[AleeeKoi]

@hemratna
Trying on a fresh installation I see that something still not working correctly.
If I update something (like a wysiwyg) that has mysql datatype equal to "text" after the updating process it become a "mediumtext". If I make another one update on the same filed the datatype become a "longtext".

So in the past days I have made a PR https://github.com/directus/api/pull/1782 that resolve this issue.

@rijkvanzanten please if its all ok can you merge it?

Edit:
Sorry @hemratna , my fault, I haven't seen that the merge was only on the directus/api repo. Looking at you code seem to be all correct. My PR is not required any more.
@rijkvanzanten , when will @hemratna 's fix be released in the installation repo directus/directus?

Thank you

[rijkvanzanten]

Yeah, I'm going to push out a release later today @AleeeKoi

[rijkvanzanten]

Fixed in #1764