CONCAT() and null question 1

[BlindPete]

Can anybody tell me why this query only returns null values for the field "test" ?

SELECT CONCAT( 'a', 'b', 'c') AS test, nid FROM someTable

test | nid
NULL | 1
NULL | 2
NULL | 3
NULL | 4
NULL | 5

Whats odd is that on our test server it correctly returns abc but on the live server i returns NULL.

Obviously I'm not inteding to CONCAT( 'a', 'b', 'c') but I I kept simplifing it to figure which field was causing it until well I was left with no fields and three static strings.

Is there some setting on the Apache server or in MySQL that would cause this?

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

 

[rzs0502]

Are you perhaps starting up the MySQL service in ANSI mode on prod?

 

[BlindPete]

Thank you but reluctantly I must admit I know squat about servers. How would i check for that?

SHOW VARIABLES or SHOW STATUS

and then what should I look for to tell if its ANSI mode or not?


-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

 

[rzs0502]

Come to think of it, it cannot be a problem with the mode.

What version of MySQL is this? Platform?

Have you tried concat('a','b','c').... without any spaces??

 

[BlindPete]

Thanks again.
Production Server:
Operating system Linux
Kernel version 2.4.20-grsec
Apache version 1.3.28 (Unix)
MySQL version 4.0.14-standard

I rent the server space so getting things changed takes time. If i could tell them as precily as possible what settings needs adjustment it would help us all.

I tried:

SELECT CONCAT( 'a', 'b', 'c' ) AS test, corid
FROM spec_course

SELECT CONCAT('a','b','c') AS test, corid
FROM spec_course

SELECT CONCAT("a","b","c&quot AS test, corid
FROM spec_course

All with the same result and below I tried this
which errored out (i got this idea from you ANSI comment)

SELECT 'a'||'b'||'c' AS test, corid
FROM spec_course

Test returns 0 (thats Zero) instead of Null

Thanks again fro your help!

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

 

[rzs0502]

Strange one... I can't seem to re-create the problem...

Try this ...

select concat_ws('','a','b','c') as X;

select_ws is an extended concat function where the first variable, '' in this case, represents the sepearator.

 

[BlindPete]

The good news is it has been fixed by the hosting company. I have asked them what the issue was so that can post it here and have nice conclusion to this thread. However they have not replied to my request for more info yet and may not tell me what was wronge in any event. I am very glad its fixed and VERY greatfull for your help.

I am surpised this forum is not more active, the PHP one is very active.


-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

 

[BlindPete]

The problem was that MySQL was using too high of a max_allowed_packet, and in addition to causing connections to be lost when large files were dumped/exported, some functions were weirdly temporarily rendered funky and broken, like CONCAT(). Don't really know why that variable being too high would make CONCAT break of all things, but changing it back down to the default seemed to magically set everything back to normal.

I would like to add that I have worked with several hosting companies over the years and this one

http://hostreflex.com/

has had the finest service of any of them. In addition to having competative rates they were very responsive to this problem and worked very quickly to resolve it.

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/