MySQL 5.5 to 5.7

[fullfatdesigns]

Hi

I've received the message about needing to update mySQL 5.5

I have 20+ websites on our server with databases. Is updating mySQL like updating php where each account needs to be fully checked after updating, or should sites that worked in 5.5 still work in 5.7? Or are there certain things I should look out for?

Regards
Wayne

 

[GOT]

By and large this is pretty seamless. It's not as iffy as updating php.

However 5.7 will enable strict mode by default and some sites may have issues with that. Mostly related to custom coded stuff though. Wordpress for example would not have any issues.

You can disable strict mode easily by editing the my.cnf.

 

[fullfatdesigns]

Great, thank you.

I'll give it a try over the weekend. Don't fancy doing this at 4 on a Friday

 

[cPanelMichael]

Hello @fullfatdesigns,

Let us know if you encounter any issues.

Thanks!

 

[fullfatdesigns]

After doing quite a bit of checking of our sites, I can't be 100% sure the coding will be compatible as some is a few years old. I've searched around about disabling strict mode all with different ways to achieve it. Is there a preferred whm/cpanel method? Can this be done in whm? Or should I be using SSH?

 

[keat63]

Having just gone through updating from 5.6 to 5.7, I asked the same question and had the same concerns.
My update only took about 10 minutes at the most.

As for strict mode disable strict mode

I was also concerned about the implications of the update failing and being stuck between versions.

Backup all MySQL Databases?

 

[fullfatdesigns]

Thanks keat63

Thats good info and glad to know I'm not the only one worrying about updating services.

Regarding the my.cnf file. How do you connect to the file in /etc for the whole server? I can access the root of each cPanel account in ftp, but how to I access the one at the root of the server?

 

[keat63]

I ftp'd in to root, using the root login account, I copied the file off, edit it and ftp'd it back up.
As root admin, I assume you can FTP as the root user ?

When I ftp as the root user, my ftp client dumps me in the root folder.
Jump back one level where you can gain access to the etc folder.

CSF also offer a free explorer plugin, which will allow you to edit and copy files via WHM.
Its a bit clumsy, but can be useful at times.

 

[fullfatdesigns]

I'm viewing using the CSF file view, but I can't see my.cnf in /etc - Should it be in there? I wonder if I should do the SSH route to do this. I've looked on the main cPanel site, but I can't find any info on the steps to take...

 

[Infopro]

I ftp'd in to root, using to root login account,

That's a bad idea. If you're not comfortable with command line you might try CSE from ConfigServer.

 

[keat63]

@Infopro Can you explain why it's a bad idea please.

 

[keat63]

Are you looking at the user account /etc folder or the root account /etc folder.

 

[Infopro]

Using FTP as root user is not secure, at all:
FTP Server – Beware of Security Risks | SSH.COM

 

[fullfatdesigns]

I was looking at the root folder in whm. I've googled about editing via SSH, but I'm finding lots of different ways and I can't find any info on cPanel site (unless I'm looking in the wrong place?)

I've used SSH before, but find I need to follow step-by-step instructions to help

 

[fullfatdesigns]

Are you looking at the user account /etc folder or the root account /etc folder.

My mistake, found the file. I didn't see it in the top list (which was directories) and it was in the list below.

 

[fullfatdesigns]

OK, I've updated. Thanks everyone for helping.

Just to confirm, I've checked with the command;

mysql -e "SELECT @@sql_mode;"

and got this response;

@@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Would that suggest disabling Strict mode was successful?

 

[cPanelMichael]

Hello @fullfatdesigns,

Here's the entry you should add to disable Strict Mode under the [mysqld] section of the /etc/my.cnf file:

sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Remember to restart MySQL after making the change with the following command:

/scripts/restartsrv_mysql

The below command should not return any output once strict mode is disabled:

mysql -i -BN -e 'SELECT @@sql_mode' | grep -E 'ONLY_FULL_GROUP_BY|STRICT_TRANS_TABLES'

Thank you.

 

[fullfatdesigns]

Hi cPanelMichael

Sorry, I should have explained all the steps I made.

I added to my my.cnf file;

sql_mode=NO_ENGINE_SUBSTITUTION

Then did

mysql -e "SELECT @@sql_mode;"

To check if it had worked.

As I've done the top bit, do I also need to do the steps you mentioned in your last post?

 

[fullfatdesigns]

I've now re-booted mySQL and re-checked and get this response;

@@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION

Does that mean its worked?

 

[cPanelMichael]

Hello @fullfatdesigns,

You could change the existing sql_mode mode entry in your /etc/my.cnf with the one I noted in my last response:

sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Or, you could leave the original entry as well. Both entries will disable strict mode, it's just a preference of what other modes you want to enable/disable. The following MySQL document is worth reading if you want to know what each mode means:

MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes

Thank you.