Cheers Ben.
In the end, I ran only this update:
ALTER TABLE
`contacts` ADD `words` TEXT NULL AFTER `vcard`;
ALTER TABLE `contacts` CHANGE `vcard` `vcard` LONGTEXT
/*!40101 CHARACTER SET utf8 */ NULL DEFAULT NULL;
ALTER TABLE `contactgroupmembers` ADD INDEX
`contactgroupmembers_contact_index` (`contact_id`);
TRUNCATE TABLE `messages`;
TRUNCATE TABLE `cache`;
I did not bother with the rest of the mysql,update.sql as it kept
on complaining that items did not exist, or there were indices or
keys columns that already existed.
Seems to work well.
S
On 24/10/11 23:19, Ben Schmidt wrote:
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:sas@mailcatch.com\r\nEND:VCARD',
'test test','sas@mailcatch.com', 'test', 'test', ' test
sas@mailcatch.com')]#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.
~Ö¿Ö~
--
Email simon AT klunky DOT co DOT uk
PGP is optional: 4BA78604
I won't accept your confidentiality
agreement, and your Emails are kept.
~Ö¿Ö~