I've got the following errors when upgrading from 1.x-alpha5 to 2.0-beta1.

Update #6013

    * Failed: ALTER TABLE {l10n_community_translation} DROP INDEX is_active
    * Failed: ALTER TABLE {l10n_community_translation} DROP INDEX is_suggestion
    * Failed: ALTER TABLE {l10n_community_translation} DROP INDEX language
    * ALTER TABLE {l10n_community_translation} DROP INDEX suggestion_active
    * ALTER TABLE {l10n_community_translation} DROP INDEX sid_language_suggestion
    * ALTER TABLE {l10n_community_translation} DROP INDEX sid
    * ALTER TABLE {l10n_community_translation} ADD INDEX is_suggestion_is_active_language (is_suggestion, is_active, language)
    * ALTER TABLE {l10n_community_translation} ADD INDEX sid_language_is_suggestion_is_active (sid, language, is_suggestion, is_active)

Checked the table and there is no index with this name... now checked hook_schema and found inconsistencies to my tables.

    'indexes' => array(
      'uid_entered' => array('uid_entered'),
      'is_suggestion_is_active_language' => array('is_suggestion', 'is_active', 'language'),
      'sid_language_is_suggestion_is_active' => array('sid', 'language', 'is_suggestion', 'is_active')
    ),

but my table looks like this:

CREATE TABLE  `drupal6`.`l10n_community_translation` (
  `tid` int(11) NOT NULL auto_increment,
  `sid` int(11) NOT NULL default '0',
  `language` varchar(12) NOT NULL default '',
  `translation` text NOT NULL,
  `uid_entered` int(11) NOT NULL default '0',
  `uid_approved` int(11) NOT NULL default '0',
  `time_entered` int(11) NOT NULL default '0',
  `time_approved` int(11) NOT NULL default '0',
  `is_suggestion` int(11) NOT NULL default '0',
  `is_active` int(11) NOT NULL default '0',
  PRIMARY KEY  (`tid`),
  KEY `l10n_community_translation_language` (`language`),
  KEY `l10n_community_translation_is_suggestion` (`is_suggestion`),
  KEY `l10n_community_translation_is_active` (`is_active`),
  KEY `uid_entered` (`uid_entered`),
  KEY `is_suggestion_is_active_language` (`is_suggestion`,`is_active`,`language`),
  KEY `sid_language_is_suggestion_is_active` (`sid`,`language`,`is_suggestion`,`is_active`)
) ENGINE=MyISAM AUTO_INCREMENT=593 DEFAULT CHARSET=utf8;

To be clear there are 3 keys that I do not see in the schema. What is the correct schema???

  KEY `l10n_community_translation_language` (`language`),
  KEY `l10n_community_translation_is_suggestion` (`is_suggestion`),
  KEY `l10n_community_translation_is_active` (`is_active`),

Should I run the below code by hand - to fix the inconsistency or does the module do this in beta2?

ALTER TABLE {l10n_community_translation} DROP INDEX l10n_community_translation_language
ALTER TABLE {l10n_community_translation} DROP INDEX l10n_community_translation_is_suggestion
ALTER TABLE {l10n_community_translation} DROP INDEX l10n_community_translation_is_active
CommentFileSizeAuthor
#17 hashkey-index_1.patch997 bytesgábor hojtsy
#14 hashkey-index.patch1.18 KBgábor hojtsy
#12 hashkey-index.patch1.19 KBgábor hojtsy

Comments

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Category: bug » support
Status: Active » Fixed

Looks like your installation was not using the keys as present in alpha5 either. Here is the alpha5 code:

http://drupalcode.org/viewvc/drupal/contributions/modules/l10n_server/l1...

328    'primary key' => array('tid'),
329	    'indexes' => array(
330	      'is_active' => array('is_active'),
331	      'is_suggestion' => array('is_suggestion'),
332	      'language' => array('language'),
333	      'suggestion_active' => array('is_suggestion', 'is_active'),
334	      'uid_entered' => array('uid_entered'),
335	      'sid_language_suggestion' => array('sid', 'language', 'is_suggestion'),
336	      'sid' => array('sid')
337	    ),

Not surprising that beta1 tries to drop keys with the names used earlier in the schema. Feel free to drop the keys which were improperly named.

hass’s picture

hass commented
Category: support » bug
Status: Fixed » Active

Sorry, but I'm reopen this now. I have always upgraded the available versions and if you break the upgrade path, yes something is broken. The module should fix the bugs. Also done a schema comparison now with schema module and found the following:

    *
      l10n_groups_group
          o column nid - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'int', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'int', 'not null' => TRUE, 'default' => 0)
          o column language - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '12', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '12', 'not null' => TRUE, 'default' => '')

    *
      l10n_community_project
          o column uri - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '50', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '50', 'not null' => TRUE, 'default' => '')
          o column title - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '128', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '128', 'not null' => TRUE, 'default' => '')
          o column connector_module - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '50', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '50', 'not null' => TRUE, 'default' => '')
          o column status - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'int', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'int', 'not null' => TRUE, 'default' => 0)
          o unique keys uri_connector_module: missing in database
          o indexes uri: missing in database
          o unique keys l10n_community_project_uri_connector_module: unexpected (not an error)
          o indexes l10n_community_project_uri: unexpected (not an error)
    *
      l10n_community_release
          o column title - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '128', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '128', 'not null' => TRUE, 'default' => '')
    *
      l10n_community_file
          o column location - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '255', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '255', 'not null' => TRUE, 'default' => '')
          o column revision - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '255', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '255', 'not null' => TRUE, 'default' => '')
    *
      l10n_community_line
          o column type - differences on: not null, default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'int', 'not null' => TRUE, 'default' => 0)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'int', 'not null' => FALSE)
    *
      l10n_community_string
          o column hashkey - difference on: not null
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'char', 'length' => 32, 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'char', 'length' => '32', 'not null' => FALSE)
    *
      l10n_community_translation
          o column sid - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'int', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'int', 'not null' => TRUE, 'default' => 0)
          o column language - difference on: default
            declared: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '12', 'not null' => TRUE)
            actual: array('description' => t('TODO: please describe this field!'), 'type' => 'varchar', 'length' => '12', 'not null' => TRUE, 'default' => '')
          o indexes l10n_community_translation_is_active: unexpected (not an error)
          o indexes l10n_community_translation_is_suggestion: unexpected (not an error)
          o indexes l10n_community_translation_language: unexpected (not an error)

I always ran update.php and never added the indexes myself that are now in the DB. Could you fix this with an update hook, please?

hass’s picture

hass commented

1. Shouldn't be line 540 in l10n_community_update_6000(): db_add_unique_key($ret, 'l10n_community_project', 'l10n_community_project_uri_connector_module', array('uri', 'connector_module')); not db_add_unique_key($ret, 'l10n_community_project', 'uri_connector_module', array('uri', 'connector_module'));

2. unique keys uri_connector_module: missing in database is also correct as the name is wrong.

3. indexes uri: missing in database is definitely missing in schema update hooks if this wasn't inside from the first day.

4. indexes l10n_community_project_uri is possible another type of this copy and past bugs like #1

5. l10n_community_string = FALSE is set in l10n_community_update_6010, but schema DEF says TRUE... looks like I ran this update path. Now - what's correct? FALSE or TRUE? Same for l10n_community_update_6007() - l10n_community_line.type.

6. No idea how this indexes came in... they are wrong?
# indexes l10n_community_translation_is_active: unexpected (not an error)
# indexes l10n_community_translation_is_suggestion: unexpected (not an error)
# indexes l10n_community_translation_language: unexpected (not an error)

hass’s picture

hass commented
Title: Inconsistent schema in l10n_community_translation table » Inconsistent schema in database tables

Changing title as there are more tables and fields inconsistent.

It's very unclear to me if the update hook definitions or the hook_schema definitions are correct. Therefore completely impossible for me to share a patch.

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented

For all 6 cases, consider the schema definition as the base. For 6, we already agreed they are wrong, were named differently by the schema before and were dropped in a recent update function.

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented

Ok, looked at localize.drupal.org's database, running the schema module against the DB and code running there (added the schema module temporarilty to that site). The only issue it found was this mismatch (no missing or extra stuff found under l10n_server):

l10n_community_string

column hashkey - difference on: not null
declared: array('description' => t('TODO: please describe this field!'), 'type' => 'char', 'length' => 32, 'not null' => TRUE)
actual: array('description' => t('TODO: please describe this field!'), 'type' => 'char', 'length' => '32', 'not null' => FALSE)

The server on localize.drupal.org was continually updated since 6.x-1.0-alpha4 (2009 July 29th) and is now on 6.x-2.0-beta1 (2010 March 22nd). Looks like you've had other issues which localize.drupal.org did not have through all the updates, so I'm reliant on you for reproducing any issues other then this one difference in defaults I could reproduce. Including a fix for this default difference would be straightforward in our code, but beyond that I'm not sure your problems are generally applicable unless reproduced elsewhere.

hass’s picture

hass commented

The absolute basic was 5.x-1.x-alpha1 or so... the site exists for a very long time and was updated from time to time with the official releases. If I'm able to find some spare time I may be able to dig deeper, but I do not like to find all the bugs and their sources. Following many years is out of scope for me. I can only assure you I have never changed the DB myself and I have for sure executed update.php to get all updates and if something have failed you have seen it in the issue queue.

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented

@hass: right, since these issues might stem from years ago. Last June this module had about 20 users while currently it hovers around 100. Consider how many users we've had in 5.x-1.x-alpha1 and you'll see that it is probably worthwhile to tell those couple users to fix up their databases by hand instead of putting in conditional DB changes which could cause whack on existing sites if gone wrong. Unless this is reproduced by others, I'm not seeing how actual code changes make more good then the potential bad it can cause.

hass’s picture

hass commented

Do you fix #6 in beta3?

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented

I plan to fix the one I could reproduce with the hashkey column default (that was #5 on your list). As said, the extra indexes from #6 you can just drop.

hass’s picture

hass commented

Maybe someone else also need this... This is what I've executed now to fix all the inconsistencies on MySQL, but this does not yet fix #6.

ALTER TABLE l10n_server_translation DROP INDEX l10n_community_translation_language;
ALTER TABLE l10n_server_translation DROP INDEX l10n_community_translation_is_suggestion;
ALTER TABLE l10n_server_translation DROP INDEX l10n_community_translation_is_active;
ALTER TABLE `l10n_server_project` DROP INDEX `l10n_community_project_uri_connector_module`, ADD UNIQUE INDEX `uri_connector_module` USING BTREE(`uri`, `connector_module`);
ALTER TABLE `l10n_server_project` DROP INDEX `l10n_community_project_uri`, ADD INDEX `uri` USING BTREE(`uri`);
ALTER TABLE l10n_groups_group ALTER COLUMN `nid` DROP DEFAULT;
ALTER TABLE l10n_groups_group ALTER COLUMN `language` DROP DEFAULT;
ALTER TABLE l10n_server_project ALTER COLUMN `uri` DROP DEFAULT;
ALTER TABLE l10n_server_project ALTER COLUMN `title` DROP DEFAULT;
ALTER TABLE l10n_server_project ALTER COLUMN `connector_module` DROP DEFAULT;
ALTER TABLE l10n_server_project ALTER COLUMN `status` DROP DEFAULT;
ALTER TABLE l10n_server_release ALTER COLUMN `title` DROP DEFAULT;
ALTER TABLE l10n_server_file ALTER COLUMN `location` DROP DEFAULT;
ALTER TABLE l10n_server_file ALTER COLUMN `revision` DROP DEFAULT;
ALTER TABLE l10n_server_line MODIFY COLUMN `type` INT(11) NOT NULL DEFAULT 0;
ALTER TABLE l10n_server_translation ALTER COLUMN `sid` DROP DEFAULT;
ALTER TABLE l10n_server_translation ALTER COLUMN `language` DROP DEFAULT;
gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Status: Active » Needs review
StatusFileSize
new hashkey-index.patch1.19 KB

Ok, let's try this one to fix the hashkey column. We need to drop the unique key registered to it before and add it after as per the docs for database compatibility.

Status: Needs review » Needs work

The last submitted patch, hashkey-index.patch, failed testing.

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Version: 6.x-2.0-beta1 » 6.x-2.x-dev
Status: Needs work » Needs review
StatusFileSize
new hashkey-index.patch1.18 KB

Well, the patch contained bad table names anyway. Let's see whether it can be applied against 2.x.

ClearXS’s picture

ClearXS commented

Can someone tell me how to repair database errors reported by some schemes, caused by this module or by other modules, without reinstalling every time again until the next new module or upgrade makes a mess of the database? Is there a course somewhere on the internet I can follow, before one should ever start working with additional modules in drupal?

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented

Any feedback on this one?

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Status: Needs review » Fixed
StatusFileSize
new hashkey-index_1.patch997 bytes

Worked for me, so committed this (updated to latest 2.x-dev).

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.