Strange GREATEST() result in 5.0.32

Hello all

I have a stored procedure that probably does a lot more than it should, but
it works fine on my test server (which is running 5.0.67). When I moved it
over to the production server, (which is running 5.0.32 and I never thought
to check that the versions were the same before) it works almost perfectly.

The issue is when this runs:
GREATEST(d_UPDATE_Time, d_START_Time)

The value is '2010-05-21 20:26:18' and not '2010-05-26 21:49:51' even though
it is greater.


Both of these variables are declared in the top of the procedure:
DECLARE d_UPDATE_Time DATETIME;
DECLARE d_START_Time DATETIME;

When I put this in, to see the values of the variables, I get the right
data:
SELECT d_UPDATE_Time, d_START_Time;

d_UPDATE_Time d_START_Time
------------------- -------------------
2010-05-26 21:49:51 2010-05-21 20:26:18


So if I ran this from the command line:
SELECT GREATEST('2010-05-26 21:49:51', '2010-05-21 20:26:18');

I get:
RESULT
-------------------
2010-05-26 21:49:51


So it works perfectly fine when it runs outside of the stored procedure, but
inside the procedure, it returns the wrong date :(

Is there something I am doing wrong? I can't seem to see anything wrong.
Also, upgrading from 5.0.32 would require upgrading both the master and
slave, and right now, that is not a good idea (we are discussing it though)


Steven Staples




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Strange GREATEST() result in 5.0.32

Steven,

On Tue, Jun 1, 2010 at 11:15 AM, Steven Staples wrote:
> Hello all
>
> I have a stored procedure that probably does a lot more than it should, b=
ut
> it works fine on my test server (which is running 5.0.67). =A0 When I mov=
ed it
> over to the production server, (which is running 5.0.32 and I never thoug=
ht
> to check that the versions were the same before) it works almost perfectl=
y.

It sounds like you're running into a bug, simply put. 5.0.32 is very
old and an amazing amount of bugs have been fixed since then. I would
not even consider running it in production. I know it'll be tough to
upgrade, but if you don't, my experience is that another of the
unfixed bugs is going to cause you serious pain anyway, such as
crashing your server.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Strange GREATEST() result in 5.0.32

Baron,

Out of curiosity, do you (or anyone else) know what could be an issue =
with
upgrading to even 5.0.93? or even the 5.1 branch?

There are a lot of stored procedures/functions, as well as the fact that =
it
is being replicated (the backup server is running multiple instances, =
and
is replicating 3 other servers). So, all the sql databases will have =
to
be updated/upgraded, but is there anything I/we should be made aware of
before we go ahead? (there is a lot of release notes to sift through)


Steven Staples


> -----Original Message-----
> From: baron.schwartz@gmail.com [mailto:baron.schwartz@gmail.com] On =
Behalf
> Of Baron Schwartz
> Sent: June 15, 2010 4:56 PM
> To: mysql@lists.mysql.com
> Subject: Re: Strange GREATEST() result in 5.0.32
>=20
> Steven,
>=20
> On Tue, Jun 1, 2010 at 11:15 AM, Steven Staples =
wrote:
> > Hello all
> >
> > I have a stored procedure that probably does a lot more than it =
should,
> but
> > it works fine on my test server (which is running 5.0.67). =A0 When =
I
moved
> it
> > over to the production server, (which is running 5.0.32 and I never
> thought
> > to check that the versions were the same before) it works almost
> perfectly.
>=20
> It sounds like you're running into a bug, simply put. 5.0.32 is very
> old and an amazing amount of bugs have been fixed since then. I would
> not even consider running it in production. I know it'll be tough to
> upgrade, but if you don't, my experience is that another of the
> unfixed bugs is going to cause you serious pain anyway, such as
> crashing your server.
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dsstaples@mnsi.net
>=20
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.829 / Virus Database: 271.1.1/2917 - Release Date: =
06/15/10
> 02:35:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Strange GREATEST() result in 5.0.32

Steven,

On Wed, Jun 16, 2010 at 9:09 AM, Steven Staples wrote:
> Baron,
>
> Out of curiosity, do you (or anyone else) know what could be an issue wit=
h
> upgrading to even 5.0.93? =A0or even the 5.1 branch?
>
> There are a lot of stored procedures/functions, as well as the fact that =
it
> is being replicated (the backup server is running multiple instances, and
> is replicating 3 other servers). =A0 So, all the sql databases will have =
to
> be updated/upgraded, but is there anything I/we should be made aware of
> before we go ahead? (there is a lot of release notes to sift through)

There are -- but unfortunately there's no magic wand :-( You really
do have to read the changelogs and determine what matters to you. You
already said you use stored procedures and functions, and a lot of the
bug fixes involve those. The more complex your usage, the more work
upgrades are. I would suggest using the mk-upgrade tool from Maatkit
to help ferret out issues.

- Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg