query working in test and not in production, help!

juronimo

I'm developing an application locally on my machine and then I upload the changes to the server. I have MySQL 5 on my local machine. I don't know what version is on the server, phpMyAdmin doesn't say.

Here is the query:

select s.mgID, s.mgLastName, s.mgFirstName, s.mgCurrentEmploymentPhone, s.mgHomePhone,  s.mgLoanType, s.mgState, s.lead_status, times_sold, DATE_FORMAT(s.mgDate,'%m/%d/%Y') AS order_date from short_loan_app s where s.lender_id = '7020' or s.mgID in (select l.mgID from lead_assign l where l.lender_id = '7020')

On the server, I get error #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 'select l . mgID from lead_assign l where l . lender_id = '7020'

On my desktop, the query runs perfectly.

juronimo

I resolved the problem, apparently I must have a real old version of mysql running in production. I found a workaround on this site here:

http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html

Apparently nested queries don't work in older version of mysql.

SELECT DISTINCT s.mgID, s.mgLastName, s.mgFirstName, s.mgCurrentEmploymentPhone, 
s.mgHomePhone, s.mgLoanType, s.mgState, s.lead_status, 
times_sold, DATE_FORMAT( s.mgDate, '%m/%d/%Y' ) AS order_date
FROM short_loan_app s, lead_assign l
WHERE s.lender_id = '7030'
OR s.mgID = l.mgID order by mgID desc

MarkR

You really want to run the same versions of everything in dev and production if possible.

I can't see any case for running < MySQL 4.1 (I use MySQL5 everywhere).

Mark

juronimo

MarkR wrote:
You really want to run the same versions of everything in dev and production if possible.

I can't see any case for running < MySQL 4.1 (I use MySQL5 everywhere).

Mark

It's not up to me, it's up to godaddy. For some reason, they're using a real old version of mysql.

MarkR

I wish people would stop writing their code based on what ancient software their crappy provider happens to provide, and would just get a decent provider instead.

Remember that you are the developer, and it is the sysadmin's job to set up what software you need, NOT your job to work-around whatever junk the sysadmin wants to dump on you.

(Even cheap) Developer time is FAR more expensive than the most extortionate of decent hosts.

So don't pay developers $$$ to save you a few pence on hosting!

Mark

juronimo

MarkR wrote:
I wish people would stop writing their code based on what ancient software their crappy provider happens to provide, and would just get a decent provider instead.

Remember that you are the developer, and it is the sysadmin's job to set up what software you need, NOT your job to work-around whatever junk the sysadmin wants to dump on you.

(Even cheap) Developer time is FAR more expensive than the most extortionate of decent hosts.

So don't pay developers $$$ to save you a few pence on hosting!

Mark

That's great in theory, but in practice it's not happening. I've already asked for a dedicated host where I can install all my own stuff, that's not happening anytime soon. For now I'm stuck with php 4 and mysql 3.

MarkR

juronimo wrote:
That's great in theory, but in practice it's not happening. I've already asked for a dedicated host where I can install all my own stuff, that's not happening anytime soon. For now I'm stuck with php 4 and mysql 3.

Approach your line manager. Explain to them that they'll save loads of money on developer time by paying a tiny bit more for hosting.

Unless they're incredibly stupid, I'm sure they will be sympathetic.

Mark