[kronolith] DB conversion woes on creating 'rampage_users' (webmail 1.2.8 to HordeGW 5.0.2)
Jan Schneider
jan at horde.org
Thu Jan 24 10:30:48 UTC 2013
Zitat von Herbert Pophal <pophal at tubit.tu-berlin.de>:
> Dear Horde folks,
>
> Probably this is not just a kronolith related issue, but I wasn't quite
> sure which mailing list to choose instead. Please let me know.
>
> I'm trying to upgrade our one year old webmail edition (1.2.8, i.e. H3)
> to Horde Groupware 5. I experience problems in converting the database,
> more specifically, creating the rampage_users table derived from
> kronolith_events.
>
>
> Environment: RHEL 6.3 (last year Solaris 10)
> PHP 5.3.3 (RedHat Packages) driven by mod_fcgid
> Separate PEAR Installation of Horde Groupware 5.0.2
>
> webmail-install failed with the error:
>
> $ PHP_PEAR_SYSCONF_DIR=$HORDE5 php -d include_path=$HORDE5/pear/php
> $HORDE5/pear/webmail-install
> ....
> ====================
>
> Fatal Error:
> QUERY FAILED: Duplicate entry 'j.doe' for key 'rampage_users_user_name'
>
> INSERT INTO `rampage_users` (user_name) VALUES ('j.doe')
>
> [Name changed]
>
>
> In order to trace what's going on I inserted some print()s in the code
> (see below), guided by the traceback (as well, below). The result is:
>
> up J.doe [20081023221501.383724nxck41bakg at webmail.tu-berlin.de, 2] 0
> ensureUsers:: INSERT INTO `rampage_users` (user_name) VALUES ('J.doe')
> ensureUsers: ---
> up SHARE: j.doe [20081023221501.383724nxck41bakg at webmail.tu-berlin.de, 2] 0
> ensureUsers:: INSERT INTO `rampage_users` (user_name) VALUES ('j.doe')
> [other updates]
> up J.doe [20081023222256.26315wukf75ifu8s at webmail.tu-berlin.de, 2] 0
> ensureUsers: ---
> up SHARE: j.doe [20081023222256.26315wukf75ifu8s at webmail.tu-berlin.de, 2] 0
> ensureUsers:: INSERT INTO `rampage_users` (user_name) VALUES ('j.doe')
>
> ...
>
> up jane.tarzan [20081024122118.19381e67ceecfm4k at webmail.tu-berlin.de, 2] 0
> ensureUsers:: INSERT INTO `rampage_users` (user_name) VALUES ('jane.tarzan')
> ensureUsers: ---
> up SHARE: Jane.Tarzan
> [20081024122118.19381e67ceecfm4k at webmail.tu-berlin.de, 2] 0
> ensureUsers:: INSERT INTO `rampage_users` (user_name) VALUES ('Jane.Tarzan')
> up jane.tarzan [20081024122359.10563syxgtkzpd8g at webmail.tu-berlin.de, 2] 0
> ensureUsers: ---
> up SHARE: Jane.Tarzan
> [20081024122359.10563syxgtkzpd8g at webmail.tu-berlin.de, 2] 0
> ensureUsers:: INSERT INTO `rampage_users` (user_name) VALUES ('Jane.Tarzan')
> up jane.tarzan [20081024122535.70608bjh8fcst09s at webmail.tu-berlin.de, 2] 0
> ensureUsers: ---
> [ a dozen times or so ]
> ...
>
> up Ascii-Unicode [20081026151751.17426vo1khzwivk8 at webmail.tu-berlin.de,
> 2] Uni-Veranstaltung
> ensureUsers:: INSERT INTO `rampage_users` (user_name) VALUES
> ('Ascii-Unicode')
> ensureUsers: ---
> up SHARE: ascii-unicode
> [20081026151751.17426vo1khzwivk8 at webmail.tu-berlin.de, 2] Uni-Veranstaltung
> ensureUsers:: INSERT INTO `rampage_users` (user_name) VALUES
> ('ascii-unicode')
>
> [Again, names are changed, but with observation of character case]
>
> up j.doe [20081028165942.59321yryb5fy1szo at webmail.tu-berlin.de, 2] 0
> ensureUsers:: INSERT INTO `rampage_users` (user_name) VALUES ('j.doe')
>
> Fatal Error:
> QUERY FAILED: Duplicate entry 'j.doe' for key 'rampage_users_user_name'
>
> INSERT INTO `rampage_users` (user_name) VALUES ('j.doe')
>
>
> HINT: When inspecting e.g. kronolith_events, there are records belonging
> to j.doe and J.doe, resp., though this is the same person. When logging
> in into the current (H3) installation as SomeBody, the portal calls me
> somebody, but a newly created calendar is owned by SomeBody.
> The 'other case' entries in rampage_users marked 'SHARE' above do not
> show up in the resulting table.
>
>
> Traceback:
> In
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/php/Horde/Db/Adapter/Mysql.php
> on line 258
>
> 1. Horde_Core_Bundle->migrateDb()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/webmail-install:32
> 2. Horde_Db_Migration_Migrator->up()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/php/Horde/Core/Bundle.php:107
> 3. Horde_Db_Migration_Migrator->_doMigrate()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/php/Horde/Db/Migration/Migrator.php:102
> 4. Horde_Db_Migration_Base->migrate()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/php/Horde/Db/Migration/Migrator.php:182
> 5. KronolithUpgradeCategoriesToTags->up()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/php/Horde/Db/Migration/Base.php:121
> 6. Content_Tagger->tag()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/kronolith/migration/18_kronolith_upgrade_categoriestotags.php:44
> 7. Content_Users_Manager->ensureUsers()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/content/lib/Tagger.php:109
> 8. Horde_Core_Bundle->migrateDb()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/webmail-install:32
> 9. Horde_Db_Migration_Migrator->up()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/php/Horde/Core/Bundle.php:107
> 10. Horde_Db_Migration_Migrator->_doMigrate()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/php/Horde/Db/Migration/Migrator.php:102
> 11. Horde_Db_Migration_Base->migrate()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/php/Horde/Db/Migration/Migrator.php:182
> 12. KronolithUpgradeCategoriesToTags->up()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/php/Horde/Db/Migration/Base.php:121
> 13. Content_Tagger->tag()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/kronolith/migration/18_kronolith_upgrade_categoriestotags.php:44
> 14. Content_Users_Manager->ensureUsers()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/content/lib/Tagger.php:109
> 15. Horde_Db_Adapter_Mysql->insert()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/content/lib/Users/Manager.php:75
> 16. Horde_Db_Adapter_Mysql->execute()
> /afs/tu-berlin.de/units/tubit/www/horde-test.tubit/htdocs/horde-5/pear/php/Horde/Db/Adapter/Mysql.php:281
>
>
> Code changes:
> 18_kronolith_upgrade_categoriestotags.php
> class KronolithUpgradeCategoriesToTags extends Horde_Db_Migration_Base
>
> public function up()
> {
> $sql = 'SELECT event_uid, event_category, event_creator_id,
> calendar_id FROM kronolith_events';
> $this->announce('Migrating event categories to tags.');
> $rows = $this->select($sql);
> foreach ($rows as $row) {
> print __FUNCTION__.' '.$row['event_creator_id'].'
> ['.(string)$row['event_uid'].', '.$this->_type_ids['event'].']
> '.$row['event_category'].PHP_EOL; //DBG
> $this->_tagger->tag(
> $row['event_creator_id'],
> array('object' => (string)$row['event_uid'], 'type' =>
> $this->_type_ids['event']),
> $row['event_category']
> );
>
> // Do we need to tag the event again, but as the share owner?
> try {
> $cal = $this->_shares->getShare($row['calendar_id']);
> if ($cal->get('owner') != $row['event_creator_id']) {
> //////// case sensitive, but same user!
> print __FUNCTION__.' SHARE: '.$cal->get('owner').'
> ['.(string)$row['event_uid'].', '.$this->_type_ids['event'].']
> '.$row['event_category'].PHP_EOL; //DBG
> $this->_tagger->tag(
> $cal->get('owner'),
> array('object' => (string)$row['event_uid'],
> 'type' => $this->_type_ids['event']),
> $row['event_category']
> );
> }
> } catch (Exception $e) {
> $this->announce('Unable to find Share: ' .
> $row['calendar_id'] . ' Skipping.');
> }
> }
> print __FUNCTION__.' ------'.PHP_EOL; //DBG, NOT reached due to
> abort
> $this->announce('Event categories successfully migrated.');
> $this->removeColumn('kronolith_events', 'event_category');
> }
>
> -------------------------
>
> Manager.php
> class Content_Users_Manager
> public function ensureUsers()
>
> // Create any users that didn't already exist
> foreach ($userName as $user => $userIndex) {
> $query = 'INSERT INTO ' . $this->_t('users') . '
> (user_name) VALUES (' . $this->toDriver($user) . ')';
> print __FUNCTION__.':: '.$query.PHP_EOL;
> // $userIds[$userIndex] = $this->_db->insert('INSERT
> INTO ' . $this->_t('users') . ' (user_name) VALUES (' .
> $this->toDriver($user) . ')');
> $userIds[$userIndex] = $this->_db->insert( $query );
> }
> print __FUNCTION__.': ---'.PHP_EOL;
>
>
> What can I do to overcome the problem?
>
> Thanks in advance.
>
> Herbert
What happened is that your authentication backend (most probably LDAP)
is case insensitive while Horde is case sensitive. You also didn't
configure a hook that normalizes the Horde user names to a canonical
format. Thus users have been able to login with different spellings,
creating different Horde users.
While running the migrations to H4/H5 there is one table
(rampage_users) which creates an index on the user name. Depending on
your MySQL configuration the created index might be case insensitive,
so it chokes on the user names that only differ on upper/lower case.
Possible solutions:
- Normalize user names before running the migration (convert any user
names to lower case, or drop records not in lower case)
- Use a table engine for rampage_users that's not case sensitive.
- Use a collation for rampage_users that's not case sensitive.
--
Jan Schneider
The Horde Project
http://www.horde.org/
More information about the kronolith
mailing list