Meshcentral: Dropped MySQL support? - MySQL is supported.

Created on 5 Mar 2021  ·  21Comments  ·  Source: Ylianst/MeshCentral

Does anyone know if support for MySQL has been dropped by MC2? I'm looking at the documentation found online (both install and user guides along with the all config options) and there's no mention of MySQL anywhere to be seen. Was there a decision to drop support in favor of NeDB and MongoDB alone?

This may be the reason why, in my dev environment, I've been following all previous notes and no tables are being generated when I start the service up ...

Fixed - Confirm & Close bug
Source
picture D4V3M0NK

All 21 comments

MySQL support is still there. The supported databases are NeDB, MongoDB, MariaDB and MySQL. I do most of my testing on MongoDB. Documentation was not updated in a while, that will need to be worked on.

For MariaDB and MySQL, this is the section to put in the settings section of the config.json:

    "MariaDB": {
      "host": "localhost",
      "user": "root",
      "password": "abc",
      "connectionLimit": 5
    }

    "MySQL": {
      "host": "localhost",
      "user": "root",
      "password": "abc",
      "port": 3307
    }

Let me know if that works.

Ylianst picture Ylianst on 5 Mar 2021

@Ylianst - thanks for the config confirmation, unfortunately I'm still not getting the schema populated.

When I run systemctl status meshcentral I get the following

$ sudo systemctl status meshcentral.service                                         
● meshcentral.service - MeshCentral Server
   Loaded: loaded (/etc/systemd/system/meshcentral.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Fri 2021-03-05 15:57:51 UTC; 9min ago
  Process: 12337 ExecStart=/usr/bin/node /INSTALL_FOLDER/meshcentral/node_modules/meshcentral (code=killed, signal=TERM)
 Main PID: 12337 (code=killed, signal=TERM)

Mar 05 15:57:48 ubuntu node[12337]:     at ChildProcess.emit (events.js:214:7)
Mar 05 15:57:48 ubuntu node[12337]:     at maybeClose (internal/child_process.js:925:16)
Mar 05 15:57:48 ubuntu node[12337]:     at Process.ChildProcess._handle.onexit (internal/child_process.js:209:5)
Mar 05 15:57:48 ubuntu node[12337]:   killed: false,
Mar 05 15:57:48 ubuntu node[12337]:   code: 1,
Mar 05 15:57:48 ubuntu node[12337]:   signal: null,
Mar 05 15:57:48 ubuntu node[12337]:   cmd: '/usr/bin/node /INSTALL_FOLDER/meshcentral/node_modules/meshcentral --launch 12337' }
Mar 05 15:57:48 ubuntu node[12337]: ERROR: MeshCentral failed with critical error, check MeshErrors.txt. Restarting in 5 seconds...
Mar 05 15:57:51 ubuntu systemd[1]: Stopping MeshCentral Server...
Mar 05 15:57:51 ubuntu systemd[1]: Stopped MeshCentral Server.

:warning: (just a note here : MeshErrors.txt. Restarting in 5 seconds... should read mesherrors.txt. Restarting in 5 seconds...)

The error file is stating that tables aren't existing even though this is how I set up the database and user:

CREATE USER 'meshcentral'@'%' IDENTIFIED BY 'PASSWORD';
CREATE SCHEMA meshcentral DEFAULT CHARACTER SET utf8mb4;
GRANT ALL PRIVILEGES on meshcentral.* to 'meshcentral'@'%';


mesherror.txt


-------- 3/5/2021, 3:57:35 PM ---- 0.7.81 --------

/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^

Error: ER_NO_SUCH_TABLE: Table 'meshcentral.events' doesn't exist
    at Query.Sequence._packetToError (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/Connection.js:526:10)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    --------------------
    at Protocol._enqueue (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/Connection.js:198:25)
    at sqlDbBatchExec (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/db.js:912:152)
    at Object.obj.StoreEvent (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/db.js:976:17)
    at Object.CreateMeshCentralServer.obj.DispatchEvent (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/meshcentral.js:1933:20)
    at RandomBytes.ondone (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/meshcentral.js:1620:21)


-------- 3/5/2021, 3:57:41 PM ---- 0.7.81 --------

/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^

Error: ER_NO_SUCH_TABLE: Table 'meshcentral.events' doesn't exist
    at Query.Sequence._packetToError (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/Connection.js:526:10)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    --------------------
    at Protocol._enqueue (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/Connection.js:198:25)
    at sqlDbBatchExec (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/db.js:912:152)
    at Object.obj.StoreEvent (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/db.js:976:17)
    at Object.CreateMeshCentralServer.obj.DispatchEvent (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/meshcentral.js:1933:20)
    at RandomBytes.ondone (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/meshcentral.js:1620:21)


-------- 3/5/2021, 3:57:48 PM ---- 0.7.81 --------

/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^

Error: ER_NO_SUCH_TABLE: Table 'meshcentral.events' doesn't exist
    at Query.Sequence._packetToError (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/Connection.js:526:10)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    --------------------
    at Protocol._enqueue (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/INSTALL_FOLDER/meshcentral/node_modules/mysql/lib/Connection.js:198:25)
    at sqlDbBatchExec (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/db.js:912:152)
    at Object.obj.StoreEvent (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/db.js:976:17)
    at Object.CreateMeshCentralServer.obj.DispatchEvent (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/meshcentral.js:1933:20)
    at RandomBytes.ondone (/INSTALL_FOLDER/meshcentral/node_modules/meshcentral/meshcentral.js:1620:21)

My config is real simple at this point


config.json


{
  "$schema": "http://info.meshcentral.com/downloads/meshcentral-config-schema.json",
  "__comment1__": "This is a simple configuration file, all values and sections that start with underscore (_) are ignored. Edit a section and remove the _ in front of the name. Refer to t
he user's guide for details.",
  "__comment2__": "See node_modules/meshcentral/sample-config-advanced.json for a more advanced example.",
  "settings": {
    "MySQL": {
        "host": "localhost",
        "user": "meshcentral",
        "password": "PASSWORD",
        "port": 3306,
        "connectionLimit": 5
    },
    "_cert": "myserver.mydomain.com",
    "WANonly": true,
    "_LANonly": true,
    "_sessionKey": "MyReallySecretPassword1",
    "_port": 443,
    "_aliasPort": 443,
    "_redirPort": 80,
    "_redirAliasPort": 80
  },
  "domains": {
    "": {
      "_title": "MyServer",
      "_title2": "Servername",
      "_minify": true,
      "_newAccounts": true,
      "_userNameIsEmail": true
    }
  },
  "_letsencrypt": {
    "__comment__": "Requires NodeJS 8.x or better, Go to https://letsdebug.net/ first before trying Let's Encrypt.",
    "email": "[email protected]",
    "names": "myserver.mydomain.com",
    "production": false
  }
}

D4V3M0NK picture D4V3M0NK on 5 Mar 2021

@Ylianst : I'm guessing at this point I could create the events table, but I was pretty sure in the past that as long as the user and schema exists, and the user has correct permissions, then the tables would be built if they didn't exist?

(also realized that my config didn't have the port set, but that didn't make a difference)

D4V3M0NK picture D4V3M0NK on 5 Mar 2021

I will have to check on this next week. Is this a new issue as a result on an update? I don't think I changed anything with MySQL in agents, but if this is due to a recent update, I can look check the difference if you know what version works.

Tables should be auto-created if not present, so. I should be able to install MySQL, import my data and remove the events table and have the server will run fine.

Ylianst picture Ylianst on 6 Mar 2021

@Ylianst , to be honest I've been keeping up to date on deployed servers, but not the installation of MC2 itself.

If I can be of any assistance in looking into this, I'm more than happy to do so - this is becoming front and center for me at the moment.

D4V3M0NK picture D4V3M0NK on 9 Mar 2021

Ok, I will take a look at it. "Keeping up to date on deployed servers, but not the installation of MC2 itself." - Can you explain this? What MeshCentral version are you on now?

Ylianst picture Ylianst on 10 Mar 2021

I just installed the latest MySQL server 8.0.23 and I do notice the MeshCentral does not support the new authentication type. You must install MySQL with the legacy type:

image

You see the error if you run node node_modules\meshcentral --debug db. I will change it to make this more obvious.

Ylianst picture Ylianst on 10 Mar 2021

Ok. After I setup MySQL with legacy authentication everything seems to work well. Let me know if that was your issue.

Ylianst picture Ylianst on 10 Mar 2021

"Keeping up to date on deployed servers, but not the installation of MC2 itself." - Can you explain this? What MeshCentral version are you on now?

What I meant was @Ylianst , I've been keeping one deployment of MeshCentral up to date with all of the latest server changes. Obviously for that deployment, there's been no requirement to test the installation.

I'm at a point now where I'm looking at a completely separate deployment, which led me to review the installation scripts that I have and the finding that the MySQL tables weren't being created. As for the MySQL install itself, there's no legacy/non-legacy option for Linux, apologies I should've made that apparent in the initial posting. The version of MySQL I'm running is 5.7.33 (Ubuntu).

Also, the user that I created on the MySQL server does have mysql_native_password for the plugin field within the mysql.user table, just in case that has any bearing on the matter (and should, I believe, address the password issue that's shown in the Windows installer screen capture you've shared).

D4V3M0NK picture D4V3M0NK on 10 Mar 2021

Just a thought: is there a way of installing a specific version of MeshCentral? Something like npm install meshcentral --version=0.72 or something similiar? That way I can attempt to install a prior version and see if I run into the same issues.

D4V3M0NK picture D4V3M0NK on 10 Mar 2021

Ok, interesting update ... when I blow the existing schema away and then restart MeshCentral, the database (and all tables) is created ... more info when I have it

D4V3M0NK picture D4V3M0NK on 10 Mar 2021

You can install any version of MeshCentral like this:

npm install [email protected]

Looking at the code, indeed on SQL (MySQL and MariaDB), MeshCentral will only create all tables if it finds that the "meshcentral" database is missing. It will not create an individual table if it's missing. Apologies, for saying otherwise earlier. With MongoDB and NeDB, re-create missing collections... but for SQL, it's all created once.

If you are missing tables, take a look at the commands I use to create the database here. You can probably run the same commands as needed to fix any missing tables.

For authentication, that does not seem to be an issue for you. So, no worries.

Ylianst picture Ylianst on 10 Mar 2021

Also, now that I am running my development machine on MySQL, I am making fixes. So, that will be in MeshCentral v0.7.85. For example, the feature to view previous logins is not working right. That will be fixed.

Ylianst picture Ylianst on 10 Mar 2021

Ok, so if the database is already created, the tables aren't checked. That's what tripped me up. I believe it would be an advantage to alter the logic slightly, because there could well be the case that the database is being hosted on an existing MySQL server: in that case, you really don't want to create the meshcentral user with DBA permissions (in order to initially create the database): typically you'd create the database, create a user and then assign permissions for that user on that database alone.

(Hope this is making sense)

D4V3M0NK picture D4V3M0NK on 10 Mar 2021

So, your asking for something like:

node node_modules/meshcentral --createdb

That would only be used for creating the database?

I just noticed that I don't support DbRecordsEncryptKey correctly with MySQL. This probably does not impact you, but looking into that now.

Ylianst picture Ylianst on 10 Mar 2021

No, not really. I'm asking for a change in the logic as MeshCentral starts up.

At the moment, you have:

  1. Does the database exist?

    1. If yes, assume that all tables exist and continue to load

    2. If no, create the database and all tables

I think it should be:

  1. Does the database exist?

    1. If yes, do all the tables exist?



      1. If yes, continue to load


      2. If no, create the tables (or create the missing tables, but that may be a little too granular and potentially screws up any referential integrity)



    2. If no, create the database and all tables

This permits an installer to create the user, the schema and assign non-DBA permissions to the user and then install MeshCentral. Due to 1.i.b, the tables would be created (the schema already exists)

D4V3M0NK picture D4V3M0NK on 10 Mar 2021
👍1

Oh. Got it!

Ylianst picture Ylianst on 10 Mar 2021

and I would certainly thrown an exception should there be any issues in performing 1.i.b. (if some tables exist and others don't there's a much bigger issue happening worthy of a fatal error)

D4V3M0NK picture D4V3M0NK on 10 Mar 2021

I just added table and index auto-creation if they are missing. Since I get a bunch of warnings if they already exists, I can't easily throw an exception if there is more issues. This said, I do not want to spend to much time on this since your probably the only one using MySQL with MeshCentral :)

I will publish v0.7.85 now with the SQL fixes.

Ylianst picture Ylianst on 10 Mar 2021

Just published MeshCentral v0.7.85 with SQL fixes & improvements. Let me know if that works.

Ylianst picture Ylianst on 11 Mar 2021

Yes! :) Tables are created, even though I had manually created the schema and things are looking good @Ylianst!
This looks like the proverbial chicken dinner, sir. Thank you!!

D4V3M0NK picture D4V3M0NK on 11 Mar 2021
👍1
Was this page helpful?
0 / 5 - 0 ratings

Related issues

MailYouLater picture MailYouLater  ·  4Comments
guerby picture guerby  ·  3Comments
hellofaduck picture hellofaduck  ·  3Comments
veitw picture veitw  ·  4Comments
LPJon picture LPJon  ·  3Comments