ejabberd crashes upon enabling MQTT with mqtt_pub doesn't exist

[pixel1138]

After recently upgrading to ejabberd 19.02, any attempt to enable MQTT crashes ejabberd with a database error that table ejabberd.mqtt_pub doesn't exist during startup. I double checked the upgrade notes, and I did not see anything relating to MQTT and MySQL. Turning off MQTT allows ejabberd to function normally.

ejabberd was upgraded using the .deb package on Ubuntu 18.04.
ejabberd is using MySQL 5.7.25

Below is the output from running ejabberdctl foreground:

17:31:25.453 [notice] Changed loghwm of /opt/ejabberd/logs/ejabberd.log to 100
17:31:25.453 [notice] Changed loghwm of /opt/ejabberd/logs/error.log to 100
17:31:25.705 [info] Loading configuration from /opt/ejabberd/conf/ejabberd.yml
17:31:29.154 [info] Loading modules for localhost and example.com
17:31:29.591 [info] Building MQTT cache for localhost, this may take a while
=CRASH REPORT==== 27-Mar-2019::17:31:29.593460 ===
  crasher:
    initial call: mod_mqtt:init/1
    pid: <0.641.0>
    registered_name: []
    exception exit: db_failure
      in function  gen_server:init_it/6 (gen_server.erl, line 358)
    ancestors: [ejabberd_gen_mod_sup,ejabberd_sup,<0.106.0>]
    message_queue_len: 0
    messages: []
    links: [<0.601.0>]
    dictionary: []
    trap_exit: false
    status: running
    heap_size: 987
    stack_size: 27
    reductions: 1138
  neighbours:

17:31:29.593 [error] SQL query 'select topic from mqtt_pub where server_host='localhost'' failed: "#42S02Table 'ejabberd.mqtt_pub' doesn't exist"
17:31:29.593 [error] SQL query 'Q69987287' at {mod_mqtt_sql,113} failed: "#42S02Table 'ejabberd.mqtt_pub' doesn't exist"
17:31:29.594 [error] CRASH REPORT Process <0.641.0> with 0 neighbours exited with reason: db_failure in gen_server:init_it/6 line 358
17:31:29.596 [critical] Failed to start module mod_mqtt: 
{error,{db_failure,{child,undefined,mod_mqtt_localhost,
                          {gen_server,start_link,
                                      [{local,mod_mqtt_localhost},
                                       mod_mqtt,
                                       [<<"localhost">>,
                                        [{match_retained_limit,1000},
                                         {max_topic_depth,8},
                                         {max_topic_aliases,100},
                                         {session_expiry,300},
                                         {max_queue,5000},
                                         {access_subscribe,[]},
                                         {access_publish,[]},
                                         {db_type,sql},
                                         {ram_db_type,mnesia},
                                         {queue_type,ram},
                                         {use_cache,true},
                                         {cache_size,1000},
                                         {cache_missed,true},
                                         {cache_life_time,3600}]],
                                       []]},
                          transient,60000,worker,
                          [mod_mqtt]}}}
17:31:29.596 [critical] ejabberd initialization was aborted because a module start failed.
=INFO REPORT==== 27-Mar-2019::17:31:31.702268 ===
    application: lager
    exited: stopped
    type: temporary

[zinid]

Yeah, I forgot to put the table schema in the release. It's fixed already, you can grab the table definition in https://github.com/processone/ejabberd/tree/master/sql

[pixel1138]

Thank you for the information! I'll run that table definition next time I'm on site.

[petertirrell]

It doesn't look like the new table definition has been added for the mssql script...