Unable to update from 8.6.13 to 8.7: "SQLSTATE[42000]: Syntax error or access violation"

@JohannFischmann, what version of MariaDB are you using?

I thought the same when I saw this.

Same error, but for taxonomy terms, it seems to be related to the entity field definition update process who's no more supported by Drupal 8.7.

I get this error when i try to list terms:

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 ') AS expression FROM tfr INNER JOIN tr ON tfr. = tr. AND tr. = 0 INNER JOIN (S' at line 1: SELECT tfr.tid AS tid, MAX(tfr.) AS expression FROM {} tfr INNER JOIN {} tr ON tfr. = tr. AND tr. = 0 INNER JOIN (SELECT t.tid AS tid, t.langcode AS langcode, MAX(t.) AS expression FROM {} t WHERE t. = :db_condition_placeholder_0 GROUP BY t.tid, t.langcode) mr ON tfr. = mr. AND tfr.langcode = mr.langcode GROUP BY tfr.tid; Array ( [:db_condition_placeholder_0] => 1 ) in Drupal\taxonomy\TermStorage->getTermIdsWithPendingRevisions() (line 404 of /home/clients/abfc430b1c3195444/sitename/core/modules/taxonomy/src/TermStorage.php).
Severity

And this error in the status report:

Entity/field definitions
Mismatched entity and/or field definitions
The following changes were detected in the entity type and field definitions.
Taxonomy term
The Taxonomy term entity type needs to be updated.
The Revision ID field needs to be installed.
The Revision create time field needs to be installed.
The Revision user field needs to be installed.
The Revision log message field needs to be installed.
The Default revision field needs to be installed.
The Revision translation affected field needs to be installed.
Custom menu link
The Custom menu link entity type needs to be updated.
The Revision ID field needs to be installed.
The Revision create time field needs to be installed.
The Revision user field needs to be installed.
The Revision log message field needs to be installed.
The Default revision field needs to be installed.
The Revision translation affected field needs to be installed.

How to fix this ?

Thanks

@jbfelix, those errors in the status report mean that you need to run the database updates for 8.7.0, either with Drush or via update.php.

@amateescu

That's what i did (with drush and update.php), but the entity fields definitions are not updated.
No additional error.

@jbfelix, do you have a database backup that could be restored and then try to run the updates again? The problem seems to be that Drupal thinks it ran all the updates, when in fact it didn't.

@JohannFischmann, I installed MariaDB 10.1.38 locally in a docker container and tried to reproduce your issue, but I couldn't. This is the SQL query generated by the node_update_8700() function:

ALTER TABLE node_field_data CHANGE `uid` `uid` INT unsigned NOT NULL COMMENT 'The ID of the target entity.';

This query is generated by \Drupal\Core\Database\Driver\mysql\Schema::changeField(). Are you using a database driver that's not provided by Drupal core?

@amateescu

I have a backup and i have already re-tested an update but the errors are still showing.

@jbfelix, then my advice is to wait a few days until 8.7.2 is released, because it will contain quite a few upgrade path fixes..

@amateescu
OK Thanks

@JohannFischmann @jbfelix Thanks for taking part in this issue. If you'd like to help us make sure the 8.8.0 update is as smooth as possible, please consider signing up for the beta testing program at https://goo.gl/forms/bMBTMRSY3sKEscUJ3

Accessing Taxonomy overview page throws error after upgrade to 8.7.1 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 ') AS expression FROM tfr INNER JOIN tr ON tfr. = tr. AND tr. = 0 INNER JOIN (S' at line 1: SELECT tfr.tid AS tid, MAX(tfr.) AS expression FROM {} tfr INNER JOIN {} tr ON tfr. = tr. AND tr. = 0 INNER JOIN (SELECT t.tid AS tid, t.langcode AS langcode, MAX(t.) AS expression FROM {} t WHERE t. = :db_condition_placeholder_0 GROUP BY t.tid, t.langcode) mr ON tfr. = mr. AND tfr.langcode = mr.langcode GROUP BY tfr.tid; Array ( [:db_condition_placeholder_0] => 1 ) in Drupal\taxonomy\TermStorage->getTermIdsWithPendingRevisions() (line 404 of /opt/web/core/modules/taxonomy/src/TermStorage.php).

Any help is apprceited.

Do you have any update on this issue

I got similar problem with taxonomy terms after some several updates from 8.6.0 to 8.7.x - now I can't open taxonomy vocabulary pages with error:

<em class="placeholder">Drupal\Core\Database\DatabaseExceptionWrapper</em>: 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 expression
FROM
 tfr
INNER JOIN  tr ON tfr. = tr. AND tr. = 0
INNER JOIN (S&#039; at line 1: SELECT tfr.tid AS tid, MAX(tfr.) AS expression
FROM
{} tfr
INNER JOIN {} tr ON tfr. = tr. AND tr. = 0
INNER JOIN (SELECT t.tid AS tid, t.langcode AS langcode, MAX(t.) AS expression
FROM
{} t
WHERE t. = :db_condition_placeholder_0
GROUP BY t.tid, t.langcode) mr ON tfr. = mr. AND tfr.langcode = mr.langcode
GROUP BY tfr.tid; Array
(
    [:db_condition_placeholder_0] =&gt; 1
)
 in <em class="placeholder">Drupal\taxonomy\TermStorage-&gt;getTermIdsWithPendingRevisions()</em> (line <em class="placeholder">404</em> of <em class="placeholder">core/modules/taxonomy/src/TermStorage.php</em>). <pre class="backtrace">Drupal\Core\Database\Statement-&gt;execute(Array, Array) (Line: 631)

All updates are correctly installed but drush entup shows outdated entity schemas. When I try to update entity schema via devel_entity_updates module - I got the error:

In DevelEntityDefinitionUpdateManager.php line 169:
                                                                                         
  The entity schema update for the taxonomy_term entity type requires a data migration.  

Manually repeating Taxonomy module update functions via:

drush eval "drupal_set_installed_schema_version('taxonomy', 8503);"
drupal upex

don't help too - they executes normally without erros:

 Switch site into maintenance mode
 Executing required previous updates
 Executing update function "8601" of module "taxonomy"
 Executing update function "8701" of module "taxonomy"
 Operating in maintenance mode off

 // update:entities
 Operating in maintenance mode on
 Starting the entity updates
 Finished the entity updates
 Operating in maintenance mode off
 // cache:rebuild

 Rebuilding cache(s), wait a moment please.

                                                                                                                        
 [OK] Done clearing cache(s).                                                                                           
                                                                                                                        

But the fatal error on taxonomy pages are still here.

I may be having this same issue.

I can't access taxonomy overview pages at all.

Those having issues with updating to 8.7.0 might try to update to the 8.6.x at the time of release of 8.7.0 first. The updates introduced in 8.6.x releases are meant to be executed with the code of 8.6.x. With 8.7.0 removing functionality, bad practice but it happened, the 8.6.x updates don't do what they were supposed to with 8.6 code when run with 8.7 code.

8.7.3 same problem. But! On localhost MariaDB 5.5.58 and working fine. But on prod server 5.5.5 - fails.

I tried to run generated query directly on MariaDB 5.5.58
`
ALTER TABLE {versions} CHANGE
`file_version__v1` `file_version__v1` NOT NULL;
`
But it is incorrect.

@parashutiki Looks like an unrelated issue, suggest to create your own.
The syntax error is because the datatype is missing: CHANGE [old_column] [new_column] [datatype] [ NOT NULL | NULL ]

@mmjvb sorry for disinformation. it not a problem. not working on both servers.
I have used combined field for ContentType and it is the place where error appears. I tried many approaches, but what i got = for some period of time message for inconsistence of fields is disappear and soon later appear again, so i decided do not fight, because i do not understood where could be a problem and what is the reason of i see this message on Status page.

Still experiencing this problem on 8.7.1 and 8.8.0 and 8.8.1

Entity/field definitions
Mismatched entity and/or field definitions
The following changes were detected in the entity type and field definitions.
Content

The node.field_staff_short_description field needs to be updated.

Taxonomy term

The Taxonomy term entity type needs to be updated.
The Revision ID field needs to be installed.
The Revision create time field needs to be installed.
The Revision user field needs to be installed.
The Revision log message field needs to be installed.
The Published field needs to be installed.
The Default revision field needs to be installed.
The Revision translation affected field needs to be installed.

Custom menu link

The Custom menu link entity type needs to be updated.
The Revision ID field needs to be installed.
The Revision create time field needs to be installed.
The Revision user field needs to be installed.
The Revision log message field needs to be installed.
The Default revision field needs to be installed.
The Revision translation affected field needs to be installed.

also

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 MariaDB server version for the right syntax to use near ') AS expression FROM tfr INNER JOIN tr ON tfr. = tr. AND tr. = 0 INNER JOIN (S' at line 1: SELECT tfr.tid AS tid, MAX(tfr.) AS expression FROM {} tfr INNER JOIN {} tr ON tfr. = tr. AND tr. = 0 INNER JOIN (SELECT t.tid AS tid, t.langcode AS langcode, MAX(t.) AS expression FROM {} t WHERE t. = :db_condition_placeholder_0 GROUP BY t.tid, t.langcode) mr ON tfr. = mr. AND tfr.langcode = mr.langcode GROUP BY tfr.tid; Array ( [:db_condition_placeholder_0] => 1 ) in Drupal\taxonomy\TermStorage->getTermIdsWithPendingRevisions() (line 404 of /srv/bindings/21f6285537fa4f6e905fbb0c02565a48/code/core/modules/taxonomy/src/TermStorage.php).

For reference: Drupal 8.7.0 Release notes:

Entity and field system changes

Entity schema operations will now leave backup tables in place for inspection. See the change record on entity update backup data for more details including how to disable this functionality.

Custom menu links and taxonomy terms are now revisionable, which allows them to be used in editorial workflows (similarly to nodes, media, and custom blocks). These changes involve an upgrade path and updates to the respective entity storages. Custom code updating these entities programmatically may need to update to take account revision creation. This may also impact API clients, exported default content, and custom database queries. See the change records for details:
Custom menu links are revisionable
Taxonomy terms are revisionable

Serialized properties of base fields are now automatically unserialized to be consistent with configurable fields. Existing workarounds for this bug might need to be adjusted if they relied on the old behavior of passing a string to those fields. Read the change record about loading serialized field properties.

Have info on what happened, but not how to deal with it:
Support for automatic entity updates has been removed: https://www.drupal.org/node/3034742

Hi, does anyone have an update on this problem ?

Adsymon no, I had to completely rebuild 3 of my sites which had this issue which took me a few weeks to copy all taxonomy terms, content, functionality over to new sites on lastest d8.9.

@effortDee Okok thanks for your answer. I "resolved" this problem by commenting hook_update() who cause this problem and run drush updb after. I dont think it's a good way but it works for me.

Update Issue Summary.

If you have this problem and fixed it, add a comment explaining what you as it might help others.

If you are experiencing this problem, add a comment including the database and version you are using.

Thanks

Adding tag

I know I'm late to the game in this but I just thought that I'd throw in my experience when trying to use the 8.x-1.10 Address module. Installing it into my 9.4.3 / PHP 8.0.13 / MariaDB 10.6.5 environment was a breeze, but using the Drupal UI to add an address field into one of my content types resulted in the following error:

There was a problem creating field Home Address: Exception thrown while performing a schema update. SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.: CREATE TABLE "user__field_home_address" ( `bundle` VARCHAR(128) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance', `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT 'A boolean indicating whether this data item has been deleted', `entity_id` INT unsigned NOT NULL COMMENT 'The entity id this data is attached to', `revision_id` INT unsigned NOT NULL COMMENT 'The entity revision id this data is attached to, which for an unversioned entity type is the same as the entity id', `langcode` VARCHAR(32) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT 'The language code for this data item.', `delta` INT unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields', `field_home_address_langcode` VARCHAR(32) NULL DEFAULT NULL, `field_home_address_country_code` VARCHAR(2) NULL DEFAULT NULL, `field_home_address_administrative_area` VARCHAR(255) NULL DEFAULT NULL, `field_home_address_locality` VARCHAR(255) NULL DEFAULT NULL, `field_home_address_dependent_locality` VARCHAR(255) NULL DEFAULT NULL, `field_home_address_postal_code` VARCHAR(255) NULL DEFAULT NULL, `field_home_address_sorting_code` VARCHAR(255) NULL DEFAULT NULL, `field_home_address_address_line1` VARCHAR(255) NULL DEFAULT NULL, `field_home_address_address_line2` VARCHAR(255) NULL DEFAULT NULL, `field_home_address_organization` VARCHAR(255) NULL DEFAULT NULL, `field_home_address_given_name` VARCHAR(255) NULL DEFAULT NULL, `field_home_address_additional_name` VARCHAR(255) NULL DEFAULT NULL, `field_home_address_family_name` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`entity_id`, `deleted`, `delta`, `langcode`), INDEX `bundle` (`bundle`), INDEX `revision_id` (`revision_id`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8mb4 COMMENT 'Data storage for user field field_home_address.'; Array ( )

I've lodged a support request in their issues queue but thought I'd post about this on here as well given the context. For whatever it's worth, I tried this module against a MySQL backend and had no problems at all. I think it's a MariaDB issue with me or else something that might be misconfigured in my MariaDB settings. Just not sure what it might be...

@Wolf_22 The error you posted above, "1118 Row size too large", is different from the one reported in this issue.

Attaching files that I have done after upgrading to Drupal 9.4, not sure if this will work for everyone but worth a try.
As usual, take DB backup before running these.

I also have the same problem, and I cannot see the taxonomy term overview page. I get this error:

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Taxonomy[taxonomy]: 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 'FROM "config_taxonomy_vocabulary" "config_taxonomy_vocabulary" LIMIT 10 OFFSET 0' at line 1: SELECT FROM "config_taxonomy_vocabulary" "config_taxonomy_vocabulary" LIMIT 10 OFFSET 0; Array ( ) in main() (line 19 of index.php).

Does anybody have some workaround or fix for this? Thank you.

"PHP message: Uncaught PHP Exception 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 ') ORDER BY LENGTH(redirect_source__query) DESC' at line 1: SELECT rid FROM "redirect" WHERE hash IN () ORDER BY LENGTH(redirect_source__query) DESC; Array
(
)

Anyone please help

Related problem: after loading a backup from two days ago (software and DB), emptying the caches, waiting a few minutes, the website worked for about 10 minutes. Then we got this message on several tables (cache..., watchdog). We can repeat this unlimited. We can assume it's not a syntax error.
Apache, drupal 9.5.5, PHP 8.1.17, MYSQL 5.7.40
We are a senior club.
Thanks in advance.

The website encountered an unexpected error. Please try again later.
Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1142 INSERT command denied to user '...............' for table 'watchdog': INSERT INTO "watchdog"

The website encountered an unexpected error. Please try again later.
Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1142 INSERT, UPDATE command denied to user '................' for table 'cache_config': INSERT INTO "cache_config"