[horde] Weird values in horde_prefs where pref_name='last_login' (possibly MySQL problem)

[Otto Stolz]

Hello,

I am testing Horde 3.1.3, Imp 4.1.3, Turba 2.1.3, under Linux (openSUSE 10.2 (i586));
preferences are held in a MySQL 5.0.26 database.

All was well, and I was about to let the bulk of my users in for a beta test.
So I fetched the users' preferences and address books from the Horde 2.2.4,
IMP 3.2.2, Turba 1.2.1 production installation, cf. below for details.

Since that action, every user gets the "Last login: Never" message.
It is definitely not the problem described in the FAQ, as it has
worked before that action.

This is not a general problem with the horde_prefs table, as other
preferences, e. g. the language setting, work perfectly well.

I have found that during login, a new entry is written to horde_prefs,
with pref_name='last_login', and a pref_value that corresponds to the
following PHP array (example):
   array('time'=>'1174326994', 'host'=>'cheiron.rz.uni-konstanz.de')
whilst the old entries have just a Unix timestamp in the pref_value field.
The latest of these old time stamps is just the minute when I dumped the
data from the production system. Cf. below, for Details from the current
state of the database.

So the questions are:
- Why does Horde write the last_login entries ín a different format
   than before?
- And why does it not recognize its own entries?
- What did I wrong?
- And, above all, how can I get Horde back to its original behaviour
   (and still have the users' preferencies in the test system)?

Thanks for any hint,
   Otto Stolz



---------- How I copied the preferences and address books --------

On the production system:
-------------------------

   mysqldump -p -u root --opt -t -c -r /tmp/horde2.sql horde2 horde_prefs turba_objects

This produced something like the following (only longer):
   LOCK TABLES `horde_prefs` WRITE;
   INSERT INTO `horde_prefs` (`pref_uid`, `pref_scope`, `pref_name`, `pref_value`)
   VALUES ('N.N at uni-konstanz.de','imp','max_msgs','20'), ....
          ('N.N at uni-konstanz.de','imp','last_login','1174049719'), ...

On the test system:
-------------------

   echo 'delete quick from horde_prefs; delete quick from turba_objects;' \
      | mysql -u root -p horde3test

This should remove the values, but let the table definitions untouched.

Additional question:
- Was it unwise to specify the »quick« option?

   mysql -u root -p horde3test < /tmp/horde2.sql

This should enter the preferencies, and address books, from the data base
dump into the existing table.

---------- Results from the Preferences database --------

Now, the horde_prefs table has the following fields:
   +------------+--------------+------+-----+---------+-------+
   | Field      | Type         | Null | Key | Default | Extra |
   +------------+--------------+------+-----+---------+-------+
   | pref_uid   | varchar(200) | NO   | PRI |         |       |
   | pref_scope | varchar(16)  | NO   | PRI |         |       |
   | pref_name  | varchar(32)  | NO   | PRI |         |       |
   | pref_value | longtext     | YES  |     | NULL    |       |
   +------------+--------------+------+-----+---------+-------+

and the following indices:
   +-----+---------+------+-----------+-----+--------+------+-------+-------+
   | Non_| Key_    | Seq_ | Column_   | Col | Cardin | Sub_ | Packed| Index_|
   | un  | name    | in_  | name      | la  | ality  | part |       | type  |
   | ique|         | index|           | tion|        |      |       |       |
   +-----+---------+------+-----------+-----+--------+------+-------+-------+
   |   0 | PRIMARY |    1 | pref_uid  | A   |   NULL | NULL | NULL  | BTREE |
   |   0 | PRIMARY |    2 | pref_scope| A   |   NULL | NULL | NULL  | BTREE |
   |   0 | PRIMARY |    3 | pref_name | A   |  44326 | NULL | NULL  | BTREE |
   +-----+---------+------+-----------+-----+--------+------+-------+-------+

Note that horde/scripts/sql/horde_prefs.mysql.sql defines:
   CREATE TABLE horde_prefs (
     pref_uid        VARCHAR(200) NOT NULL,
     pref_scope      VARCHAR(16) NOT NULL DEFAULT '',
     pref_name       VARCHAR(32) NOT NULL,
     pref_value      LONGTEXT NULL,
     PRIMARY KEY (pref_uid, pref_scope, pref_name)
   );
   CREATE INDEX pref_uid_idx ON horde_prefs (pref_uid);
   CREATE INDEX pref_scope_idx ON horde_prefs (pref_scope);

Additional questions:
   What about pref_uid_idx, and pref_scope_idx?
   - Should they be displayed in response to »show index from horde_prefs;«?
   - Should I re-create those indices?
   - If so, how did I loose them?

------------------------- End of details ----------------------------