Category: Database independence (Non-Mysql, ADOdb Postgres, Oracle, etc)

Postgres returns row length error to tiki-view_forum.php upon attempt to write data from long e-mail to a forum with email-to-forum functionality enabled.

Hi all!
I am building a tiki in italian, english, german, french and spanish (not published yet).

My development environment:
- XAMPP 2.5 for Windows
- Apache/2.2.8 (Win32) DAV/2 mod_ssl/2.2.8 OpenSSL/0.9.8g mod_autoindex_color PHP/5.2.5
- PHP Version 5.2.5
- DBMS Postgres 8.3.1 (running remotely on an Ubuntu box)

tikiwiki version: 1.9.10.1

I'm having a problem using the i18n feature: if I activate "Multilingual" and I select "Italiano (it)", and then I try to update my homepage, I get the following error message:

---
Warning: postgres7 error: ERROR: operator does not exist: bigint = character varying LINE 1: ...cts" as t2 LEFT JOIN "tiki_pages" p ON p."page_id"=t2."objId... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in query: select t2."objId", t2."lang", p."pageName"as "objName" from "tiki_translated_objects" as t1, "tiki_translated_objects" as t2 LEFT JOIN "tiki_pages" p ON p."page_id"=t2."objId" where t1."traId"=t2."traId" and t2."objId"!= t1."objId" and t1."type"=? and t1."objId"=?
in C:\xampp\htdocs\tikiwiki\lib\tikidblib.php on line 134

Context:
File tiki-index.php
Url tiki-index.php
Query:
select t2."objId", t2."lang", p."pageName"as "objName" from "tiki_translated_objects" as t1, "tiki_translated_objects" as t2 LEFT JOIN "tiki_pages" p ON p."page_id"=t2."objId" where t1."traId"=t2."traId" and t2."objId"!= t1."objId" and t1."type"=? and t1."objId"=?
Values:
0 wiki page
1 2
Message:
Error Message ERROR: operator does not exist: bigint = character varying LINE 1: ...cts" as t2 LEFT JOIN "tiki_pages" p ON p."page_id"=t2."objId... ^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

---

The some operation, running on a MySql5 based installation, doesn't happen.
Which is my fault ? What another thing I must check and where ?
Thanks in advance !

Giuseppe

When user files area is empty it causes error displaying the content.
My tikiwiki is 1.9.1 with php5 and Postgresql 8.x
For more details see --> https://sourceforge.net/tracker/index.php?func=detail&aid=1283628&group_id=64258&atid=506846

when downloading image/jpeg type of user avatar it fails due to problem in execution of SQL query. It seems that binary uploads that for sure contains special characters destroys the SQL statament to be queries.
My TikiWiki is 1.9.1 configured with Postgersql 8.x
For more info see --> https://sourceforge.net/tracker/index.php?func=detail&aid=1283624&group_id=64258&atid=506846

using long Chinese characters as browser title will cause the browser title borken, for example, if you use '机电工程师' as browser title, the browser will show '机电工程帛'.

it's maybe an adodb problem.

{syntax type="tiki" editor="plain"}
Hello,

!A problem completely identified
This is a new formulation of 3029 and 3057 problem after near 200 hours of test and debug.

!The problem is now precise and (I hope so) near solution (but depends of quality team (this part has been rewrite for 5.0):

__The solution that I propose establish the compatibility of 4.2 with php 5.3.2.__

!!Not alone may be
!!!Can't end test of changes to submit (just tiki-db-dbo.php)

I can't affirm that I wrote a complete debug version because the execution stop on another problem (track [http://dev.tikiwiki.org/tiki-view_tracker_item.php?trackerId=5&itemId=3115], so it is not ended. I can't be sure that everything is OK.

!!!Note about time spent to be able to participate efficiently to the team

The time spend is not only on TikiWiki, a had to solved installation problems and find a bug into easyphp and addresses resolution on the server.

The problem began with 4.1 and php 5.3.0 and I upgrade to 4.2 and 5.3.2 to be sure of the problem and the available solution.

!Span of the problem :
# Definition of main classes TikiDb and TikiDb_Pdo and PDO->function execution
# Progs : tiki-db-pdo.php, tikisession-pdo.php and naturally pdo.php, tikiDb.php
# PDO extension and behavior of objects and classes

!Description of the nature of the problem and is generation

note : Because I found the nature of the problem, I don't start from the nature of the crash but the analysis, but I produce a selected track made with xdebug var_dump)

# In tiki-db-pdo.php:40 we create the Pdo object
<$dbTiki = new PDO("$db_tiki:$db_hoststring;dbname=$dbs_tiki", $user_tiki, $pass_tiki); >

# just later In tiki-db-pdo.php:49 we set the singleton of TikiDb_Pdo into TikiDb abstract class with scope operator
<TikiDb::set( new TikiDb_Pdo( $dbTiki ) ); >

# further we call In tikisession-pdo.php:28 we can find the session read
< $sth = TikiDb::get()->prepare($qry); >
This instruction generates the fatal error :
__ Fatal error: Call to undefined method TikiDb_Pdo::prepare() in D:\Trebly\Teawik-ld8-422a\lib\tikisession-pdo.php on line 28__

!Explanation and solution implemented
The reason is simple and explained by the error comment :
we try to run the prepare function which is a PDO function on a TikiDb_Pdo object which has no reason to inherit of these Pdo function even <TikiDb::get()->db->prepare> could have a meaning.

Then the solution is in what is made in ZEND lib (not yet used in 4.2), create an interface to PDO into Class TikiDb_Pdo.
This functions and it is written.

! A major question
One important question is why and how the same instructions seems to be available and functions well on Linux (I have a site which runs normally with 4.1, not yet upgraded to 4.2, a to-do)

I believe that the actual solution that I have developed for 4.2 on Windows with PDO (compiled) extension, should be extended to the normal version.

As I have said before I can't confirm completely that the solution is correctly tested and free of bug because, the program have a FATAL ERROR PDOException: SQLSTATE[HY093] later on the prefs restoration I create another report for this.

!!!Complements of information (working around finding and solving bugs):
Note 1 (complement, I will made another little track for this) :
the execute statement written execute() without parameter generates each time a warning message when xdebug is used and php error management activated with STRICT option (default in 5.3.2). This makes necessarily the code lightly slower.

Note 2 :
I developed for other debug for this an enhanced error template according to the error handler which displays much more informations about the context and, into these, the parameter (title) of execute statement. This make an enhancement to find much more quickly the errors.
I will implement a test so that it should be displayed only for administrator.

{syntax type="tiki" editor="plain"}
Hello,

These is a partial solution to ticket 3029 which I can't access.
I was wrong in my search of causes of the problem.

__The installation in my configuration described in 3029 don't supports the following :__

The TikiWiki sequence with tiki-setup_base and tikisession-pdo
first creates the object
$dbTiki = new PDO(......)
just after seems to extends the class by
TikiDb::set( new TikiDb_Pdo( $dbTiki ) );

Most installations are able to hold the following (tikisession:open)
TikiDb::get()->setAttribute(PDO:....);
TikiDb::get()->getAttribute(PDO:....);

and just after execute tikisession:read with this

$sth=TikiDb::get()->prepare($sql);
$sth->execute();

This don't functions (probably php 3.3.1), the PDO methods and attributes can't be accessed by this mean.

The scope operator is not able to reach PDO (builtin by extension) class PDO in my context :
either windows version (improbable) and php 3.3.1
On my services provider OVH which run php 3.2.x everything functions

Zend uses interfaces and other objects.
In my opinion :
$sth must be set as new object with "PDO_Statement" properties (interface of zend and implementation)
When I implement the good properties and class I go much more further
in the execution.

But how to make function whole 4.1 with this scope operator problem ?

lib/tikilib and lib/score/scorelib.php functions send no timestamp argument to tiki_users_score isert queries. This results in query failure because tiki_users_score tstamp column in Tiki 1.9 distrib. contains a 'not null' constraint.

---


The following PostGres query sets the default timestamp of tiki_users_score alter column tstamp to 'now', which ensures against attempted insertion of NULL values from the tiki codebase:

alter table tiki_users_score alter column tstamp set default now();

This should be migrated to the community site, and handled with ((doc:Organic groups)) and ((doc:User Trackers))

Within lib/stats/statslib.php there is a missing space between an identifier and the WHERE token, for SQLite ADOdb strips out the backtick resulting in an SQL error.

After grepping the code I can see that there are a few places that are going to cause this error.

lib/stats/statslib.php
$query_cant = "select count(*) from `tiki_pages` tp left join `tiki_links` tl on tp.`pageName` = tl.`toPage` left join `tiki_structures` ts on tp.`page_id`= ts.`page_id`where tl.`toPage` IS NULL and `ts`.page_id IS NULL";
$query = "select `pageName`, `hits`, `page_size` as `len` ,`lastModif`, `user`, `ip`, `comment`, `version`, `flag` from `tiki_pages` tp left join `tiki_links` tl on tp.`pageName` = tl.`toPage` left join `tiki_structures` ts on tp.`page_id`= ts.`page_id`where tl.`toPage` IS NULL and `ts`.page_id IS NULL $mid order by ".$this->convert_sortmode($sort_mode);

lib/faqs/faqlib.php
$query = "delete from `tiki_faqs`where `title`=?";

lib/charts/chartlib.php
$query = "delete from `tiki_charts_votes`where `user`=? and `itemId`=?";

lib/admin/adminlib.php
$query = "delete from `tiki_dsn`where `name`=? and `dsn`=?";
$query = "delete from `users_permissions`where `permName`=?";
$query = "delete from `tiki_tags`where `tagName`=? and `pageName`=?";

Several SQL queries only work with mysql because it is not SQL92 compliant. Some bugs are already fixed now but at least the case sensitivity bug is still there.
If you create a table with columns and later use a query you have to use the exact name and not a name with different cases, an example is:
Warning: postgres7 error: ERROR: column i.categoryID does not exist in query:
select i."calitemId" as "calitemId", i."name" as "name", i."description" as "description", i."start" as "start", i."end" as "end", i."url" as "url", i."status" as "status", i."priority" as "priority", c."name" as "calname", i."calendarId" as "calendarId", i."locationId" as "locationID", i."categoryID" as "categoryID", i."nlId" as "nlId" from "tiki_calendar_items" as i left join "tiki_calendars" as c on i."calendarId"=c."calendarId" where ((i."calendarId"=?) and ((i."start" > ? and i."end" < ?) or (i."start" < ? and i."end" > ?)) and ((c."personal"='y' and i."user"=?) or c."personal" != 'y')) order by "start" asc
in /usr/share/tikiwiki/lib/tikidblib.php on line 134

this is because the column is named categoryId and not categoryID.
I think in this example i manually fixed on my side another Id that is locationId where the query used locationID which is not existent.

This not only occurs in the calender section but this is just the sections that i remember where the error occurs.

In the new version of tikiwiki 3.0 is the tiki-3.0-pgsql.sql wrong. It looks like a copy of the mysql file. BUT: not only the pgsql, also the sqlite and oci8-file.

There are still the ENGINE elements, and some special types from mysql like KEY instead of INDEX or Triger for pgsql

{syntax type="tiki" editor="plain"}
Hello,

This is a new formulation of 3029 problem after near 200 hours of test and debug.

!The problem is now precise and near solution :
I can't affirm that I wrote debug because the execution stop on another problem.
The time spend is not only on TikiWiki, a had to solved installation problems and find a bug into easyphp and addresses resolution on the server.
The problem began with 4.1 and php 5.3.0 and I upgrade to 4.2 and 5.3.2 to be sure of the problem and the available solution.

!Span of the problem :
# Definition of main classes TikiDb and TikiDb_Pdo and PDO->function execution
# Progs : tiki-db-pdo.php, tikisession-pdo.php and naturally pdo.php, tikiDb.php
# PDO extension and behavior of objects and classes

!Description
note : Because I found the nature of the problem, I don't start from the nature of the crash but the analysis, but I produce a selected track made with xdebug var_dump)

# In tiki-db-pdo.php:40 we create the Pdo object
<$dbTiki = new PDO("$db_tiki:$db_hoststring;dbname=$dbs_tiki", $user_tiki, $pass_tiki); >

# just later In tiki-db-pdo.php:49 we set the singleton of TikiDb_Pdo into TikiDb abstract class with scope operator
<TikiDb::set( new TikiDb_Pdo( $dbTiki ) ); >

# further we call In tikisession-pdo.php:28 we can find the session read
< $sth = TikiDb::get()->prepare($qry); >
This instruction generates the fatal error :
__ Fatal error: Call to undefined method TikiDb_Pdo::prepare() in D:\Trebly\Teawik-ld8-422a\lib\tikisession-pdo.php on line 28__

!Explanation and solution implemented
The reason is simple and explained by the error comment :
we try to run the prepare function which is a PDO function on a TikiDb_Pdo object which has no reason to inherit of these Pdo function even <TikiDb::get()->db->prepare> could have a meaning.

Then the solution is in what is made in ZEND lib (not yet used), create an interface to PDO into Class TikiDb_Pdo.
This functions and it is written.

! A major question
One important question is why and how the same instructions seems to be available and functions well on Linux (I have a site which runs normally with 4.1, not yet upgraded to 4.2, a to-do)

I believe that the actual solution that I have developed for 4.2 on Windows with PDO (compiled) extension, should be extended to the normal version.

As I have said before I can't confirm completely that the solution is correctly tested and free of bug because, the program have a FATAL ERROR PDOException: SQLSTATE[HY093] later on the prefs restoration I create another report for this.

Note :
the execute statement written execute() without parameter generates each time a warning message when xdebug is activated with STRICT option (default in 5.3.2) for errors handling in php, and necessarily makes the code lightly slower. I developed an enhanced error template according to the error handler which displays much more informations about the context and, into these, the parameter (title) of execute statement. This make an enhancement to find much more quickly the errors.

tiki-backup.php was like performing surgery on yourself. It can work but it's risky.

This would avoid this type of issue:
http://tikiwiki.org/tiki-view_forum_thread.php?topics_offset=1&forumId=6&comments_parentId=21274

Maybe at each release, we could add one test of the DB to see if the most recent sql additions were done.


phplist does this well. Once click and your DB is in the right format.

many object names too long for oracle which has max size of 30 for any object (column, table, sequence, etc.)

able to modify some items such as sequence and trigger that are internal to db and not in tiki code.

initial oracle-1.9-oci8.sql had many errors including the use of datatype integer and auto-increment which doe not exist in oracle. additional insert to blob column not formed to use oracle utl_raw.cast_to_raw. oracle quote in sql field insert should be two quotes ('') instead of backslash quote (\')

main tables still not available
tiki_content_templates_sections
tiki_forums
because outbound_mails_for_inbound_mails column
tiki_live_support_message_comments

is there a workaround that if I change the object name in the database to be <=30, that the code will not abort.

- Usage of "CLOB" in Oracle different from "Text" in MySQL, so usage of every table has to be checked.
(especially where LOB-Index is used as a reference (e.g. tiki_languages))

- Oracle can not use CLOBs for indices so search functionality has to be checked (fulltext index?)

fields defined varchar(256) fail on MySQL 4.0

db/tiki.slq lines 4868 and 4877

the last two tables added: tiki_webmail_contacts_ext and tiki_webmail_contacts_fields

I would fix it if i had check-in powers sorted out (working on it) but i'm not sure that wouldn't it break anything else (in webmail?)