Koozali.org: home of the SME Server

[SOLVED] MySQL57 and i-bay

Offline michelandre

  • *
  • 261
  • +0/-0
[SOLVED] MySQL57 and i-bay
« on: October 16, 2018, 12:21:48 AM »
Hi all,

Is there a way to force an i-bay to use MySQL57 by default, the same way as in Server Manager for PHP (ex: PHP71) etc...

I installed WordPress in Primary and used this variable in wp-config.php to define MySQL57 as default:

Code: [Select]
define('DB_HOST', 'localhost:/var/lib/mysql/mysql57.sock');
All is working well and WordPress's extension Duplicator is using PHP71 and MySQL57.

I am trying to install Wordfence extension and it uses the default MySQL from SME Server socket i.e. "/var/lib/mysql/mysql.sock" but it uses PHP71 as I defined it as default for the whole server in Server Manager.  I connot find the same kind of parameter as in WordPress for Wordfence.

I created a file in /etc/profile.d/ to define MySQL57 as the default for the use of MySQL but still, Wordfence does not use MySQL57.

It looks like it is "/etc/e-smith/templates/etc/httpd/conf/httpd.conf/90php_rhscl" that is forcing i-bays to use PHP71 or any other version.

How can I create a custom-template to do the same thing with MySQL57.

Any suggestion appreciated,

Michel-André
« Last Edit: October 17, 2018, 03:50:04 AM by michelandre »

Offline mmccarn

  • *
  • 2,626
  • +10/-0
Re: MySQL57 and i-bay
« Reply #1 on: October 16, 2018, 02:41:37 PM »
It looks to me like wordfence (installed as a plugin) uses the same database that wordpress is using.

I'm running wordpress at my server's primary URL (but actually installed in a 'wordpress' ibay) as described here:
https://forums.contribs.org/index.php/topic,53719.msg279608.html#msg279608
- SME 9.2
- PHP 7.1 (via SCL)
- Mysql 5.7 (via SCL and "define('DB_HOST', 'localhost:/var/lib/mysql/mysql57.sock');")

I installed wordfence as a plugin to see what would happen.  During setup I was prompted to enter my premium key (I don't have one, so I skipped this step).  Wordfence created all of its tables in the mysql57 database that WordPress is using, and there is an "upgrade to premium" button in the settings section of the plugin.

Code: [Select]
# mysql57 wordpress -e "show tables;"
+-----------------------+
| Tables_in_wordpress   |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
| wp_wfblockediplog     |
| wp_wfblocks7          |
| wp_wfconfig           |
| wp_wfcrawlers         |
| wp_wffilechanges      |
| wp_wffilemods         |
| wp_wfhits             |
| wp_wfhoover           |
| wp_wfissues           |
| wp_wfknownfilelist    |
| wp_wflivetraffichuman |
| wp_wflocs             |
| wp_wflogins           |
| wp_wfnotifications    |
| wp_wfpendingissues    |
| wp_wfreversecache     |
| wp_wfsnipcache        |
| wp_wfstatus           |
| wp_wftrafficrates     |
+-----------------------+

Offline michelandre

  • *
  • 261
  • +0/-0
Re: MySQL57 and i-bay
« Reply #2 on: October 16, 2018, 05:25:52 PM »
Hi mmccarn,

Thank you very much for your comment, I really appreciate it.

Very strange. This morning, I restarted my Virtual Machine and Wordfence uses myswl57.sock.

Wordfence  >  Tools  > Diagnostics  > at the bottom of the page, expand Other Tests  >  "Click to view your system's configuration in a new window"  >  in the middle of the page, under the title "mysql":
- "mysql.default_host" localhost:/var/lib/mysql/mysql57.sock
- "mysql.default_socket" /var/lib/mysql/mysql57.sock.

I started the previous snapshot of this VM and this one uses "/var/lib/mysql/mysql.sock". The only difference is that on the latest VM, I installed a Let's Encrypt certificate.

On both of the VM, I installed the Duplicator extension before installing Wordfence. On both of them, Duplicator > Tools, i see:
- PHP: Version 7.1.22 // MySQL: Version 5.7.16.

I will reinstall Wordfence on one VM that have only WordPress installed and let you know.

Again, thank you very much,

Michel-André

Offline mmccarn

  • *
  • 2,626
  • +10/-0
Re: MySQL57 and i-bay
« Reply #3 on: October 17, 2018, 12:44:26 AM »
I don't think the default values shown by phpinfo affect "correctly written web apps"**.

When I do what you describe (wordfence -> tools -> diagnostics -> view config in new window), I see two different and incorrect settings - one for mysql, and a different incorrect value (set by custom template fragment) for mysqli.  However, if I expand the 'MySQL' portion of the Diagnostics tab, it tells me I'm running v5.7.16.

(If for some reason you need the PHP default sql versions to display as 5.7 you may need to create custom template fragments .../opt/remi/php71/root/etc/php.ini/80ModuleSettings23MySQLi and .../opt/remi/php71/root/etc/php.ini/80ModuleSettings08MySQL)


Quote from: extract from wordfence phpinfo
mysql
MySQL Support   enabled
Active Persistent Links    0
Active Links    0
Client API version    mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $
Directive   Local Value   Master Value
mysql.allow_local_infile   On   On
mysql.allow_persistent   On   On
mysql.connect_timeout   60   60
mysql.default_host   no value   no value
mysql.default_password   no value   no value
mysql.default_port   no value   no value
mysql.default_socket   /var/lib/mysql/mysql.sock   /var/lib/mysql/mysql.sock
mysql.default_user   no value   no value
mysql.max_links   Unlimited   Unlimited
mysql.max_persistent   Unlimited   Unlimited
mysql.trace_mode   Off   Off
mysqli
MysqlI Support   enabled
Client API library version    mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $
Active Persistent Links    0
Inactive Persistent Links    0
Active Links    1
Directive   Local Value   Master Value
mysqli.allow_local_infile   On   On
mysqli.allow_persistent   On   On
mysqli.default_host   no value   no value
mysqli.default_port   no value   no value
mysqli.default_pw   no value   no value
mysqli.default_socket   /var/lib/mysql/mysql55.sock   /var/lib/mysql/mysql55.sock
mysqli.default_user   no value   no value
mysqli.max_links   Unlimited   Unlimited
mysqli.max_persistent   Unlimited   Unlimited
mysqli.reconnect   Off   Off
mysqli.rollback_on_cached_plink   Off   Off

Quote from: wordfence diagnostics mysql info
MySQL Database version and privileges.

    Database Version
    5.7.16
    Checking if MySQL user has DELETE privilege
    OK
    Checking if MySQL user has INSERT privilege
    OK
    Checking if MySQL user has UPDATE privilege
    OK
    Checking if MySQL user has SELECT privilege
    OK
    Checking if MySQL user has CREATE TABLE privilege
    OK
    Checking if MySQL user has ALTER TABLE privilege
    OK
    Checking if MySQL user has DROP privilege
    OK
    Checking if MySQL user has TRUNCATE privilege
    OK


** I say "correctly written web apps" because I ran into a web app a few weeks ago that accepted custom database settings during the install, then used TCP port 3306 at a later stage of the installation for the database compatibility check.

Offline michelandre

  • *
  • 261
  • +0/-0
Re: MySQL57 and i-bay
« Reply #4 on: October 17, 2018, 03:42:48 AM »
HHi mmccarn,

The funny thing I found was that Wordfence plays with the user:group property of wp-content/wflogs/config-livewaf.php, config-synced.php, config-transient.php and template.php. It wants to make them www:www. I changed all of them to admin:shared but after 1 minutes ot two, some of them return to www:www. That is not a problem as one is member of the other's group but why playing around like that?

Also, at the installation time, Wordfence makes the files in wp-content/wflogs/ r--r----- and some of them r--------- then it complains that it can not read them.

I gave the command you suggested: (mysql57 wordpress -e "show tables;") and for sure, Wordfence was using MySQL57 tables.

From that, I was sure then that it was the certificate interfering. All day, I did many re-installation of Wordfence and all kind of manipulations for the certificate to finally make the VM a sub-domain of the main SME Server; I added dev.toto.com as the first domain in the dehydrated domains.txt file because Let's Encrypt issues the certificate for the first entry in that file. I also added entries in wp-config.php file for relocation to dev.toto.com. I asked a new certificate and Wordfence was no more complaining about reconnecting to the server.  Wodfence was now sending titi@toto.com an email whenever I connect to WordPress because I have administrator rights; before it was not sending them.

But, the use of the mysql.sock was the same. I found out it was reported differently on two places.

I fired up my original VM and the mysql.sock was reported differently on the two places. So, it had nothing to do with the certificate.

Since I worked on it all day, I went to lunch and returned thinking that I will add a comment about my findings when I saw your message.

Thank you very much for your findings and all your precious time spent on my problem. I owe you quite a few beers or a few joints as it will be legal here tomorrow 8)

Michel-André

Offline ReetP

  • *
  • 3,722
  • +5/-0
Re: [SOLVED] MySQL57 and i-bay
« Reply #5 on: October 17, 2018, 08:18:57 PM »
Re the TCP Port issue I found something similar myself recently.

There's an obscure bug in PHP where if you use localhost rather than 127.0.0.1 it will ignore the chosen port and default to 3306....

https://github.com/tsolucio/corebos/issues/367

The only reference to it is a comment on one of the command in the PHP documentation.

Always a good read:
https://eev.ee/blog/2012/04/09/php-a-fractal-of-bad-design/
...
1. Read the Manual
2. Read the Wiki
3. Don't ask for support on Unsupported versions of software
4. I have a job, wife, and kids and do this in my spare time. If you want something fixed, please help.

Bugs are easier than you think: http://wiki.contribs.org/Bugzilla_Help

If you love SME and don't want to lose it, join in: http://wiki.contribs.org/Koozali_Foundation

Offline michelandre

  • *
  • 261
  • +0/-0
Re: [SOLVED] MySQL57 and i-bay
« Reply #6 on: October 17, 2018, 08:23:31 PM »
Hi ReetP,

Thank you for your comment.

There is a note on usage at: https://wiki.contribs.org/Software_Collections:MySQL57.

Quote
Possible host strings (localhost can be replaced with 127.0.0.1 if needed):

    localhost:/var/lib/mysql/mysql57.sock
    localhost;unix_socket=/var/lib/mysql/mysql57.sock
    localhost:3309

I tried all of them and it did not change the problem.
I think the best solution is what mmccarn suggested above.

Code: [Select]
mysql.default_socket =  /var/lib/mysql/mysql.sock
mysqli.default_socket =  /var/lib/mysql/mysql57.sock
I am not sure about the = or doubling the value string?

Michel-André
« Last Edit: October 17, 2018, 08:37:50 PM by michelandre »

Offline ReetP

  • *
  • 3,722
  • +5/-0
Re: [SOLVED] MySQL57 and i-bay
« Reply #7 on: October 17, 2018, 09:02:38 PM »
Yes it was just a note for reference.
...
1. Read the Manual
2. Read the Wiki
3. Don't ask for support on Unsupported versions of software
4. I have a job, wife, and kids and do this in my spare time. If you want something fixed, please help.

Bugs are easier than you think: http://wiki.contribs.org/Bugzilla_Help

If you love SME and don't want to lose it, join in: http://wiki.contribs.org/Koozali_Foundation