paragraphs_post_update_rebuild_parent_fields() fails on update [#3076648]

Problem/Motivation

While updating the module (drush updatedb):

Failed: Drupal\Core\Database\DatabaseExceptionWrapper: 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 &#039;AS                  [error]
FROM
node_revision__field_address f
INNER JOIN paragraphs_item_revision_fiel&#039; at line 1: SELECT f.entity_id AS entity_id, f. AS
FROM
{node_revision__field_address} f
INNER JOIN {paragraphs_item_revision_field_data} p ON f. = p.revision_id
WHERE ((p.parent_id &lt;&gt; f.entity_id)) OR (p.parent_type &lt;&gt; :db_condition_placeholder_0) OR (p.parent_field_name &lt;&gt; :db_condition_placeholder_1)
ORDER BY p.revision_id ASC
LIMIT 100 OFFSET 0; Array
(
    [:db_condition_placeholder_0] =&gt; node
    [:db_condition_placeholder_1] =&gt; field_address
)
 in paragraphs_post_update_rebuild_parent_fields() (regel 240 van /var/www/html/web/modules/contrib/paragraphs/paragraphs.post_update.php).

If I check the $current_field array, the revision_column is NULL for multiple fields on my site. Strange?

array(6) {                                                                 
  ["entity_type_id"]=>                                                     
  string(4) "node"                                                         
  ["field_name"]=>                                                         
  string(13) "field_address"                                               
  ["revision_table"]=>                                                     
  string(28) "node_revision__field_address"                                
  ["entity_id_column"]=>                                                   
  string(9) "entity_id"                                                    
  ["revision_column"]=>                                                    
  NULL                                                                     
  ["langcode_column"]=>                                                    
  string(8) "langcode"                                                     
}

Proposed resolution

See Patch.

Remaining tasks

Review patch.

User interface changes

None

API changes

None

Data model changes

None

Comment	File	Size	Author
	paragraphs_no_revision_column_name.patch	721 bytes	tomhollevoet
	8.x-1.x: PHP 7.2 & MySQL 5.5, D8.8.6 164 pass PHP 7.3 & PostgreSQL 9.5, D8.9.0-beta3 164 pass PHP 7 & SQLite 3.14, D8.9.0-beta3 159 pass, 6 fail

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

22 August 2019 at 19:31

tomhollevoet created an issue. See original summary.

Comment #2

Spokje

Dutch

CreditAttribution: Spokje at Annertech commented 25 September 2019 at 17:29

Exact same error using the same (drush updb) method.
Weirdly this is the first out of 25 sites that fails in this way. All others had no problems at all.

Anyway: RTBC for me.

Comment #3

Spokje

Dutch

CreditAttribution: Spokje at Annertech commented 25 September 2019 at 17:31

Status:

Needs review

» Reviewed & tested by the community

Comment #4

Berdir

German

Switzerland

CreditAttribution: Berdir at MD Systems GmbH commented 30 September 2019 at 20:24

field_address? Is that really a paragraph field or is it something else?

Comment #5

Spokje

Dutch

CreditAttribution: Spokje at Annertech commented 30 September 2019 at 20:50

@Berdir:

In my case the error is/was:

>  [error]  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 'AS
> FROM
> paragraph_revision__field_p_tl_tiles f
> INNER JOIN paragraphs_item_revis' at line 1: SELECT f.entity_id AS entity_id, f. AS
> FROM
> {paragraph_revision__field_p_tl_tiles} f
> INNER JOIN {paragraphs_item_revision_field_data} p ON f. = p.revision_id
> WHERE (((p.parent_id <> f.entity_id)) OR (p.parent_type <> :db_condition_placeholder_0) OR (p.parent_field_name <> :db_condition_placeholder_1)) AND ((p.langcode = f.langcode))
> ORDER BY p.revision_id ASC
> LIMIT 100 OFFSET 0; Array
> (
>     [:db_condition_placeholder_0] => paragraph
>     [:db_condition_placeholder_1] => field_p_tl_tiles
> )

So at the very least the parent is a paragraph.

Looking at the report at /admin/reports/fields:

Field name 	        Entity type  Field type 	
field_p_tl_tiles 	paragraph    Entity reference revisions (module: entity_reference_revisions)

Comment #6

Spokje

Dutch

CreditAttribution: Spokje at Annertech commented 30 September 2019 at 20:55

(Semi?) Interesting addendum: If I do the paragraphs update together with a Drupal Core update from D8.6.17 => D8.7.7, the patch is not needed and the post-update does not fail on the mentioned field.

Comment #7

Berdir

German

Switzerland

CreditAttribution: Berdir at MD Systems GmbH commented 30 September 2019 at 20:59

I'm more interested in the target entity type of field_p_tl_tiles than its host entity type :)

Comment #8

Spokje

Dutch

CreditAttribution: Spokje at Annertech commented 30 September 2019 at 21:08

Its parent is another Paragraph.

Comment #9

Berdir

German

Switzerland

CreditAttribution: Berdir at MD Systems GmbH commented 21 May 2020 at 09:14

Status:

Reviewed & tested by the community

» Fixed

I still don't understand when this happens, but it shouldn't interfere with anything that currently works. We have upgrade path tests now, they work, the sqlite fails seem unrelated.