Update to 7.x-1.3 fails on MSSQL and PostgreSQL

I encountered a similar failure upgrading to 7.x-1.3 on MSSQL. Drupal 7.54 on Windows, IIS 7.5, PHP 5.6, and MSSQL Server.

PDOException: SQLSTATE[21S01]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Column name or number of supplied values does not match table definition.: INSERT INTO taxonomy_index_tmp OUTPUT ('')SELECT tx.nid, tx.tid, tx.sticky, tx.created FROM taxonomy_index tx GROUP BY tx.nid, tx.tid; Array ( ) in entityreference_update_7100() (line 178 of E:\inetpubtest\wwwroot\www\sites\all\modules\entityreference\entityreference.install).

Same thing on Postgres 9.6.2. Slightly different error code.

Failed: PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "tx.sticky" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: INSERT INTO taxonomy_index_tmp SELECT tx.* ^: INSERT INTO taxonomy_index_tmp SELECT tx.* FROM taxonomy_index tx GROUP BY tx.nid, tx.tid; Array ( ) in entityreference_update_7100() (line 178 of /web/xxx/uploads/sites/all/modules/entityreference/entityreference.install).

It also fails on PostgreSQL.

DatabaseSchemaObjectExistsException: Tabel taxonomy_index_tmp bestaat al. in DatabaseSchema->createTable() (regel 660 van /srv/Http_Server/Parkstadveendam-nl/drupal/includes/database/schema.inc).

The following updates returned messages

entityreference module

Update #7100
Failed: DatabaseSchemaObjectExistsException: Tabel taxonomy_index_tmp bestaat al. in DatabaseSchema->createTable() (regel 660 van /srv/Http_Server/Parkstadveendam-nl/drupal/includes/database/schema.inc).

After deleting the table taxonomy_index_tmp, the script fails on

The following updates returned messages

entityreference module

Update #7100
Failed: PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "tx.sticky" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: INSERT INTO drupalprod.taxonomy_index_tmp SELECT tx.* ^: INSERT INTO drupalprod.taxonomy_index_tmp SELECT tx.* FROM drupalprod.taxonomy_index tx GROUP BY tx.nid, tx.tid; Array ( ) in entityreference_update_7100() (regel 178 van /srv/Http_Server/Parkstadveendam-nl/drupal/sites/all/modules/entityreference/entityreference.install).

Update... this was a bit of a mess. I was able to get the update script to run simply by adding the groupBy clause that the error mentions. I had to do it for each column in the table so I dropped these two lines into the .install:

    $select->groupBy('tx.sticky');
    $select->groupBy('tx.created');

Here's the resulting problem that arises. The update creates a temporary table called taxonomy_index_tmp, moves the data from taxonomy_index to the new table (removing duplicates as it goes), then removes the taxonomy_index table and renames the _tmp in its place. That's all fine, but when this process first errors out you now have an empty _tmp table, at that point running the update a will fail when it tries to create the _tmp table again.

So, if your update failed you'll need to remove the _tmp table, add those two groupBy clauses to the .install, and rerun the update. After that life is good. This module is absolutely critical to my website so I don't have time for a fix to be issued and had to get a work-around.

Best of luck!

I changed the title of this issue in case any other PostgreSQL users are working through this problem, FYI.

I also am seeing the same thing on PostgreSQL. I have one question: if I make the changes suggested in #6, it will run. but will the net result of the new code be to 'remove duplicate rows' in the table (which is the intention of the upgrade code), or will you end up with a table identical to the original? If so, does this code need to take a different tack to accomplishing the removal of duplicate rows?

I confirmed the problem. The PDOException seems to occur since the following query is not valid.

SELECT tx.* FROM taxonomy_index tx GROUP BY tx.nid, tx.tid;

I believe we need to adjust the select query in entityreference_update_7100() to one like:

  SELECT tx.nid AS nid, tx.tid AS tid, MAX(sticky) AS sticky, MAX(created) AS created
  FROM taxonomy_index tx
  GROUP BY tx.nid, tx.tid;

I made a first trial patch.

entityreference_update_7100() was added in #1924444: Duplicate entries with taxonomy_index index..

I don't have a Postgres or mssql environment to test this patch with. Can some else try it and report back?

I tested patch #10 and worked fine.

However, I think this piece of code should be added before 'db_create_table('taxonomy_index_tmp', $tx_schema);':

if (db_table_exists('taxonomy_index_tmp')) {
  db_drop_table('taxonomy_index_tmp');
}

It's in case someone tried the previous (unpatched) update without success as the temporary table remains (at least it did on my instance) and prevents the patched version to work properly. But it's mitigated since if an error occurs during the update process, you're supposed to reload a dump of your database and then, the table should not exists there.

I tested with both a db with a failed update and "fresh non-updated" db. With the "failed update" db, I had to manually drop the table (DROP TABLE taxonomy_index_tmp;) and the patched update worked. With the other, the patched update worked straight.

So it's up to you to consider or not that the table 'taxonomy_index_tmp' may already exist and should be taken into account... current patch works for people who did not try the original update version. :)

UPDATE: 'forgot to mention I'm using PostgreSQL 9.4.

@guignonv, I like your idea. I revised the patch.

(BTW, I think this problem occurs not only with MSSQL and PostgreSQL as I experienced this with MySQL.)

Good catch with the update hook and the tmp table.

That's one for Postgres. Now just need someone from the MSSQL side of things to try it out. I'll commit the patch to a branch to make it easier to test the update. Once I hear back, I'll merge into dev.

Here's the link to the branch: https://www.drupal.org/node/1246440/git-instructions/2877592-update-7100...

The patch appears to have worked for me. Drupal 7.54 on Windows, IIS 7.5, PHP 5.6, and MSSQL Server.

Thanks everyone! I'll release 1.4 soon to fix this specifically.

I tested patch #14 and worked fine.
PosgreSQL 9.1.3, Drupal 7.54

Same here. Patch #14 worked fine for me with Php 7, PosgreSQL 9.6, Drupal 7.54.
Please can we release a new version of entity reference with this patch?

1.4 will be out in a few minutes.

@minorOffense thank you!

Upgrade from patched 1.3 to 1.4 worked fine. However, when upgrading another site from 1.2 to 1.4, I encountered another database error:

Update #7100
Failed: PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Error: The new name 'taxonomy_index_nid_df' is already in use as a OBJECT name and would cause a duplicate that is not permitted.: EXEC sp_rename :old, :new, :type; Array ( [:old] => dbo.taxonomy_index_tmp_nid_df [:new] => taxonomy_index_nid_df [:type] => OBJECT ) in db_rename_table() (line 2865 of E:\inetpubtest\wwwroot\jurorportal\includes\database\database.inc).

Drupal 7.54 on Windows, IIS 7.5, PHP 5.6, and MSSQL Server.

I created a new issue: https://www.drupal.org/node/2881201