[limesurvey-developers] DB backup while comfort updating.

Discussion:

Andrea Suisani

2015-04-27 09:09:46 UTC

Hi all,

I'm interested in providing a patch to add a way to backup the DB while
comfort updating a limesurvey installation backed up by PostgreSQL.

I've given a quick glance to the code that implement the MySQL backup
(helpers/admin/backupdb_helper.php) and I see that the helper scan
the db schema searching for all the tables and then dump tables content
and definition using normal sql statements ("select *" for the data and
"SHOW CREATE TABLE" for the definition).

For the PostgreSQL backup I could follow the same approach, but I want
also to propose an alternative way to tackle the problem at hand.

Why not just using external executables (e.g. pg_dump / mysqldump) to perform
the task?

Pros:

- those external tools are built to do this exact task and there's
no way we can write code that is better at performing this kind of
action.

- eliminate the burden of maintaining the helper code.

- don't need to add new credential to authenticate to the db server
since we could use the one provided during the initial setup.

Cons:

- add a new soft dependency (pg_dump/mysqldump have to
be installed)

- if limesurvey is using php strict mode we should symlink
those exec safe_mode_exec_dir

Was this approach already considered and discarded in the past, and
more to the point do you think it is a viable solution to this issue?

regards

Andrea

--
***@opinioni.net
+39 041 97 07 99

Gabriel Jenik

2015-04-27 10:13:46 UTC

Permalink

Hi,

Isn't backup being made automatically when updating.?
I think it 0 is deleted when successfully finished.

Cheers

Post by Andrea Suisani
Hi all,
I'm interested in providing a patch to add a way to backup the DB while
comfort updating a limesurvey installation backed up by PostgreSQL.
I've given a quick glance to the code that implement the MySQL backup
(helpers/admin/backupdb_helper.php) and I see that the helper scan
the db schema searching for all the tables and then dump tables content
and definition using normal sql statements ("select *" for the data and
"SHOW CREATE TABLE" for the definition).
For the PostgreSQL backup I could follow the same approach, but I want
also to propose an alternative way to tackle the problem at hand.
Why not just using external executables (e.g. pg_dump / mysqldump) to perform
the task?
- those external tools are built to do this exact task and there's
no way we can write code that is better at performing this kind of
action.
- eliminate the burden of maintaining the helper code.
- don't need to add new credential to authenticate to the db server
since we could use the one provided during the initial setup.
- add a new soft dependency (pg_dump/mysqldump have to
be installed)
- if limesurvey is using php strict mode we should symlink
those exec safe_mode_exec_dir
Was this approach already considered and discarded in the past, and
more to the point do you think it is a viable solution to this issue?
regards
Andrea
--
+39 041 97 07 99
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
_______________________________________________
limesurvey-developers mailing list
https://lists.sourceforge.net/lists/listinfo/limesurvey-developers

Andrea Suisani

2015-04-27 10:27:31 UTC

Permalink

Hi Gabriel,

Post by Gabriel Jenik
Hi,
Isn't backup being made automatically when updating.?
I think it 0 is deleted when successfully finished.

If you're using any db but mysql, no backup will be performed
by limesurvey during the comfrot upgrade.

look at application/controllers/admin/update.php line 295

https://github.com/LimeSurvey/LimeSurvey/blob/master/application/controllers/admin/update.php#L295

regards,

Andrea

Post by Gabriel Jenik
Cheers
Hi all,
I'm interested in providing a patch to add a way to backup the DB while
comfort updating a limesurvey installation backed up by PostgreSQL.
I've given a quick glance to the code that implement the MySQL backup
(helpers/admin/backupdb_helper.php) and I see that the helper scan
the db schema searching for all the tables and then dump tables content
and definition using normal sql statements ("select *" for the data and
"SHOW CREATE TABLE" for the definition).
For the PostgreSQL backup I could follow the same approach, but I want
also to propose an alternative way to tackle the problem at hand.
Why not just using external executables (e.g. pg_dump / mysqldump) to perform
the task?
- those external tools are built to do this exact task and there's
no way we can write code that is better at performing this kind of
action.
- eliminate the burden of maintaining the helper code.
- don't need to add new credential to authenticate to the db server
since we could use the one provided during the initial setup.
- add a new soft dependency (pg_dump/mysqldump have to
be installed)
- if limesurvey is using php strict mode we should symlink
those exec safe_mode_exec_dir
Was this approach already considered and discarded in the past, and
more to the point do you think it is a viable solution to this issue?
regards
Andrea
--
+39 041 97 07 99
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
_______________________________________________
limesurvey-developers mailing list
https://lists.sourceforge.net/lists/listinfo/limesurvey-developers
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
_______________________________________________
limesurvey-developers mailing list
https://lists.sourceforge.net/lists/listinfo/limesurvey-developers

--
Andrea Suisani
***@opinioni.net
041 97 07 99

Marcel Minke

2015-04-27 10:53:03 UTC

Permalink

Hi @all,

currently Limesurvey only supports MySQL DB backup, be it during
ComfortUpdate or at the admin interface ("SQL" backup icon).

Best regards
Marcel

Post by Andrea Suisani
Hi Gabriel,

Post by Gabriel Jenik
Hi,
Isn't backup being made automatically when updating.?
I think it 0 is deleted when successfully finished.

If you're using any db but mysql, no backup will be performed
by limesurvey during the comfrot upgrade.
look at application/controllers/admin/update.php line 295
https://github.com/LimeSurvey/LimeSurvey/blob/master/application/controllers/admin/update.php#L295
regards,
Andrea

Sam Mousa

2015-04-27 11:02:40 UTC

Permalink

Hi Andrea,

Using external tool limits the usability for people using hosting providers
that disable exec.
If you plan to implement this functionality I would recommend implementing
it in the appropriate schema class. (
https://github.com/LimeSurvey/LimeSurvey/tree/2.06/application/core/db)

While you are at it, feel free to move the backupdb helper code to the
schema as well ;-)

Cheers,

Sam

Andrea Suisani

2015-04-28 10:10:49 UTC

Permalink

Hi Sam,

Post by Sam Mousa
Hi Andrea,
Using external tool limits the usability for people using hosting providers that disable exec.

Fair enough.

I just want to underline that the feature as I described it:

- requires very little modification to the codebase (almost a one-liner)

- and it could be provided only if this two requirements are met (a.k.a something is
better than nothing:

o) external tool has to be installed

o) lime survey has to be able to execute the external toll via exec/system

Now, I'm not an expert in php development but I think that a php script could
determine if a exec/system are enabled and if pg_dump/mysqldump are present in
the system.

That said, If we're going to take the same approach used in the mysql case
I have to mention what I think it is a critical point in postgresql case
maintenance wise:

there's no mysql's "SHOW CREATE TABLE" counterpart in PostgreSQL.

To replicate such functionality you have to fiddle with postgres internal catalogs
and those are not part of a stable "API", they could change without notice
in new releases.

Just to give you a hint here:

http://stackoverflow.com/a/16154183

you'll find the code of a pl/pgsql store procedure you should use to get the
same functionality as mysql's "SHOW CREATE TABLE".

I think it could be possible to achieve that same result using the info stored
into information_schema, this solution is better because infotmation_schema belongs
to the group official ways to access this kind of data.

One last question: is it ok to just backup tables without any other sql objects (e.g. indexes)?

I'm asking because it seems to me that the code that perform the dump of the mysql
db does not take care of indexes.

Post by Sam Mousa
If you plan to implement this functionality I would recommend implementing it in the appropriate schema class. (https://github.com/LimeSurvey/LimeSurvey/tree/2.06/application/core/db)

sure

Post by Sam Mousa
While you are at it, feel free to move the backupdb helper code to the schema as well ;-)

:)

regards,

Andrea

--
***@opinioni.net
+39 041 97 07 99

Denis Chenu

2015-04-28 10:43:38 UTC

Permalink

Hi Andrea,

Post by Andrea Suisani
o) lime survey has to be able to execute the external toll via exec/system

There are a lot of user who don't have access to exec function with PHP.

You need to test if :
- exec is not in disable function
- exec are OK to be used

Denis

Andrea Suisani

2015-04-28 11:15:22 UTC

Permalink

Post by Sam Mousa
Hi Andrea,

Post by Andrea Suisani
o) lime survey has to be able to execute the external toll via exec/system

There are a lot of user who don't have access to exec function with PHP.
- exec is not in disable function
- exec are OK to be used

you're right. I thought checking if exec is enabled was enoguh, but it
doesn't seems the case, we also need to check it could be actually executed.

Though I think that the "implement-the-backup-as-a-plugin" you've proposed
is the bast way forward.

Andrea

Sam Mousa

2015-04-28 10:44:35 UTC

Permalink

Yii has this built-in:
http://www.yiiframework.com/doc/api/1.1/CPgsqlSchema#findColumns-detail

Note that Yii also will allow you to create a new table based on a name and
column lists:
http://www.yiiframework.com/doc/api/1.1/CDbSchema#createTable-detail

Basically we should use those and let the framework deal with database
specific stuff.

This will automatically support all DBMSes; it needs some research to check
how we duplicate keys:
http://www.yiiframework.com/doc/api/1.1/CPgsqlSchema#findConstraints-detail

Hope this helps, cheers,

Sam

Post by Andrea Suisani
Hi Sam,

Post by Sam Mousa
Hi Andrea,
Using external tool limits the usability for people using hosting

providers that disable exec.
Fair enough.
- requires very little modification to the codebase (almost a one-liner)
- and it could be provided only if this two requirements are met (a.k.a something is
o) external tool has to be installed
o) lime survey has to be able to execute the external toll via exec/system
Now, I'm not an expert in php development but I think that a php script could
determine if a exec/system are enabled and if pg_dump/mysqldump are present in
the system.
That said, If we're going to take the same approach used in the mysql case
I have to mention what I think it is a critical point in postgresql case
there's no mysql's "SHOW CREATE TABLE" counterpart in PostgreSQL.
To replicate such functionality you have to fiddle with postgres internal catalogs
and those are not part of a stable "API", they could change without notice
in new releases.
http://stackoverflow.com/a/16154183
you'll find the code of a pl/pgsql store procedure you should use to get the
same functionality as mysql's "SHOW CREATE TABLE".
I think it could be possible to achieve that same result using the info stored
into information_schema, this solution is better because
infotmation_schema belongs
to the group official ways to access this kind of data.
One last question: is it ok to just backup tables without any other sql
objects (e.g. indexes)?
I'm asking because it seems to me that the code that perform the dump of the mysql
db does not take care of indexes.

Post by Sam Mousa
If you plan to implement this functionality I would recommend

implementing it in the appropriate schema class. (
https://github.com/LimeSurvey/LimeSurvey/tree/2.06/application/core/db)
sure

Post by Sam Mousa
While you are at it, feel free to move the backupdb helper code to the

schema as well ;-)
:)
regards,
Andrea
--
+39 041 97 07 99
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
_______________________________________________
limesurvey-developers mailing list
https://lists.sourceforge.net/lists/listinfo/limesurvey-developers

Andrea Suisani

2015-04-28 10:59:59 UTC

Permalink

Hi Sam,

Post by Sam Mousa
http://www.yiiframework.com/doc/api/1.1/CPgsqlSchema#findColumns-detail
http://www.yiiframework.com/doc/api/1.1/CDbSchema#createTable-detail
Basically we should use those and let the framework deal with database specific stuff.
This will automatically support all DBMSes; it needs some research to check how we duplicate keys: http://www.yiiframework.com/doc/api/1.1/CPgsqlSchema#findConstraints-detail
Hope this helps, cheers,

super helpful thanks, I didn't think about it but now that you've mentioned it seems logical
to through yii active record to this kind of stuff.

Andrea

Carsten Schmitz

2015-04-28 10:29:14 UTC

Permalink

Hello Andrea,

the problem with such a dependancy is that it won't work with most
providers as most have the ability to shell_exec any commands
deactivated by PHP - so for a majority of users this won't work.
However for power users this might be an alternative though it is a
security risk at the same time so I am not too happy to implement it in
the core.

For example with MySQL we considered to remove the backup alltogether as
it will fail due to script timeouts with bigger databases, and as you
noticed it will never be as complete as a real backup done by
MySQL/Postgres/MSSQL tools.

So the best option would be the proposal by Denis to make this a plugin.
That way people who absolutely need/want it can install a plugin to gain
this functionailty. Also this functionality could be extended to MySQL
and MSSQL (if the path and execution parameter of the plugin can be
configured easily).

If you have questions please let us know.

Best regards from Hamburg/Germany

Carsten Schmitz

LimeSurvey Project Leader
***@limesurvey.org

http://www.limesurvey.org

Post by Andrea Suisani
Hi Sam,

Post by Sam Mousa
Hi Andrea,
Using external tool limits the usability for people using hosting providers that disable exec.

Fair enough.
- requires very little modification to the codebase (almost a one-liner)
- and it could be provided only if this two requirements are met (a.k.a something is
o) external tool has to be installed
o) lime survey has to be able to execute the external toll via exec/system
Now, I'm not an expert in php development but I think that a php script could
determine if a exec/system are enabled and if pg_dump/mysqldump are present in
the system.
That said, If we're going to take the same approach used in the mysql case
I have to mention what I think it is a critical point in postgresql case
there's no mysql's "SHOW CREATE TABLE" counterpart in PostgreSQL.
To replicate such functionality you have to fiddle with postgres internal catalogs
and those are not part of a stable "API", they could change without notice
in new releases.
http://stackoverflow.com/a/16154183
you'll find the code of a pl/pgsql store procedure you should use to get the
same functionality as mysql's "SHOW CREATE TABLE".
I think it could be possible to achieve that same result using the info stored
into information_schema, this solution is better because infotmation_schema belongs
to the group official ways to access this kind of data.
One last question: is it ok to just backup tables without any other sql objects (e.g. indexes)?
I'm asking because it seems to me that the code that perform the dump of the mysql
db does not take care of indexes.

sure

Post by Sam Mousa
While you are at it, feel free to move the backupdb helper code to the schema as well ;-)

:)
regards,
Andrea

Sam Mousa

2015-04-28 10:50:20 UTC

Permalink

If you make that plugin for power users, check this article:

http://viget.com/extend/backup-your-database-in-git

It will explain how to use GIT to create a versioning system allowing you
to go back in time to any point where you backed up the database, without
growing extremely.

On Tue, Apr 28, 2015 at 12:29 PM, Carsten Schmitz <

Post by Carsten Schmitz
Hello Andrea,
the problem with such a dependancy is that it won't work with most
providers as most have the ability to shell_exec any commands
deactivated by PHP - so for a majority of users this won't work.
However for power users this might be an alternative though it is a
security risk at the same time so I am not too happy to implement it in
the core.
For example with MySQL we considered to remove the backup alltogether as
it will fail due to script timeouts with bigger databases, and as you
noticed it will never be as complete as a real backup done by
MySQL/Postgres/MSSQL tools.
So the best option would be the proposal by Denis to make this a plugin.
That way people who absolutely need/want it can install a plugin to gain
this functionailty. Also this functionality could be extended to MySQL
and MSSQL (if the path and execution parameter of the plugin can be
configured easily).
If you have questions please let us know.
Best regards from Hamburg/Germany
Carsten Schmitz
LimeSurvey Project Leader
http://www.limesurvey.org

Post by Andrea Suisani
Hi Sam,

Post by Sam Mousa
Hi Andrea,
Using external tool limits the usability for people using hosting

providers that disable exec.

Post by Andrea Suisani
Fair enough.
- requires very little modification to the codebase (almost a one-liner)
- and it could be provided only if this two requirements are met (a.k.a

something is

Post by Andrea Suisani
o) external tool has to be installed
o) lime survey has to be able to execute the external toll via

exec/system

Post by Andrea Suisani
Now, I'm not an expert in php development but I think that a php script

could

Post by Andrea Suisani
determine if a exec/system are enabled and if pg_dump/mysqldump are

present in

Post by Andrea Suisani
the system.
That said, If we're going to take the same approach used in the mysql

case

Post by Andrea Suisani
I have to mention what I think it is a critical point in postgresql case
there's no mysql's "SHOW CREATE TABLE" counterpart in PostgreSQL.
To replicate such functionality you have to fiddle with postgres

internal catalogs

Post by Andrea Suisani
and those are not part of a stable "API", they could change without

notice

Post by Andrea Suisani
in new releases.
http://stackoverflow.com/a/16154183
you'll find the code of a pl/pgsql store procedure you should use to get

the

Post by Andrea Suisani
same functionality as mysql's "SHOW CREATE TABLE".
I think it could be possible to achieve that same result using the info

stored

Post by Andrea Suisani
into information_schema, this solution is better because

infotmation_schema belongs

Post by Andrea Suisani
to the group official ways to access this kind of data.
One last question: is it ok to just backup tables without any other sql

objects (e.g. indexes)?

Post by Andrea Suisani
I'm asking because it seems to me that the code that perform the dump of

the mysql

Post by Andrea Suisani
db does not take care of indexes.

Post by Sam Mousa
If you plan to implement this functionality I would recommend

implementing it in the appropriate schema class. (
https://github.com/LimeSurvey/LimeSurvey/tree/2.06/application/core/db)

Post by Andrea Suisani
sure

Post by Sam Mousa
While you are at it, feel free to move the backupdb helper code to the

schema as well ;-)

Post by Andrea Suisani
:)
regards,
Andrea

------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
_______________________________________________
limesurvey-developers mailing list
https://lists.sourceforge.net/lists/listinfo/limesurvey-developers

Andrea Suisani

2015-04-28 11:04:47 UTC

Permalink

Hi Carsten,

got it.

Post by Carsten Schmitz
For example with MySQL we considered to remove the backup alltogether as
it will fail due to script timeouts with bigger databases, and as you
noticed it will never be as complete as a real backup done by
MySQL/Postgres/MSSQL tools.
So the best option would be the proposal by Denis to make this a plugin.
That way people who absolutely need/want it can install a plugin to gain
this functionailty. Also this functionality could be extended to MySQL
and MSSQL (if the path and execution parameter of the plugin can be
configured easily).

ok.

this is a clear roadmap.

Post by Carsten Schmitz
If you have questions please let us know.

I will surely have somes as soon as I'll start looking at the plugins
architecture.

Andrea

Denis Chenu

2015-04-27 12:06:49 UTC

Permalink

Hi,

Think a external plugin can do automatic backup. Using cron event.

And an external plugin can ask support pg_dump/mysqldump easily.

Denis