On 25/10/11 2:03 AM, Simon Loewenthal wrote: > A bit confusing, because, unless I have misinterpreted the results below, > there > are indices on the columns so the /drop index/ should work:- > > mysql> show index in messages; > +----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | > Cardinality | Sub_part | Packed | Null | Index_type | Comment | > +----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | messages | 0 | PRIMARY | 1 | message_id | A | 0 | NULL | NULL | | BTREE | | > | messages | 0 | uniqueness | 1 | user_id | A | 0 | NULL | NULL | | BTREE | | > | messages | 0 | uniqueness | 2 | cache_key | A | 0 | NULL | NULL | | BTREE | > | > | messages | 0 | uniqueness | 3 | *uid* | A | 0 | NULL | NULL | | BTREE | | > | messages | 1 | created_index | 1 | created | A | 0 | NULL | NULL | | BTREE > | | > | messages | 1 | index_index | 1 | user_id | A | 0 | NULL | NULL | | BTREE | | > | messages | 1 | index_index | 2 | cache_key | A | 0 | NULL | NULL | | BTREE > | | > | messages | 1 | index_index | 3 | *idx* | A | 0 | NULL | NULL | | BTREE | | > +----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Looks to me like there are four indexes there: - `PRIMARY` which on column message_id - `uniqueness` on columns user_id, cache_key, uid - `created_index` on column created - `index_index` on columns user_id, cache_key, idx So there is indeed no index named `idx` nor one named `uid`, though both those *columns* do appear in other indexes. Perhaps an easier way to understand the table structure is to issue SHOW CREATE TABLE messages; HTH, Ben. > ERROR 1091 (42000) at line 7: Can't DROP 'idx'; check that column/key exists > ERROR 1091 (42000) at line 7: Can't DROP 'uid'; check that column/key exists > > > On 24/10/11 16:46, BH wrote: >> The rest of the upgrade script probably didn't run, to be honest I have >> no idea how it works as I have not done one for a long time. Perhaps you >> are able to run it again, if so either remove that 'DROP INDEX' from the >> query that gets run or create the index so it is able to drop it. >> >> >> On 24/10/2011 10:40 PM, Simon Loewenthal wrote: >>> On 24/10/11 16:28, Simon Loewenthal wrote: >>>> On 24/10/11 15:52, BH wrote: >>>>> Hi, >>>>> >>>>> As long as line 7 is actually a 'DROP INDEX' you should be fine, it just >>>>> means the index it was doing to remove wasn't there. >>>>> >>>>> With mySQL, when you run 'describe messages' it will only show the >>>>> columns from the table, not any indexes. If you want to show indexes, >>>>> use 'show index in messages'. >>>>> >>>>> On 24/10/2011 9:16 PM, Simon Loewenthal wrote: >>>>>> >>>>>> Hi, >>>>>> >>>>>> After a RC upgrade (.5.4 -> 0.6), can one blindly run the >>>>>> mysql.update.sql, or should I adjust it accordingly to the release I am >>>>>> upgrading from? I ask as I tried and it complained:- >>>>>> >>>>>> root@peon /www/roundcube/SQL # mysql*-p roundcubemail< mysql.update.sql >>>>>> * >>>>>> Enter password: >>>>>> ERROR 1091 (42000) at line 7: Can't DROP 'idx'; check that column/key >>>>>> exist >>>>>> >>>>>> Entry in the mysql.update.sql reads: >>>>>> >>>>>> ALTER TABLE `messages` >>>>>> DROP INDEX `idx`, >>>>>> DROP INDEX `uid`; >>>>>> >>>>>> >>>>>> mysql> describe messages; >>>>>> +------------+------------------+------+-----+---------------------+----------------+ >>>>>> | Field | Type | Null | Key | Default | >>>>>> Extra | >>>>>> +------------+------------------+------+-----+---------------------+----------------+ >>>>>> | message_id | int(11) unsigned | NO | PRI | NULL | >>>>>> auto_increment | >>>>>> | user_id | int(10) unsigned | NO | MUL | 0 >>>>>> | | >>>>>> | del | tinyint(1) | NO | | 0 >>>>>> | | >>>>>> | cache_key | varchar(128) | NO | | NULL >>>>>> | | >>>>>> | created | datetime | NO | MUL | 1000-01-01 00:00:00 >>>>>> | | >>>>>> | *idx* | int(11) unsigned | NO | | 0 >>>>>> | | >>>>>> | *uid* | int(11) unsigned | NO | | 0 >>>>>> | | >>>>>> | subject | varchar(255) | NO | | NULL >>>>>> | | >>>>>> | from | varchar(255) | NO | | NULL >>>>>> | | >>>>>> | to | varchar(255) | NO | | NULL >>>>>> | | >>>>>> | cc | varchar(255) | NO | | NULL >>>>>> | | >>>>>> | date | datetime | NO | | 1000-01-01 00:00:00 >>>>>> | | >>>>>> | size | int(11) unsigned | NO | | 0 >>>>>> | | >>>>>> | headers | text | NO | | NULL >>>>>> | | >>>>>> | structure | text | YES | | NULL >>>>>> | | >>>>>> +------------+------------------+------+-----+---------------------+----------------+ >>>>>> 15 rows in set (0.00 sec) >>>>>> >>>>>> >>>>>> Regards, S >>>>>> >>>>>> >>>>>> >>>> Does this mean that it ran successfully or, it bombed out when it >>>> tried to drop an inexistent index? If the later, then I can comment >>>> out this portion and run again. >>>> >>>> Although, there is now an index, so I think this ran ;) >>>> >>>> | Table | Non_unique | Key_name | Seq_in_index | Column_name | >>>> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment >>>> | messages | 1 | index_index | 3 | idx | >>>> A | 0 | NULL | NULL | | BTREE | >>> >>> The words column is not there. I think the update script failed, because >>> its in it: >>> ALTER TABLE `contacts` ADD `words` TEXT NULL AFTER `vcard`; >>> >>> roundcube: MDB2 Error: no such field (-19): _doQuery: [Error message: >>> Could not execute statement]#012[Last executed query: INSERT INTO >>> contacts (user_id, changed, del, `vcard`, `name`, `email`, `firstname`, >>> `surname`, `words`) VALUES (5, now(), 0, >>> 'BEGIN:VCARD\r\nVERSION:3.0\r\nN:test;test;;;\r\nFN:test >>> test\r\nEMAIL;type=INTERNET;type=HOME:[email protected]\r\nEND:VCARD', >>> 'test test','[email protected]', 'test', 'test', ' test >>> [email protected]')]#012[Native code: 1054]#012[Native message: Unknown >>> column 'words' in 'field list' >>> >>> > > > -- > Email simon AT klunky DOT co DOT uk > PGP is optional: 4BA78604 > I won't accept your confidentiality > agreement, and your Emails are kept. > ~Ö¿Ö~ > > > -- List info: http://lists.roundcube.net/users/ BT/8f4f07cd
