Hi all. I recently had an idea that I would like to implement in phpmyadmin. In the development docs it is mentioned that ideas about new features should be discussed on the list before implementing it.
I was working on MySQL (with phpmyadmin) in my last job and I faced some problems. Basically when I entered an incorrect query it led to data loss and there was no way to recover it. I searched online and there were a few people like me who also experienced similar problems. Recovery is only possible when there is data backup and only to the extent of the frequency of the backups.
Having read about binary logs in MySQL I came up with the idea to implement a solution that is able to recover the database by undoing only the offending queries. However, to implement this it would be necessary to have a web interface to view and select the statements that need to be undone. I felt that implementing it as a part of phpmyadmin would be better than implementing a standalone solution because it would gel very well with it. Also I want this project to be open source.
I don't have a lot of experience so I don't know if this is a good idea or if there is a better approach to solve this. I would very much like your suggestions on this.
Thanks, Ashish
Get Outlook for Androidhttps://aka.ms/ghei36
Hello
ashish kakar píše v Po 26. 12. 2016 v 15:06 +0000:
Hi all. I recently had an idea that I would like to implement in phpmyadmin. In the development docs it is mentioned that ideas about new features should be discussed on the list before implementing it.
I was working on MySQL (with phpmyadmin) in my last job and I faced some problems. Basically when I entered an incorrect query it led to data loss and there was no way to recover it. I searched online and there were a few people like me who also experienced similar problems. Recovery is only possible when there is data backup and only to the extent of the frequency of the backups.
Having read about binary logs in MySQL I came up with the idea to implement a solution that is able to recover the database by undoing only the offending queries. However, to implement this it would be necessary to have a web interface to view and select the statements that need to be undone. I felt that implementing it as a part of phpmyadmin would be better than implementing a standalone solution because it would gel very well with it. Also I want this project to be open source.
I don't have a lot of experience so I don't know if this is a good idea or if there is a better approach to solve this. I would very much like your suggestions on this.
The idea is indeed interesting, however I am afraid that the binary logs do not have the information to rollback the transaction as they are only interested in moving forward (replaying transaction on replicated hosts). So you can certainly figure out what query has been executed, but there is no way to get the old data.
I was thinking about this, while it is "wrong" for the reasons stated it could be modified into an "are you sure". Delete and update queries are the "dangerous" ones, and both of these have where clauses, how hard would it be to say "you'll potentially bugger up 1,000,000 rows, see result set below".
Alec
On 02/01/17 14:33, Michal Čihař wrote:
Hello
ashish kakar píše v Po 26. 12. 2016 v 15:06 +0000:
Hi all. I recently had an idea that I would like to implement in phpmyadmin. In the development docs it is mentioned that ideas about new features should be discussed on the list before implementing it.
I was working on MySQL (with phpmyadmin) in my last job and I faced some problems. Basically when I entered an incorrect query it led to data loss and there was no way to recover it. I searched online and there were a few people like me who also experienced similar problems. Recovery is only possible when there is data backup and only to the extent of the frequency of the backups.
Having read about binary logs in MySQL I came up with the idea to implement a solution that is able to recover the database by undoing only the offending queries. However, to implement this it would be necessary to have a web interface to view and select the statements that need to be undone. I felt that implementing it as a part of phpmyadmin would be better than implementing a standalone solution because it would gel very well with it. Also I want this project to be open source.
I don't have a lot of experience so I don't know if this is a good idea or if there is a better approach to solve this. I would very much like your suggestions on this.
The idea is indeed interesting, however I am afraid that the binary logs do not have the information to rollback the transaction as they are only interested in moving forward (replaying transaction on replicated hosts). So you can certainly figure out what query has been executed, but there is no way to get the old data.
Developers mailing list Developers@phpmyadmin.net https://lists.phpmyadmin.net/mailman/listinfo/developers
Hi
Alec Teal píše v Po 02. 01. 2017 v 14:40 +0000:
I was thinking about this, while it is "wrong" for the reasons stated it could be modified into an "are you sure". Delete and update queries are the "dangerous" ones, and both of these have where clauses, how hard would it be to say "you'll potentially bugger up 1,000,000 rows, see result set below".
Honestly I don't think we should add more confirmations for people writing SQL themselves. Most of them would be annoyed by this and would turn of the confirmations immediately. The rest would keep them open, but would get used to click on them after every query, so I doubt this will serve the purpose well.
Obviously you can do harm with SQL, but there is no way we could figure out whether given query is actually dangerous or not. For example when the query involves stored procedures or triggers, you have no way to figure out from the query itself what it actually does.
We already do confirmations for some (DROP/DELETE/TRUNCATE) queries and I think this is good enough.
PS: This feature was broken in half of the places, I've just fixed it: https://github.com/phpmyadmin/phpmyadmin/commit/248a42af7efef7d54b446cc faa25c99c7e6853ac
-
Alec Teal -
ashish kakar -
Michal Čihař