State of the MySQL support

[Mario Frank]

Hey Guys and Ladies,

as you probably have seen the current problem with moving tags in the
hierarchy making
the DB corrupt, I would like to address this in a broader way.

I am aware that the MySQL support is experimental (for a quite long time).
But I also experience that users complaining about problems in
combination with MySQL
becomes more frequent. The long-established devs of you may have other
experiences,
so correct me if my experience is due to my quite short time in the
digiKam world.

As I am trying to fix the current problem, this is no overall solution
to the experimental state.

So, what I like to address is the following:
1) Do we have some "specialists" in MySQL in our ranks? I am no
specialist here, I am more experienced in postgres.
2) Could we determine how many users are affected by such problems, i.e.
what is the ratio of MySQL users?
3) What are the plans for MySQL support?
4) Is further support for other DBMS desirable (I think of postgres)?

Regards,
Mario

[Gilles Caulier-4]

2017-08-09 21:27 GMT+02:00 Mario Frank <[hidden email]>:

> Hey Guys and Ladies,
>
> as you probably have seen the current problem with moving tags in the
> hierarchy making
> the DB corrupt, I would like to address this in a broader way.
>
> I am aware that the MySQL support is experimental (for a quite long time).
> But I also experience that users complaining about problems in
> combination with MySQL
> becomes more frequent. The long-established devs of you may have other
> experiences,
> so correct me if my experience is due to my quite short time in the
> digiKam world.
>
> As I am trying to fix the current problem, this is no overall solution
> to the experimental state.
>
> So, what I like to address is the following:
> 1) Do we have some "specialists" in MySQL in our ranks? I am no
> specialist here, I am more experienced in postgres.

Richard Mortimer <richm at oldelvet dot org dot uk>, normally in this
room is the expert in Mysql. He has already posted a lots of patches
to improve DB backend stability and schema. He also guide Swati while
summer 2016.

> 2) Could we determine how many users are affected by such problems, i.e.
> what is the ratio of MySQL users?

Good question. The only way is to check bugzilla. As i reroute all
reports by sub-categories, it can be more easy. There is a
database-mysql component in bugzilla.

> 3) What are the plans for MySQL support?

To improve and finalize. This is important especialy to have a network
backend with multi-users supports (as multi-digiKam clients running on
different computer / OS). This is the typical use case in photo
agency.

> 4) Is further support for other DBMS desirable (I think of postgres)?

There is an entry in bugzilla to add postgressql support. The low
level must be easy as a Qt plugin exist. The most complicated is to
write a schema compatible with this kind of DB. We need another expert
here.

Gilles

[Marcel Wiesweg]

My opinion: MySQL support is important, to support network setups, to address
possible performance problems and issues with parallel access inherent to
sqlite.
The classification of "experimental" is due to the fact that none of us
(inactive me included) ever managed to make this bug-free.

On the technical side, I believe most problems come from attempts to find
replacement constructs for sqlite triggers. Here I would say: if there is no
solution for a certain problem in MySQL, it may be solved by going away from
triggers and do things in code. For example triggers on deletion, this can be
solved from C++ within an transaction.
The most difficult problem is the tags tree I think, here I do not have a good
idea.

Marcel

> Hey Guys and Ladies,
>
> as you probably have seen the current problem with moving tags in the
> hierarchy making
> the DB corrupt, I would like to address this in a broader way.
>
> I am aware that the MySQL support is experimental (for a quite long time).
> But I also experience that users complaining about problems in
> combination with MySQL
> becomes more frequent. The long-established devs of you may have other
> experiences,
> so correct me if my experience is due to my quite short time in the
> digiKam world.
>
> As I am trying to fix the current problem, this is no overall solution
> to the experimental state.
>
> So, what I like to address is the following:
> 1) Do we have some "specialists" in MySQL in our ranks? I am no
> specialist here, I am more experienced in postgres.
> 2) Could we determine how many users are affected by such problems, i.e.
> what is the ratio of MySQL users?
> 3) What are the plans for MySQL support?
> 4) Is further support for other DBMS desirable (I think of postgres)?
>
> Regards,
> Mario

[Gilles Caulier-4]

Mario, Marcel,

Look here :

https://bugs.kde.org/show_bug.cgi?id=355831#c76

Richard has posted an important patch about Mysql schema improvement.
Read well the comments.I tested the patch and still some
dysfunctions.But it's a first step...

Gilles

2017-08-10 19:15 GMT+02:00 Marcel Wiesweg <[hidden email]>:

> My opinion: MySQL support is important, to support network setups, to address
> possible performance problems and issues with parallel access inherent to
> sqlite.
> The classification of "experimental" is due to the fact that none of us
> (inactive me included) ever managed to make this bug-free.
>
> On the technical side, I believe most problems come from attempts to find
> replacement constructs for sqlite triggers. Here I would say: if there is no
> solution for a certain problem in MySQL, it may be solved by going away from
> triggers and do things in code. For example triggers on deletion, this can be
> solved from C++ within an transaction.
> The most difficult problem is the tags tree I think, here I do not have a good
> idea.
>
> Marcel
>
>
>
>> Hey Guys and Ladies,
>>
>> as you probably have seen the current problem with moving tags in the
>> hierarchy making
>> the DB corrupt, I would like to address this in a broader way.
>>
>> I am aware that the MySQL support is experimental (for a quite long time).
>> But I also experience that users complaining about problems in
>> combination with MySQL
>> becomes more frequent. The long-established devs of you may have other
>> experiences,
>> so correct me if my experience is due to my quite short time in the
>> digiKam world.
>>
>> As I am trying to fix the current problem, this is no overall solution
>> to the experimental state.
>>
>> So, what I like to address is the following:
>> 1) Do we have some "specialists" in MySQL in our ranks? I am no
>> specialist here, I am more experienced in postgres.
>> 2) Could we determine how many users are affected by such problems, i.e.
>> what is the ratio of MySQL users?
>> 3) What are the plans for MySQL support?
>> 4) Is further support for other DBMS desirable (I think of postgres)?
>>
>> Regards,
>> Mario
>

[Mario Frank]

Yes, I saw that some time ago.

But what confuses me: There are triggers in MySQL (5.5) and also in
MariaDB (10)

https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

https://mariadb.com/kb/en/mariadb/create-trigger/

I do not know since when triggers are supported in these systems. But at
least since

the mentioned versions.

Potentially, it is now possible to switch to triggers for MySQL.

Cheers,

Mario


Am 10.08.2017 um 21:23 schrieb Gilles Caulier:

> Mario, Marcel,
>
> Look here :
>
> https://bugs.kde.org/show_bug.cgi?id=355831#c76
>
> Richard has posted an important patch about Mysql schema improvement.
> Read well the comments.I tested the patch and still some
> dysfunctions.But it's a first step...
>
> Gilles
>
> 2017-08-10 19:15 GMT+02:00 Marcel Wiesweg <[hidden email]>:
>> My opinion: MySQL support is important, to support network setups, to address
>> possible performance problems and issues with parallel access inherent to
>> sqlite.
>> The classification of "experimental" is due to the fact that none of us
>> (inactive me included) ever managed to make this bug-free.
>>
>> On the technical side, I believe most problems come from attempts to find
>> replacement constructs for sqlite triggers. Here I would say: if there is no
>> solution for a certain problem in MySQL, it may be solved by going away from
>> triggers and do things in code. For example triggers on deletion, this can be
>> solved from C++ within an transaction.
>> The most difficult problem is the tags tree I think, here I do not have a good
>> idea.
>>
>> Marcel
>>
>>
>>
>>> Hey Guys and Ladies,
>>>
>>> as you probably have seen the current problem with moving tags in the
>>> hierarchy making
>>> the DB corrupt, I would like to address this in a broader way.
>>>
>>> I am aware that the MySQL support is experimental (for a quite long time).
>>> But I also experience that users complaining about problems in
>>> combination with MySQL
>>> becomes more frequent. The long-established devs of you may have other
>>> experiences,
>>> so correct me if my experience is due to my quite short time in the
>>> digiKam world.
>>>
>>> As I am trying to fix the current problem, this is no overall solution
>>> to the experimental state.
>>>
>>> So, what I like to address is the following:
>>> 1) Do we have some "specialists" in MySQL in our ranks? I am no
>>> specialist here, I am more experienced in postgres.
>>> 2) Could we determine how many users are affected by such problems, i.e.
>>> what is the ratio of MySQL users?
>>> 3) What are the plans for MySQL support?
>>> 4) Is further support for other DBMS desirable (I think of postgres)?
>>>
>>> Regards,
>>> Mario

[Richard Mortimer-2]

The problem with triggers in MySQL is that you quite often need elevated
privilege to set them up. That becomes a problem for an end-user program
especially when it comes time to update the database etc.

I very much thing that the more practical way is to implement a set of
statements that get wrapped in a transaction within the digikam xml/SQL
setup. I have not looked at the tags code for a while (been very busy
with other things in the past year) but thought that I had produced
enough SQL that would emulate the use of tags. From memory there is also
a database check/repair function in digikam now and it would probably be
a good idea to give that the capability of regenerating the lft and rgt
values in the tags tree.

Regards

Richard

On 11/08/2017 22:36, Mario Frank wrote:

> Yes, I saw that some time ago.
>
> But what confuses me: There are triggers in MySQL (5.5) and also in
> MariaDB (10)
>
> https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html
>
> https://mariadb.com/kb/en/mariadb/create-trigger/
>
> I do not know since when triggers are supported in these systems. But at
> least since
>
> the mentioned versions.
>
> Potentially, it is now possible to switch to triggers for MySQL.
>
> Cheers,
>
> Mario
>
>
> Am 10.08.2017 um 21:23 schrieb Gilles Caulier:
>> Mario, Marcel,
>>
>> Look here :
>>
>> https://bugs.kde.org/show_bug.cgi?id=355831#c76
>>
>> Richard has posted an important patch about Mysql schema improvement.
>> Read well the comments.I tested the patch and still some
>> dysfunctions.But it's a first step...
>>
>> Gilles
>>
>> 2017-08-10 19:15 GMT+02:00 Marcel Wiesweg <[hidden email]>:
>>> My opinion: MySQL support is important, to support network setups, to address
>>> possible performance problems and issues with parallel access inherent to
>>> sqlite.
>>> The classification of "experimental" is due to the fact that none of us
>>> (inactive me included) ever managed to make this bug-free.
>>>
>>> On the technical side, I believe most problems come from attempts to find
>>> replacement constructs for sqlite triggers. Here I would say: if there is no
>>> solution for a certain problem in MySQL, it may be solved by going away from
>>> triggers and do things in code. For example triggers on deletion, this can be
>>> solved from C++ within an transaction.
>>> The most difficult problem is the tags tree I think, here I do not have a good
>>> idea.
>>>
>>> Marcel
>>>
>>>
>>>
>>>> Hey Guys and Ladies,
>>>>
>>>> as you probably have seen the current problem with moving tags in the
>>>> hierarchy making
>>>> the DB corrupt, I would like to address this in a broader way.
>>>>
>>>> I am aware that the MySQL support is experimental (for a quite long time).
>>>> But I also experience that users complaining about problems in
>>>> combination with MySQL
>>>> becomes more frequent. The long-established devs of you may have other
>>>> experiences,
>>>> so correct me if my experience is due to my quite short time in the
>>>> digiKam world.
>>>>
>>>> As I am trying to fix the current problem, this is no overall solution
>>>> to the experimental state.
>>>>
>>>> So, what I like to address is the following:
>>>> 1) Do we have some "specialists" in MySQL in our ranks? I am no
>>>> specialist here, I am more experienced in postgres.
>>>> 2) Could we determine how many users are affected by such problems, i.e.
>>>> what is the ratio of MySQL users?
>>>> 3) What are the plans for MySQL support?
>>>> 4) Is further support for other DBMS desirable (I think of postgres)?
>>>>
>>>> Regards,
>>>> Mario
>