Trinitycore: 100% sql free core installation

[Aokromes]

Description:

Feature Request: 100% SQL free installation

on run worldserver:

If databases are present, check they contain all needed tables.

If they don't contain all needed tables warn about database(s) is used by some other project and don't allow to use then to install tc.

If databases are missing or they don't have data, show the following messages:

• "Databases missing, we need root user password or user with CREATE and GRANT permissions. Press enter for root user or write user name with CREATE and GRANT permissions."

• "We will create 3 (4) databases named auth_335, characters_335, world_335. If you want to use different names, write them separated by "," don't use spaces on database names. Press enter for default names."

  "Database names already exists and they don't contain data, you want to use them?"

  "Database name already exists and it have data, but they don't contain trinitycore tables, are you sure this is the database(s) you want to use?"

  "Databases auth_335, characters_335, world_335 created"

• "We will create one user to allow to use the databases, by default this user will be trinitycore. Press enter to accept or write desired username."
  "User name name already exist and it has access to the following databases: databases, are you sure this is the user you want to use?"
  "Username name created and assigned to the following databases: databases"

• "We will give (user name) one random password, it will be shown after this row. Press enter to accept or write your desired password."

and after that, update conf files.

Current behaviour: (Tell us what happens.) You need to run create_mysql.sql, then start core.

Expected behaviour: (Tell us what should happen instead.) You start core.

Branch(es): 3.3.5 / master (Tell us which branch(es) this issue affects.)
ALL
TC rev. hash/commit: 0659a85

TDB version: ALL

Operating system: ALL

[MrSmite]

I think this is a great idea but please allow it to be optional:

EnableDBAutoCreate
0 - False, user must create tables manually before running core
1 - True (default), core creates tables at first run

I prefer to do my DB stuff manually to keep my mind up to date.

[ghost]

To me, the issue post is somewhat confusing or misleading (not sure if that is the case for others). The only thing that hinders automatic creation of the databases in a completely new installation is the fact that the local MySQL server is not yet configured to

GRANT USAGE ON * . * TO 'trinity'@'localhost' IDENTIFIED BY 'trinity' [...]
+
GRANT ALL PRIVILEGES ON world/characters/auth/hotfixes . * TO 'trinity'@'localhost' [...]

With that fixed, the core is perfectly capable of creating the missing DBs as specified in the .conf files.

I can appreciate the suggestion to write back the names of custom DB names to the .conf files. Myself, I use 335_ as a DB prefix (instead of suffix) for the DBs in my 3.3.5 branch, grouping the DBs in my GUI editor.

[Aokromes]

@tkrokli the idea of this all is to fix reduce the problems for starters AND increase security.

With this system the db section of wiki will be totally deprecated, because the core will do all the needed job of create users and databases and add permissions to users.

We increase security, no longer "trinity" user and password on machines with phpmyadmin allowing hackers to break into databases.

We stop people reusing database names for different wow versions by mistake or bad knowledge and breaking core.

We stop the need of edit sql files / conf files to allow custom database names.

We also can stop the need of "create" conf files, the core can do the job of copy .conf.dist files and populate database names and password without human intervention.

[Rochet2]

At the moment its great though how you can have multiple config files, maybe in different folders of cores and just drop the DB to automatically recreate the tables. This would no longer be possible because you would need to drop the tables anyways and you are forced to re enter the table information correctly as its not automatically read from configs.

If the core asks for the table names and user passwords etc then it would be great if there was an option in config to use the current system for the table names and user name and passwords taken from config.
Or it could use the information from configs if its already there.

[Aokromes]

well, some additional questions can be added if we find users/databases names/database content already exists.

[Naios]

We could also change the core config into a more machine readable format like yaml or json and alter the config when doing some core actions so that commands may also affect the permanent config.

[Aokromes]

maybe credentials must go on standalone file.

[Naios]

Theoretically we could also provide the possibility to split config files in general

[MrSmite]

We increase security, no longer "trinity" user and password on machines with phpmyadmin allowing hackers to break into databases.

We stop people reusing database names for different wow versions by mistake or bad knowledge and breaking core.

We stop the need of edit sql files / conf files to allow custom database names.

We also can stop the need of "create" conf files, the core can do the job of copy .conf.dist files and populate database names and password without human intervention.

These all seem like user issues that don't necessarily need a solution but instead education:

• If you're dumb enough to use a default admin then you deserve to get your database hacked. The easiest way to prevent this is to leave the CONF entries __blank__ forcing users to enter something meaningful or the core won't start.
• We have to manually edit the various AHBot entries so what's hard about editing a few DB names while you're in there?
• CONF files are already copied by Visual Studio into the build directory. I don't see the point in having the core generate them. Besides, it isn't really "without human intervention" if you have to type answers into the console.

Personally I like the flexibility we have now which allows me to have multiple realms. I can manually set something like this up and have three realms running in less than a minute:

335_pve_chars
335_pve_world
335_pvp_chars
335_pvp_world
335_test_chars
335_test_world

335_pve.conf
335_pvp.conf
335_test.conf

Each of the DBs could have a different admin, allowing different users to be able to moderate different realms without having access to all of them. From the discussion above it seems as if this would be difficult with the suggested implementation.

[Aokromes]

you missed the point "If databases are missing, show the following messages:" :P

[MrSmite]

you missed the point "If databases are missing, show the following messages:" :P

No, I saw that. I was just saying (in many sentences with fancy github markdown) that it seems like trying to solve a problem that doesn't need solving. I haven't seen very many people say it's too hard to get started.

I'm not against it but I probably wouldn't use it so I hope it would be flexible enough to allow us to continue the current method.

[Aokromes]

if you create the databases (with the "create_mysql or hand create) and edit config, the system will skip this. And yes, it's a problem that needs to be solved because to much people is having problems.

[DDuarte]

Current behaviour: (Tell us what happens.) You need to run create_mysql.sql, then start core.

As long as you configure the host, username and password correctly in worldserver.conf, you do not need create_mysql.sql or to create databases manually, worldserver already does that for you.

[Aokromes]

if you setup the databases to use existing users/use root has credentials.

[Seyden]

it happens so often that a certain sql update is missing and i get angry that i can't test my stuff, because a field is missing. Thats when i would love the idea that the core just auto creates all databases.. in that case i don't have to care anymore about inserting all sqls

[Aokromes]

usually missing sql updates are fixed on same day.

[Naios]

What we could do is to ask whether the core should be started anyway in case of an update fails to apply.

[Aokromes]

No, better no, that can end on strange crashes.

[MrSmite]

With this system the db section of wiki will be totally deprecated

I still like the overall idea but just to clarify, you mean in the installation section only, right?

The wiki DB tables are still useful for people who want to add custom stuff. Also, there are times when the wiki has helped to make some of the internal code easier to understand.

Also also, there are some bug hunters that know SQL but not C++ and if they have no idea what the DB fields are supposed to mean, it's difficult to suggest fixes.

[Aokromes]

ofc installation only :)

[Aokromes]

added 1 thing more to make installation more idiot-prof safe:

If databases are present, check they contain all needed tables.

If they don't contain needed tables warn about database(s) is used by some other project and don't allow to use then to install tc.

[Rushor]

@MrSmite are you still here?

[MrSmite]

@MrSmite are you still here?

Yes, occasionally

[Rushor]

Nice man :)