Jump to content
John W

MySQL 5.7 gotcha

Recommended Posts

A change from MySQL 5.6 to 5.7 is that fields must contain a default value at least if nothing is being inserted.  This happened to me when a table was modified for something added that later I removed and didn't remove the field.  I guess there's two lessons here.  Hope this helps someone ahead of time. 


I'm not really a dog.

Share this post


Link to post
Share on other sites

I presume that "autoincrement" fields would not need an explicit default.

In the non-default fields, the old way was to insert a NULL? And that's no longer automatically done? Presumably you could still give a NULL as the default?

Share this post


Link to post
Share on other sites

It has to do with some of the strict options in sql mode enabled by default.  Setting the server to sql-mode=""  solved it, but I'd rather fix the problem rather than work around.  In the two cases I ran into, it was an unused field anyway.  Both were part of old mods that I should have removed anyway.  I'm embarrassed because it errored on my live site.  I normally test things out pretty well.  


I'm not really a dog.

Share this post


Link to post
Share on other sites

Bad Doggy! Bad, Bad, Doggy!

So, what's the solution for ongoing changes needed to osC for someone going to MySQL 5.7 (as eventually we all will)? Would this be adding something like DEFAULT NULL (or whatever the correct syntax is) to any (non autoincrement) field without an existing default value? Or should we be giving a reasonable value as a default, especially if NULL is not explicitly handled in the PHP code (or NOT NULL given)? I would call this "fixing the problem" rather than a work-around. There are lots of fields without default values in Frozen, so there may well be some which get INSERTed with a missing default value. At least, all the changes would be confined to oscommerce.sql, and no PHP changes would be needed.

Share this post


Link to post
Share on other sites
Posted (edited)

I'm not positive on this, but it seems that it must have a default value and I think null is fine.  I only had an issue with a couple old fields and it's because of the stricter settings.  I upgrade Mysql so rarely that I don't remember all the issues.  But 5.7 has some default settings that can cause issues.  There's a warning on Cpanel WHm when upgrading to 5.7 it will likely cause problems.  Problem with Cpanel is you can't downgrade, or at least not easily.   This link is helpful https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sql-mode

This link is also helpful as there are some files like best sellers and order history that will error with this setting on.  https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

 

Edited by John W

I'm not really a dog.

Share this post


Link to post
Share on other sites

I need to expand on that.  The default was only a problem when the field wasn't being used.  In my case, the field just needed to be deleted.  I'll run my test site some more looking for problems, but I have too many things going on right now and not enough sleep.  It's hard being me sometimes.


I'm not really a dog.

Share this post


Link to post
Share on other sites
Posted (edited)

FWIW I ran into the same issue after an upgrade from MariaDB 10.1 to 10.3 (and unfortunately cPanel dowgrades are not supported).

Throwing a msyql error 1366 on checkout in checkout_process.php. In my case it's the insert(s) around line 241

Has to do with not being able to insert an empty string in a numeric field type

https://jira.mariadb.org/browse/MCOL-2059

 

Edited by jimlongo

Share this post


Link to post
Share on other sites

Did you solve your error?  Was it addon code or stock OSC code? 

Ultimately, Cpanel is moving to Maria and they didn't have MySQL 5.7 as an option until they were pressured into doing it.  I'll probably stick with 5.7 for a while.

I have Cpanel also, and the fact that downgrades are not supported has always worried me.   In my case, I tested MySQL 5.7 on my test server, but I didn't do it thoroughly enough. 


I'm not really a dog.

Share this post


Link to post
Share on other sites
Posted (edited)

The code that I needed to change only involved custom code as far as I can tell, and only inserts so it was fairly easy to find where it was happening.

The solution in my case was to not send an empty string as a numeric value.  I suppose in the past it had been type cast as 0. So when encountering this in the code simply set the empty string to 0. In my cases NULL was not acceptable, it must be numeric.

if ($val==""){$val=0;}

Otherwise the error would be a very ugly mysql error 1366 - incorrect integer value. 

I've been using the MariaDB replacement to MYSQL for a couple of years, and just updated from 10.1 to 10.3 on Friday. 

Edited by jimlongo

Share this post


Link to post
Share on other sites

I think that osC should be fixed to run with MySQL 5.7 at its default settings, as that's what most sites will end up running on. All fields should have a reasonable default of the right type (not NULL if you can help it). There may be some cases where PHP code has to be fixed to provide a correct type of data (e.g., a 0 instead of ""), but we'll probably find those only through trial and error (MySQL runtime errors).

Share this post


Link to post
Share on other sites

jimlongo, are you using the Aria db engine on MariaDB?  Is 10.3 the first time you've hit a problem with MariaDB? 


I'm not really a dog.

Share this post


Link to post
Share on other sites
Posted (edited)

John, I'm not aware what Aria db engine is.

I did an inplace installation of Maria10.1 over (probably) 5.5 once cPanel implemented it . . .  it was so long ago I forgot.  For those who don't know and are wondering, MariaDB is a drop in replacement for MYSQL. The reason behind it is to be open source instead of controlled by Oracle. There are pros and cons to each

Have never had any problems with it.  Everything is the same.

 

Edited by jimlongo

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×