Jump to content
Latest News: (loading..)
puggybelle

Change from MySQL 5.5 to 5.6 creates problems

Recommended Posts

Why would a change like that stop my code from running as before?

I have some code inserted into product_info.php and product_listing.php ( and two new functions in general.php) that creates keyword highlighting in my website.

All search returns result in the keyword being searched for highlighted in blue in both titles and descriptions.

The MySQL upgrade has killed it.  It's only highlighting now in titles, not descriptions, which makes the whole thing pointless.

Why would a MySQL change do that?

- Andrea

Share this post


Link to post
Share on other sites

There are no errors in the log file, other than the odd image missing here and there which is my fault.

Let me give you some more information.

I had my webhost move my site to a new server.  PayPal requires TLS 1.2 and I was on TLS 1.1, so I had them move my site for that purpose. 

That's when the trouble began.  Not just the code mentioned previously that has stopped working correctly, but issues in the Admin side, too.

Like...I can no longer create or edit an item without assigning it a manufacturer.  Never had to do that before.  If I don't assign one, I get this:

Quote

1366 - Incorrect integer value: '' for column 'manufacturers_id' at row 1

Then I have to manually type over the URL in my web browser to go back to mysite.com/admin and start again because if I hit the back button, the entire item will be erased from the database.  Like, poof - gone.  Weird things like that started happening after the server switch.  I'm assuming it's something to do with the MySQL change because of the error I just posted, but I don't know.  There are things on both the public side and the administrative side that are behaving differently and I don't know why.

Share this post


Link to post
Share on other sites

Just to give you an example of other 'weirdness' that is happening.

Session IDS are not disappearing after the first click or two.  They're even appearing in Order Process emails, like this:

Quote

Order Number: 7210
Detailed Invoice: https://www.mysite.com/account_history_info_order_id-7210.html?osCsid=dbfb997ef1d6f5f261a1bf80da0b50df
Date Ordered: Monday - May 28, 2018

Didn't have that problem before the server switch, either.

 

Share this post


Link to post
Share on other sites
Posted (edited)

mmm

i think this should fix the manufacturers id issue.........
backup your table (not that that would work, as your mysql version not allow empty values)..............

run this in phpmyadmin:
 

UPDATE products SET manufacturers_id= NULL WHERE manufacturers_id = ''

i assume that when you before assigned a manufacturer id to a product you wanted to edit.
and try to edit it again, it works.... right?

After your run that sql update........
you need to try to edit a product that has NO manufacturers_id assigned.

Edited by wHiTeHaT

Share this post


Link to post
Share on other sites

A lot of these issues sound like a PHP upgrade problem, not a MySQL upgrade. Are you sure you didn't end up on a different level of PHP during your server move? What osC release are you running? Older versions start to break like this at recent PHP levels, and will only get worse as your host upgrades.


If you are running the "official" osC 2.3.4 or 2.3.4.1 download, your installation is obsolete! Get the latest community-supported responsive "Edge" release

Share this post


Link to post
Share on other sites

The version of PHP has been at 5.2.17 the whole time....I think.

It's the MySQL version that changed.  I contacted the webhost yesterday and asked them for some history regarding server changes in the last 6 months and they say the only thing that changed was the version of MySQL when they put me on a new server.

Don't yell at me....I'm running 2.2 and have been since 2005.

I installed the newest, responsive version on another server last week using PHP 7.0 and Mariadb

And I'm having no fun whatsoever with any of it.

I'm just trying to keep what I have going until I can rebuild using the new version.

Share this post


Link to post
Share on other sites

Found a new file in my root folder, installed on May 22, 2018 and I didn't put it there.

It's called _pi_.php

Its only contents are:

<? phpinfo(); ?><? echo ''.date("Y-m-d H-i-s")."\n"; ?>
<? phpinfo(); ?>

What is this?

Share this post


Link to post
Share on other sites

Can you explain?  I changed some code to get the percentage symbol working again in CCGV (another of my problems that began after the server switch) but I don't understand what this new file is.  ???  I did not put it there.

Share this post


Link to post
Share on other sites

Well, go to your web site and do a http://youwebsite.com/_pi_.php u should be able to see the output. It tells your webserver php configuration. Someone put it there, check out the file properties like timestamp, user/group, and etc. It could be problematic if you did not put it there. I would just delete it or change the filename to a non .php extension or just change is execution privilege to nothing or not executable.

39 minutes ago, puggybelle said:

Can you explain?  I changed some code to get the percentage symbol working again in CCGV (another of my problems that began after the server switch) but I don't understand what this new file is.  ???  I did not put it there.

 

Share this post


Link to post
Share on other sites

Yeah, it's giving me a long screen full of PHP configuration info.  I've changed the permissions on it to 400 for the time being.  Assuming the webhost put it there?

I figured out why my keyword highlighting isn't working.  The & sign is being replaced with _ (underscore) and the = sign is being replaced with a dash (-) in the url that's generated when searching.

Those are the default settings in the Admin panel for SEO-G urls.  I changed them to & and = but no luck.

Is there any way to put something in that would override what the server is doing?  Like you did to fix my % problem in CCGV? I guess I'm in strict mode. 

 

Share this post


Link to post
Share on other sites

I couldn't tell you if you don't hv an idea on who put it there. I'd look at the timestamp and usr/grp and I hope it gives u a warm and fuzzy feeling that it must be the host.

I don't know squat about SEO-G urls, but if it is caching using MySQL, try caching with the file system if it is an avail option...probably same/similar fix as before in the SEO-G urls codes...

1 hour ago, puggybelle said:

Yeah, it's giving me a long screen full of PHP configuration info.  I've changed the permissions on it to 400 for the time being.  Assuming the webhost put it there?

I figured out why my keyword highlighting isn't working.  The & sign is being replaced with _ (underscore) and the = sign is being replaced with a dash (-) in the url that's generated when searching.

Those are the default settings in the Admin panel for SEO-G urls.  I changed them to & and = but no luck.

Is there any way to put something in that would override what the server is doing?  Like you did to fix my % problem in CCGV? I guess I'm in strict mode. 

 


 

Share this post


Link to post
Share on other sites

Alright!  I'm getting somewhere.

The default separators in the SEO-G Admin are _ and -

So my code in product_listing.php originally looked like this (and worked perfectly)

$parm_add = '&keywords='.stripslashes($HTTP_GET_VARS['keywords']).'&hl=1';

But, if I'm in strict mode (I guess)...the $ and = separators will not play nice, so I replaced the code with _ and -

$parm_add = '_keywords-'.stripslashes($HTTP_GET_VARS['keywords']).'_hl-1';

It's a thing of beauty!  Keyword highlighting in both titles and descriptions.

But, for one word only.  If someone types in a name surrounded by quotation marks...like "Bob Davis"...no keyword highlighting.  I remember having problems with that when I first installed this....the quotation marks....so I'm hopeful to get that going.

And the constant appearance of session ids in the URL seems to have vanished.  I hope!  Isn't this all so strange?

Now, if I could figure out how to get that highlighting code to work in 2.3.4.1 CE, I would be absolutely THRILLED TO PIECES!

So far, no luck.  The code in the new OSC is NOTHING like what's in my old shop.  Lots of work to do!

Thanks for your help! 

Share this post


Link to post
Share on other sites
17 hours ago, clustersolutions said:

I couldn't tell you if you don't hv an idea on who put it there. I'd look at the timestamp and usr/grp and I hope it gives u a warm and fuzzy feeling that it must be the host.

Better yet, talk to your host and see if it was them. I can't imagine why a host would insert such a thing, when they have all the information they need at hand, so I suspect that a hacker planted it (possibly malware through this old osC or some other vector). Anyway, keep an eye out for such files mysteriously appearing, and think seriously about upgrading to the current levels of PHP (7.1) and osC (2.3.4.1 BS Edge/Frozen). Anything earlier is a security hazard -- I'm surprised your host hasn't pressured you to upgrade! As it stands, you have severe security problems. Don't forget to sweep your PC(s) for viruses and malware (especially keystroke loggers and password sniffers), and after cleaning, change every password in sight!


If you are running the "official" osC 2.3.4 or 2.3.4.1 download, your installation is obsolete! Get the latest community-supported responsive "Edge" release

Share this post


Link to post
Share on other sites

Hi Phil:

I'm trying to set up a new version of my site using 2.3.4.1 on a server with PHP 7, but...in the meantime...my moneymaker is the old shop and I have to keep that running until I'm ready to throw a switch and run on Edge. 

My webhost has absolutely encouraged me to upgrade over the years!  The thought of having to do it all over again is sickening, really, but I totally understand the need to do so.  Honestly, the only thing that really kicked me into gear regarding Edge....is seeing all those 'your site is not mobile-friendly' messages in Google.  If that hadn't started, I really think I'd still be trying to hang on to what I have.  I got a nice thirteen years out of it!

Question:  Is EDGE compatible with MariaDB?  That's what I've got on the new server, along with PHP 7.0.  I just read a few other posts about Maria that have made me very nervous. 

Share this post


Link to post
Share on other sites

Per https://mariadb.com/resources/blog/how-migrate-mysql-mariadb-linux-five-steps , MariaDB is supposed to be a drop-in replacement for MySQL, but a number of people have reported problems trying to run osC on it. Probably, osC coders have taken certain liberties with SQL that MySQL has historically allowed, but MariaDB doesn't. I have absolutely no idea what changes will be needed for osC to run on MariaDB. If your host doesn't have MySQL, you may have no choice but to find another server (and possibly another host). Talk with your present host to see if they are willing to dedicate a server or two to MySQL for the time being.


If you are running the "official" osC 2.3.4 or 2.3.4.1 download, your installation is obsolete! Get the latest community-supported responsive "Edge" release

Share this post


Link to post
Share on other sites

Just to add, if anyone is interested.

My webhost did indeed confirm that my new server's MySQL is in strict mode.  Thus, all my headaches.

And they will not disable it.  Something about how I need to "optimize website queries".

The other server, where I intend to build anew, has the MariaDB and it's setting are:

[1]
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+

That means...no strict mode, yes? 

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

×