[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [xmlblaster] MySQL Bugs



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