error in Box and Access MySQL Schema

Bizarre edge cases and 'critical' are two parts of the Venn diagram which don't intersect.

Resetting to normal

127 user-defined blocks doesn't seem that far-fetched, particularly for a large site and/or an older one.

This is only a problem with the MySQL schema, in any case. PostgreSQL's serials are integers, so they have a much higher limit.

I don't have MySQL available, so I'd feel very uncomfortable submitting a patch for this (moreso for the update than the schema), but looking through the schema, the boxes bid has the smallest auto_increment limit of all the tables, as a tinyint. The vast majority of the tables use int(10) as their auto_incrementing primary keys, except for one (filter_formats) that has int(4), and another (watchdog) that has int(5).

So, I'd suggest changing it to one of those three. I imagine any of them would be large enough for even the largest sites, though the int(10) would be safest and most consistent with the rest of the schema.

Probably all tinyint should be changed. Or check all auto_increment fields to be changed to "int" or "smallint".

This seems silly- saving a few bytes of disk space by using tinyint is not going to be of benefit. Also, what if I want an autogenerated, custom block per page? (not a good idea, I'm sure, for performance- but feasible). So, it seems to me like bid should be the same as nid.

problem exists is 5.x too- should be fixed in HEAD first. Patch to follow (requires system update...).

What about the "custom" field in the blocks table?

Ok, something funny is going on, since there is already a system update that would change `bid` to an INT for any update from 4.7:

function system_update_1004() {
  // Increase the size of bid in boxes and aid in access
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
    $ret[] = update_sql("ALTER TABLE {access} CHANGE `aid` `aid` INT( 10 ) NOT NULL AUTO_INCREMENT ");
    $ret[] = update_sql("ALTER TABLE {boxes} CHANGE `bid` `bid` INT( 10 ) NOT NULL AUTO_INCREMENT ");
      break;
    case 'pgsql':
      // No database update required for PostgreSQL because it already uses big SERIAL numbers.
      break;
  }
  return $ret;
}

So, at the moment the updated schema won't match the schema of a fresh install.

Proposed solution: the numbers in parens should be removed (per updated code style) and the 5.x schema fixed so that `bid` and `aid` are INT for any fresh 5.x install under mysql (patch attached).

int is used in other mysql column definitions.
we haven't released 5 so no worries updating the update_N function.

fixes an obvious error.

Committed to CVS HEAD. We might want to backport this to Drupal 4.7. I leave that up to Gerhard to decide.

What we could do is to only put it into database.*.mysql files. I don't think having a db update is neccessary.

I've reconsidered: I'd like a patch for 4.7. How will this affect the 4->5 upgrade path, though?

Will mysql/postgresql throw an error if the ALTER TABLE does not change anything?

Seems fine for mysql (4.1.18) to run it multiple times:

mysql> ALTER TABLE boxes CHANGE `bid` `bid` INT NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE boxes CHANGE `bid` `bid` INT NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

yeah, I guess the upgrade path won't be affected

Just for the record. So this patch is already comitted to 5.x but is now in the process of being comitted to the 4.7 branch also?

needs a 4.7 patch

No backticks in your queries please.

-K

backticks removed, should be good.

the mysdql 4.0 part was missing. I added that and committed.