Re: [xmlblaster] MySQL Bugs

[Peter Bennett <bencom at bencom.co.nz>]

I think I may have it....

These statments run fine...

MySQL

select * from TEST_ENTRIES where queueName='callback_QueuePluginpeekMsg' 
and nodeId='xmlBlaster_192_168_44_1_3412' and prio='(select max(prio) 
from TEST_ENTRIES where queueName="callback_QueuePluginpeekMsg" AND 
nodeId="xmlBlaster_192_168_44_1_3412")' ORDER BY dataId ASC;


postgres as per current code except for typo fix dataid -> dataId
which obviously postgres does not care about... MySQl does.

select * from TEST_ENTRIES where queueName='callback_QueuePluginpeekMsg' 
and nodeId='xmlBlaster_192_168_44_1_3412' and prio=(select max(prio) 
from TEST_ENTRIES where queueName='callback_QueuePluginpeekMsg' AND 
nodeId='xmlBlaster_192_168_44_1_3412') ORDER BY dataId ASC;


Note the quotes...
Also note that there is a typo in the statement in the
order by part dataid instead of dataId...

Also note the above MySQL statement does not work in postgres...

I have also tried the statement below.

Fails postgres ERROR:  Attribute '(select max(prio) from TEST_ENT'
not found

Works in MySQL.

select * from TEST_ENTRIES where queueName='callback_QueuePluginpeekMsg' 
and nodeId='xmlBlaster_192_168_44_1_3412' and prio="(select max(prio) 
from TEST_ENTRIES where queueName='callback_QueuePluginpeekMsg' AND 
nodeId='xmlBlaster_192_168_44_1_3412')" ORDER BY dataId ASC;

You would have a better idea than I on how to work around this issue...

Regards






Peter Bennett wrote:
> Greetings
>
> I beleive the difference in the databases is that
> MySQL will return one null record for the query
> were as postgres returns one empty record...
> Thats in the case where the record does not exist.
>
> Most likely a test for null in the routine will fix that bug.
>
> Imagonnatryitnow. :)
>
> Regards
>
>
> Michele Laghi wrote:
>
> > Hi Peter,
> > I installed MySQL 4.1.10 on linux and I believe that mysql still has 
> > problems in handling rollbacks (even if the metadata says it supports 
> > transactions).
> >
> > I say that since the following test went wrong:
> >
> > I have three entries in the DB (lets say entries 2,3,4). I set 
> > autocommit to false and make a request where I delete the entries 
> > 1,2,3,4,5. This throws (as for the other DBs) an exception. I then 
> > make a rollback and delete every single entry individually. In the 
> > case of mySql no entry is deleted. That's because they where deleted 
> > in the first place ignoring the rollback directive.
> >
> > So this is a problem. I will have a closer look to it in the next days.
> >
> > Michele
> >
> >
> > Peter Bennett wrote:
> >
> > > Greetings
> > >
> > > Is this meant to happen?
> > >
> > > Class org.xmlBlaster.test.classtest.AllTests
> > >
> > > testPeekMsg
> > > Failure
> > >
> > > I_QueueTest.peekMsg(callback:QueuePlugin/peekMsg)[org.xmlBlaster.util.queue.jdbc.JdbcQueueCommonTablePlugin]: 
> > > Exception thrown: XmlBlasterException errorCode=[resource.db.unknown] 
> > > serverSideException=false node=[xmlBlaster] 
> > > location=[JdbcManagerCommonTable.getEntriesBySamePriority] 
> > > message=[An unknown error with the backend database using JDBC 
> > > occurred : java.sql.SQLException: Syntax error or access violation, 
> > > message from server: "You have an error in your SQL syntax near 
> > > 'select max(prio) from TEST_ENTRIES where 
> > > queueName='callback_QueuePluginpeekMsg'' at line 1"]
> > >
> > > junit.framework.AssertionFailedError: 
> > > I_QueueTest.peekMsg(callback:QueuePlugin/peekMsg)[org.xmlBlaster.util.queue.jdbc.JdbcQueueCommonTablePlugin]: 
> > > Exception thrown: XmlBlasterException errorCode=[resource.db.unknown] 
> > > serverSideException=false node=[xmlBlaster] 
> > > location=[JdbcManagerCommonTable.getEntriesBySamePriority] 
> > > message=[An unknown error with the backend database using JDBC 
> > > occurred : java.sql.SQLException: Syntax error or access violation, 
> > > message from server: "You have an error in your SQL syntax near 
> > > 'select max(prio) from TEST_ENTRIES where 
> > > queueName='callback_QueuePluginpeekMsg'' at line 1"] at 
> > > org.xmlBlaster.test.classtest.queue.I_QueueTest.peekMsg(I_QueueTest.java:605) 
> > >
> > > at 
> > > org.xmlBlaster.test.classtest.queue.I_QueueTest.testPeekMsg(I_QueueTest.java:444) 
> > >
> > > 1.745