Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Query Failure using Many to Many when saving an item #2013

Closed
cjstage opened this issue Dec 28, 2017 · 13 comments
Closed

Query Failure using Many to Many when saving an item #2013

cjstage opened this issue Dec 28, 2017 · 13 comments

Comments

@cjstage
Copy link

cjstage commented Dec 28, 2017 •
edited by rijkvanzanten

For security issues, please email support@getdirectus.com directly.

To help us resolve your issue more quickly, please use the following template:

For feature requests/voting please use the Directus Request Tool, or come chat with us through getdirectus.com. Accepted feature requests will be added to GitHub Issues for assignment.

Version Info

  • Directus version and branch: Branch Master
  • PHP version: PHP 7.1.8
  • MySQL version: 5.5.42
  • Web server: Apache
  • OS name and version: Ubuntu 14.04.5
  • AWS Instance LAMP with PHP 7.1 Certified by Bitnami-7.1.8-0 on Ubuntu

Expected Behavior

Create a Many to Many relationship, save item successfully.

Actual Behavior

Throws query error: This query failed: INSERT INTO web_placements (name, url) VALUES ('face', 'face') in...

Steps to Reproduce

  1. Create Tables and Junction Table
  2. Add Many to Many relationship
  3. Add entries to item
  4. Attempt to save

Schema Dump, Logs, or Screenshots

Next RuntimeException: This query failed: INSERT INTO `web_placements` (`name`, `url`) VALUES ('face', 'face') in /opt/bitnami/apache2/htdocs/d1/directus/api/core/Directus/Database/TableGateway/BaseTableGateway.php:783
Stack trace:
#0 /opt/bitnami/apache2/htdocs/d1/directus/vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(264): Directus\Database\TableGateway\BaseTableGateway->executeInsert(Object(Zend\Db\Sql\Insert))
#1 /opt/bitnami/apache2/htdocs/d1/directus/api/core/Directus/Database/TableGateway/BaseTableGateway.php(396): Zend\Db\TableGateway\AbstractTableGateway->insert(Array)
#2 /opt/bitnami/apache2/htdocs/d1/directus/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(156): Directus\Database\TableGateway\BaseTableGateway->addOrUpdateRecordByArray(Array)
#3 /opt/bitnami/apache2/htdocs/d1/directus/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(597): Directus\Database\TableGateway\RelationalTableGateway->manageRecordUpdate('web_placements', Array, 2, Array, false, Arra$
#4 /opt/bitnami/apache2/htdocs/d1/directus/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(190): Directus\Database\TableGateway\RelationalTableGateway->addOrUpdateToManyRelationships(Object(Directus\Database\Object\Ta$
#5 /opt/bitnami/apache2/htdocs/d1/directus/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(77): Directus\Database\TableGateway\RelationalTableGateway->manageRecordUpdate('ad_report', Array, 1)
#6 /opt/bitnami/apache2/htdocs/d1/directus/api/routes/A1/Entries.php(202): Directus\Database\TableGateway\RelationalTableGateway->updateRecord(Array, 1)
#7 [internal function]: Directus\API\Routes\A1\Entries->row('ad_report', '1')
#8 /opt/bitnami/apache2/htdocs/d1/directus/vendor/slim/slim/Slim/Route.php(468): call_user_func_array(Array, Array)
#9 /opt/bitnami/apache2/htdocs/d1/directus/vendor/slim/slim/Slim/Slim.php(1357): Slim\Route->dispatch()
#10 /opt/bitnami/apache2/htdocs/d1/directus/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call()
#11 /opt/bitnami/apache2/htdocs/d1/directus/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call()
#12 /opt/bitnami/apache2/htdocs/d1/directus/api/core/Directus/Slim/CorsMiddleware.php(19): Slim\Middleware\MethodOverride->call()
#13 /opt/bitnami/apache2/htdocs/d1/directus/api/core/Directus/Slim/HttpCacheMiddleware.php(18): Directus\Slim\CorsMiddleware->call()
#14 /opt/bitnami/apache2/htdocs/d1/directus/api/core/Directus/Slim/ResponseCacheMiddleware.php(37): Directus\Slim\HttpCacheMiddleware->call()
#15 /opt/bitnami/apache2/htdocs/d1/directus/vendor/slim/slim/Slim/Slim.php(1302): Directus\Slim\ResponseCacheMiddleware->call()
#16 /opt/bitnami/apache2/htdocs/d1/directus/api/core/Directus/Application/Application.php(103): Slim\Slim->run()
#17 /opt/bitnami/apache2/htdocs/d1/directus/api/api.php(534): Directus\Application\Application->run()
#18 {main}
@Nicolaidavies
Copy link

Nicolaidavies commented Jan 2, 2018 •
edited by rijkvanzanten

I am facing the same issue when trying to add multiple files.

Running version 6.4.2 from https://github.com/directus/directus-docker.

Happy to provide full bug report if necessary.

Logs are pretty much identical to the ones above:

Next RuntimeException: This query failed: INSERT INTO `directus_activity` (`activity`) VALUES ('3') in /var/www/html/api/core/Directus/Database/TableGateway/BaseTableGateway.php:783
Stack trace:
#0 /var/www/html/vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(264): Directus\Database\TableGateway\BaseTableGateway->executeInsert(Object(Zend\Db\Sql\Insert))
#1 /var/www/html/api/core/Directus/Database/TableGateway/BaseTableGateway.php(396): Zend\Db\TableGateway\AbstractTableGateway->insert(Array)
#2 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(615): Directus\Database\TableGateway\BaseTableGateway->addOrUpdateRecordByArray(Array, 'directus_activi...')
#3 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(190): Directus\Database\TableGateway\RelationalTableGateway->addOrUpdateToManyRelationships(Object(Directus\Database\Object\Table), Array, Array, false, Array)
#4 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(77): Directus\Database\TableGateway\RelationalTableGateway->manageRecordUpdate('places', Array, 1)
#5 /var/www/html/api/core/Directus/Services/EntriesService.php(39): Directus\Database\TableGateway\RelationalTableGateway->updateRecord(Array, 1)
#6 /var/www/html/api/routes/A1/Entries.php(29): Directus\Services\EntriesService->createEntry('places', Array, Array)
#7 [internal function]: Directus\API\Routes\A1\Entries->rows('places')
#8 /var/www/html/vendor/slim/slim/Slim/Route.php(468): call_user_func_array(Array, Array)
#9 /var/www/html/vendor/slim/slim/Slim/Slim.php(1357): Slim\Route->dispatch()
#10 /var/www/html/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call()
#11 /var/www/html/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call()
#12 /var/www/html/api/core/Directus/Slim/CorsMiddleware.php(19): Slim\Middleware\MethodOverride->call()
#13 /var/www/html/api/core/Directus/Slim/HttpCacheMiddleware.php(18): Directus\Slim\CorsMiddleware->call()
#14 /var/www/html/api/core/Directus/Slim/ResponseCacheMiddleware.php(37): Directus\Slim\HttpCacheMiddleware->call()
#15 /var/www/html/vendor/slim/slim/Slim/Slim.php(1302): Directus\Slim\ResponseCacheMiddleware->call()
#16 /var/www/html/api/core/Directus/Application/Application.php(103): Slim\Slim->run()
#17 /var/www/html/api/api.php(534): Directus\Application\Application->run()
#18 {main}

@wellingguzman
Copy link

Hey @cjstage, @Nicolaidavies thanks for the report/logs, would it be possible to share the schema (mysql dump) of the database you are using, in order to us replicate this issue?

@Nicolaidavies
Copy link

@wellingguzman please find a dump here: https://www.dropbox.com/s/9hvp7ha4647d9i6/backup.sql?dl=1

@iKonrad
Copy link

iKonrad commented Jan 11, 2018 •
edited

I had a similar issue and it turned out that there wasn't a default value set for the 'sort' property which caused the insert query to fail. Maybe that's the case?

Best thing to be 100% sure, is to copy the query in question and try to run it manually in MySQL.

@wellingguzman
Copy link

Thanks @Nicolaidavies. I will give it a shot later today.

@iKonrad it likely to be that as well, but the logs are different in this case, or can be both.

Ref to sort issue ticket: #2007

@Nicolaidavies
Copy link

@wellingguzman I just noticed that I sent you the wrong mysql dump. I removed the row giving me issues from the table and took a mysql dump of that. Not particularly useful when trying to figure out what's wrong. Please find the mysql dump with the issue here: https://www.dropbox.com/s/6uclxutfv1mmb7z/backup.sql?dl=1

I apologize for the inconvenience. I hope I didn't waste your time.

Let me also elaborate a bit on the issue:

Other than not being able to create a new row with a type of multiple files, I am also not able to view other rows that were created without the multi files type.

I also noticed that the logs I posted earlier were incomplete. Please find the full logs here:

ERROR - 2018-01-14T15:33:02-05:00 - PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'activity' in 'field list' in /var/www/html/vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php:239
Stack trace:
#0 /var/www/html/vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php(239): PDOStatement->execute()
#1 /var/www/html/vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(308): Zend\Db\Adapter\Driver\Pdo\Statement->execute()
#2 /var/www/html/api/core/Directus/Database/TableGateway/BaseTableGateway.php(757): Zend\Db\TableGateway\AbstractTableGateway->executeInsert(Object(Zend\Db\Sql\Insert))
#3 /var/www/html/vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(264): Directus\Database\TableGateway\BaseTableGateway->executeInsert(Object(Zend\Db\Sql\Insert))
#4 /var/www/html/api/core/Directus/Database/TableGateway/BaseTableGateway.php(396): Zend\Db\TableGateway\AbstractTableGateway->insert(Array)
#5 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(615): Directus\Database\TableGateway\BaseTableGateway->addOrUpdateRecordByArray(Array, 'directus_activi...')
#6 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(190): Directus\Database\TableGateway\RelationalTableGateway->addOrUpdateToManyRelationships(Object(Directus\Database\Object\Table), Array, Array, false, Array)
#7 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(77): Directus\Database\TableGateway\RelationalTableGateway->manageRecordUpdate('places', Array, 1)
#8 /var/www/html/api/core/Directus/Services/EntriesService.php(39): Directus\Database\TableGateway\RelationalTableGateway->updateRecord(Array, 1)
#9 /var/www/html/api/routes/A1/Entries.php(29): Directus\Services\EntriesService->createEntry('places', Array, Array)
#10 [internal function]: Directus\API\Routes\A1\Entries->rows('places')
#11 /var/www/html/vendor/slim/slim/Slim/Route.php(468): call_user_func_array(Array, Array)
#12 /var/www/html/vendor/slim/slim/Slim/Slim.php(1357): Slim\Route->dispatch()
#13 /var/www/html/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call()
#14 /var/www/html/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call()
#15 /var/www/html/api/core/Directus/Slim/CorsMiddleware.php(19): Slim\Middleware\MethodOverride->call()
#16 /var/www/html/api/core/Directus/Slim/HttpCacheMiddleware.php(18): Directus\Slim\CorsMiddleware->call()
#17 /var/www/html/api/core/Directus/Slim/ResponseCacheMiddleware.php(37): Directus\Slim\HttpCacheMiddleware->call()
#18 /var/www/html/vendor/slim/slim/Slim/Slim.php(1302): Directus\Slim\ResponseCacheMiddleware->call()
#19 /var/www/html/api/core/Directus/Application/Application.php(103): Slim\Slim->run()
#20 /var/www/html/api/api.php(534): Directus\Application\Application->run()
#21 {main}

Next Zend\Db\Adapter\Exception\InvalidQueryException: Statement could not be executed (42S22 - 1054 - Unknown column 'activity' in 'field list') in /var/www/html/vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php:244
Stack trace:
#0 /var/www/html/vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(308): Zend\Db\Adapter\Driver\Pdo\Statement->execute()
#1 /var/www/html/api/core/Directus/Database/TableGateway/BaseTableGateway.php(757): Zend\Db\TableGateway\AbstractTableGateway->executeInsert(Object(Zend\Db\Sql\Insert))
#2 /var/www/html/vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(264): Directus\Database\TableGateway\BaseTableGateway->executeInsert(Object(Zend\Db\Sql\Insert))
#3 /var/www/html/api/core/Directus/Database/TableGateway/BaseTableGateway.php(396): Zend\Db\TableGateway\AbstractTableGateway->insert(Array)
#4 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(615): Directus\Database\TableGateway\BaseTableGateway->addOrUpdateRecordByArray(Array, 'directus_activi...')
#5 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(190): Directus\Database\TableGateway\RelationalTableGateway->addOrUpdateToManyRelationships(Object(Directus\Database\Object\Table), Array, Array, false, Array)
#6 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(77): Directus\Database\TableGateway\RelationalTableGateway->manageRecordUpdate('places', Array, 1)
#7 /var/www/html/api/core/Directus/Services/EntriesService.php(39): Directus\Database\TableGateway\RelationalTableGateway->updateRecord(Array, 1)
#8 /var/www/html/api/routes/A1/Entries.php(29): Directus\Services\EntriesService->createEntry('places', Array, Array)
#9 [internal function]: Directus\API\Routes\A1\Entries->rows('places')
#10 /var/www/html/vendor/slim/slim/Slim/Route.php(468): call_user_func_array(Array, Array)
#11 /var/www/html/vendor/slim/slim/Slim/Slim.php(1357): Slim\Route->dispatch()
#12 /var/www/html/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call()
#13 /var/www/html/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call()
#14 /var/www/html/api/core/Directus/Slim/CorsMiddleware.php(19): Slim\Middleware\MethodOverride->call()
#15 /var/www/html/api/core/Directus/Slim/HttpCacheMiddleware.php(18): Directus\Slim\CorsMiddleware->call()
#16 /var/www/html/api/core/Directus/Slim/ResponseCacheMiddleware.php(37): Directus\Slim\HttpCacheMiddleware->call()
#17 /var/www/html/vendor/slim/slim/Slim/Slim.php(1302): Directus\Slim\ResponseCacheMiddleware->call()
#18 /var/www/html/api/core/Directus/Application/Application.php(103): Slim\Slim->run()
#19 /var/www/html/api/api.php(534): Directus\Application\Application->run()
#20 {main}

Next RuntimeException: This query failed: INSERT INTO `directus_activity` (`activity`) VALUES ('4') in /var/www/html/api/core/Directus/Database/TableGateway/BaseTableGateway.php:783
Stack trace:
#0 /var/www/html/vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(264): Directus\Database\TableGateway\BaseTableGateway->executeInsert(Object(Zend\Db\Sql\Insert))
#1 /var/www/html/api/core/Directus/Database/TableGateway/BaseTableGateway.php(396): Zend\Db\TableGateway\AbstractTableGateway->insert(Array)
#2 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(615): Directus\Database\TableGateway\BaseTableGateway->addOrUpdateRecordByArray(Array, 'directus_activi...')
#3 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(190): Directus\Database\TableGateway\RelationalTableGateway->addOrUpdateToManyRelationships(Object(Directus\Database\Object\Table), Array, Array, false, Array)
#4 /var/www/html/api/core/Directus/Database/TableGateway/RelationalTableGateway.php(77): Directus\Database\TableGateway\RelationalTableGateway->manageRecordUpdate('places', Array, 1)
#5 /var/www/html/api/core/Directus/Services/EntriesService.php(39): Directus\Database\TableGateway\RelationalTableGateway->updateRecord(Array, 1)
#6 /var/www/html/api/routes/A1/Entries.php(29): Directus\Services\EntriesService->createEntry('places', Array, Array)
#7 [internal function]: Directus\API\Routes\A1\Entries->rows('places')
#8 /var/www/html/vendor/slim/slim/Slim/Route.php(468): call_user_func_array(Array, Array)
#9 /var/www/html/vendor/slim/slim/Slim/Slim.php(1357): Slim\Route->dispatch()
#10 /var/www/html/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call()
#11 /var/www/html/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call()
#12 /var/www/html/api/core/Directus/Slim/CorsMiddleware.php(19): Slim\Middleware\MethodOverride->call()
#13 /var/www/html/api/core/Directus/Slim/HttpCacheMiddleware.php(18): Directus\Slim\CorsMiddleware->call()
#14 /var/www/html/api/core/Directus/Slim/ResponseCacheMiddleware.php(37): Directus\Slim\HttpCacheMiddleware->call()
#15 /var/www/html/vendor/slim/slim/Slim/Slim.php(1302): Directus\Slim\ResponseCacheMiddleware->call()
#16 /var/www/html/api/core/Directus/Application/Application.php(103): Slim\Slim->run()
#17 /var/www/html/api/api.php(534): Directus\Application\Application->run()
#18 {main}

Thanks for the help!

@scallyw4g
Copy link

I just ran into the exact same issue which was solved by adding a default value to the sort on the junction table, as suggested by @iKonrad

@Nicolaidavies
Copy link

@jjbandit & @iKonrad thank you for the suggestion.

Where exactly do I need to add a sort value? Is it on the table I created (places), directus_activity or directus_files.

How do I add a default value to the sort? Is it through the directus UI or do I have to run some SQL?

@Nicolaidavies
Copy link

Just to clarify, I am not joining any custom tables I created.

I just want to add a 'multiple files' column to a table I created. In other words there is nothing custom, Ideally this should work out of the box as it is functionality provided by directus.

Please see a screen recording here: https://www.dropbox.com/s/4n1i73184s0c7sw/Directus_errors.mov?dl=0

As you can see from the video, I am able to save before adding this new column. But as soon as I add the multiple files column I am unable to save.

@wellingguzman
Copy link

Hey @Nicolaidavies, when you create the multiple files column, you didn't create the correct relationship, and make directus activity as your junction table.

Read how to the m2m (or any other relationships) works on Directus, here: https://medium.com/directus/understanding-relationships-it-isnt-complicated-35e0dcf78450

@scallyw4g
Copy link

@Nicolaidavies You need to create a junction table - this is a third table that will act as a sort of glue between the directus_files and places tables you're attempting to join.

This new table, let's call it files_places_junction, should have two columns, one for the numeric ID of the file record your joining, and one for the numeric ID of the places record your joining. Adding the default sorting value (or presumably de-selecting sort when creating the table) should get you what you want.

@Nicolaidavies
Copy link

@jjbandit Thank you for your answer. I got it working.

@wellingguzman Thank you for the continued help.

I was under the impression that the default values that were filled in after selecting "Multiple files" would create the correct relationship.

Since I got it working I'm just curious at this point: how would one setup the relationship with directus_activity? Is it possible to change the default values so you don't have to change anything after selecting "Multiple files"?

Once again, thanks for creating directus, it's saved me several hours if not days of work!

@wellingguzman
Copy link

When you choose multiple files it automatically pick directus_files as related table, but you would need to create a new junction table, that will make the connections between Places and Directus Files, and that table, ex: places_images will have three columns at least id, place_id and file_id.

Let us know if this works for you.

As this is a configuration issue and not an actual bug I will close this ticket.

Note: the next version will include a default value (nullable) for sort columns.

Ref: d758bc5#diff-34b430e833d5845fe44fc8956d48de07

@github-actions github-actions bot locked as resolved and limited conversation to collaborators Feb 5, 2024
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@wellingguzman @Nicolaidavies @iKonrad @scallyw4g @cjstage