Newsletter mail_queue not created.

constantijnw
Joined: 19 Mar 2005

Newsletter mail_queue not created.

Posted:09 Nov 2009 (22:07 UTC)
BW 2.7
Integrity check fails because table mail_queue is not created.

The following table details are given:
mail_queue_id I8 PRIMARY,
content_id I4 NOTNULL,
email C(160) NOTNULL,
nl_content_id I4 NOTNULL,
user_id I4,
url_code C(32),
queue_date I8 NOTNULL,
begin_date I8,
sent_date I8,
last_read_date I8,
last_read_ip C(39),
`reads` I2 NOTNULL DEFAULT '0',
mail_error X
CONSTRAINT ', CONSTRAINT `mailq_content_ref` FOREIGN KEY (`nl_content_id`) REFERENCES `bwliberty_content`( `content_id` )
, CONSTRAINT `mailq_user_ref` FOREIGN KEY (`user_id`) REFERENCES `bwusers_users`( `user_id` )'

Doesn't look like a complete mysql table definition statement to me.

What could have been the cause of this failure?
What is the full mysql statement for creating this table?
Lester Caine
Joined: 24 Apr 2004

Re: Newsletter mail_queue not created.

Posted:11 Nov 2009 (07:27 UTC)
constantijnw - run the installer in debug mode ( check box at the bottom of the add package page ) and let us know what MySQL generates for that table. An immediate thought is that there should NOT be back ticks on the `reads` field name, but I think that is a different bug.

None of the main developers actually run MySQL so the odd problem there needs your help to pin down!
constantijnw
Joined: 19 Mar 2005

Re: Newsletter mail_queue not created.

Posted:11 Nov 2009 (12:57 UTC)
Here it is:

(mysql): CREATE TABLE bwmail_queue ( mail_queue_id BIGINT NOT NULL, content_id INTEGER NOT NULL, email VARCHAR(160) NOT NULL, nl_content_id INTEGER NOT NULL, user_id INTEGER, url_code VARCHAR(32), queue_date BIGINT NOT NULL, begin_date BIGINT, sent_date BIGINT, last_read_date BIGINT, last_read_ip VARCHAR(39), reads SMALLINT NOT NULL DEFAULT 0, mail_error TEXT , CONSTRAINT mailq_content_ref FOREIGN KEY (nl_content_id) REFERENCES bwliberty_content( content_id ) , CONSTRAINT mailq_user_ref FOREIGN KEY (user_id) REFERENCES bwusers_users( user_id ), PRIMARY KEY (mail_queue_id) )ENGINE=INNODB

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 'reads SMALLINT NOT NULL DEFAULT 0, mail_error T' at line 13

ADODB_DataDict.ExecuteSQLArray(Array1) % line 160, file: install_packages.php
include_once(/srv/www/html/bwtest/install/install_packages.php) % line 160, file: install.php

ADOConnection._Execute(CREATE TABLE bwmail_queue (
mail_queue_id BIGINT NOT NULL,
content_id INTEGER NOT NULL,
email ..., false) % line 1010, file: adodb.inc.php
ADOConnection.Execute(CREATE TABLE bwmail_queue (
mail_queue_id BIGINT NOT NULL,
content_id INTEGER NOT NULL,
email ...) % line 377, file: adodb-datadict.inc.php


Running MySQL 5.0.45 on CentOS 5.3
Lester Caine
Joined: 24 Apr 2004

Re: Newsletter mail_queue not created.

Posted:11 Nov 2009 (13:17 UTC)
The ` ` around reads flags it as a reserved word in some database, sounds as if this is the case in MySQL. For most databases we have now stripped the reserved words, and replaced them with ones that do not have a restriction, but it looks like this one has slipped through. The `reads` should be changed to "reads" but I think that has now been stripped from the installer. I'll flag this up on the IRC channel ....
constantijnw
Joined: 19 Mar 2005

Re: Newsletter mail_queue not created.

Posted:11 Nov 2009 (13:42 UTC)
Almost right: `reads` instead of "reads" fixes the problem. Will file report.
Lester Caine
Joined: 24 Apr 2004

Re: Newsletter mail_queue not created.

Posted:12 Nov 2009 (07:07 UTC)
reads has been replaced with hits in CVS, and there is an upgrade to correct existing databases. But if you would prefer to fix it manually, then rename the field in the database, and replace reads with hits in lines 88 and 209 of BitNewsletterMailer.php

In practice I'm not even sure that the value is being displayed, since the integral 'hits' function probably now handles it, but it has been left function rather than simply removing it.
constantijnw
Joined: 19 Mar 2005

Re: Newsletter mail_queue not created.

Posted:12 Nov 2009 (11:26 UTC)
Thanks Lester! Adapted the installation manually. In v2.7 BitNewsletterMailer.php reads is located in line 88 and 209. Installation of the package went flawless now.
  Page 1 of 1  1