Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

1064 Error - MySql5


kymar

Recommended Posts

OK - I've spent a full day investigating this problem, reading up on similar but non-identical MySql upgrade problems, relating to both 5 and earlier versions. I've tried numerous experiments, and nothing's worked yet.

 

Background: I was forced to migrate my site to a new server employing MySql5. Though I was prepared to handle the move myself, it turned out that most of the work was done by a technician working with my web host (even while a supervisor was telling me that it was virtually impossible!). I had to change a few variables in the configure files, and handle the db myself, but I was gratified to find that, after my site (heavily modified from the last 2.2 release) was relatively quickly brought to 99% functionality, and in all key respects.

 

Back to the problem:

 

When I go to customers in Admin, I get the following error message:

 

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 ' ) left join address_book a on c.customers_id = a.customers_id and c.customers_' at line 1

 

select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_country_id from ( customers c, c.customers_firstname limit, ) left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id order by c.customers_lastname 0 50

 

This error corresponds to the customers query around line 719:

 

$customers_query_raw = "select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_country_id from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . $search . " order by c.customers_lastname, c.customers_firstname";

 

What's particularly odd about this error is that the search function works fine - the customers are found, with both the main columns and the right infobox displayed. It's only the display of the main pre-search results page that doesn't function - specifically the list of customers underneath the column headings. The orders.php page also works fine, as do my order editing and related tools, and for that matter categories, search, and the rest on both admin and catalog sides.

 

I've applied the fix to split_page_results (relating to handling of the $offset), and other experiments I've tried have included some involving the $search variable, which the old customers.php initially declares as empty ($search = '') and which my version of admin/categories.php handles with an if/then format using a query that includes the $search variable when it's already set, using a query without the $search variable if it hasn't been set. My attempts to implement a corresponding structure in customers.php didn't work, nor have experiments with tep_ functions, numrows, and so on.

 

I'm stumped for now. Incidentally, I'm also wondering why this one hasn't come up before, since I'd think that almost everyone migrating up the chain of mysql versions would have encountered it sooner or later.

Link to comment
Share on other sites

If you look at the 2 queries they do not match

 

They start to differ here

 

from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a

 

from ( customers c, c.customers_firstname limit, ) left join

 

You need to find

 

c.customers_firstname limit

 

and once you find it post the file path and name, the line no and the whole statement.

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

If you look at the 2 queries they do not match

 

They start to differ here

 

from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a

 

from ( customers c, c.customers_firstname limit, ) left join

 

You need to find

 

c.customers_firstname limit

 

and once you find it post the file path and name, the line no and the whole statement.

 

I don't know where the items from within the parentheses come from - the terms in this sequence don't exist anywhere in the suite. The only other raw query in customers.php that uses a left join occurs earlier at line 224 of customers.php, and obviously diverges from the one used in the error message. Even a basic term like "customers_firstname" occurs only in a handful of other files (including add-ons), and not in a form resembling this one. My W.A.G. is that it has something to do with the way that TABLE_CUSTOMERS is interpreted when the query is executed. (Forgive me if my terminology is a bit off, I'm purely a hack-my-way-through-it kind of guy when it comes to mysql at this point.) Also, for some reason, as as I said, the query seems to work fine for the search operation, and even for row display after a successful search.

 

If it would help you to help me, and it's more convenient, I can re-produce the entire customers.php file. I've checked using an s unmodified from the last 2.2 release, downloaded just the other day, to make sure I wasn't relying on something corrupted or incidentally altered.

Link to comment
Share on other sites

  • 4 weeks later...

No one ever supplied an answer to this problem or echoed it - which kind of surprises me since it seems to be a problem that anyone with the basic customers.php and no major changes - should have encountered upon migrating to mysql5.

 

Or maybe everyone else has a mysql5 expert sitting around who figures out the problem for them...

 

Anyway, I did manage to solve the problem by hacking away at it, so I'm supplying my answer here. As ever, I'm sure there's a more elegant solution that someone more up to speed on mysql syntax or possibly php could supply, but it works for me. I hope it will be helpful for others who encounter the same or similar errors/dysfunctionalities. If a more skilled coder can provide a better answer, please do!

 

the old, dysfunctional code can be found in customers.php following line 713

 

  $search = '';
if (isset($HTTP_GET_VARS['search']) && tep_not_null($HTTP_GET_VARS['search'])) {
  $keywords = tep_db_input(tep_db_prepare_input($HTTP_GET_VARS['search']));
  $search = "where c.customers_lastname like '%" . $keywords . "%' or c.customers_firstname like '%" . $keywords . "%' or c.customers_email_address like '%" . $keywords . "%'";
}

 

What works adequately is:

 

	$keywords = '';
if (isset($HTTP_GET_VARS['search']) && tep_not_null($HTTP_GET_VARS['search'])) 
{
	  $keywords = tep_db_input(tep_db_prepare_input($HTTP_GET_VARS['search']));
	  }
$search = "where c.customers_lastname like '%" . $keywords . "%' or c.customers_firstname like '%" . $keywords . "%' or c.customers_email_address like '%" . $keywords . "%'";

 

Explanation: The problem appears to occur in the immediately following database query at line 720 in the original customers.php, line 719 in the edited version.

 

	$customers_query_raw = "select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_country_id from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . $search . " order by c.customers_lastname, c.customers_firstname";

 

In the original version, when there's no item being searched, replacing the variable $search = '' (the default) must violate syntax rules. Replacing it with the entire $search "where..." clause, meaning a search for $keywords = '' (i.e., any character), seems to work fine.

 

I suspect that some other 1064 errors I see being reported may be based on related problems. The clue here was that, even when I was receiving the original error message, searches for particular customers worked fine.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...