Strange MySQL query anomaly

[fattyjules]

I have two MySQL servers, one's a test the other's development. This query;

select * from pr_stops as Stop where stop.id = 1

...works ok on my test server, but gives the error "Unknown column 'stop.id' in 'where clause'" on my development server. If I change 'Stop' to 'stop', it works ok.

Unfortunately this query is generated by the CakePHP framework, so I can't just edit the query manually.

test: MySQL 5.5.14
dev: MySQL 5.5.8

Can anyone help? Thanks.

[Dan Grossman]

MySQL :: MySQL 5.0 Reference Manual :: 8.2.2 Identifier Case Sensitivity

In short, the case-sensitivity of the underlying operating system determines case-sensitivity of identifiers in queries.

[fattyjules]

That makes sense, one is Windows and the other is Linux. Thank you so much for the link, I'll follow it up!

[fattyjules]

In case anyone else stumbles across this post with the same problem, here's how I solved it.

It turned out that I could change the query. In one of my controllers I was querying the model like this;

$this->Stop->find('first',array(
    'conditions' => array(
      'stop.id' => $id
    )
  )
);

Windows had no problem with this, but Linux didn't like it. After changing 'stop.id' to 'Stop.id', all fixed!