Dada mail 11.22.0 , MYSQL error after New Draft -> Send test or Send Mass mail

[prbt2016]

Hello @justingit ,

I was in the process of manual installation of Dada mail downloaded from sourceforge on MYSQL 8.

However , after successful manual install getting following error while New draft -> Send test or Send mass mail i.e :

Yikes! App/Server Problem!

So to check I checked under datadirectory logs i.e .dada_files/.logs/errors.txt, following error is thrown i.e :

DBD::mysql::st execute failed: Column 'last_modified_timestamp' cannot be null at DADA/MailingList/MessageDrafts.pm line 329.
cannot do statement 'UPDATE dada_message_drafts SET role = ?, draft = ?, last_modified_timestamp = NULL WHERE list = ? AND id = ?'! Column 'last_modified_timestamp' cannot be null
 at DADA/App/MassSend.pm line 1993.

What could be the issue . Could you please replicate and fix this?

On another server with MYSQL 5.6 , creating drafts works fine . Is Dada mail supported on MYSQL 8?.

[justingit]

Saving a draft seems to work fine for me in v11.22.0 and using MySQL 8,

justin@m1 pro_dada % mysql --version
mysql  Ver 8.0.33 for macos11.7 on arm64 (Homebrew)

Is your version of MySQL even newer?

Saving a draft with tracing turned on:

in save() at DADA/MailingList/MessageDrafts.pm line 144.
role exists: draft at DADA/MailingList/MessageDrafts.pm line 167.
save_role exists: draft at DADA/MailingList/MessageDrafts.pm line 175.
$id:  at DADA/MailingList/MessageDrafts.pm line 206.
id undefined. at DADA/MailingList/MessageDrafts.pm line 210.
QUERY: INSERT INTO dada_message_drafts (list, role, draft, created_timestamp, last_modified_timestamp) VALUES (?,?,?, NOW(), NOW()) at DADA/MailingList/MessageDrafts.pm line 235.
execute params: $VAR1 = [
          'jj',
          'draft',
          'attachment1=
...things removed...
'
        ];

But the error you posted doesn't happen when you save a new draft, but copy a draft into something else, like a stationery or schedule. That also seems to work:

id defined. at DADA/MailingList/MessageDrafts.pm line 277.
QUERY: UPDATE dada_message_drafts SET role = ?, draft = ?, last_modified_timestamp = NULL WHERE list = ? AND id = ? at DADA/MailingList/MessageDrafts.pm line 322.
$draft attachment1=
...things removed...

Table looks like this:

mysql> describe dada_message_drafts;
+-------------------------+-------------+------+-----+-------------------+-----------------------------------------------+
| Field                   | Type        | Null | Key | Default           | Extra                                         |
+-------------------------+-------------+------+-----+-------------------+-----------------------------------------------+
| id                      | int         | NO   | PRI | NULL              | auto_increment                                |
| list                    | varchar(16) | YES  |     | NULL              |                                               |
| created_timestamp       | timestamp   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| last_modified_timestamp | timestamp   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| name                    | varchar(80) | YES  |     | NULL              |                                               |
| screen                  | varchar(80) | YES  |     | NULL              |                                               |
| role                    | varchar(80) | YES  |     | NULL              |                                               |
| draft                   | mediumtext  | YES  |     | NULL              |                                               |
+-------------------------+-------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

mysql> 

the "last_modified_timestamp" is set to have NULL values allowed. I don't see anything that says that that column can't be null in the statement that created the table,

https://github.com/justingit/dada-mail/blob/2818ac480be455acc5d7909024c4f80969159582/app/dada/extras/SQL/mysql_schema.sql#L132C1-L142C1

CREATE TABLE IF NOT EXISTS dada_message_drafts (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
list varchar(16),
created_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
last_modified_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
name varchar(80), 
screen varchar(80),
role varchar(80),
draft mediumtext
);

The last time this was changed was commented by you, actually:

https://github.com/justingit/dada-mail/issues/822

If I look in the code,

https://github.com/justingit/dada-mail/blob/2818ac480be455acc5d7909024c4f80969159582/app/dada/DADA/MailingList/MessageDrafts.pm#L306C5-L310C40

There's a comment that says,

# NOW() works just fine, too.

So maybe just change that query above it to,

$query =
                    'UPDATE '
                  . $self->{sql_params}->{message_drafts_table}
                  . ' SET role = ?, draft = ?, last_modified_timestamp = NOW() WHERE list = ? AND id = ?';

and maybe that'll help you get on your way,

[prbt2016]

Hello @justingit ,

Thanks for being quick on reply.

Sorry not while creating a new draft . It occurs while New Draft . Put some content in the draft and go to Send test or send mass mail it fails . I have edited the subject and content of the issue . Please check Send test and send Mass mail. Is it working?.

[justingit]

Yes that seems to work fine as well,

[prbt2016]

Hello @justingit ,

Changing code to :

$query =
                    'UPDATE '
                  . $self->{sql_params}->{message_drafts_table}
                  . ' SET role = ?, draft = ?, last_modified_timestamp = NOW() WHERE list = ? AND id = ?';

Works fine, without any issues.

Is it possible that you could incorporate the changes in that file itself?.

[justingit]

Is it possible that you could incorporate the changes in that file itself?.

I'd like to truly understand why the code and SQL is working for everyone else using MySQL 8 - including me - but not you. Is your MySQL tables or MySQL version different?