Installation option: create database

Bloke · 2021-04-19 23:27:29

In the upcoming 4.8.6 there’s a potential change, which we can back out if it proves problematic (though it seems to work under my testing). Until now, you have been forced to do the following:

Establish a known-good database via some MySQL client.
Run Textpattern setup.

As of right now in 4.8.6-dev, you can skip step 1 if you wish. During setup when it asks for your database credentials there’s a new option labelled Create database? with a yes/no option. Choose ‘no’ (the current default) if you’ve already established a database, although it’ll do no harm to choose the ‘yes’ option because it checks to see if the database exists first and leaves it alone if so. If, however, you type in a database name that doesn’t exist and select ‘yes’ then the database is immediately created in the next step where it shows you the config.php file.

I say ‘immediately’ but there are a few checks that run first before the database is created:

Database connectivity test (as always).
A new privileges check.

That second item is something we should have done years ago to avoid SQL errors when the user account assigned to the database doesn’t have sufficient privs granted to it. If you try to install now with a user that has some missing grants, installation will be halted before partial damage can occur due to a faulty DB user setup. It will also tell you which privs you need to add to meet the minimum spec.

Incidentally, this system extends to automated installers. If you wish to auto-create the given database, you can add ‘create’ to your JSON config file:

{
    "database": {
        "user": "somebody",
        "password": "something",
        "host": "localhost",
        "db_name": "txp486",
        "table_prefix": "",
        "create": "1"
    }, ...
}

And then kick it off as normal:

php /path/to/textpattern/setup/setup.php --config=setup-config.json

Would anyone who has time, please battle test this for both manual and automatic installation routes. Thank you.

If it’s broadly acceptable, we’ll need to backfill Crowdin with the extra setup strings (sorry, Phil, I forgot to exclude the strings when I committed). We can tweak the English strings first if anyone thinks they can be clearer before unleashing them on translators. Note also that there’s a string change to push to all langs: using_db now accepts {charset} as a second param, so there’s no need for us to append it manually in code any more. I’ll raise an issue on the tracker for that change, plus the four new strings (and one deletion) if we think this idea is going to stick around, but I think it offers a smoother installation experience so I hope it stays.

I did play around with an option for overwriting the database too (essentially: drop and recreate) but it proved rather clumsy. Since the drop/create has to be performed immediately, the action would be potentially devastating – it’s messy if it’s deferred until the final step because of the way txplib_db.php works.

The other reason I didn’t offer a splat option is because it’s unclear when using a table prefix whether ‘overwrite’ will just delete the tables that match your prefix or if it should drop the entire database. In the interests of safety, and not making things too complicated, I’ve elected to avoid the issue entirely. So if you want to delete a database or start afresh with an existing DB, you’ll need to do that outside of Textpattern, just as you do now.

I hope this feature is useful. It’s certainly handy for automated installs not to have to establish a separate DB first :)

phiw13 · 2021-04-20 06:21:16

If I understand correctly

the default process is still basically the same “traditional” set up process
a new additional way exist where you can create a DB right out of the set up process
for über-geeks, additional automation (which is way above my head)

I did the classic process earlier today and that worked flawlessly, as far as I can tell. Question, you write:

A new privileges check.

Does that check run always or only when creating a DB through the Textpattern set up process (that is, when going with process [2] or [3]) ? I think the answer is yes, but I am clear on that. And it certainly would be good to check up front if the user has all priveleges to play with Textpattern.

Hmm, if I have some time later this week, I might try procedure 2 on a server where I don’t have enough privileges to create a DB that way (have to go through the hosting admin panel).

Dragondz · 2021-04-20 07:33:30

Hi Stef,

Is creating the config.php file automatically is also a step that can ease further th installation.

My old proposal

Cheers.

Bloke · 2021-04-20 08:10:32

phiw13 wrote #329924:

the default process is still basically the same “traditional” set up process

a new additional way exist where you can create a DB right out of the set up process

for über-geeks, additional automation (which is way above my head)

Correct on all counts.

Does that check run always or only when creating a DB through the Textpattern set up process

Only during setup. It’s another pre-flight check before you get going. We state the required grant privileges but it doesn’t hurt to programmatically verify that – at least for now. Not sure about when we move to PDO as it may need refactoring: the MySQL docs say that the USER_PRIVILEGES table is non-standard so it depends if PDO shields us from that or not.

if I have some time later this week, I might try procedure 2 on a server where I don’t have enough privileges to create a DB that way (have to go through the hosting admin panel).

Thank you. Any tests you can perform would be ace. I’ve tried it on my live and MAMP setups by adding a user with reduced privileges and it worked fine… ish. There was an issue on MAMP, however, and I’m not sure if this is Mac specific but it’s to do with localhost and only seemed to rear its head during automated installation – the UI route was fine.

In the interests of documenting it for future, this is what I did:

Added a user account ‘bob’@‘localhost’ to MySQL via Sequel Pro and only set a few permissions.
Set my setup-config.json file to use localhost.
Ran setup and watched it fail to connect.

Turns out I had to scour around in MAMP and Sequel Pro to find that it was actually using port 8889 instead of the standard 3306. And it was using 127.0.0.1 instead of localhost. But when I changed that in my JSON file (below), it connected fine but then didn’t verify bob’s permissions.

    "database": {
        "user": "bob",
        "password": "bobbob",
        "host": "127.0.0.1:8889",
        "db_name": "auto486",
        "table_prefix": "",
        "create": "1"
    },

The reason is subtle. At the moment – and I might change this – if it fails to get any permissions it silently skips the check. This is pretty much falling back on what we have in 4.8.5 – no check. But if you’re expecting it to fail (as I was because I’d deliberately set bob to have very few permissions) this can be a head-scratcher.

What it was doing was going off to the database and asking for the permissions for ‘bob’@‘127.0.0.1’ and not finding any. It then skipped the check but (for reasons I can’t fathom) then built the database tables just fine using bob’s account.

I had to go into Sequel Pro’s User management area, twisty down bob and add a second account ‘bob’@‘127.0.0.1’, and set that account to have fewer permissions. Then it correctly threw the permissions violation during setup. But when I went in and changed the account to give enough permissions, it then failed to install because it fell back on bob localhost to create the tables. I verified that my /etc/hosts had the required localhost=>127.0.01 mapping in it.

At the moment I’m not entirely sure why the information schema check requires the user account to match the host in the config file, but after that, it seems to fall back on localhost. I would expect if the config file says that the host is 127.0.0.1, it should use that all the time. Investigation will continue as and when time permits (and/or reports come in that might flick a lightbulb on). But as it only seems to affect automated installs, it’s not 100% vital to track this down right now (though it would be nice).

Bloke · 2021-04-20 08:24:01

Dragondz wrote #329927:

Is creating the config.php file automatically is also a step that can ease further th installation.

We’ve flip-flopped over this many times. And every time we (or I) think it’s a good idea, when it comes down to implementation there’s simply too much at stake to do it programmatically. At the moment, for stable releases, we automatically delete /textpattern/setup for you, and warn in Diagnostics if it’s still there. But if you’re running dev code, this doesn’t happen.

As it stands, once config.php is in place you cannot overwrite it via /setup. It always fails to get past the 2nd step. So in theory we’re protected in case setup is left lying around. It’s always a good idea to remove it, but the risks are limited – you can’t even run setup/setup.php to hack the automated installer from the browser because it checks if the environment is the command line first and bombs out if not. Not sure about from cURL() – we’d have to check that.

So, I dunno. In theory we could offer it as an option. Part of me thinks it would be nice. Another part thinks it’s a security nightmare waiting to happen.

Dragondz · 2021-04-20 09:53:52

Bloke wrote #329930:

So, I dunno. In theory we could offer it as an option. Part of me thinks it would be nice. Another part thinks it’s a security nightmare waiting to happen.

Yes should think more about that.

phiw13 · 2021-04-20 12:23:02

Bloke thanks for your reply. With your story about testing a less-privileged user locally, I might even try myself. On the other hand a more ‘real world’ test on a live server might be more useful.

Textpattern CMS

Textpattern CMS support forum

#1 2021-04-19 23:27:29