♥John W Posted February 28, 2019 Share Posted February 28, 2019 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. Link to comment Share on other sites More sharing options...
MrPhil Posted February 28, 2019 Share Posted February 28, 2019 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? Link to comment Share on other sites More sharing options...
♥John W Posted March 1, 2019 Author Share Posted March 1, 2019 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. Link to comment Share on other sites More sharing options...
MrPhil Posted March 1, 2019 Share Posted March 1, 2019 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. Link to comment Share on other sites More sharing options...
♥John W Posted March 1, 2019 Author Share Posted March 1, 2019 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 I'm not really a dog. Link to comment Share on other sites More sharing options...
♥John W Posted March 1, 2019 Author Share Posted March 1, 2019 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. Link to comment Share on other sites More sharing options...
jimlongo Posted March 4, 2019 Share Posted March 4, 2019 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 Link to comment Share on other sites More sharing options...
♥John W Posted March 4, 2019 Author Share Posted March 4, 2019 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. Link to comment Share on other sites More sharing options...
jimlongo Posted March 4, 2019 Share Posted March 4, 2019 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. Link to comment Share on other sites More sharing options...
MrPhil Posted March 4, 2019 Share Posted March 4, 2019 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). Link to comment Share on other sites More sharing options...
♥John W Posted March 4, 2019 Author Share Posted March 4, 2019 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. Link to comment Share on other sites More sharing options...
jimlongo Posted March 5, 2019 Share Posted March 5, 2019 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. Link to comment Share on other sites More sharing options...
♥John W Posted March 5, 2019 Author Share Posted March 5, 2019 Jim, the Aria db engine is their next gen replacement for the MyISAM engine. https://mariadb.com/kb/en/library/aria-storage-engine/ I'm not really a dog. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.