Author Topic: Automatic Addressbook SQL Error  (Read 7960 times)

Offline mootabolife

  • Newbie
  • *
  • Posts: 7
Automatic Addressbook SQL Error
« 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:

Quote
Query : 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?

Offline dmcleman

  • Newbie
  • *
  • Posts: 2
Automatic Addressbook SQL Error
« Reply #1 on: April 11, 2011, 05:28:43 AM »
Same problem here - any answers?

Offline mootabolife

  • Newbie
  • *
  • Posts: 7
Automatic Addressbook SQL Error
« Reply #2 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

Offline @dmin

  • Jr. Member
  • **
  • Posts: 14
Automatic Addressbook SQL Error
« Reply #3 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;
« Last Edit: April 20, 2011, 03:28:07 PM by @dmin »

Offline roundcube76

  • Newbie
  • *
  • Posts: 5
Automatic Addressbook SQL Error
« Reply #4 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`)
) ;
« Last Edit: June 22, 2011, 06:44:56 AM by rosali »

Offline mootabolife

  • Newbie
  • *
  • Posts: 7
Automatic Addressbook SQL Error
« Reply #5 on: June 28, 2011, 08:16:58 AM »
Quote from: roundcube76;35431
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`)
) ;

 
Without the cascade and delete calls, your USERIDs will be affected I believe.

Offline roundcube76

  • Newbie
  • *
  • Posts: 5
Automatic Addressbook SQL Error
« Reply #6 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 ?

Offline rosali

  • Hero Member
  • *****
  • Posts: 2,533
Automatic Addressbook SQL Error
« Reply #7 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 ...
« Last Edit: June 30, 2011, 02:00:47 PM by rosali »
Regards,
Rosali
__________________
MyRoundcube Project (commercial)

Offline JocelynD

  • Newbie
  • *
  • Posts: 6
Automatic Addressbook SQL Error
« Reply #8 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