MySQL 8 and IPS 4.3?

[KT Walrus]

Anyone tried running IPS 4.3 with MySQL 8 GA yet?

Any issues?

 

[H5K]

No, but I switched to MariaDB and it’s way better. 

[Rhett]

On 4/23/2018 at 6:26 PM, KT Walrus said:

Anyone tried running IPS 4.3 with MySQL 8 GA yet?

Any issues?

 

 

2 hours ago, HanMan5000 said:

No, but I switched to MariaDB and it’s way better. 

Stick with MariaDB, which has been the defacto standard for a while now and then focus on your community. ?   They pull the latest and greatest from mysql and many other enhancements which in turn make worrying about mysql thing of the past.  ?

[KT Walrus]

Oracle made MySQL 8.0 way simpler to run a HA cluster in production with InnoDB Cluster. Most of the settings can now be set dynamically and don't require a restart (changes can be persisted cross restarts). You can configure and setup/manage a cluster remotely without having to ssh to the server to edit/change a config file. This makes running MySQL in containers using Docker or Kubernetes even simpler. Replication is greatly improved and MySQL 8 now has pretty good support for Spatial data (lat/longs are fully supported). I've wanted to sort profiles by distance from the current user for a long time and MySQL 8 fully supports these queries with the GIS support. Also, JSON documents are no longer second class citizens (the actual post data in IPS4 could be stored as JSON documents, where likes, votes, and even comments could be included in a single JSON document for a topic for very efficient retrieval for display to the users). I'm working on an email app for IPS4 which will store mail messages into mailboxes as JSON documents. It should be way easier to maintain the JSON documents instead of structured SQL rows for posts/messages.

I'm really looking forward to upgrading to MySQL 8 for IPS4. Oracle really added a bunch of very useful features to MySQL (that is why they chose to release it as MySQL 8.0 when the previous version is MySQL 5.7).

[DSystem]

Mysql lost the turn.

MariaDB 10 is now the developer's favorite ?

[BomAle]

I am reading about incompatibility from mysql to mariadb

there is some special alert related on invisioncommunity suite before transition mysql to mariadb (lastest mysql 5.7 to mdb 10.2)?

[Rhett]

46 minutes ago, BomAle said:

I am reading about incompatibility from mysql to mariadb

there is some special alert related on invisioncommunity suite before transition mysql to mariadb (lastest mysql 5.7 to mdb 10.2)?

There are no issues, MariaDB is a direct replacement for Mysql, and actually the default on most operating systems. 

What alert and where are you referring to? 

[BomAle]

23 minutes ago, Rhett said:

What alert and where are you referring to? 

No any at this moment ?

The idea is to switch to mariadb also on the local installation with windows.

[Rhett]

7 minutes ago, BomAle said:

No any at this moment ?

The idea is to switch to mariadb also on the local installation with windows.

You had me until you mentioned windows. ?  I wouldn't touch it if it's working. 

 

[H5K]

On 4/28/2018 at 6:37 PM, BomAle said:

I am reading about incompatibility from mysql to mariadb

there is some special alert related on invisioncommunity suite before transition mysql to mariadb (lastest mysql 5.7 to mdb 10.2)?

I literally just uninstalled MySQL and installed MariaDB 10 with via WHM and my community worked flawlessly. Only took a few minutes. 

On 4/28/2018 at 3:43 PM, KT Walrus said:

Oracle made MySQL 8.0 way simpler to run a HA cluster in production with InnoDB Cluster. Most of the settings can now be set dynamically and don't require a restart (changes can be persisted cross restarts). You can configure and setup/manage a cluster remotely without having to ssh to the server to edit/change a config file. This makes running MySQL in containers using Docker or Kubernetes even simpler. Replication is greatly improved and MySQL 8 now has pretty good support for Spatial data (lat/longs are fully supported). I've wanted to sort profiles by distance from the current user for a long time and MySQL 8 fully supports these queries with the GIS support. Also, JSON documents are no longer second class citizens (the actual post data in IPS4 could be stored as JSON documents, where likes, votes, and even comments could be included in a single JSON document for a topic for very efficient retrieval for display to the users). I'm working on an email app for IPS4 which will store mail messages into mailboxes as JSON documents. It should be way easier to maintain the JSON documents instead of structured SQL rows for posts/messages.

I'm really looking forward to upgrading to MySQL 8 for IPS4. Oracle really added a bunch of very useful features to MySQL (that is why they chose to release it as MySQL 8.0 when the previous version is MySQL 5.7).

Do you guys think MySql 8 will be better than MariaDB?

[KT Walrus]

7 hours ago, HanMan5000 said:

Do you guys think MySql 8 will be better than MariaDB?

Since IPS4 only uses features that are present in MariaDB and MySQL, both will work just fine for most IPS4 installations. It only really matters if you have some custom apps installed on your site that takes advantage of the additional features of MySQL 8.0 or if you need to scale up or better maintain your database.

MySQL 8.0 advantages for me are:

• Built-in High Availability InnoDB cluster
• Better crash recovery (MySQL 8.0 using a transaction safe data dictionary) meaning more reliability
• Can be remotely managed without ssh access to the database server and changes can be persisted cross restarts
• Proper support for lat/longs (using new SRID 4326)
• NoSQL support for JSON documents

All my custom apps will assume these MySQL 8.0 features are available to IPS4. I especially am looking forward to using lat/long data much more and for storing unstructured data (JSON encoded) which still can be queried/updated using SQL. Also, MySQL 8.0 clusters are much easier to manage and can better be run using containers for the nodes in the clusters.

I just wish the IPS4 core and built-in apps would bundle R/W queries into proper transactions (with COMMIT/ROLLBACK semantics) so clusters can better maintain data integrity over time and the enhanced High Availability/reliability of MySQL 8.0 are not sabotaged by IPS4 lack of proper transactions in a few places (like posting new content).

[ASTRAPI]

All new stuff from Mysql 8 will be on MariaDB also so no big deal ?

[ASTRAPI]

Well a few more bad news for Mysql 8:

Quote

I did run the test both with MySQL 8.0 and MariaDB 10.3 with the InnoDB storage engine and by having identical values for all InnoDB variables, table-definition-cache and table-open-cache. I turned off performance schema for both databases. All test are run with a user with an empty password (to keep things comparable and because it's was too complex to generate a password in MySQL 8.0)

Results are in seconds to complete:

Operation         |MariaDB|MySQL-8|

-----------------------------------
ATIS              | 153.00| 228.00|
alter-table       |  92.00| 792.00|
big-tables        | 990.00|2079.00|
connect           | 186.00| 227.00|
create            | 575.00|4465.00|
insert            |4552.00|8458.00|
select            | 333.00| 412.00|
table-elimination |1900.00|3916.00|
wisconsin         | 272.00| 590.00|
-----------------------------------

This is of course just a first view of the performance of MySQL 8.0 in a single user environment. Some reflections about the results:

• Alter-table test is slower (as expected) in 8.0 as some of the alter tests benefits of the instant add column in MariaDB 10.3.
• connect test is also better for MariaDB as we put a lot of efforts to speed this up in MariaDB 10.2
• table-elimination shows an optimization in MariaDB for the Anchor table model, which MySQL doesn't have.
• CREATE and DROP TABLE is almost 8 times slower in MySQL 8.0 than in MariaDB 10.3. I assume this is the cost of 'atomic DDL'. This may also cause performance problems for any thread using the data dictionary when another thread is creating/dropping tables.
• When looking at the individual test results, MySQL 8.0 was slower in almost every test, in many significantly slower.
• The only test where MySQL was faster was "update_with_key_prefix". I checked this and noticed that there was a bug in the test and the columns was updated to it's original value (which should be instant with any storage engine). This is an old bug that MySQL has found and fixed and that we have not been aware of in the test or in MariaDB.
• While writing this, I noticed that MySQL 8.0 is now using utf8mb4 as the default character set instead of latin1. This may affect some of the benchmarks slightly (not much as most tests works with numbers and Oracle claims that utf8mb4 is only 20% slower than latin1), but needs to be verified.
• Oracle claims that MySQL 8.0 is much faster on multi user benchmarks. The above test indicates that they may have done this by sacrificing single user performance.
• We need to do more and many different benchmarks to better understand exactly what is going on. Stay tuned!

Short summary of my first run with MySQL 8.0:

• Using the new caching_sha2_password authentication as default for new installation is likely to cause a lot of problems for users. No old application will be able to use MySQL 8.0, installed with default options, without moving to MySQL's client libraries. While working on this blog I saw MySQL users complain on IRC that not even MySQL Workbench can authenticate with MySQL 8.0. This is the first time in MySQL's history where such an incompatible change has ever been done!
• Atomic DDL is a good thing (We plan to have this in MariaDB 10.4), but it should not have such a drastic impact on performance. I am also a bit skeptical of MySQL 8.0 having just one copy of the data dictionary as if this gets corrupted you will lose all your data. (Single point of failure)
• MySQL 8.0 has several new reserved words and hasremoved a lot of variables, which makes upgrades hard. Before upgrading to MySQL 8.0 one has to check all one's databases and applications to ensure that there are no conflicts.
• As my test above shows, if you have a single deprecated variable in your configuration files, the installation of MySQL will abort and can leave the database in inconsistent state. I did of course my tests by installing into an empty data dictionary, but one can assume that some of the problems may also happen when upgrading an old installation.

source: http://monty-says.blogspot.co.id/2018/04/congratulations-to-oracle-on-mysql-80.html

 

And another one:

Important issue with MySQL 8.0 on initial release that is overlooked is whether or not 3rd party backup tools support the MySQL 8.0 changes.

Oracle MySQL 8.0 has been declared GA but a critical piece is missing …
MySQL 8 is a fantastic release embedding the work of brilliant Oracle engineering.
I will not detail all the great features of MySQL 8 as there are a lot of great presentations around it. What’s New in MySQL 8.0? (Generally Available)

One of my main concern regarding MySQL 8 is that it comes without an online backup solution. You only have access to online backup through MySQL Enterprise Backup that you get with a MySQL Enterprise Edition subscription. This is 5000 USD per server per year.So if you have 20 servers for 10 years usage of online backup you will give 1 million $ to Oracle

Of course at some point there might be a third party online backup solution available for MySQL 8. But writing this piece of code is tricky as MySQL 8 has introduced major design changes : disk format, innoDB data dictionary … Another practical problem is that MySQL is designed, developed and debugged behind closed doors. The third party cannot expect much help from Oracle to develop and fix its backup solution. The third party online backup solution can break following any upstream change done by Oracle. And I imagine that bugs in this area can be quite tricky to detect and fix without access to Oracle engineering !

Imagine the online backup bug happens after a security fix you have to apply to be compliant. You have the dilemma : being compliant but loosing online backup capability or being uncompliant and having online backup. The third solution is of course to pay Oracle to have access to closed source MySQL Enterprise which includes MySQL Backup.

MariaDB comes with a solution by having a bundled online backup solution on which it has full control and that does not depend on MySQL upstream code. This is the benefit for MariaDB to be a true fork ?

source: http://serge.frezefond.com/2018/04/a-critical-piece-is-missing-for-oracle-mysql-8-ga/

[pequeno]

On 4/30/2018 at 8:39 AM, HanMan5000 said:

I literally just uninstalled MySQL and installed MariaDB 10 with via WHM and my community worked flawlessly. Only took a few minutes. 

I replaced MySQL with MariaDB 10 and IPS4.3 works fine.

[TDBF]

On 4/28/2018 at 5:03 PM, Rhett said:

 

Stick with MariaDB, which has been the defacto standard for a while now and then focus on your community. ?   They pull the latest and greatest from mysql and many other enhancements which in turn make worrying about mysql thing of the past.  ?

But they still don't make a decent cup of tea yet! ?

[AlexWright]

56 minutes ago, TDBF said:

But they still don't make a decent cup of tea yet! ?

So don't send over HTCPCP?

[LukasGr.]

I am a newbie database-wise. Does anyone of you have tips/guides how to upgrade either from MySQL 5.5 to 5.7 or higher or how to switch from MySQL to MariaDB?

Besides backups, anything super special to consider before upgrading/switching?

[ASTRAPI]

It depends from your setup...

If you use for example any panel like cpanel or if it is a setup without any panel e.t.c

You can find many tutorials on that in Google..

My advice is to not upgrade from 5.5 to 5.7 directly.

First backup your databases

Then check for any configuration values that you may use at your configuration file my.cnf that are not compatible with the new versions....

Set the forum as offline

Then upgrade to 5.6 and then to 5.7

[LaCollision]

Hi,

Thanks for all the info.

Does run IPS 4.x flawlessly with MySQL 8?

[b416]

I just upgraded my test site to MySQL 8 (4.4.1 + PHP 7.3 + redis), everything seems to be working Ok (faster?). 

Waiting for my users to go to sleep to migrate in production  🙂

 

 

 

 

[b416]

Done in production, the users are happy, it seems faster, especially the stream Unread Content that loads instantly now

 

[AlexJ]

On 3/18/2019 at 8:58 AM, b416 said:

especially the stream Unread Content that loads instantly now

Can you share your forum link?  

[b416]

4 hours ago, AlexJ said:

Can you share your forum link?  

https://parapsihopatologija.com/forums/

the language used is mostly serbian