Update from 1.3 to 2.x does not preserve auto_increment on FID column

Quick update: I just tried to add a new flag on another site with flag 6.x-2.0-beta6. That results in a green message, saying: "Flag testflag was saved" (localized interface; original message text is probably: Flag @name has been saved), but "testflag" is neither listed at ./admin/build/flags, nor saved to the database. So the issue already starts with a vanilla flag 6.x-2.0-beta6.

Upgraded to 6.x-2.x-dev on the affected site. The (sub-) issue "Can not create new flags" exists here as well. When trying to import an an exported flag from 6.x-2.0-beta6, I'm not getting a WSOD with this version; the behaviour is identical to the manual creation: Positive confirmation message, but no flag is saved to the database. If the export is somehow modified, I'm not getting a refusal message (like in Views) but the WSOD.

Also verified the sub-issue on several other sites that were upgraded from 6.x-1.3 or earlier to 6.x-2.0-beta6. All those sites are affected and do not allow to configure new flags; behaviour as described in #1.

The "Can not create new flags" issue does not appear on sites where the 'flag' module has not been upgraded or where still flag-6.x-1.3 is used. Very nasty in combination with #1302480: Unflag does not work, though :-(

Thanks for the reply.

Indeed... On the affected site:

mysql> describe flag_types;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| fid   | smallint(5) unsigned | NO   | MUL | 0       |       |
| type  | varchar(32)          | NO   |     |         |       |
+-------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe flags;
+--------------+----------------------+------+-----+---------+-------+
| Field        | Type                 | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| fid          | smallint(5) unsigned | NO   | PRI | 0       |       |
| content_type | varchar(32)          | YES  |     |         |       |
| name         | varchar(32)          | YES  | UNI |         |       |
| title        | varchar(255)         | YES  |     |         |       |
| global       | tinyint(4)           | YES  |     | 0       |       |
| options      | text                 | YES  |     | NULL    |       |
+--------------+----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

On a unaffected site:

mysql> describe flag_types;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| fid   | smallint(5) unsigned | NO   | MUL | 0       |       |
| type  | varchar(32)          | NO   |     |         |       |
+-------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe flags;
+--------------+----------------------+------+-----+---------+----------------+
| Field        | Type                 | Null | Key | Default | Extra          |
+--------------+----------------------+------+-----+---------+----------------+
| fid          | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| content_type | varchar(32)          | NO   |     |         |                |
| name         | varchar(32)          | YES  | UNI |         |                |
| title        | varchar(255)         | YES  |     |         |                |
| roles        | varchar(255)         | YES  |     |         |                |
| global       | tinyint(4)           | YES  |     | 0       |                |
| options      | text                 | YES  |     | NULL    |                |
+--------------+----------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

So what's the suggested action - uninstall 'flag' and related modules, drop the 'flag'-related tables (flag_content, flag_counts, flag_types, flags), and start from scratch on all sites with broken "auto_increment" ?

Or maybe something like alter table flags add fid Int NOT NULL AUTO_INCREMENT key; or ALTER TABLE flags AUTO_INCREMENT = 1 might work (just suggestions from Googling around the MySQL manual)?

Rerunning flag_update_6002() (referenced in #420250: Flag D5->D6 Update Doesn't Add Auto-increment to flags.fid Column) gives me on the affected site:

flag module
Update #6002
Failed: ALTER TABLE {flag_content} DROP PRIMARY KEY
Failed: ALTER TABLE {flag_content} ADD `fcid` INT unsigned auto_increment DEFAULT NULL, ADD PRIMARY KEY (fcid)
ALTER TABLE {flag_content} CHANGE `fcid` `fcid` INT unsigned NOT NULL auto_increment
ALTER TABLE {flag_content} ADD UNIQUE KEY fid_content_type_content_id_uid (fid, content_type, content_id, uid)
Update #6003
No queries
Update #6004
No queries
Update #6200
No queries
Update #6201
ALTER TABLE {flag_content} DROP KEY fid_content_type_content_id_uid
ALTER TABLE {flag_content} ADD UNIQUE KEY fid_content_id_uid (fid, content_id, uid)
ALTER TABLE {flag_counts} ADD INDEX count (count)
Update #6202
ALTER TABLE {flag_content} DROP KEY fid_content_id_uid
Failed: ALTER TABLE {flag_content} DROP INDEX content_type_uid
Failed: ALTER TABLE {flag_content} ADD `sid` INT unsigned NOT NULL DEFAULT 0
Failed: ALTER TABLE {flag_content} ADD UNIQUE KEY fid_content_id_uid_sid (fid, content_id, uid, sid)
Failed: ALTER TABLE {flag_content} ADD INDEX content_type_uid_sid (content_type, uid, sid)
Update #6203
DELETE FROM {flag_counts} WHERE count = 0
Update #6204
ALTER TABLE {flag_counts} DROP PRIMARY KEY
ALTER TABLE {flag_counts} ADD PRIMARY KEY (fid, content_id)
Update #6205
No queries
Update #6206
ALTER TABLE {flag_content} ADD INDEX content_type_content_id_uid_sid (content_type, content_id, uid, sid)
Update #6207
Failed: ALTER TABLE {flag_counts} ADD `last_updated` INT unsigned NOT NULL DEFAULT 0, ADD INDEX last_updated (last_updated)
Update #6208
ALTER TABLE {flag_counts} DROP INDEX count
Failed: ALTER TABLE {flag_counts} DROP INDEX last_updated
Failed: ALTER TABLE {flag_counts} ADD INDEX fid_count (fid, count)
Failed: ALTER TABLE {flag_counts} ADD INDEX fid_last_updated (fid, last_updated)

Whoops?

I don't know enough about the database structures of the 'flag' module to offer real help with this, but at least on almost all my sites where I have updated to 6.x-2.0-beta6 from flag 6.x-1.3 or earlier the error is consistent. I compared the 'flag' table on a rough dozend of sites, and the structure almost always looks like this:

mysql> describe flags;
+--------------+----------------------+------+-----+---------+-------+
| Field        | Type                 | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| fid          | smallint(5) unsigned | NO   | PRI | 0       |       |
| content_type | varchar(32)          | YES  |     |         |       |
| name         | varchar(32)          | YES  | UNI |         |       |
| title        | varchar(255)         | YES  |     |         |       |
| roles        | varchar(255)         | YES  |     |         |       |
| global       | tinyint(4)           | YES  |     | 0       |       |
| options      | text                 | YES  |     | NULL    |       |
+--------------+----------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

On these sites I'm not using anything but a vanilla 'flag' module (no flag-related modules), so at least this sub-issue definitely can not be caused by an interference between flag "core" and flag "contrib".

Regarding the MySQL version: I'm running Ver 14.14 Distrib 5.1.49 from Debian GNU/Linux 6.0 "Squeeze", so that's not much out of the ordinary, also.

Since 'flag' does not depend on other modules (like 'ctools'), what could interfere with database updates except Drupal core?

Regarding rerunning flag_update_6002(), I understand that running those updates a second time might not [can not|will not] work; however that's a bit surprising since that's the way major CCK updates (e.g. from D5 to D6) were accomplished: Keep rerunning update.php, until all errors are gone. Considering that and regarding the update errors quoted in #4, I don't see an attempt to alter the field "fid" in the "flag" table (if I'm even looking at the right database table, and if I tried to rerun the right database update).

but it's still possible to fix it (the database) manually be going through and manually making all the indexes/keys/columns.

Well, I'm afraid this is clearly out of my league, but at least I can offer a pretty consistent testbed, in case you try to add the auto-increment column a third time, or something like this ;)

Regarding the one site with 6.x-2.0-beta6 that actually has this auto_increment property, the only major configuration difference compared to my other sites is that this site hadn't the 'boost' module enabled. But is it even theoretically possible that some module tampers with the ability of Drupal core to update the database schema??

Sorry for burdening you with more work :-(

Again, I'm willing to provide any help I can give, including access to database dumps in various stages, if that helps.

OK, before dropping 'Flag' completely, I tried this:

mysql> ALTER TABLE flags CHANGE COLUMN fid fid int(10) unsigned NOT NULL auto_increment;
Query OK, 4 rows affected (0.18 sec)
Records: 4  Duplicates: 0  Warnings: 0

That gives me this schema:

mysql> describe flags;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| fid          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| content_type | varchar(32)      | YES  |     |         |                |
| name         | varchar(32)      | YES  | UNI |         |                |
| title        | varchar(255)     | YES  |     |         |                |
| global       | tinyint(4)       | YES  |     | 0       |                |
| options      | text             | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

However, I'm still at a loss what "manually making all the indexes/keys/columns" actually would require. It would really help to get some instructions which tables and/or fields need to be modified as well.

Please note that the field "content_type" has Null=YES, by my unaffected site has Null=No. I don't know what the correct definition would be.

However, the SQL one-liner gives me the following changes:

• I can create new flags again;
• I can import the backed-up flag that got "lost" (however, it's just listed but seems not to work)
• I can edit existing flags and save changes

Is it really that easy?

PS: Since the issue queue has numerous issues related to schema changes, maybe it would help to verify the current schema at some point. This could notify the user about problems, for example, at the status report page (./admin/reports/status).

Also, for the sake of the experiment I set up an older version of the affected site and directly updated from 6.x-1.3 to 6.x-2.x-dev. But I executed the SQL statement from above (ALTER TABLE flags CHANGE COLUMN fid fid int(10) unsigned NOT NULL auto_increment;) before running update.php/drush updatedb (!) When running drush updatedb, this gave me:

The following updates are pending:

 flag module
 6200 - Convert role access to have separate "flag" and "unflag" permissions.
 6201 - Refine the indexes.   The content type inclusion actually slowed down on unique key. And a count  index would be helpful for sorting by counts.
 6202 - Add the sid column and unique index on the flag_content table.
 6203 - Remove count = 0 rows from the count tables.
 6204 - Remove "content type" from the flag_counts primary key.
 6205 - Provide a better index on the flag_content table to include 'uid' and 'sid'.
 6206 - Correction to flag_update_6205(). Convert unique key to an index.
 6207 - Adds column last_updated to flag_counts table.
 6208 - Convert flag_count indexes to include FID for more efficient indexing.

 flag_actions module
 6200 - Add a "repeat_threshold" value to all existing Flag actions.

Do you wish to run all pending updates? (y/n): y
Executing flag_update_6200
ALTER TABLE {flags} DROP roles
Executing flag_update_6201
ALTER TABLE {flag_content} DROP KEY fid_content_type_content_id_uid
ALTER TABLE {flag_content} ADD UNIQUE KEY fid_content_id_uid (fid, content_id, uid)
ALTER TABLE {flag_counts} ADD INDEX count (count)
Executing flag_update_6202
ALTER TABLE {flag_content} DROP KEY fid_content_id_uid
ALTER TABLE {flag_content} DROP INDEX content_type_uid
ALTER TABLE {flag_content} ADD `sid` INT unsigned NOT NULL DEFAULT 0
ALTER TABLE {flag_content} ADD UNIQUE KEY fid_content_id_uid_sid (fid, content_id, uid, sid)
ALTER TABLE {flag_content} ADD INDEX content_type_uid_sid (content_type, uid, sid)
Executing flag_update_6203
DELETE FROM {flag_counts} WHERE count = 0
Executing flag_update_6204
ALTER TABLE {flag_counts} DROP PRIMARY KEY
ALTER TABLE {flag_counts} ADD PRIMARY KEY (fid, content_id)
Executing flag_update_6205
Executing flag_update_6206
ALTER TABLE {flag_content} ADD INDEX content_type_content_id_uid_sid (content_type, content_id, uid, sid)
Executing flag_update_6207
ALTER TABLE {flag_counts} ADD `last_updated` INT unsigned NOT NULL DEFAULT 0, ADD INDEX last_updated (last_updated)
Executing flag_update_6208
ALTER TABLE {flag_counts} DROP INDEX count
ALTER TABLE {flag_counts} DROP INDEX last_updated
ALTER TABLE {flag_counts} ADD INDEX fid_count (fid, count)
ALTER TABLE {flag_counts} ADD INDEX fid_last_updated (fid, last_updated)
Executing flag_actions_update_6200
ALTER TABLE {flag_actions} ADD `repeat_threshold` SMALLINT NOT NULL DEFAULT 0
ALTER TABLE {flag_actions} CHANGE `threshold` `threshold` SMALLINT NOT NULL DEFAULT 0

'all' cache was cleared
Finished performing updates.

All updates were run successfully, and no errors were indicated. Final table schema:

mysql> describe flags;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| fid          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| content_type | varchar(32)      | YES  |     |         |                |
| name         | varchar(32)      | YES  | UNI |         |                |
| title        | varchar(255)     | YES  |     |         |                |
| global       | tinyint(4)       | YES  |     | 0       |                |
| options      | text             | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Maybe that little line of SQL is the manual fix?