4.7.3 - any changes required for MySQL 8.0 compatibility?

gaekwad · 2019-10-07 13:44:13

Is anyone running Textpattern 4.7.3 on MySQL 8.0 successfully? If so, were any changes required to get it working properly?

I’ve got an auto-installed Textpattern 4.7.3 on a Percona (MySQL) 8.0 + PHP 7.3 server. I can access the login page, and I know the credentials are correct, but I get a generic “Internal error” after I submit the form. There’s an HTTP 401 error thrown in the access log, but nothing beyond that.

I’m running 4.8.0-dev on the same server, same user and password, logs in with no issues.

Any advice very warmly received. Thank you!

Bloke · 2019-10-07 14:24:55

I haven’t tried it but suspect there may be issues. If you flick the production_status pref to debug in the database, do you get any more detailed info in the error message?

gaekwad · 2019-10-07 14:27:51

Bloke wrote #319570:

I haven’t tried it but suspect there may be issues. If you flick the production_status pref to debug in the database, do you get any more detailed info in the error message?

Actually, I didn’t try that – good thinking. I’ll report back.

Edit: thanks for the suggestion, Stef. The only change on the login page with debug status is the ‘Internal error’ appears twice now, rather than once. Nothing extra in the logs.

Last edited by gaekwad (2019-10-07 14:33:00)

etc · 2019-10-07 15:36:33

4.7.3 is still using PASSWORD() if other methods fail, so you’d have to generate MySQL hashes in PHP to avoid calling it. Hopefully, every txp user has logged in at least once since txp 4.4 (?) era, so the issue must be very marginal.

Bloke · 2019-10-07 16:11:32

Okay. My guess, as Oleg suggests, is that it’s hitting the PASSWORD() call and falling over.

It should check more recent style passwords first and only fall back on trying ones using PASSWORD() if that fails. So the only way it would be hitting that would be if the passwords themselves (on the demo server, for example) were generated in that manner by the batch script.

Altering the creation mechanism to generating them from PHP would solve it.

But as a general rule, yes, Oleg’s right, there shouldn’t be many people hitting this issue in the real world besides those that haven’t logged in since 4.3ish.

gaekwad · 2019-10-07 19:55:40

Thank you, gentlemen – very much appreciated.

gaekwad · 2019-10-10 14:35:55

Bloke wrote #319577:

Altering the creation mechanism to generating them from PHP would solve it.

But as a general rule, yes, Oleg’s right, there shouldn’t be many people hitting this issue in the real world besides those that haven’t logged in since 4.3ish.

Hi Stef – thanks for the code chunk you sent me. I think I’m almost there with the PHP method, but I’d appreciate some clarification if you’re able to shed some light.

The site in question — beta-demo.textpattern.co running 4.7.3-rc.1 — is a proving ground for 4.7.3-era code. I have a file in the root that I’m calling via command line:

<?php
define('txpinterface', 'css');

require __DIR__ . '/index.php';

$lines = file('/var/www/vhosts/textpattern.co/beta-demo/staging/users_1.txt');
$counter = 1;

foreach ($lines as $row) {
    $parts = do_list($row, '|');
    $name = $parts[0];
    $pass = \Txp::get('\Textpattern\Password\Hash')->hash($parts[1]);

    create_user($name, "{$name}@example.com", $pass, "Managing Editor #{$counter}", 2);
    $counter++;
}
?>

That loops through the named .txt file with username|password credentials…:

managing-editor1|managing-editor1
managing-editor2|managing-editor2
managing-editor3|managing-editor3
managing-editor4|managing-editor4
managing-editor5|managing-editor5
[…]

…and appears to generate the password hashes for said users. Here’s an excerpt from the database export after I run that .php script above:

INSERT INTO `txp_users` (`user_id`, `name`, `pass`, `RealName`, `email`, `privs`, `last_access`, `nonce`) VALUES
(7998,	'managing-editor998',	'$P$Bcgqgtzp2cOZiqlgmckPy3mfwLGQIS/',	'Managing Editor #998',	'managing-editor998@example.com',	2,	NULL,	'275af75df3de649436c9484804bd9cf5'),
(7999,	'managing-editor999',	'$P$B7Zp021G5cTdKbWpb17j.XkFl00IPg.',	'Managing Editor #999',	'managing-editor999@example.com',	2,	NULL,	'81dde1525016087c31df635ec6f7d4a7'),
(7997,	'managing-editor997',	'$P$B/xEX2Ys7Jl790rhIGOjCpr0HzHMyt/',	'Managing Editor #997',	'managing-editor997@example.com',	2,	NULL,	'98498c193afd2428ec89cb7c8b27a23c'),
(7996,	'managing-editor996',	'$P$B/M2pbesUXW9T3RrHelLG6DBEcb7yT/',	'Managing Editor #996',	'managing-editor996@example.com',	2,	NULL,	'7c925deb22353fec3d921345d4d09164'),
(7994,	'managing-editor994',	'$P$BXzQXko5RZwZY4eP9qwfXca29u/yZs1',	'Managing Editor #994',	'managing-editor994@example.com',	2,	NULL,	'3e828e8cd822037c0b5b70ab7742db44'),
(7995,	'managing-editor995',	'$P$BSHiXThVgdhS52JF2AS13pUxhYM6HZ.',	'Managing Editor #995',	'managing-editor995@example.com',	2,	NULL,	'a203e94f47b6d76b5890ece14c1f96dc'),
(7993,	'managing-editor993',	'$P$B1ItlIfAsSreMaWXC7dXEr/9N3e8wE0',	'Managing Editor #993',	'managing-editor993@example.com',	2,	NULL,	'cb4027a8f5da85cef1dbccf5b209f1ee'),
(7992,	'managing-editor992',	'$P$BINT.q6KBkgRd.WON4DXKVmvInYshz.',	'Managing Editor #992',	'managing-editor992@example.com',	2,	NULL,	'eb28c49c0618c1a18eeb63a6b85e87d8'),
[…]

When I try to log in with a username + password combo from the above, I get “Internal error”, and nothing of note in the access or error logs.

I get the feeling I’m 99% of the way there (thanks to your invaluable help), and I’m missing something. Any ideas or pointers, please? Thank you in advance.

Last edited by gaekwad (2019-10-10 15:57:21)

Bloke · 2019-10-10 15:51:56

D’oh! Sorry, the create_user() function takes the plain password and then hashes it for you internally, so there’s no need to hash it outside manually and pass it in.

Thus…

foreach ($lines as $row) {
    $parts = do_list($row, '|');
    $name = $parts[0];
    $pass = $parts[1];

    create_user($name, "{$name}@example.com", $pass, "Managing Editor #{$counter}", 2);
    $counter++;
}

EDIT: and since your passwords are the same as your logins, you don’t really need to put them twice in the file with the pipes. You can just use single values and use the value twice. Even better, you don’t even need to use a separate file. You could programmatically generate the names in a loop, just like Jukka did. Even if you want to use a different pass to the login, you can do that programmatically too.

gaekwad · 2019-10-10 16:06:52

Thanks, Stef – I was getting nowhere with Jukka’s code after some hours, but the penny just dropped — and now it works. Users are now being created correctly and cleanly.

The code was sound, but the driver was lost!

Bloke · 2019-10-10 16:36:40

Sweet!

gaekwad · 2019-10-10 17:44:01

OK, I’m 99.9% there: PHP user bludgeoning works perfectly with 4.7.3 RC1 (aka beta-demo), and 4.7.3 (aka release-demo), but the same build script seems to flip out when I use it with 4.8.0-dev (aka dev-demo). Should I be using a different route with 4.8.0 user creation?

There are two kinds of user account I’m generating: accounts for anyone to use (Managing Editors through to No Access, a thousand of each), and a single Publisher account that owns the default article, but nobody can use this unless they guess the password…

So, here’s how I make the Publisher account:

<?php
define('txpinterface', 'css');

require '/absolute/path/to/root/index.php';

create_user("textpattern-robot", "textpattern-robot@example.com", "verycomplexpasswordinplaintexthere", "Textpattern Demo Site Robot", 1);
?>

<?php
define('txpinterface', 'css');

require '/absolute/path/to/root/index.php';

for ($i = 1; $i <= 1000; $i++) {
    $name = 'managing-editor' . $i;
    create_user($name, "{$name}@example.com", $name, "Managing Editor #{$i}", 2);
}
?>

I create the Publisher first, then chew through the other users in ascending order: Managing Editors are done after the Publisher since most people use that account to log in, then the other types of account get done in turn.

The release and beta sites use the 4.7.3 codebase, and the rebuild process takes ~25 seconds for each one, pegging the CPU (as expected) in the process. All fine, and I can streamline this to make it faster soon.

However, the dev site script, which uses the same routine as the release and beta sites, inserts the Publisher…and then seems to stall. The CPU is pegged, and a few minutes later nothing has happened.

Has the create_user function changed sufficiently between 4.7.3 and 4.8.0-dev that I need to rewrite it? I’m using the same PHP CLI version to run the scripts, so I’m not sure what’s going on.

etc · 2019-10-10 18:29:50

gaekwad wrote #319622:

Has the create_user function changed sufficiently between 4.7.3 and 4.8.0-dev that I need to rewrite it? I’m using the same PHP CLI version to run the scripts, so I’m not sure what’s going on.

As suggested by @petecooper, 4.8 uses the native password_hash() function that should be available since PHP 5.5. Can you try whether echo password_hash('hello', PASSWORD_DEFAULT); is working?

Textpattern CMS

Textpattern CMS support forum

#1 2019-10-07 13:44:13