How should I fix an incorrect query in hook_update_N()? I made a new release of a module, and soon afterwards an issue was filed that the hook_update_N() used SQL that didn't work on PostgreSQL. In this situation I can see two solutions:

(Let's say the release with the problem is 1.1 and that N=6100.)


Modify update_6100 to have a fixed query and release as 1.2. If a user with Postgres updated to 1.1, got the error, and then updated to 1.2 they still wouldn't have run the query as it wasn't put in a new hook_update_N(). However they should've seen the error when doing the update, and the issue's in the issue queue, and can include instructions on how to fix the problem. It feels ugly to modify an existing (released) update function though.
Add update_6101 that has the fixed query. In the case of this particular query however, it would be destructive to run it after the user's started using some of the new functionality. So if a user with MySQL updated to 1.1, used the module a lot, and then updated to 1.2 there would be a good chance they'd lose data. I could check what DB's being used in update_6101 so that it would only be run for users with Postgres, but that feels ugly too. Also if someone with Postgres ran update_6100, saw the errors and then fixed and ran the query manually, they also could lose data when updating to 1.2.


Note that there's no way to detect if the query has run successfully, or has never been run.

I'd like to know what people think is the best way to deal with this situation?
Something similar happened with Panels, update 7301, a new field was added, and while the column creation happened, Drupal wouldn't accept the update as valid.

If you check the code in panels.install, you will see that the action taken by merlinofchaos, was to modify the 7301 to first check "Does this column already exists? Then smile and pretend we just created it, otherwise create it now."

If merlin does it that way, I would do it that way. Relevant issue here, maybe that will help further.

Since each of your scenarios above have potential for data loss, which is serious, and people dont actually take backups, and in your case, there is no, way to tell, then perhaps you will need to force the user to tell you. Refuse the update unless the variable module_name_has_1_1_content is set to either TRUE or FALSE. Then either you can add a form in the admin UI with an explanation and yes/no button that just sets the variable. Or just one "I have read the issue and understand the consequences, let me update" button.

Perhaps you can provide instructions for "How to export and import all content". That way the user can export, update, truncate the table, and import.