Forums  ›  General  ›  General discussions
 

Another change in upcoming hosting requirements.

 

I ran the command and it came up with 5.7.36

Then your all set there. You do not need to update your database server as it supports InnoDB.

Okay, I'll wait. I have a long way to go and I don't want to make it more difficult. No rush.

I ran the command and it came up with 5.7.36

Oh, i should mention. I think you only changed the collation default for the database. My guess is all of the tables in that database are still at their original collation.

I mention it because i just realized you said it only took a second.

All of the tables will need to be changed as well.

So if that is the case, just wait. I will be providing a conversion script to convert the entire database, all it's tables and making sure the columns that need to be binary are also set.

 

I guess the question is: When upgrading to the latest version of MYSQL, assuming it isn't already there, can I expect any issues? I just want to get my site up and running again.

I'm just a bit concerned about the comments in previous posts about CPU anfd other  issues when converting the database.

Thanks.

The issues i was pointing out when converting the database would occur if i was to upgrade using the cheetah update script. I have decided i will not be doing it that way. I will be providing other instructions on how to update it which will be much faster and less of a problem. Not everyone has Admirer or other tools to easily do that.

You have updated the collation, but unless you changed the engine to InnoDB it's not where it needs to be for the next version of cheetah yet.

Running this in phpMyAdmin should show you if your current database server supports InnoDB

SHOW VARIABLES LIKE "innodb_version"

Thanks for that Deano.  utf8mb4_unicode_ci was there but I missed it. 

Using Admirer, I changed the database to utf8mb4_unicode_ci and it only took a second. I then tested it on the site and all seems well. I haven't done any tests because it's a live site, but I'm hoping that this part of the equation has been solved. I guess the question is: When upgrading to the latest version of MYSQL, assuming it isn't already there, can I expect any issues? I just want to get my site up and running again.

I'm just a bit concerned about the comments in previous posts about CPU anfd other  issues when converting the database.

Thanks.

The difference in those is mostly how database searches are performed.

You do not want to use bin for a table collation. Cheetah uses bin in only two table columns that require bin. Bin is for binary storage and is used only when a table column requires it. And that would be specified only for the collation of the column that needs to be in binary.

Cheetah will be using utf8mb4_unicode_ci for all tables. The varchar and text table columns will default to what the table is set to use unless otherwise specified in the table column itself. Dolphin for example just goes with the database server default which is general_ci for most servers.

The difference between unicode_ci and general_ci is minor and has to do with how accurate search results are when dealing with different languages.

Key differences

  • utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.

  • utf8mb4_general_ci is a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations, such as when using particular languages or characters.

    On modern servers, this performance boost will be all but negligible. It was devised in a time when servers had a tiny fraction of the CPU performance of today's computers.

 

And if your doing this for cheetah, you don't really need to worry about it. I will be providing a way to convert the database when the time comes for the next update. All you need to worry about is if you have a mysql version that is compatible with the InnoDB storage engine. Most already do.

You can easily find out by running this query in phpMyAdmin.

SHOW VARIABLES LIKE "innodb_version"

If InnoDB is supported, it will show a InnoDB version in the results like mine returns this result.

Variable_name Value
innodb_version 10.3.31

 

I am starting a new project with Cheetah and now wondering how I should manage that new project.

I'm in the same position. Due to the garbage and clutter left behind from uninstalled third party modules, I bit the bullet and rebuilt my site profile by profile. I had just finished when I got this news and it knocked me for a six. I'm using Admirer and it has provision to convert an existing database from utf8 to utf8mb4, but there seem to be a couple of versions and I'm not sure which one to try:

utf8mb4_bin

utf8mb4_general

There doesn't seem to be one specifically for English.

Can I just press "Change" after selecting the appropriate (?) version? Will this do the heavy number crunching you've been discussing here, or am I being naive?

Regarding the version of MYSQL my server uses, I haven't checked because I'm planning to use a different panel. I'll update the MYSQL version then.

 

On a new install, you can change the settings before installing the module.

And on existing installs, the database table can be exported, then the exported file changed to the new engine and character set. Then the original table dropped and the changed version imported.

Either way, the modules can be changed.

Actually not really. Most third party modules have their own database settings which most likely will be utf8 for the character set and and MyISAM for the engine. However, they will still work.

But keep in mind that if the module uses the tinymce editor anywhere, the emotions may not work without changing the character set to utf8mb4 on the field that needs it.

 

Will third party modules be affected by these changes?

If a third party module sets the database engine and character set, the answer is yes and third party modules do.  On a new install, you can change the settings before installing the module.  

I have too many questions re this, but the one that concerns me most is: Will third party modules be affected by these changes?

 

Deano; when will these database changes be in a final release?  I am starting a new project with Cheetah and now wondering how I should manage that new project.

I am hoping for the the next release end of December or so.

Not very many people are using cheetah at this point, so i figure it is best to do these kinds of upgrades sooner rather than later.

 

The problem is going to be finding a reliable way to upgrade existing databases. Especially on shared hosting.

This reminds me of a project where I converted a Dolphin 6.x site to Dolphin 7.4 ; I rewrote the old Dolphin migration tool to work on most of the 7.4 config.  I hit a snag when it came to migrating photos because the site was running on shared hosting.  The client's hosting was limiting the time a script could run and I couldn't change it.  At the time I had limitations on renting a server so instead set up the LAMP stack on my local machine, downloaded the database and site content, set up a new Dolphin site, gave the migration script access to all the memory in the machine (which was limited at the time to three gigs of ram) and set the execution time so it had days to run if needed.  It worked; I got the migration done and uploaded the converted database and photos back to the server.

In the spirit of helping Cheetah to grow, I would be happy to help in converting a database free of charge if someone  needed help.  I have access to servers where script execution time and memory limitations won't be a factor.

Deano; when will these database changes be in a final release?  I am starting a new project with Cheetah and now wondering how I should manage that new project.

 

This will change the hosting minimum requirements for the MySQL server from version 4.1.2 to 5.5.3 or MariaDB Version 10.x

I want to add that some Linux distros default repositories will include MariaDB but that version is often one of the 5.x releases.  To get the latest MariaDB, you will need to add the MariaDB repo to your repo list.  This is made easy by the MariaDB folks.  Just visit https://mariadb.org/download/?t=repo-config and put in the information on your OS and server architecture and it will give you the text to insert in your repo file.

 

The problem is going to be finding a reliable way to upgrade existing databases. Especially on shared hosting.

Deano, before spending a large amount of time on solutions that will work on shared hosting, I would first consider how many people are using Cheetah on shared hosting?  You might not need to worry about the limitations of those on shared hosting.  I just convinced someone that is starting a new project with Cheetah to move off of the TMD shared hosting to a VPS (unfortunately also with TMD).  I would think that anyone using Cheetah would also be a member here; I would poll the members to see if you need to worry about shared hosting where they limit how long a script can run or how much memory a script can use.

Thank you for sharing.

Oh and i will also be changing from the MyISAM database engine to the InnoDB engine which has been the default engine used by mysql servers for quite some time now.

Setting up a new install with these database changes will not be a big problem. The problem is going to be finding a reliable way to upgrade existing databases. Especially on shared hosting. The sql commands to convert the database tables take time to execute and put a load on the server during conversion.

I have just posted a new issue to GitHub for Cheetah. https://github.com/CheetahWSB/Cheetah/issues/189

To solve issues with the new TinyMCE editor, Cheetahs database will be upgraded from utf8 to utf8mb4.

This will change the hosting minimum requirements for the MySQL server from version 4.1.2 to 5.5.3 or MariaDB Version 10.x

Older versions of MySQL will no longer work.

Version 4.1 of MySQL was released in 2004 and even version 5.5 in 2010. Both are more than 10 years old. No one should be running a version that old now anyway, so this upgrade should not be a issue for anyone.

As a side note: Wordpress made this upgrade to their database back in 2015.