-
1. Re: Field with NULL value is replaced by a non-NULL field during modify
shawkins Aug 28, 2013 10:07 AM (in response to sgujja)Can you post your extension or detail how/when it is modifying the source query?
-
2. Re: Field with NULL value is replaced by a non-NULL field during modify
sgujja Oct 7, 2013 3:30 PM (in response to shawkins)I am issuing the following SQL from Teiid Designer Plugin on Eclipse.
UPDATE security_ldap.BASEUSER SET EXPDATE = {d '2014-05-05'}, CONSID = NULL WHERE security_ldap.BASEUSER.DN = 'TSSWOLF' and SECURITY_LDAP.BASEUSER.SYSID = 'DE29'
(Note: Prior to issuing this command, I had a value 'A' in CONSID field(it is a varchar), I am trying to null the value out now)
This will go through the extended translator we have for LDAP and convert the SQL to the DN, attributes, user information, in a way LDAP understands and sends it to the LDAP we have running in the background.
So the above query is converted to the following for LDAP server.
Attributes:
replace: EXPDATE
one value, length 10
field(EXPDATE), value(2014-05-05)
replace: CONSID
one value, length 10
field(CONSID), value(2014-05-05)
dn="tssacid=TSSWOLF,tssadmingrp=acids,host=test,o=Company,c=US"
As you can see from the above LDAP information, CONSID and EXPDATE both have date in their value. From the SQL query we sent, notice that we sent CONSID=NULL, but in the outcome we have CONSID=2014-05-05.
Why was this converted from a NULL to date value that is for the other field, is something I am trying to understand.
-
3. Re: Field with NULL value is replaced by a non-NULL field during modify
shawkins Oct 7, 2013 4:05 PM (in response to sgujja)> From the SQL query we sent, notice that we sent CONSID=NULL, but in the outcome we have CONSID=2014-05-05.
I am still somewhat confused by your scenario/description. Perhaps it would be best to start at the beginning. Use an unmodified ldap translator and a source model insert with no data roles etc. to first validate that Teiid is working the way you would expect.
You can see in the LDAPUpdateExecution that the translator is quite straight-forward in producing - https://github.com/teiid/teiid/blob/master/connectors/translator-ldap/src/main/java/org/teiid/translator/ldap/LDAPUpdateExecution.java
Can you provide a query plan or command log showing the pushdown command sent to the translator?
Otherwise you'll have to being posting your customization that would assist in reproducing what you are seeing.
-
4. Re: Field with NULL value is replaced by a non-NULL field during modify
sgujja Mar 7, 2014 10:15 AM (in response to shawkins)Steve,
Sorry for the delay in my response. Was caught up with other work.
Coming back to this issue, here is what we do to reproduce it:
We are modifying two fields from our home grown application (UI), which talks to TEIID table, which then talks to our another home grown product via CA LDAP server.
Here is the snippet of our initial UI.
As you can see, the console ID field is empty, and there is a value in Expiration Date.
The next step we do is put a value(Y) for Console ID and remove the Expiration Date.
We send this request through the UI to LDAP. It goes in and modifies the changed fields successfully.
As you can see, the value in Console ID is Y and nothing is put in Expiration Date.
Now, when we change those again, by removing Y from Console ID and put a Date in Expiration date as follows:
…and submit the request. It now fails with an LDAP message, which is a valid error, based on what is going to LDAP from UI.
This is the error.
When the request is submitted, here is what we see in the UI log, as what is coming through TEIID.
Execute UPDATE tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US SET EXPDATE = {d '2014-12-12'}, CONSID = NULL WHERE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE.DN = '"tssacid=TSSWOLF,tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US"' against org.teiid.translator.ldap.LDAPUpdateExecution@305a822e with userid: allje05
But when we see in the LDAP log, here is what is coming through LDAP.
dn (tssacid=TSSWOLF,tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US)
0000: 30 1c 0a 01 02 30 17 04 07 45 58 50 44 41 54 45 0....0...EXPDATE
daemon: select: listen=6 active_threads=0 tvp=NULL
0010: 31 0c 04 0a 32 30 31 34 2d 31 32 2d 31 32 30 1b 1...2014-12-120.
daemon: select: listen=7 active_threads=0 tvp=NULL
0020: 0a 01 02 30 16 04 06 43 4f 4e 53 49 44 31 0c 04 ...0...CONSID1..
0030: 0a 32 30 31 34 2d 31 32 2d 31 32 .2014-12-12
conn=1738 op=6 modifications:
replace: EXPDATE
one value, length 10
replace: CONSID
one value, length 10
As you can see here, CONSID and EXPDATE, both are getting date in them. This is what is inputted from the UI to LDAP, even though we see the correct SQL.
Note:
One thing we noticed during our testing is very interesting and thought this might help in determining the problem.
We entered the same query in two different ways from the TEIID Designer plugin, and got two different results.
The first case is when we entered:
Execute UPDATE tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US SET CONSOLE=NULL, EXPDATE = {d '2014-12-12'} WHERE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE.DN = '"tssacid=TSSWOLF,tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US"'
This works, I mean the query is passed in correctly to LDAP, and we get the right results back.
Here, notice the CONSOLE=NULL first and the EXPDATE, with a value second, just before the "WHERE" keyword.
The second case is when we entered:
Execute UPDATE tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US SET CONSOLE=Y, EXPDATE = NULL WHERE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE.DN = '"tssacid=TSSWOLF,tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US"'
This doesn't work and gives the error described above.
Here, notice the CONSOLE with value is first and EXPDATE with NULL second and just before "WHERE" keyword.
We also have a third case, where we flipped the order.
Execute UPDATE tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US SET EXPDATE = {d '2014-12-12'}, CONSOLE=NULL WHERE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE.DN = '"tssacid=TSSWOLF,tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US"'
This case fails as well. Notice the NULL at the end before "WHERE" keyword.
I am not sure if this is the case, but some how having a NULL field at the end, before the "WHERE" keyword seems to cause this problem.
We had a different error an year ago regarding MALFORMED DATABASE ERROR, as discussed in:
Re: Malformed Attribute Value - LDAP Translator - is this a bug?
I could be wrong, could this fix have caused this issue now? I can't remember exactly if this used to work before, but we certainly started noticing this behavior after the Patch was applied.
-
5. Re: Field with NULL value is replaced by a non-NULL field during modify
shawkins Mar 10, 2014 11:04 AM (in response to sgujja)Yes, I see the issue now as well. The null value is picking up the value of the previous set. Can you log this as an issue?
Thanks for providing a detailed post,
Steve
-
6. Re: Field with NULL value is replaced by a non-NULL field during modify
shawkins Mar 10, 2014 4:07 PM (in response to shawkins)I went ahead and logged / worked this issue as [TEIID-2891] LDAP null update handling can produce invalid updates - JBoss Issue Tracker so that it will get into 8.7 Beta2.
Thanks,
Steve
-
7. Re: Field with NULL value is replaced by a non-NULL field during modify
sgujja Mar 10, 2014 4:20 PM (in response to shawkins)Thank you Steve,
We originally had this issue logged for the above problem. It was closed at that time, as we did not have enough details.
-
8. Re: Field with NULL value is replaced by a non-NULL field during modify
sgujja Mar 24, 2014 10:05 AM (in response to sgujja)Steve,
Thank you. The fix provided in: [TEIID-2891] LDAP null update handling can produce invalid updates - JBoss Issue Tracker] resolved our issue.