Missing table definition for MSSQL: mqtt_pub

[petertirrell]

Environment

• ejabberd version: 19.09.1
• Erlang version: erl +V
• OS: Linux (Debian)
• Installed from: official deb/rpm

Configuration (only if needed): grep -Ev '^$|^\s*#' ejabberd.yml

loglevel: 4
...

Errors from error.log/crash.log

No errors

Bug description

I am trying to set up ejabberd against an MSSQL backend and I ran into the same issue as bug #2843 regarding the missing mqtt_pub table. It looks like the table definition for that table has not been added to the mssql.sql scipt at https://github.com/processone/ejabberd/tree/master/sql .

I moved on locally by porting the MySQL script (sort of?) to MSSQL and adding the table to get past the error, but I'm not fully confident in my porting skills.

I could spend some time in making sure I have the type conversions correct and make a PR, but it looks like in the source there are two different MySQL table definitions for that table: mysql.new.sql and mysql.sql. Which is the correct schema for the mqtt_pub table that should be added to the MSSQL script?

Thanks,

[zinid]

I moved on locally by porting the MySQL script (sort of?) to MSSQL and adding the table to get past the error, but I'm not fully confident in my porting skills.

So we're. And currently we have no running MSSQL to check against. We also cannot test ejabberd against MSSQL in Travis, so things are quite complicated.

[badlop]

it looks like in the source there are two different MySQL table definitions for that table: mysql.new.sql and mysql.sql. Which is the correct schema for the mqtt_pub table that should be added to the MSSQL script?

Yes, those are the "default" and the "new" schemas, see https://docs.ejabberd.im/admin/configuration/database-ldap/#default-and-new-schemas

Looking at mssql.sql, it seems to be based on the default schema, that is mysql.sql.

Did you look at this, are you interested at all, or otherwise this issue can be closed?

[petertirrell]

Hi, and thanks for the reply! Honestly, once I got our install up and running I haven't really looked into this that much more. Feel free to close the issue. I still have my original solution - taking what I guess was the default schema based on mysql and using getting the missing mssql table set up based on that.

Thanks again,

[wahur666]

The missing tables still not part of the mssql, an update would be useful.

[badlop]

@wahur666 : If @petertirrell shares here his definition of the missing tables, and you can try them, and you confirm they work too for you... they could be included in ejabberd.

[petertirrell]

@wahur666 @badlop , here's what I have for the MSSQL schema for mqtt_pub. I believe that was the only table that was missing that I had to create. Creating it got rid of the original error I was running into (complaining that the table wasn't found), but we're not actually using any mqtt functionality for our ejabberd installation.

USE [ejabberd]
GO

/****** Object:  Table [dbo].[mqtt_pub]    Script Date: 5/12/2021 11:08:29 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[mqtt_pub](
	[username] [varchar](191) NOT NULL,
	[server_host] [varchar](191) NOT NULL,
	[resource] [varchar](191) NOT NULL,
	[topic] [varchar](191) NOT NULL,
	[qos] [tinyint] NOT NULL,
	[payload] [varbinary](max) NOT NULL,
	[payload_format] [tinyint] NOT NULL,
	[content_type] [text] NOT NULL,
	[response_topic] [text] NOT NULL,
	[correlation_data] [varbinary](max) NOT NULL,
	[user_properties] [varbinary](max) NOT NULL,
	[expiry] [int] NOT NULL,
 CONSTRAINT [i_mqtt_topic_server] PRIMARY KEY CLUSTERED 
(
	[topic] ASC,
	[server_host] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

[wahur666]

@badlop @petertirrell I can confirm, that the code works, thank you :) Is there a way to reinitialize the database, because switching from Mnesia, there is literally nothing in it.

[badlop]

You have two things to check:
A) The account you provide to login in webadmin... does it have admin rights in ejabberd.yml ?
B) https://docs.ejabberd.im/developer/ejabberd-api/admin-api/#export2sql

[wahur666]

@badlop It was the first one, thank you for the help :)

[badlop]

Thanks! I've committed them to ejabberd