[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 14:50:57 UTC 2013
Zitat von Herbert Pophal <pophal at tubit.tu-berlin.de>:
> 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?
Correct.
>> 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.
Eh, yes, I meant the opposite. You need a collation or index engine
that *is* case sensitive, so that JohnDoe is *not* considered a
duplicate to johndoe.
> 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)
--
Jan Schneider
The Horde Project
http://www.horde.org/
More information about the kronolith
mailing list