| Project: | Link |
| Version: | 7.x-1.x-dev |
| Component: | Code |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs review |
Issue Summary
Hello!
In case you'll load (with node_load) node with filled Link field, then will fill programmatically some other field, and then will try to save your new value with field_attach_update(), you'll receive an error. In case you have empty Link field, everything will be fine. In case you do not load node in your module (for example, you use an argument sent by hook_node_insert(), everything is all right as well. This problem is specific to Link module, other contrib fields (Date, for example) are not affected.
Error message:
<?php
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1: INSERT INTO {field_data_field_link1} (entity_type, entity_id, revision_id, bundle, delta, language, field_link1_url, field_link1_title, field_link1_attributes) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, ); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 8 [:db_insert_placeholder_2] => 8 [:db_insert_placeholder_3] => article [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => <a href="http://sky2.me" title="http://sky2.me" rel="nofollow">http://sky2.me</a> [:db_insert_placeholder_7] => TestLinkTitle ) in field_sql_storage_field_storage_write() (line 424 of /var/www/test.sky37.pp.ua/public_html/modules/field/modules/field_sql_storage/field_sql_storage.module).
?>To reproduce this issue, you need clean D7.8 installation, Link 7.x-1.x-dev from 2011-Sep-25 (or 7.x-1.0-beta1, doesn't matter) enabled, and my test module TestLink (attached below). Then you should add 4 fields to Article content type (all fields with default settings): field_integer1 (Integer), field_integer2 (Integer), field_text1 (Text) and field_link1 (Link).
Now we'll add new node (type Article, obviously): Title will be set to TestTitle1, and field_text1 will be set to TestText. After saving node everything is fine. TestLink module loaded our node, added appropriate values to field_integer1 and field_integer2, saved them, no errors appeared.
Let's add one more node. Tile - TestTitle2, field_text1 - TestText, Link Title - TestLinkTitle, Link URL - http://sky2.me. That's it, we received error message listed behind. It was triggered by field_attach_update.
Set priority to major, because this bug can affect other modules, and it is not that simple to realise the reason of unexpected behaviour.
| Attachment | Size |
|---|---|
| linktest.zip | 788 bytes |
Comments
#1
please put not to use links and code which should not be executed inside code tags. This time I did it for you.
Thanks for understanding.
(I'll try to reproduce your issue)
#2
Just an idea from another issue ... do you have list field enabled?
#3
I have list field enabled for the other node type (without Link field) on my primary site. On my test installation I have no additional fields except listed in the first post. Now I tried to disable core List module (it is enabled by default), but nothing changed, error message is just the same. So, I think, this bug is not caused by interference of Link module and list field.
And thank you for formatting my first message!
#4
LInk module depends on list field. Thats why I ask. Since list field is core it isn't mentioned somewhere (what I will change)
but ...uhm ... 2 be honest I pretty have no idea what you actually are trying to do with your module and where it interferes / conflicts with link field, since you don't want to save a link but rather another field value ?? So, why is your code touching the link field value on the way back?
#5
Well, my module integrates Astrometry.net astrophoto solver with Drupal, so you can upload your astrophoto to Drupal site, and then this module fetches appropriate nodes from DB, gives image to Astrometry program, and then parse it's answer, and add various data to that node. So you can know, what region of sky is on your photo, are there any nebulas or galaxies and so on.
But all that has nothing to do with our problem. I created test module specifically to be sure there are no problems with my code. So each and every module, which tries to load node (with node_load), then tries to add some value to node field, and then save new values with field_attach_update(), should face it. It does nothing with Link field, but has some interference with it - and it is caused by usage of standard core APIs, not by some module-specific code. That's the point of this issue, and that's why I think it is rather serious. I would never mark an issue as 'major', if it only affects my own module.
And, of course, I do not know the reason of such interference - otherwise there were a patch in the first message. But looks like my knowledge of core and Link internals is not deep enough.
#6
Hi,
Was getting this issue too so I look a little deeper in the APIs and found a fix by putting "field_attach_load" after the node_load:
<?php
$node = node_load($nid);
field_attach_load('node', array($node->nid => $node));
?>
Fixed it for me, maybe it'll work for you!
Cheers
Tom
#7
@renat: thanks for clarifying,
BTW: as an enthusiast for anything all about astrophysics, stars and the universe I like the idea of your site very much :)
@thomjjames, thanks for pointing to this.
I will look if we can do here anything from our site to make it easier with link field in future.
#8
@Digidog, I'm glad to hear that! Hope we'll be able to make Drupal best CMS for astronomical purposes ever.
As of our issue, solution from #6 don't work for me - neither for Astrometry, nor for LinkTest module (listed in the first message).
#9
It is already :)! Beside yours, I remember that I saw already some websites based on Drupal regarding the topic stars and science. Can't remember where, but I know that there were a subsite of NASA running on Drupal.
#10
I am also experiencing this problem.
The issue seems to relate to the link attributes.
I am unable to create / update any nodes with links set.
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1: INSERT INTO {field_data_field_related_links} (entity_type, entity_id, revision_id, bundle, delta, language, field_related_links_url, field_related_links_title, field_related_links_attributes) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, ); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 4962 [:db_insert_placeholder_2] => 6437 [:db_insert_placeholder_3] => page [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => http://www.google.com [:db_insert_placeholder_7] => google ) in field_sql_storage_field_storage_write() (line 450 of /www/apps/drupal7/modules/field/modules/field_sql_storage/field_sql_storage.module).#11
I had similar issue. The solution for me was to add a call to "field_attach_presave" before "field_attach_update". Calling "field_attach_presave" will call "hook_field_presave" which the Link modules uses to make sure the attributes value is filled.
I filed patch for the term_merge module with the same issue here: http://drupal.org/node/1229740#comment-5200084
#12
Yes, call to "field_attach_presave" helps me too. Looks like it should be used every time we use "field_attach_update" function? And, consequently, Link module works as expected, but we should rather update Drupal API documentation?
#13
I have the same issue
trying to delete translation of node which has a translatable link attached and I get the same error
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1: INSERT INTO {field_data_field_webcheckin} (entity_type, entity_id, revision_id, bundle, delta, language, field_webcheckin_url, field_webcheckin_title, field_webcheckin_attributes) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, ); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 88 [:db_insert_placeholder_2] => 88 [:db_insert_placeholder_3] => airline_carrier [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => en [:db_insert_placeholder_6] => https://yahoo.com/SearchWebCheckIn.aspx [:db_insert_placeholder_7] => ) in field_sql_storage_field_storage_write() (line 448 of /home/example.com/public_html/d7/modules/field/modules/field_sql_storage/field_sql_storage.module).I don't understand how other people fixed this problem ?
Why is this issue a feature request ?
#14
Hi, Giorgos!
That issue should not be marked as a "feature request", as for me - but, probably, it should be marked as "works as designed". Because both for me and for @tedbow this issue was caused by missed call to "field_attach_presave" function behind "field_attach_update". And that call was missed in other contributed modules, not in Link itself. That modules tried to save data without giving an opportunity to Link module to make necessary preprocessing.
Would be nice to have more information about your installation, and you probably should try to disable other contrib modules - if problem will vanish, we will be able to find, where "field_attach_presave" function call should be added.
#15
@renat
thanks I am going to try your suggestion
BTW I was trying to delete translation of field entity translatable node and was getting this horible error
on the other hand if I try to delete the node than all works fine
#16
I traced the cause of this problem to another module (OpenCalais - http://drupal.org/project/opencalais - opencalais.module, line 352) trying to call node_view in its node_presave hook. This results in link_field_prepare_view being called before the link has been saved to the database, subsequently resulting in the attributes being passed to the SQL storage unserialized (see _link_sanitize).
Therefore, to prevent this problem occurring, I figured an easy fix would be to check for the presence of $node->nid in _link_sanitize - ensuring the node has been saved.
Patch is attached.
#17
#18
Unfortunately can't test patch from #16, because I do not use OpenCalais module, so in my case there were other problems, not connected with it.
#19
I don't think it's specific to the OpenCalais module. Any module that attempts to build the node content before it has been saved to the database will cause this issue.
My patch resolves that.
#20
This is also a problem I am having. I have nodes that use the Link module and work fine, but when I create another Node Reference field and try to update a node that has the Link field populated I get the following error:
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1: INSERT INTO {field_data_field_shop_website} (entity_type, entity_id, revision_id, bundle, delta, language, field_shop_website_url, field_shop_website_title, field_shop_website_attributes) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, ); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 7290 [:db_insert_placeholder_2] => 7290 [:db_insert_placeholder_3] => shop [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => www.example.com [:db_insert_placeholder_7] => ) in field_sql_storage_field_storage_write() (line 448 of /var/www/vhosts/landofink.com/dev/modules/field/modules/field_sql_storage/field_sql_storage.module).Update:
I applied the patch above and it resolve my ability to edit and save nodes with a node reference that had a link field. However, I was also getting an error when trying to bulk reassign authors using VBO and that is still the case:
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1: INSERT INTO {field_data_field_artist_website} (entity_type, entity_id, revision_id, bundle, delta, language, field_artist_website_url, field_artist_website_title, field_artist_website_attributes) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, ); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 7293 [:db_insert_placeholder_2] => 7293 [:db_insert_placeholder_3] => artist [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => example.com [:db_insert_placeholder_7] => ) in field_sql_storage_field_storage_write() (line 448 of /var/www/vhosts/landofink.com/dev/modules/field/modules/field_sql_storage/field_sql_storage.module).Resolution: Patch Corresponding Node References File http://drupal.org/files/add_field_attach_presave-1354816-1.patch
#21
Note that the CNR bug mentioned in the post above has been commited in the meantime and the problem doesn't occur anymore with the latest cnr-7.x-4.x-dev. (In case someone lands here when debugging this)
#22
Oh excellent. I spent hours tracking this down yesterday. For some reason Googling the error code didn't find this thread or the CNR thread. Thanks!
#23
I had the same problems after deleting a translation (a entity translation). Patch #16 worked for me!
thanks!