search

lsuits / lsu-block_quickmail

*Louisiana State University ITS no longer maintains this Project.* A Moodle block that provides selective, bulk emailing within courses
45 stars 68 forks source link

Error in plugin install of latest release v2.2.0 - missing 'course_id' column #270

Closed logan-reynolds closed 5 years ago

logan-reynolds commented 5 years ago

When trying to do a fresh install of the latest plugin version 2019012400 release v2.2.0 from master branch commitid 9d2d8e2 I'm seeing the following error after confirming plugin install on both Moodle 3.1 and 3.4, which prevents it from being installed:

Debug info: Key column 'course_id' doesn't exist in table
CREATE TABLE mdl_block_quickmail_signatures (
id BIGINT(10) NOT NULL auto_increment,
user_id BIGINT(10) NOT NULL,
title VARCHAR(125) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
signature LONGTEXT COLLATE utf8mb4_unicode_ci NOT NULL,
default_flag TINYINT(1) NOT NULL DEFAULT 0,
usermodified BIGINT(10) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
timedeleted BIGINT(10),
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicsign_use_ix (user_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='Stores signatures for users'
;
CREATE TABLE mdl_block_quickmail_config (
id BIGINT(10) NOT NULL auto_increment,
coursesid BIGINT(11) NOT NULL,
name VARCHAR(25) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
value VARCHAR(125) COLLATE utf8mb4_unicode_ci,
CONSTRAINT PRIMARY KEY (id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='Stores config info for teachers, per instance basis.'
;
CREATE TABLE mdl_block_quickmail_messages (
id BIGINT(10) NOT NULL auto_increment,
course_id BIGINT(10) NOT NULL,
user_id BIGINT(10) NOT NULL,
message_type VARCHAR(8) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
notification_id BIGINT(10) NOT NULL DEFAULT 0,
alternate_email_id BIGINT(10) NOT NULL DEFAULT 0,
signature_id BIGINT(10) NOT NULL DEFAULT 0,
subject LONGTEXT COLLATE utf8mb4_unicode_ci,
body LONGTEXT COLLATE utf8mb4_unicode_ci,
editor_format SMALLINT(3) NOT NULL DEFAULT 1,
sent_at BIGINT(10) NOT NULL,
to_send_at BIGINT(10) NOT NULL,
is_draft TINYINT(1) NOT NULL DEFAULT 0,
send_receipt TINYINT(1) NOT NULL DEFAULT 0,
send_to_mentors TINYINT(1) NOT NULL DEFAULT 0,
is_sending TINYINT(1) NOT NULL DEFAULT 0,
no_reply TINYINT(1) NOT NULL DEFAULT 0,
usermodified BIGINT(10) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
timedeleted BIGINT(10),
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicmess_cou_ix (course_id)
, KEY mdl_blocquicmess_use_ix (user_id)
, KEY mdl_blocquicmess_not_ix (notification_id)
, KEY mdl_blocquicmess_alt_ix (alternate_email_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='All message batches including saved drafts and sent messages'
;
CREATE TABLE mdl_block_quickmail_msg_recips (
id BIGINT(10) NOT NULL auto_increment,
message_id BIGINT(10) NOT NULL,
user_id BIGINT(10) NOT NULL,
sent_at BIGINT(10) NOT NULL,
moodle_message_id BIGINT(10) NOT NULL DEFAULT 0,
usermodified BIGINT(10) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicmsgreci_sen_ix (sent_at)
, KEY mdl_blocquicmsgreci_mes_ix (message_id)
, KEY mdl_blocquicmsgreci_use_ix (user_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='The recipient users of a specific message'
;
CREATE TABLE mdl_block_quickmail_draft_recips (
id BIGINT(10) NOT NULL auto_increment,
message_id BIGINT(10) NOT NULL,
type VARCHAR(7) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
recipient_type VARCHAR(6) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
recipient_id BIGINT(10) NOT NULL,
recipient_filter LONGTEXT COLLATE utf8mb4_unicode_ci,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicdrafreci_mes_ix (message_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='The recipient entity keys of a specific message'
;
CREATE TABLE mdl_block_quickmail_msg_ad_email (
id BIGINT(10) NOT NULL auto_increment,
message_id BIGINT(10) NOT NULL,
email VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
sent_at BIGINT(10) NOT NULL,
usermodified BIGINT(10) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicmsgademai_mes_ix (message_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='Additional emails that a specific message was sent to'
;
CREATE TABLE mdl_block_quickmail_msg_attach (
id BIGINT(10) NOT NULL auto_increment,
message_id BIGINT(10) NOT NULL,
path LONGTEXT COLLATE utf8mb4_unicode_ci NOT NULL,
filename LONGTEXT COLLATE utf8mb4_unicode_ci NOT NULL,
usermodified BIGINT(10) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicmsgatta_mes_ix (message_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='The files that are attached to a parent message'
;
CREATE TABLE mdl_block_quickmail_alt_emails (
id BIGINT(10) NOT NULL auto_increment,
setup_user_id BIGINT(10) NOT NULL,
course_id BIGINT(10) NOT NULL DEFAULT 0,
user_id BIGINT(10) NOT NULL DEFAULT 0,
email VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
firstname VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
lastname VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
allowed_role_ids VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
is_validated TINYINT(1) NOT NULL DEFAULT 0,
usermodified BIGINT(10) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
timedeleted BIGINT(10),
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicaltemai_cou_ix (course_id)
, KEY mdl_blocquicaltemai_set_ix (setup_user_id)
, KEY mdl_blocquicaltemai_use_ix (user_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='Alternate email addresses that a user or course may send fro'
;
CREATE TABLE mdl_block_quickmail_notifs (
id BIGINT(10) NOT NULL auto_increment,
name VARCHAR(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
type VARCHAR(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
course_id BIGINT(10) NOT NULL,
user_id BIGINT(10) NOT NULL,
is_enabled TINYINT(1) NOT NULL DEFAULT 0,
conditions LONGTEXT COLLATE utf8mb4_unicode_ci,
message_type VARCHAR(8) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
alternate_email_id BIGINT(10) NOT NULL DEFAULT 0,
subject LONGTEXT COLLATE utf8mb4_unicode_ci NOT NULL,
signature_id BIGINT(10) NOT NULL DEFAULT 0,
body LONGTEXT COLLATE utf8mb4_unicode_ci NOT NULL,
editor_format SMALLINT(3) NOT NULL DEFAULT 1,
send_receipt TINYINT(1) NOT NULL DEFAULT 0,
send_to_mentors TINYINT(1) NOT NULL DEFAULT 0,
no_reply TINYINT(1) NOT NULL DEFAULT 0,
usermodified BIGINT(10) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
timedeleted BIGINT(10),
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicnoti_cou_ix (course_id)
, KEY mdl_blocquicnoti_use_ix (user_id)
, KEY mdl_blocquicnoti_alt_ix (alternate_email_id)
, KEY mdl_blocquicnoti_sig_ix (signature_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='Parent notifications of sub class notification types.'
;
CREATE TABLE mdl_block_quickmail_event_notifs (
id BIGINT(10) NOT NULL auto_increment,
notification_id BIGINT(10) NOT NULL,
model VARCHAR(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
time_delay_amount BIGINT(10) NOT NULL DEFAULT 0,
time_delay_unit VARCHAR(10) COLLATE utf8mb4_unicode_ci,
mute_time_amount BIGINT(10) NOT NULL DEFAULT 0,
mute_time_unit VARCHAR(10) COLLATE utf8mb4_unicode_ci,
usermodified BIGINT(10) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
timedeleted BIGINT(10),
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicevennoti_cou_ix (course_id)
, KEY mdl_blocquicevennoti_use_ix (user_id)
, KEY mdl_blocquicevennoti_alt_ix (alternate_email_id)
, KEY mdl_blocquicevennoti_sig_ix (signature_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='Event based notification type instances extending a parent n'
;
CREATE TABLE mdl_block_quickmail_rem_notifs (
id BIGINT(10) NOT NULL auto_increment,
notification_id BIGINT(10) NOT NULL,
model VARCHAR(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
object_id BIGINT(10) NOT NULL,
max_per_interval BIGINT(10) NOT NULL DEFAULT 0,
schedule_id BIGINT(10) NOT NULL,
last_run_at BIGINT(10),
next_run_at BIGINT(10),
is_running TINYINT(1) NOT NULL DEFAULT 0,
usermodified BIGINT(10) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
timedeleted BIGINT(10),
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicremnoti_sch_ix (schedule_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='Reminder based notification type instances extending a paren'
;
CREATE TABLE mdl_block_quickmail_schedules (
id BIGINT(10) NOT NULL auto_increment,
unit VARCHAR(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
amount BIGINT(10) NOT NULL,
begin_at BIGINT(10) NOT NULL,
end_at BIGINT(10),
usermodified BIGINT(10) NOT NULL,
timecreated BIGINT(10) NOT NULL,
timemodified BIGINT(10) NOT NULL,
timedeleted BIGINT(10),
CONSTRAINT PRIMARY KEY (id)
, KEY mdl_blocquicsche_sch_ix (schedule_id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='Schedule records for schedulable persistents.'
;
CREATE TABLE mdl_block_quickmail_event_recips (
id BIGINT(10) NOT NULL auto_increment,
event_notification_id BIGINT(10) NOT NULL,
user_id BIGINT(10) NOT NULL,
notified_at BIGINT(10) NOT NULL,
CONSTRAINT PRIMARY KEY (id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='The recipient users of a specific event notification'
Error code: ddlexecuteerror
Stack trace:

    line 485 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown
    line 1005 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
    line 77 of /lib/ddl/database_manager.php: call to mysqli_native_moodle_database->change_database_structure()
    line 425 of /lib/ddl/database_manager.php: call to database_manager->execute_sql_arr()
    line 370 of /lib/ddl/database_manager.php: call to database_manager->install_from_xmldb_structure()
    line 890 of /lib/upgradelib.php: call to database_manager->install_from_xmldb_file()
    line 434 of /lib/upgradelib.php: call to upgrade_plugins_blocks()
    line 1742 of /lib/upgradelib.php: call to upgrade_plugins()
    line 683 of /admin/index.php: call to upgrade_noncore()
heather-williams commented 5 years ago

I noticed in the code it looks like it might be related to tables being created with keys based on fields that don't exist in the tables?

<TABLE NAME="block_quickmail_event_notifs" COMMENT="Event based notification type instances extending a parent notification record.">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="notification_id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="The parent notification id for this event notification."/>
        <FIELD NAME="model" TYPE="char" LENGTH="30" NOTNULL="true" SEQUENCE="false" COMMENT="The key representing the event notification model."/>
        <FIELD NAME="time_delay_amount" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" COMMENT="Amount of time that this event notification will be delayed once trigge
red to send."/>
        <FIELD NAME="time_delay_unit" TYPE="char" LENGTH="10" NOTNULL="false" SEQUENCE="false" COMMENT="Unit of time that this event notification will be delayed once triggered to send."/
>
        <FIELD NAME="mute_time_amount" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" COMMENT="Amount of time that must elapse since last trigger before triggering aga
in."/>
        <FIELD NAME="mute_time_unit" TYPE="char" LENGTH="10" NOTNULL="false" SEQUENCE="false" COMMENT="Unit of time that must elapse since last trigger before triggering again."/>
        <FIELD NAME="usermodified" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="The user who created/modified the object. It is automatically set."/>
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="The timestamp at which the record was modified. It is automatically set."/>
        <FIELD NAME="timemodified" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="The timestamp at which the record was modified. It is automatically set, and defaults to
 0."/>
        <FIELD NAME="timedeleted" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" COMMENT="The timestamp at which the record was deleted. It is automatically set, and defaults to 
0."/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
        <KEY NAME="course_id" TYPE="foreign" FIELDS="course_id" REFTABLE="course" REFFIELDS="id"/>
        <KEY NAME="user_id" TYPE="foreign" FIELDS="user_id" REFTABLE="user" REFFIELDS="id"/>
        <KEY NAME="alternate_email_id" TYPE="foreign" FIELDS="alternate_email_id" REFTABLE="block_quickmail_alt_emails" REFFIELDS="id"/>
        <KEY NAME="signature_id" TYPE="foreign" FIELDS="signature_id" REFTABLE="block_quickmail_signatures" REFFIELDS="id"/>
      </KEYS>
    </TABLE>

Also here:

<TABLE NAME="block_quickmail_schedules" COMMENT="Schedule records for schedulable persistents.">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="unit" TYPE="char" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="The unit of time for which this scheduled is based."/>
        <FIELD NAME="amount" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="The amount of time of the unit of time for which this schedule is based."/>
        <FIELD NAME="begin_at" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="The timestamp for which this schedule will begin."/>
        <FIELD NAME="end_at" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" COMMENT="The timestamp for which this schedule will end."/>
        <FIELD NAME="usermodified" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="The user who created/modified the object. It is automatically set."/>
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="The timestamp at which the record was modified. It is automatically set."/>
        <FIELD NAME="timemodified" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="The timestamp at which the record was modified. It is automatically set, and defaults to
 0."/>
        <FIELD NAME="timedeleted" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" COMMENT="The timestamp at which the record was deleted. It is automatically set, and defaults to
0."/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
        <KEY NAME="schedule_id" TYPE="foreign" FIELDS="schedule_id" REFTABLE="block_quickmail_schedules" REFFIELDS="id"/>
      </KEYS>
    </TABLE>
rrusso commented 5 years ago

Resolved in master. This was a case of copy pasta from when Chad originally wrote the install xml and all our testing was upgrade related. We never actually did a clean install. Thanks for finding this silly mistake.