[xwiki-devs] Ideas on preventing problems in the database caused by a bug (e.g. XWIKI-1841)
15 Nov
2007
15 Nov
'07
9:16 p.m.
Hi,
due to the translation bug (XWIKI-1841) I spent some time looking for
inconsistent rows in our database (Postgres 8.1) and I found several
documents inserted twice into the database, which lead to problems when
editing and viewing the documents. For luck our coporate wiki has just 23
pages with problems caused by this bug (the same xwd_fullname with different
xwd_id fields / the select statement can be found at the end of this mail,
perhaps anyone is intrested in).
The thing I'm quite confused about is that there are no unique constraints set
on the the columns which could have prevented these problems. The DBMS would
have forced the XWiki core to generate an error instead of inserting
inconsistent data sets into the database.
I neither know much about Hibernate, nor I have tested whether XWiki would use
constraints with other DBMS, but would it be possible to set those
constraints in future versions?
At the moment I'm quite scared about reactivating I18N for our corporate wiki,
because an similar programming might occour again, but with a clean
constraint setup, those errors would not happend silently in the background,
but instead appear as they occur transparent to everyone.
From my point of view it should be possible to set an unique constraint on
xwd_fullname and xwd_language. Up to now I did't understand why there is a
need for the fields xwd_default_language and xwd_translation, but on the
other I didn't investigate any time in this. Wouldn't it be enough to have
the fields xwd_fullname and xwd_language? (The next thing is: Why is there a
need for xwd_web, xwd_name and xwd_fullname? - Just because I'm curious.)
Are there any similar thoughs? What do you think? For a modern DBMS (not MySQL
3.23 ;)) shoud be able to "support" XWiki and to prevent inconsistencies and
corruption of data.
But nevertheless I love XWiki 1.1! The renaming feature and the panel support
are great. Just these two new features made any problems during the migration
from 0.9.840 forgotten. :)
The mentioned query from above for searching inconsistent rows in the database
(working for Postgres 8.1):
select xwd_fullname, xwd_version
from xwikidoc
where xwd_fullname in
(select xwd_fullname
from xwikidoc
group by xwd_fullname
having count(*) > 1)
order by xwd_fullname;
--
Best regards,
Fabian.
20 Nov
20 Nov
1:58 a.m.
Fabian Gorsler wrote:
A flaw in the initial design...
xwd_translation isn't really needed, but
we need a way to specify the default language. There is an idea to store
this not in the document table, but in a document index table, so that
changing the default language will be easier, among other things. But
that is for another version of XWiki, and then indeed it will be enough
to have only xwd_fullname and xwd_language.
Hi,
due to the translation bug (XWIKI-1841) I spent some time looking for
inconsistent rows in our database (Postgres 8.1) and I found several
documents inserted twice into the database, which lead to problems when
editing and viewing the documents. For luck our coporate wiki has just 23
pages with problems caused by this bug (the same xwd_fullname with different
xwd_id fields / the select statement can be found at the end of this mail,
perhaps anyone is intrested in).
The thing I'm quite confused about is that there are no unique constraints set
on the the columns which could have prevented these problems. The DBMS would
have forced the XWiki core to generate an error instead of inserting
inconsistent data sets into the database.
I neither know much about Hibernate, nor I have tested whether XWiki would use
constraints with other DBMS, but would it be possible to set those
constraints in future versions?
We can't make only xwd_fullname unique, as the same document can have
more than one translation, so we need to put a unique constraint on the
(xwd_fullname, xwd_language) pair.
At the moment I'm quite scared about reactivating
I18N for our corporate wiki,
because an similar programming might occour again, but with a clean
constraint setup, those errors would not happend silently in the background,
but instead appear as they occur transparent to everyone.
If you pay attention to what language you are editing, there should be
no errors.
From my point
of view it should be possible to set an unique constraint on
xwd_fullname and
xwd_language. Up to now I did't understand why there is a
need for the fields xwd_default_language and xwd_translation, but on the
other I didn't investigate any time in this. Wouldn't it be enough to have
the fields xwd_fullname and xwd_language? (The next thing is: Why is there a
need for xwd_web, xwd_name and xwd_fullname? - Just because I'm curious.)
Because at first there was only xwd_web and xwd_name, and xwd_fullname
was added only for making shorter queries. We still need xwd_web in many
queries, and computing the space name out of xwd_fullname in SQL is not
such a valid option (due to performance).
Are there any similar thoughs? What do you think? For
a modern DBMS (not MySQL
3.23 ;)) shoud be able to "support" XWiki and to prevent inconsistencies and
corruption of data.
But nevertheless I love XWiki 1.1! The renaming feature and the panel support
are great. Just these two new features made any problems during the migration
from 0.9.840 forgotten. :)
The mentioned query from above for searching inconsistent rows in the database
(working for Postgres 8.1):
select xwd_fullname, xwd_version
from xwikidoc
where xwd_fullname in
(select xwd_fullname
from xwikidoc
group by xwd_fullname
having count(*) > 1)
order by xwd_fullname;
Sergiu
22 Nov
22 Nov
8:48 p.m.
New subject: [xwiki-devs] Ideas on preventing problems in the database caused by a bug (e.g. XWIKI-1841)
Hi Sergiu,
On Tuesday 20 November 2007 01:58:07 Sergiu Dumitriu wrote:
We can't make only xwd_fullname unique, as the
same document can have
more than one translation, so we need to put a unique constraint on the
(xwd_fullname, xwd_language) pair.
that's what I meant. I will set the constraint on our staging area and test
how the wiki behaves.
A flaw in the initial design... xwd_translation
isn't really needed, but
we need a way to specify the default language. There is an idea to store
this not in the document table, but in a document index table, so that
changing the default language will be easier, among other things. But
that is for another version of XWiki, and then indeed it will be enough
to have only xwd_fullname and xwd_language.
Because at first there was only xwd_web and xwd_name,
and xwd_fullname
was added only for making shorter queries. We still need xwd_web in many
queries, and computing the space name out of xwd_fullname in SQL is not
such a valid option (due to performance).
Thanks for the information and thanks for your response!
--
Best regards,
Fabian.
6143
days inactive
6150
days old
2 comments
2 participants
participants (2)
-
Fabian Gorsler -
Sergiu Dumitriu