SQLSTATE[42000]: Syntax error or access violation after upgrading to 8.x-1.7

And after reinstallation you can't anyway access to the settings form of simple sitemap or even on the content type form.

Seems that the column language_code is missing in the database.

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'language_code' in 'where clause': SELECT s.sitemap_string AS sitemap_string FROM {simplesitemap} s WHERE (language_code = :db_condition_placeholder_0); Array ( [:db_condition_placeholder_0] => fr ) in Drupal\simplesitemap\Simplesitemap->get_sitemap_from_db() (line 55 of modules/simple_sitemap/src/Simplesitemap.php).
Drupal\Core\Database\Statement->execute(Array, Array)
Drupal\Core\Database\Connection->query('SELECT s.sitemap_string AS sitemap_string
FROM 
{simplesitemap} s
WHERE  (language_code = :db_condition_placeholder_0) ', Array, Array)
Drupal\Core\Database\Driver\mysql\Connection->query('SELECT s.sitemap_string AS sitemap_string
FROM 
{simplesitemap} s
WHERE  (language_code = :db_condition_placeholder_0) ', Array, Array)
Drupal\Core\Database\Query\Select->execute()
Drupal\simplesitemap\Simplesitemap->get_sitemap_from_db()

The hook_schema do not contains anymore the language_code field.

Edit : but seems that it's normal in regard to the save_sitemap() method.

In version 1.7

/**
 * Implements hook_schema().
 */
function simplesitemap_schema() {
  $schema['simplesitemap'] = array(
    'description' => 'Holds XML sitemaps as strings for quick retrieval.',
    'fields' => array(
      'id' => array(
        'description' => 'Sitemap chunk unique identifier.',
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
      ),
      'sitemap_string' => array(
        'description' => 'XML sitemap chunk string.',
        'type' => 'text',
        'size' => 'big',
        'not null' => TRUE,
      ),
      'generated' => array(
        'description' => 'Timestamp of sitemap chunk generation.',
        'type' => 'int',
        'default' => 0,
        'not null' => TRUE,
      ),
    ),
    'primary key' => array('id'),
  );
  return $schema;
}

In version 1.5

/**
 * Implements hook_schema().
 */
function simplesitemap_schema() {
  $schema['simplesitemap'] = array(
    'description' => 'Holds XML sitemaps as strings for quick retrieval.',
    'fields' => array(
      'language_code' => array(
        'description' => 'Language code of the sitemap used as unique identifier.',
        'type' => 'varchar',
        'not null' => TRUE,
        'length' => 12,
      ),
      'sitemap_string' => array(
        'description' => 'XML sitemap string.',
        'type' => 'text',
        'size' => 'big',
        'not null' => TRUE,
      ),
    ),
    'primary key' => array('language_code'),
  );
  return $schema;
}

Hi,

I made drush up simple_sitemap.
The hook_update_N have been applied.

I revert simplesitemap to version 1.5 and all is fine. I try then to do an antoher update with drush. Same issue. I checked if database was well updated. It's OK.
I ran the command drush devel-reinstall, without success.

I also try to uninstall simplesitemap and install the 1.7 version from scratch. Same issue.

I wonder is this issue is not due to the content of the sitemap saved in the field "sitemap_string".
I will try to do some more debug.

I revert to version 1.5 and upgraded to 1.6. Simple sitemap works fine.
Upgraded from 1.6 to 1.7. Same issue.

After digging in MySQL, seems that this issue is because the word "generated" is used for a column's title in the simplesitemap table. Since MySQL 5.7.6, "generated" is a reserved word (see https://dev.mysql.com/doc/refman/5.7/en/keywords.html) and so must be quoted if used as a column title. Or more simpler, we can use an another word for this column's title.

And I'm using MySQL 5.7.9.

The patch attached change the word "generated" used in the table by "sitemap_date" (and in the code of course). After applying this patch, simplesitemap 1.7 works fine on MySQL 5.7.9.

thanks for your review

Hi,

I was a bit lucky. After debugging your code, and found nothing, it remained only as a possible cause MySQL itself. And while playing with fields properties in the database, I've got a warning message about this reserved word :-D

Sorry, I forgot that we can't modify existing hook_update_N but add new one.

Testing your patch.
I reinstall simplesitemap on version 1.5 and then do the update to the dev version. The hook simplesitemap_update_8106() don't apply => error : Unable to change the field. field 'generated' not exists.
I reinstall simplesitemap on version 1.7 and then do the update to the dev version. The hook simplesitemap_update_8106() don't apply => error : Unable to change the field. field 'generated' not exists.

In fact, as I'am on MySQL 5.7.9, using db_change_field, db_drop_field on this reserved name does not work.
I success to change the database schema with a db_drop_table and a db_creta_table. but it's a little overkill.

I managed to change the name of the column with a direct query on the table, because I can escape the column title in the query

Database::getConnection()->query('alter table `simplesitemap` CHANGE `generated` sitemap_created int(11) NOT NULL ');

Patch attached. This patch applies to the DEV version