Text only | Text with Images

Roundcube Community Forum

Third Party Contributions => API Based Plugins => Topic started by: mootabolife on April 05, 2011, 08:29:55 AM

Title: Automatic Addressbook SQL Error
Post by: mootabolife on April 05, 2011, 08:29:55 AM
When attempting to install the latest version of auto addressbook by running the SQL code provided, I receive the following error:

QuoteQuery : CREATE TABLE `collected_contacts` (  `contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,  `changed` datetime NOT NULL DEFAULT...

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--!40008
   --ON DELETE CASCADE
   --ON UPDATE CASCADE
)' at line 15

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000
---------------------------------------------------

from the following provided queries:

Code Select
CREATE TABLE `collected_contacts` (
 `contact_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `changed` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00',
 `del` TINYINT(1) NOT NULL DEFAULT '0',
 `name` VARCHAR(12\8) NOT NULL,
 `email` VARCHAR(128) NOT NULL,
 `firstname` VARCHAR(128) NOT NULL,
 `surname` VARCHAR(128) NOT NULL,
 `vcard` TEXT NULL,
 `user_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(`contact_id`),
 INDEX `user_collected_contacts_index` (`user_id`,`email`),
 CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`)
   --!40008
   --ON DELETE CASCADE
   --ON UPDATE CASCADE
) ;

The previous auto addressbook SQL code worked fine.  This is using the latest version downloaded yesterday.  This was changed due to the 0.51 upgrade compatibility issue.

This is running on MySQL 5.1 on IIS7.  RC 0.5.1.

What does the --!40008 switch do exactly?
Title: Automatic Addressbook SQL Error
Post by: dmcleman on April 11, 2011, 05:28:43 AM
Same problem here - any answers?
Title: Automatic Addressbook SQL Error
Post by: mootabolife on April 13, 2011, 05:54:32 PM
I'm going to work on a fix soon.  I'm guessing it has to do with SQL 5.1 vs 5.5
Title: Automatic Addressbook SQL Error
Post by: @dmin on April 20, 2011, 02:51:43 PM
Same problem here. RC 0.5.1, mysql 5.1.49 on debian squeeze. Any updates?

EDIT:

One of our programmers got it to work using this, it's working fine for us:

Code Select
CREATE TABLE `collected_contacts` (
  `contact_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `email` varchar(128) NOT NULL,
  `firstname` varchar(128) NOT NULL,
  `surname` varchar(128) NOT NULL,
  `vcard` text,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`contact_id`),
  KEY `user_collected_contacts_index` (`user_id`,`email`),
  CONSTRAINT `collected_contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Title: Automatic Addressbook SQL Error
Post by: roundcube76 on June 22, 2011, 06:20:31 AM
use this script its work fine for me

Code Select
CREATE TABLE `collected_contacts` (
 `contact_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `changed` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00',
 `del` TINYINT(1) NOT NULL DEFAULT '0',
 `name` VARCHAR(12\8) NOT NULL,
 `email` VARCHAR(128) NOT NULL,
 `firstname` VARCHAR(128) NOT NULL,
 `surname` VARCHAR(128) NOT NULL,
 `vcard` TEXT NULL,
 `user_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(`contact_id`),
 INDEX `user_collected_contacts_index` (`user_id`,`email`),
 CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`)
) ;
Title: Automatic Addressbook SQL Error
Post by: mootabolife on June 28, 2011, 08:16:58 AM
Quote from: roundcube76;35431use this script its work fine for me

Code Select
CREATE TABLE `collected_contacts` (
 `contact_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `changed` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00',
 `del` TINYINT(1) NOT NULL DEFAULT '0',
 `name` VARCHAR(12\8) NOT NULL,
 `email` VARCHAR(128) NOT NULL,
 `firstname` VARCHAR(128) NOT NULL,
 `surname` VARCHAR(128) NOT NULL,
 `vcard` TEXT NULL,
 `user_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(`contact_id`),
 INDEX `user_collected_contacts_index` (`user_id`,`email`),
 CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`)
) ;

Without the cascade and delete calls, your USERIDs will be affected I believe.
Title: Automatic Addressbook SQL Error
Post by: roundcube76 on June 30, 2011, 03:09:49 AM
Hi,
but with cascade delete and update i have an error when i excute the script.
Have you any idea ?
Title: Automatic Addressbook SQL Error
Post by: rosali on June 30, 2011, 01:58:35 PM
You can use your mod. The only downside is, that you'll have dead entries in 'collected_contacts' table if you wipe out a user from Roundcube's default 'users' table.

This plugin was originally developped by J.D. (see comments in code). I adopted it for a while. J.D. wanted to have it back, but now it looks like as it is not maintained again.

Don't ask me to take it back. I've decided to pass away all address book related plugins. Perhaps there is someone else who volunteers as a hoster  a n d maintainer. It is a nice plugin ...
Title: Automatic Addressbook SQL Error
Post by: JocelynD on August 27, 2011, 09:25:16 AM
Hi,

...And sorry for long silence, exams + long travel...
I'll normaly be more reactive now and keep an eye on that forum from times to times also.

Thanks for those who brought support & solutions, I commited, see  Roundcube plugin(s) - Anomalie #23: MySQL - #1005 - Can't create table - Redmine (http://code.crapouillou.net/issues/23)
Text only | Text with Images