Pionium's picture
Pionium - Sat, 2011/03/19 - 00:36

Lamp Stack - MySQL - TKLBAM

Hi there, I'd like to start with an apology. I'm sorry for being late to the party! I've only just discovered TurnKey, and I am so excited and impressed with it. What a great product.

Now for my problem: I have just started a new piece of development, and I wanted to transfer the work from one development host to another, so I ran a backup on the source appliance, and then performed a restore on the target.

All looked fine, everything I expected transferred across... EXCEPT the database view. Tables, Routines and data all transferred perfectly, but the views (the one and only view) did not. Is there a known issue with this, or have I done something wrong?

Thanks in advance

Andrew

Forum: 
Support
Tags: 
lamp
tklbam
mysql
restore
backup
view
Liraz Siri's picture

Liraz Siri - Sun, 2011/03/20 - 05:53
Thanks for reporting this. I admit I didn't take database views into account in my testing so this might be a bug. I have a round of development scheduled for tklbam so hopefully I'll take a look at this soon. In the meantime as a workaround you could add a TKLBAM hook (see /etc/tklbam/hooks) that creates the missing view following a restore.
Pionium's picture

Pionium - Sun, 2011/03/20 - 14:37

Thank you for your advice on this. I will look into putting a hook in place to backup and restore the missing view.

In my case it is not too critical, since it is currently only a single view, but as my development progresses, there will be more.

Once again, thanks for your help

Andrew

Logan Chase's picture

Logan Chase - Thu, 2011/03/24 - 19:05

i have the same problem. did you manage to solve it? i have lots of views and they not being backed up or restored.

how do you write a hook to save and restore mysql views? the example hook isnt very helpful.

logan

Pionium's picture

Pionium - Fri, 2011/03/25 - 21:41

Hi Logan,

No, I haven't solved this yet. I tried a couple of times unsuccessfully to create pre-backup and post-restore hooks to transfer the view across, but it didn't work.

Since I only have a single view, I didn't worry about it, as I can easily recreate it after the restore. Hopefully this bug will be fixed when I have more views.

Since you have more views, perhaps you will have more success in your investigations. Good luck. If you do find the solution, then please let me know.

All the best

Andrew

Liraz Siri's picture

Liraz Siri - Tue, 2011/12/06 - 00:25

Still on my todo list. Should be getting back to TKLBAM development real soon now.

Liraz Siri's picture

Liraz Siri - Wed, 2013/11/20 - 04:23

Support for MySQL views and triggers support was added to TKLBAM 1.3:

https://github.com/turnkeylinux/tracker/issues/7

Thanks to Pionium, Logan and Marko for reporting this.

Jeremy Davis's picture

Jeremy Davis - Fri, 2017/04/21 - 02:36
Firstly, sorry for the slow reply. I have opened a new bug on our issue tracker. If you'd care to add more info directly there, please feel free. Otherwise posting back here is fine too.

Can you please tell me what version of TurnKey and TKLBAM you are using? Please give details for both the backup source system (i.e. where the backup came from) and the target system (i.e. where you restored the backup to).

FYI the info is easiest gathered from the commandline, e.g. on my local TKLDev (lines starting with '#' are the commands from a root shell): 

# turnkey-version
turnkey-tkldev-14.1-jessie-amd64
# apt-cache policy tklbam
tklbam:
  Installed: 1.4.1+17+g71478bd
  Candidate: 1.4.1+17+g71478bd
  Version table:
 *** 1.4.1+17+g71478bd 0
        999 http://archive.turnkeylinux.org/debian/ jessie/main amd64 Packages
        100 /var/lib/dpkg/status

Also, it's probably a moot point now (~3 weeks too late), but you can roll back a restore. Obviously that's only relevant if you are restoring to the same machine as the backup came from, but it may be worth knowing.

I'm guessing from your post you probably don't, but if you still have access to the original DB (i.e. a full dump direct from MySQL - not what's in the backup), is it possible for you to sanitise it and share it with us (privately)? Also a copy of the DB dumped from the restored system would also be useful. If so (either the original DB or both), please email it/them to "support AT turnkleylinux.org". Then we can do some testing and try to understand why it didn't work for you. FWIW since TKLBAM v1.3 was released (almost 4 years ago) you are the first to report issues with MySQL views not restoring properly. So my guess is that there is either some recent regression or some edge case in your scenario that we aren't accounting for.

For future reference, I recommend regular testing of backups. You can do a restore (followed by a rollback) on the same machine if you'd like (and it's probably worth testing too) but personally I highly recommend at least occasional restore to a new machine (of the same TurnKey version, or at least the same major version).

Actually many TurnKey users use TKLBAM as a means to migrate data between a "dev" instance (often a local VM) and a "production" instance (often a Hub server). Using that sort of workflow has the bonus advantage of making regular testing of your backups a regular event. It also makes upgrading TurnKey versions (at least the initial testing/auditing phase as noted in the docs) an easy addition.

Also for future reference, you are best off starting a new thread (and link to a previous thread if you think it's relevant). You will get a much quicker response that way as unanswered threads, or new comments on active threads generally get priority over new posts to old threads.

Add new comment