[kronolith] DB conversion woes on creating 'rampage_users' (webmail 1.2.8 to HordeGW 5.0.2)

Thu Jan 24 14:41:18 UTC 2013

On 01/24/2013 11:30 AM, Jan Schneider wrote:
> 
> 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. 

We delegated authentication to imp which logs in to imap server driven
by cyrus authenticating against kerberos in turn. Thus the whole
authentication chain is case insensitive.

> You also didn't
> configure a hook that normalizes the Horde user names to a canonical

Indeed, nobody did pay attention to that at the moment we switched to Horde.

> format. Thus users have been able to login with different spellings,
> creating different Horde users.

I suppose you are talking of '_username_hook_frombackend()' in H3 and
'authusername()' in H5, right?

> 
> 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)

We considered that but weren't sure whether this won't raise other
problems, e.g. some object id containg a hash derived from Horde user
name which will be broken afterwards.

As I stated in my OP, some normalization takes place since after login
as 'SomeBody' I was called 'somebody' by the portal.

> - Use a table engine for rampage_users that's not case sensitive.
> - Use a collation for rampage_users that's not case sensitive.

Well, all tables have the default collation which is case insensitive.

Thank you very much. I well appreciate your hints and tips.

Herbert

-- 
*Multitasking* /v./ Screwing up several things at once
                             (gefunden bei Oliver Cromm in desd)