Oscommerce MS2 Works on mysql5.0.19 but not on mysql5.0.81

[zxedxz]

When I test it on mysql5.0.19 everything works, but when i try it on mysql5.0.81 it appears the following error. It seems that it have something to do with the split_page_results (I have the fixed code for mysql5 already). And the limit # 24 that appears on the error is linked to the search display that is setup under the maximum value in admin.

 

I tried everything I could but still can't fix this. Anyone can help me?

 

Displaying 1 to 1 (of 1 products)  	  1

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 0, 24' at line 1

limit 0, 24

[TEP STOP]

[MrPhil]

That's an odd one. If it failed on all levels of MySQL, the first thing I'd say is that you screwed up some code while putting in the MySQL fixes. However, since it works on 5.0.19, that's probably not it. When you switch to 5.0.81, is anything else changing, such as PHP version? If not, the only other thing I can think of is there's something corrupted or otherwise wrong in the MySQL installation. If this is on your server, you might want to consider re-installing 5.0.81 from a fresh install package. Is 5.0.81 considered "production" level or "beta"/release candidate? Maybe there's a bug in it that should be reported.

 

Could there be anything in your code (different end-of-line bytes, perhaps) that is truncating your SQL statement, so that the only thing read in (on 5.0.81) is the LIMIT clause? In the source, is that on a separate line at the end? Did you overlook something in the configuration of MySQL, where it's not recognizing certain line breaks? If it's not the LIMIT clause, look to the preceding part of the query for something out of whack. If you can isolate the MySQL call that produces the error, echo out the call and see if there's anything strange in it -- any difference seen between the 5.0.19 and 5.0.81 versions (that may or may not be MySQL related). Feel free to post the two versions here (unless they are absolutely the same, including any invisible bytes) and I'll take a look at it.

[zxedxz]

I tried it using a local server. those that install everything into a folder. i installed two copies. one with the mysql5.0.19 and the other with 5.0.81. the php is the same. Then duplicated the osc folder into this two server. Somehow the 5.0.19 works completely fine. But the 5.0.81 show only those statements mentioned. Also if i point directly to the product_info page it shows this:

 

Product not found!
Continue 	

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY products_ordered desc LIMIT 0, 4' at line 1

SELECT SQL_CALC_FOUND_ROWS BY products_ordered desc LIMIT 0, 4

[TEP STOP]

 

This only happens under 5.0.81. What changed in this new version? PS I tried it with 5.0.53 version and also the same problem. Only 5.0.19 and under works.

[spooks]

Have u thought of simply upgrading to rc2a? Its not a good idea to be still using old versions.

 

 

Upgrading osC from 2.2 MS2 to 2.2 RC2a http://addons.oscommerce.com/info/6654

 

 

[Jan Zonjee]

Have u thought of simply upgrading to rc2a? Its not a good idea to be still using old versions.

Personally, i think this might come from some mod for the bestsellers box. Perhaps the code uses capitalization like FROM instead of from and that would confuse the split_page_results class.

 

I think we would be wasting our time looking for a problem in the standard osC code IMHO.

[MrPhil]

SELECT SQL_CALC_FOUND_ROWS BY products_ordered desc LIMIT 0, 4

Well, that's certainly a bogus SQL command! It makes no sense at all -- it's missing a bunch of stuff: field(s), FROM table name, and ORDER keyword at a minimum.

 

Are these two cases using the same directories and files on your server, and you're only switching between two MySQL instances? Or are you running two separate copies of osC? If the latter, and you swear that your installation code is identical on both servers, either something got corrupted while copying files from one to the other, or there's some subtle configuration difference in PHP. Is one a copy of the other, or were they customized/modified separately? I wouldn't expect MySQL to be able to digest that SQL command, so there's no point in looking for a MySQL bug.

 

I'll repeat my request that you find the offending SQL command in the PHP code, and print out the command in both cases. Are they exactly the same, and match the above? In that case, I have to confess that I have no idea why it works on one version and not the other (it doesn't look like correct SQL to me). Is it on a single line, or are there line-ends within the string? Maybe the two MySQLs are configured a bit differently as to how they handle line-ends.

[zxedxz]

Thank you all for the reply. To tell the true, i don't even know where to find this wrong code because i can't find out where is giving me the problem. 1)This is a heavily edited oscommerce.

2)I run two servers with same php, but different mysql. I duplicated the folders and imported the database on both.

3)Is there any contribution that I should look up that might cause this problem?

[zxedxz]

Okey, I tried to upgrade the php from the working mysql 5.0.19 and now runs on php 5.16 and still works. So it is not something related to php, but mysql itself. What has changed from mysql higher than 5.0.40? Is there any contributions that doesn't work with this new version of mysql? Please note that my oscommerce was created around at the begining of 2007 and haven't upgraded since then.

 

Thank you all for your help.

[Jan Zonjee]

3)Is there any contribution that I should look up that might cause this problem?

My suggestion would be to check includes/boxes/best_sellers.php and see if SELECT SQL_CALC_FOUND_ROWS is in there. Or just see if it is a standard osC file or not.

[zxedxz]

nope. Best_sellers.php is fine. I have some controbutions that affect best_sellers.php, but i tried disabling the best_seller box, but still have the problem

[Jan Zonjee]

nope. Best_sellers.php is fine.

As germ expressed here we are no wizards. We can't see your files in some crystal ball and tell you where the problem is.

Most text editors will allow searching for a piece of text in files in a directory so that is what I would do. SQL_CALC_FOUND_ROWS is not in standard osC as far as I know.

[zxedxz]

I did a search, but can't find that code. Any other suggestions?

[Jan Zonjee]

I did a search, but can't find that code. Any other suggestions?

You have the files. We don't. Research and search. Search for files with products_ordered. Research the error. Do you only get it when you are logged-in or only when you are logged-out or both. Which pages, etcetera.

 

Don't expect magic from us.

[MrPhil]

According to the MySQL web site (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html), SQL_CALC_FOUND_ROWS is a legitimate pseudo-fieldname, which when you use a LIMIT, tells you how many rows would have been returned had you not used LIMIT. It requires a second call,

SELECT FOUND_ROWS();

to actually get the value. It is useful for "returned results x of y" type output.

 

Given that SQL_CALC_FOUND_ROWS doesn't seem to be found in standard osCommerce, it must be added by some contribution or custom coding on your site. Furthermore, the query

SELECT SQL_CALC_FOUND_ROWS BY products_ordered desc LIMIT 0, 4

is, as I said before, missing a bunch of stuff. I think you need a "real" field list, such as *, although I won't swear to it. You definitely need a table name (and FROM), and you appear to be doing a sort (order), which would be ORDER BY, not just BY.

 

If none of your files include SQL_CALC_FOUND_ROWS,

1) you didn't accidentally look for SQL_CALC_ROWS_FOUND instead, did you?

2) you looked at "require" and "include" statements to see if some file is being pulled in from outside the directories you were looking in? Including other domains?

3) you tried looking for FOUND_ROWS()? It should be a query right after the offending one.

 

Once you find the query where this appears, you can check if the PHP file is corrupted in one version, and if not, do a run-time echo of the queries to compare the two. Something has to be different! Is 5.0.81 considered a production level release, or is it beta on the 5.0 branch? If it's considered beta, don't use it -- back off to an earlier "ready for prime time" level (and report the bug in this beta level).

[jhande]

This isn't much help, but I am running osC 2.2 MS2 on a server with MySQL 5.0.81, PHP 5.2.9, Apache 2.2.13.

 

I did however make all the edits/updates as they became available regarding MySQL & PHP 5.

 

 

 

 

 

[zxedxz]

I tried everything, and decided to give up on that and start over. As you said, something must be giving a problem for it to work perfectly on one mysql verison and not on the other.

 

I'm starting with a fresh copy of the oscommerce and it works on both database. So there is nothing with the server installation. I can't really compare it with a working oscommerce because this cart is so heavily modified that i can't remember what it have on it. You can't really tell if it oscommerce anymore by just looking at it. ;-)

 

Thank you all for your help. I appreciate this community. Thanks