Zotlabs|Hubzilla Development

Königsberg, Russia, Mon, 24 Sep 2018 22:33:09 +0000 last edited: Thu, 27 Sep 2018 09:54:48 +0000

!Hubzilla Support Forum !Hubzilla Development

I found that #Hubzilla (and seems a lot of other #PHP software) have issue with #MySQL 8. In my case this is a latest release 8.0.12.
According documentation MySQL changed Preferred Authentication Plugin on new caching_sha2_password type. It causes authentication errors like

mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client

To avoid this replace authentication scheme for users on mysql_native_password by

ALTER USER 'user@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Until software will not be updated to work with the new password schema will be a good idea to leave the old driver on by adding default_authentication_plugin = mysql_native_password in [mysqld] section of MySQL configuration file.

show all 25 Comments

Mon, 24 Sep 2018 23:12:15 +0000

Hubzilla doesn't care about the SQL authentication method. It is opaque to the application and handled by the PDO mysql driver. So this is suggesting that the PDO drivers also need updating if you decide to upgrade to MySQL8.

Mon, 24 Sep 2018 23:22:00 +0000

I'm now on PHP 7.2.10 and I ran into this problem with both Hubzilla and phpMyAdmin for example. Probably, some additional settings are required on PHP side.

Tue, 25 Sep 2018 03:30:58 +0000

http://php.net/manual/en/ref.pdo-mysql.php

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release. In the meantime, the mysql_xdevapi extension does support it.

It appears that the version numbers provided by php.net weren't entirely accurate and a future release which supports this auth plugin is still in the future.

Tue, 25 Sep 2018 10:59:08 +0000

I hope it will arrive in PHP 7.3 only and for now we need to avoid of new MySQL 8 authentication schema with caching_sha2_password.

Tue, 25 Sep 2018 21:23:33 +0000 last edited: Tue, 25 Sep 2018 21:27:32 +0000

One more quite unpleasant thing with MySQL 8 found.
There appeared the "Keywords and Reserved Words" mechanism.
Now some part of Hubzilla SQL code where name of tables or/and field are matches with such reserved words produces an errors like

2018-09-25T20:45:58Z:LOG_ERR:1d0e049086:dba_driver.php:459:db_logger: dba_pdo: ERROR: SELECT id, hash, gname FROM groups WHERE deleted = 0 AND uid = 2 ORDER BY gname ASC
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups WHERE deleted = 0 AND uid = 2 ORDER BY gname ASC' at line 1
2

MySQL documentation recommend to use backticks ` to quote such words or do not use them as fields and/or tables names.
Because of Hubzilla supports PgSQL we can not use backticks in SQL code (or it can be removed before SQL will be directed to PgSQL). So we need to have a workaround for this issue.

Tue, 25 Sep 2018 22:28:56 +0000

MySQL 8 keywords and reserved words compatibility (#1273) · Issues · hubzilla / core

I found quite unpleasant thing with MySQL 8 compatibility. There appeared the "Keywords and Reserved Words" mechanism. https://dev.mysql.com/doc/refman/8.0/en/keywords.html Now some part of Hubzilla SQL code where name of tables or/and...

Tue, 25 Sep 2018 23:15:13 +0000

MySQL 8 compatibility (!1285) · Merge Requests · hubzilla / core

#1273

Tue, 25 Sep 2018 23:24:00 +0000

Sigh... They keep taking more "general computer" words and making them reserved so we can't use them in one type of code or another - even when the words we are using are the best descriptions of the concepts we are abstracting.

In any case we have a TQUOT constant which evaluates to the table quoting character for the type of database driver we're using. Please use this instead of backticks - which we've tried very hard to remove from the codebase in SQL statements.

An alternative is to rename the table itself; maybe to 'grp'. Then we would have cleaner, more generic SQL.

Tue, 25 Sep 2018 23:52:37 +0000 last edited: Tue, 25 Sep 2018 23:53:50 +0000

@Mike Macgirvin I made the merge request (see above). I don't think that this is best solution but at least it works.
Perhaps you are right and more straight way with table rename will be much better.

Wed, 26 Sep 2018 00:12:33 +0000

I can no longer merge cleanly between zap/osada and hubzilla; so what I'm doing on those other projects is rename to pgrp and pgrp_member.

Wed, 26 Sep 2018 09:27:53 +0000

I see. Anyway we need to have a solution for Hubzilla too before new 3.8 version will be released.

Wed, 26 Sep 2018 09:52:24 +0000

I'm suggesting that as a Hubzilla solution. I'd send a patch file but it wouldn't be of much value.

Mario Vavti

Wed, 26 Sep 2018 10:05:47 +0000

I'm suggesting that as a Hubzilla solution.

Renaming? Yes, id like to keep diffs as small as possible...

Wed, 26 Sep 2018 10:11:09 +0000

Renaming? Yes, id like to keep diffs as small as possible...

I don't mind. I'll close previous merge request.

Wed, 26 Sep 2018 10:51:57 +0000

(off-topic. - is there anything known as a likely cause of 2 of my 3 channel clones are only getting/seeing Max's comments and not @Mario Vavti or @Mike Macgirvin 's? I get other content from them (other threads and messages - eg. I got Mike's reply on the Reshares discussion) but not this thread. I was very confused about the conversation until I looked at the forum page itself. The 3rd clone seems to have everything. If the two out-of-sync clones shared a server I would understand, but one of them shares a VPS with the working one. The other is on a separate box. What's more.... the working one is running master and the unworking ones are on dev and 3.6.2. - so no real commonalities anywhere that I can find.)

Wed, 26 Sep 2018 10:57:27 +0000

@M. Dent Same thing here. I see everything on main channel @Max Kostikov but some part of comments are missed (e.g. from @Mario Vavti) in its clone at zotum.net hub.

Wed, 26 Sep 2018 11:01:45 +0000

Interesting.... I also notice that my notifications say I have 47 unseen items in the Support forum, but they don't "clear" (haven't looked in the database itself yet).

Wed, 26 Sep 2018 11:05:38 +0000 last edited: Wed, 26 Sep 2018 12:32:48 +0000

Ok... That cleared after scrolling down a couple of pages and getting more articles. Confused. Still can't see the other replies, but notifications cleared up.

Wed, 26 Sep 2018 19:12:51 +0000

BTW I followed the way recommended by @Mike Macgirvin with table rename from groups to grp on my experimental hub Zotum.
If I didn't missed something this is complete list of files where we SQL code need to be changed with new table name.

modified: Zotlabs/Lib/Group.php
 modified: Zotlabs/Lib/Libsync.php
 modified: Zotlabs/Module/Acl.php
 modified: Zotlabs/Module/Contactgroup.php
 modified: Zotlabs/Module/Group.php
 modified: Zotlabs/Module/Import.php
 modified: Zotlabs/Module/Lockview.php
 modified: Zotlabs/Module/Network.php
 modified: Zotlabs/Module/Settings/Channel.php
 modified: Zotlabs/Widget/Activity_filter.php
 modified: include/acl_selectors.php
 modified: include/api_zot.php
 modified: include/channel.php
 modified: include/group.php
 modified: include/items.php
 modified: include/security.php
 modified: include/text.php
 modified: include/zot.php

Thu, 27 Sep 2018 00:05:40 +0000

For comparison here's the checkin on zap...

https://framagit.org/macgirvin/zap/commit/2b846a9c0bf11a9219cf744c53cde7db3ae35d5b

Note I used the table name 'pgrp' and also changed group_member to 'pgrp_member'. Some of this will merge from a diff, but there is no include/zot.php in zap, and changing the member table name added two more files to the list: Module/Connections.php and include/xchan.php. Don't try and merge it via git as many of the files have changed dramatically and it will make a mess. I know this because I've accidentally made a mess a few times now.

Thu, 27 Sep 2018 08:39:30 +0000

Thank you!
I hope @Mario Vavti will adopt this changes into Hubzilla soon.

Thu, 27 Sep 2018 09:09:48 +0000

@so funny

How did wow@zotum.net disappear?

Zotum is in experimental stage now (as you can see from this thread) and no any guarantees can be provided. If you wish you can register there again. Sorry for inconveniences.

Thu, 27 Sep 2018 23:14:45 +0000 last edited: Thu, 27 Sep 2018 23:18:27 +0000

I prepared the merge request in Zap style.
Hope I did not missed something there.
https://framagit.org/hubzilla/core/merge_requests/1290

Fri, 28 Sep 2018 00:18:43 +0000

That looks good - and I found a couple of things in your diffs that I missed in mine. Thanks.

Mario Vavti

Fri, 28 Sep 2018 08:03:21 +0000

Thanks!

Hope I did not missed something there.

I took care of the changes in the addons repository...

Hubzilla and PHP opcache

Königsberg, Russia, Sun, 01 Jul 2018 20:10:50 +0000 last edited: Sun, 01 Jul 2018 20:25:41 +0000

!Hubzilla Development
I'm use Hubzilla with latest PHP 7.2 release under FreeBSD environment (H2O HTTP/2 web-server + PHP-FPM).
It works quite fast but I think it can be faster with PHP opcache.
Perhaps experienced Hubzilla hub owners have any recommendations on opcache settings?

131@diaspora.xpr.pl

Mon, 02 Jul 2018 06:17:22 +0000 from Diaspora

Czy ten wpis jest widoczny na Hubzilli?

!Hubzilla Development

Ta linijka dziwnie wygląda na Diasporze.

Thu, 12 Jul 2018 18:42:13 +0000 last edited: Thu, 12 Jul 2018 19:26:52 +0000

Well, let me publish my results on #PHP opcache usage with #Hubzilla.
As I wrote above I'm use PHP 7.2 in #FreeBSD 11.2 environment.
I installed php72-opcache package and changed only two parameters vs default configuration

root@beta:/usr/local/www/tiksi.net # cat /usr/local/etc/php/ext-10-opcache.ini
zend_extension=opcache.so
opcache.revalidate_freq=60
opcache.memory_consumption=64

After about a week of usage I might say that 64Mb memory buffer for opcache is more that enough not only for Hubzilla, but for other stuff that I have on same server (e.g. Roundcube, my own blog based on #Bludit flatfile #CMS and phpMyAdmin).
Memory consumption is remains less than 50Mb with cache hit rate about 99.96%.

Here is scripts usage visualisation.

And about performance. I didn't run special benchmarks but subjectively page load speed was dramatically increased. So I can recommend to use opcache for Hubzilla.