6.0.0-git
2019-03-18

[#8608] Wrong datatype "?" to "unsigned bigint" in datatree. \lib\Horde\DataTree\sql.php
Summary Wrong datatype "?" to "unsigned bigint" in datatree. \lib\Horde\DataTree\sql.php
Queue Horde Framework Packages
Queue Version FRAMEWORK_3
Type Bug
State No Feedback
Priority 2. Medium
Owners
Requester c-master (at) freenet (dot) de
Created 2009-09-28 (3458 days ago)
Due 12/31/2009 (3364 days ago)
Updated 2009-12-07 (3388 days ago)
Assigned 2009-09-29 (3457 days ago)
Resolved 2009-12-04 (3391 days ago)
Milestone
Patch No

History
2009-12-07 08:00:04 c-master (at) freenet (dot) de Comment #27 Reply to this comment
Sybase is doing nothing. The case isn't even assigned to someone. 
Thats it: I'm giving up. When there is a new Version of Sybase (I 
think it is 11 at the moment) I'll try again.
Anyway: many many thanks for your help!
2009-12-04 15:43:56 Jan Schneider Comment #26
State ⇒ No Feedback
Reply to this comment
Without any feedback from Sybase, there's not much we can do at the 
current state.
2009-11-12 14:09:49 c-master (at) freenet (dot) de Comment #25 Reply to this comment
Still nothing from sybase. I'm almost about to give up :( They 
obviously don't care about their customers.
2009-11-05 19:41:21 c-master (at) freenet (dot) de Comment #24 Reply to this comment
...no. Nothing yet :(
I'll check the sybase ticket tomorrow.
If sybase won't answer: Is there any kind of workaround for my problem?

regards
2009-11-05 12:56:25 Jan Schneider Comment #23 Reply to this comment
Any update yet?
2009-10-07 14:17:08 c-master (at) freenet (dot) de Comment #22 Reply to this comment
I've just created a case in the sybase-bugtracker with No. 11561634 
... See what happens next.
2009-10-05 14:00:04 Jan Schneider Comment #21 Reply to this comment
I would rather like to avoid that.
2009-10-05 13:34:15 c-master (at) freenet (dot) de Comment #20 Reply to this comment
The problem is that we can't use any of the casts that we already
apply when using pgsql or mssql. There might be a syntax that works
for Sybase, but since we have a single driver for ODBC it might break
other ODBC-driven databases.
If there is a solution, might it be possible to have 2 ODBC-drivers? 
One for Sybase, another for all other ODBC-driven databases?
2009-10-05 12:20:52 Jan Schneider Comment #19 Reply to this comment
The problem is that we can't use any of the casts that we already 
apply when using pgsql or mssql. There might be a syntax that works 
for Sybase, but since we have a single driver for ODBC it might break 
other ODBC-driven databases.
2009-10-02 14:17:58 c-master (at) freenet (dot) de Comment #18 Reply to this comment
Another interesting fact in statment number 2:



When I change this line

(a1.attribute_name = 'owner' AND a1.attribute_value = 'root') OR



to this line

(a1.attribute_name = 'owner' AND a1.attribute_value = 'root') AND



it works! But maybe it won't in certain circumstances, because the 
logic is changed.
2009-10-02 14:08:41 c-master (at) freenet (dot) de Comment #17 Reply to this comment
Half success!

First problem is solved, but there's another (same) one.

This statement now works:

SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT JOIN 
horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id LEFT 
JOIN horde_datatree_attributes a2 ON a2.datatree_id = c.datatree_id 
WHERE c.group_uid = 'horde.shares.kronolith' AND (a1.attribute_name = 
'perm_groups' AND a1.attribute_key IN ('1', '2') AND 
(a1.attribute_value & 2) = 2)  AND a2.attribute_name = 'name'  GROUP 
BY c.datatree_id, c.datatree_name, c.datatree_order, 
a2.attribute_value ORDER BY a2.attribute_value ASC



...after I changed the following values (Sybase Database Options):

Blocking = Off

Optimization_Goal = First-Row

Optimization_Level = 0

Optimization_Logging = On

Prefetch = Off



Now the same problem on a even more complex statement. (It can be 
found in the already attached file horde.log) Error code again -157. 
Message 'conversion from "Kalender von root" to unsigned bigint not 
possible'.



SELECT c.datatree_id, c.datatree_name FROM horde_datatree c

LEFT JOIN horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id

LEFT JOIN horde_datatree_attributes a2 ON a2.datatree_id = c.datatree_id

LEFT JOIN horde_datatree_attributes a3 ON a3.datatree_id = c.datatree_id

WHERE c.group_uid = 'horde.shares.kronolith' AND

(

   (



     (a1.attribute_name = 'owner' AND a1.attribute_value = 'root') OR



     (a1.attribute_name = 'perm_users' AND a1.attribute_key = 'root' 
AND (a1.attribute_value & 2) = 2) OR

     (a1.attribute_name = 'perm_creator' AND (a1.attribute_value & 2) = 2) OR

     (a1.attribute_name = 'perm_default' AND (a1.attribute_value & 2) = 2) OR

     (a1.attribute_name = 'perm_groups' AND a1.attribute_key IN ('1', 
'2') AND (a1.attribute_value & 2) = 2)

   ) AND a2.attribute_name = 'owner' AND a2.attribute_value = 'root')

AND a1.datatree_id = a2.datatree_id AND a3.attribute_name = 'name'



GROUP BY c.datatree_id, c.datatree_name, c.datatree_order, 
a3.attribute_value ORDER BY a3.attribute_value ASC



-------

But when I remove the line

     (a1.attribute_name = 'owner' AND a1.attribute_value = 'root') OR

it runs perfectly!
2009-10-01 16:42:38 Jan Schneider Comment #16 Reply to this comment
Is there any
possibility for you to do some testing with a Sybase-DB?
No
2009-10-01 16:32:08 c-master (at) freenet (dot) de Comment #15 Reply to this comment
When I get a workaround for that running, I'll post it here!!

At this moment I also don't know what is wrong with sybase. I will try 
several database-settings and report back. Is there any possibility 
for you to do some testing with a Sybase-DB?
2009-10-01 09:45:41 Jan Schneider Comment #14 Reply to this comment
It sounds to me as if Sybase is doing some bad query optimization. 
From the error message it looks like it is doing the 
(a1.attribute_value & 2) = 2 comparison on rows where attribute_value 
contains 'Kalender von root'. This is an expected value for rows that 
have attribute_name = 'name'. But we explicitly ask for attribute_name 
= 'perm_groups' in the query *first*. No idea, why it gets the order 
correct without the group_uid part. And I don't know how to tell 
Sybase to get its optimization correctly done either.
2009-10-01 07:59:48 c-master (at) freenet (dot) de Comment #13
New Attachment: hordeStatements.JPG Download
Reply to this comment
I found something interesting! When I change this statement:



SELECT c.datatree_id, c.datatree_name FROM horde_datatree c

LEFT JOIN horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id

LEFT JOIN horde_datatree_attributes a2 ON a2.datatree_id = c.datatree_id

WHERE (c.group_uid = 'horde.shares.kronolith') AND

  (a1.attribute_name = 'perm_groups' AND a1.attribute_key IN ('1', '2')

AND (a1.attribute_value & 2) = 2) AND a2.attribute_name = 'name'

GROUP BY c.datatree_id, c.datatree_name, c.datatree_order, 
a2.attribute_value ORDER BY a2.attribute_value ASC



to this statement



SELECT c.datatree_id, c.datatree_name FROM horde_datatree c

LEFT JOIN horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id

LEFT JOIN horde_datatree_attributes a2 ON a2.datatree_id = c.datatree_id

WHERE

  (a1.attribute_name = 'perm_groups' AND a1.attribute_key IN ('1', '2')

AND (a1.attribute_value & 2) = 2) AND a2.attribute_name = 'name'

GROUP BY c.datatree_id, c.datatree_name, c.datatree_order, 
a2.attribute_value ORDER BY a2.attribute_value ASC



the execution works and returns the values:

datatree_id = 2     and    datatree_name = 'root'



if I put "... where (c.group_uid = 'horde.shares.kronolith') AND ..." 
back in, the same error appeares... See attachment 'hordeStatements.JPG'
2009-09-30 18:11:21 c-master (at) freenet (dot) de Comment #12 Reply to this comment
I guess Sybase has some kind of problem with this part of the sql-statement:

"... AND a1.attribute_key IN ('1', '2') AND (a1.attribute_value & 8) = 
8)  AND a2.attribute_name = 'name' ...".

Because attribute_value is varchar(255) and not an integer sybase 
seems not to refer to the actual VALUE of attribute_value but instead 
to the TYPE of attribute_value. Maybe it needs some explicit 
conversation at this time.

OR (my 2nd guess)

The mapping between attribute_name and attribute_value somehow fails 
and horde (or sybase?) just takes a wrong value from attribute_value.
2009-09-30 13:52:59 Jan Schneider Comment #11 Reply to this comment
That is correct. The values are saved fine. But horde attempts to 
convert the varchar to bigint for some reason and fails. I don't 
know why this conversion is needed, or if it is needed at all. 
Anyway it fails with error: "Umwandeln von Kalender von root auf 
unsigned bigint nicht möglich" [pid 1052 on line 1252 of 
"\lib\Horde\DataTree\sql.php"]
-> I translated it to english: "Conversion from 'Kalender von root'
to unsigned bigint not possible"
No, it's not Horde that is converting. The error message comes from 
Sybase. And I don't know either why this conversion would be needed, 
or when Sybase is trying to do the conversion.
2009-09-30 13:23:43 c-master (at) freenet (dot) de Comment #10 Reply to this comment
I'm not sure what you mean with that. Why should attribute_value be
converted to bigint? The column is a string, and from your screenshot
it save both string and integer values just fine, like expected.
That is correct. The values are saved fine. But horde attempts to 
convert the varchar to bigint for some reason and fails. I don't know 
why this conversion is needed, or if it is needed at all. Anyway it 
fails with error: "Umwandeln von Kalender von root auf unsigned bigint 
nicht möglich" [pid 1052 on line 1252 of "\lib\Horde\DataTree\sql.php"]

-> I translated it to english: "Conversion from 'Kalender von root' to 
unsigned bigint not possible"


2009-09-30 10:14:27 Jan Schneider Comment #9 Reply to this comment
Table Data visible in attachment. The Problem is attribute_value
which can not be converted to bigint (because it is a string)
I'm not sure what you mean with that. Why should attribute_value be 
converted to bigint? The column is a string, and from your screenshot 
it save both string and integer values just fine, like expected.
2009-09-30 09:28:30 c-master (at) freenet (dot) de Comment #8
New Attachment: hordeTables.JPG Download
Reply to this comment
Looks like scripts/sql/create.sybase.sql (on which the groupware
script is based) is not really up-to-date. The datatree_order column
is missing and the horde_datatree_attributes is missing completely.
I created the missing and invalid tables on my own, by analyzing 
horde-error-logs and using the datatree-create statements.



My tables look like this (sql form)



CREATE TABLE "php"."horde_datatree" (

        "datatree_id" integer NOT NULL,

        "group_uid" varchar(255) NOT NULL,

        "user_uid" varchar(255) NOT NULL,

        "datatree_name" varchar(255) NOT NULL,

        "datatree_parents" varchar(255) NOT NULL,

        "datatree_order" integer NULL,

        "datatree_data" varchar(255) NULL,

        "datatree_serialized" smallint NOT NULL DEFAULT 0,

        PRIMARY KEY ( "datatree_id" )

);



CREATE TABLE "php"."horde_datatree_attributes" (

        "datatree_id" integer NOT NULL,

        "attribute_name" varchar(255) NOT NULL,

        "attribute_key" varchar(255) NULL,

        "attribute_value" varchar(255) NULL

);



Table Data visible in attachment. The Problem is attribute_value which 
can not be converted to bigint (because it is a string)
2009-09-29 22:33:26 Jan Schneider Comment #7 Reply to this comment
Oh, and how did you create them in the first place, since there is no
MDB2_Driver_odbc?
I created the tables via 'scripts/sql/groupware.sybase.sql' statement
and then replaced 'datatree_data TEXT NOT NULL' with 'datatree_data
VARCHAR(255) NOT NULL' by hand. (Did not help)
Looks like scripts/sql/create.sybase.sql (on which the groupware 
script is based) is not really up-to-date. The datatree_order column 
is missing and the horde_datatree_attributes is missing completely.
2009-09-29 17:56:00 c-master (at) freenet (dot) de Comment #6 Reply to this comment
How do the datatree tables look like? Can you dump the table structures?
I will, tomorrow when i'm back at work.
Oh, and how did you create them in the first place, since there is no
MDB2_Driver_odbc?
I created the tables via 'scripts/sql/groupware.sybase.sql' statement 
and then replaced 'datatree_data TEXT NOT NULL' with 'datatree_data 
VARCHAR(255) NOT NULL' by hand. (Did not help)
2009-09-29 10:05:12 Jan Schneider Version ⇒ FRAMEWORK_3
 
2009-09-29 10:04:46 Jan Schneider Comment #5 Reply to this comment
Oh, and how did you create them in the first place, since there is no 
MDB2_Driver_odbc?
2009-09-29 10:04:09 Jan Schneider Comment #4
State ⇒ Feedback
Reply to this comment
How do the datatree tables look like? Can you dump the table structures?
2009-09-29 10:01:01 Jan Schneider Comment #3 Reply to this comment
When using the share method "sql" horde crashes completely because of
missing MDB2_ODBC driver.
Because there isn't any one.
2009-09-28 15:38:41 c-master (at) freenet (dot) de Comment #2
New Attachment: horde.log Download
Reply to this comment
forgot the attachment....
2009-09-28 15:37:49 c-master (at) freenet (dot) de Comment #1
Type ⇒ Bug
State ⇒ Unconfirmed
Priority ⇒ 2. Medium
Summary ⇒ Wrong datatype "?" to "unsigned bigint" in datatree. \lib\Horde\DataTree\sql.php
Due ⇒ 2009-12-31
Queue ⇒ Horde Framework Packages
Milestone ⇒
Patch ⇒ No
Reply to this comment
Sybase database with horde groupware v 1.2.4. Database attached via 
ODBC. (Works perfectly)

When using the share method "sql" horde crashes completely because of 
missing MDB2_ODBC driver.

When using the share method "dataTree" (as workaround) horde says 
"HORDE [error] [kronolith] DB Error: unknown error: SELECT 
c.datatree_id, c.datatree_name FROM 
horde_datatree..................Umwandeln von Kalender von root auf 
unsigned bigint nicht möglich]................."

As a result no shared calendars are configurable. Calendars could be 
created, but don't appear in calendar-settings...

I strongly believe, the dataTree issue is a real bug. Please solve it

Saved Queries