Really ODD MySQL query result

[ethicaldesign]

Has anyone ever experienced anything like this before.

I've spent a good chunk of the day trying to work out why a relatively complex MySQL multiple join query isn't working. At first I thought it was me but in the process of trying to track down the problem, I've come across:

select * from cds_Lnk WHERE ProdID='S0014935';
+----------+----------------+
| ProdID | RelatedProduct |
+----------+----------------+
|935 | S0326379
|935 | S0665343
+----------+----------------+

Obviously the ProdID should display as S0014935 in the ProdID column. For some reason it's cropping off everything but the last 3 characters.

This is a straightforward varchar column. I've tried dropping this table, recreating and repopulating with records and still the same problem.

I get the feeling that the reason my more complex Join queries weren't working is because they're joined on this ProdID which is being listed incorrectly.

Oddly, if you select only the ProdID it displays properly.

These results are being displayed when querying through the mysql command prompt in a ssh session (I use mysql directly like this when working out some of my queries to check results before coding in php as it's usually quicker this way).

Anyone got any ideas?

[Anoop]

Can you give the query that was used to create the table

[ethicaldesign]

Thanks for your reply Anoop,

The table was created using PhpAdmin, but a dump of the structure in it's current form (I've modified it several times in an attempt to work out why it's going wrong) is:

CREATE TABLE cds_Lnk (
ProdID varchar(40) NOT NULL default '',
RelatedProduct varchar(40) NOT NULL default ''
) TYPE=MyISAM;

I originally had ProdID and RelatedProduct indexed, but I've deleted these incase they were causing a problem.

Any help you could offer would be great because I'm still stuck with this (have moved on to writing the rest of the code to come back to this later and all of the other queries that I'm using, some of them much more complex than what I'm trying to do with this one, are working fine).

It definitely seems to be something to do with this table, though what completely escapes me at the moment.

I've tried dropping the whole database and recreating it and repopulating it and still the same problem. Tried dropping the table and recreating it and repopulating it and still the same problem.

About the only thing that I haven't tried yet is upgrading MySQL incase there's some error with the db server itself, but I'm not sure if that's going to be the case as it only seems to effect this one table (that's very simple). It's about time I upgraded to fedora core 3 (still on FC1 at the moment), so I'm planning to do that tomorrow and see if it works it out.

If you can suggest a solution this would be great though as it would allow me to put off that upgrade until I have more time (I've got a terrible feeling I'll install FC3 and the latest version of MySQL and the same problem will exist - my instinct tells me I'm missing something simple but what I just don't know).

[Anoop]

There seems to have some problem with your MySql version. I couldn't recreate your problem. Everything worked fine on my machine.
Have you checked other varchar fields in any other tables. Are they having the same problem ?

[ethicaldesign]

Thanks for trying Anoop. The problem isn't happening with other tables, that also use the ProdID field as a varchar with the same data, just this one.

The only difference is that this one can contain duplicates of ProdID in both fields (ProdID and RelatedProduct). It needs to be this way for the purpose of the table and the data it's being populated with (which I'm not responsible for creating myself). I can't see why it should be a problem though because there aren't any unique primary key definitions being made so that table should hold duplicates on either field without a problem.

Perhaps it is a problem with my install of MySQL. I'll try upgrading it tomorrow.

Thanks for your help :)