Hi all,
Very new to all things sql, so please bear with me...
I performed the upgrade, and things were going well, until I got to the SQL commands I need to enter to upgrade the db schema...
The first few were successful, but I'm getting errors for a few of the commands...
1.
mysql> ALTER TABLE `contacts` ALTER `firstname` SET DEFAULT '';
ERROR 1054 (42S22): Unknown column 'firstname' in 'contacts'
2.
mysql> ALTER TABLE `contacts` ALTER `surname` SET DEFAULT '';
ERROR 1054 (42S22): Unknown column 'surname' in 'contacts'
3.
mysql> CREATE TABLE `contactgroups` (
-> `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
-> `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
-> `del` tinyint(1) NOT NULL DEFAULT '0',
-> `name` varchar(128) NOT NULL DEFAULT '',
-> PRIMARY KEY(`contactgroup_id`),
-> CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`)
-> REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
-> INDEX `contactgroups_user_index` (`user_id`,`del`)
-> ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
ERROR 1005 (HY000): Can't create table 'roundcube.contactgroups' (errno: 150)
and
4.
mysql> CREATE TABLE `contactgroupmembers` (
-> `contactgroup_id` int(10) UNSIGNED NOT NULL,
-> `contact_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
-> `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
-> PRIMARY KEY (`contactgroup_id`, `contact_id`),
-> CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`)
-> REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
-> CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`)
-> REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE
-> ) /*!40000 ENGINE=INNODB */;
ERROR 1005 (HY000): Can't create table 'roundcube.contactgroupmembers' (errno: 150)
I know I probably just need to create the fields for the first two errors, and I'm thinking maybe the last line with the /*! stuff is causing the last two errors, but have no clue how to fix this...
Help!!!
Ok, did a quick mysqldump of the db, and here's what I see...
1. The 'firstname' field is currently named 'first_name' - as is 'middle_name' and 'last_name'...
So... should I just rename this field? Or are the update instructions wrong?
2. There is no 'surname' field - just create it?
No idea on the other errors when trying to create the two new tables 'contactgroups' and 'contactgroupmembers'...
Would appreciate some help here.
The contacts table never (as far as I know) had any columns named with an underscore like "first_name", are you sure you are in the right database?
Hi skaero,
Yes, I was in the right database... weird...
Oh well, since there weren't that many people using it yet - none of them using it as their primary mail client - I just went ahead and started with a new one.
They are really liking it though, so some very well may start using it more often...
Thanks for the reply...
Aha...
After your reply, I went and peeked again, and there were two databases... roundcube, and roundcubemail... the config file was pointed at the roundcubemail db, with a username of roundcube, and I got confused, and connected to the roundcube database when I executed the update commands...
I just performed them on the correct db and everything went without a hitch...
Now... is there an easy way to replace the new blank database I already created with the now updated old one?
Thanks again for your help...
Oh - and the reason I want to continue using the new db I created is the old one didn't fit in with my new (since I installed 0.3.1) naming conventions for my databases...
You'll just have to export the tables in the old database and then import them into the new database.
Worked great, back up and running with all data restored...
Thanks again skaero, and Merry Christmas/Happy New Year (or whatever you celebrate, if anything)! :)