simple_sys_tree deleted
32 views
Skip to first unread message
v.lo...@ifbcon.de
Sep 2, 2016, 1:00:36 AM9/2/16
to Simple Groupware
Hello,
i fear I need some help:
somehow I managed to delete the table simple_sys_tree from the database AND the backup.
Everything is up and running, but the folders are not there anymore.
I checked the database and the simple store, everything is fine, except the folder tree.
I can create and edit new things in every module, but the old ones are invisible
Can anybody help me how to re-arrange the system?
i fear I need some help:
somehow I managed to delete the table simple_sys_tree from the database AND the backup.
Everything is up and running, but the folders are not there anymore.
I checked the database and the simple store, everything is fine, except the folder tree.
I can create and edit new things in every module, but the old ones are invisible
Can anybody help me how to re-arrange the system?
Simple Groupware version: 0.745 Simple Groupware language: de PHP Version: 5.4.36-0+deb7u3 Database + Version: mysql 554 Server OS: Linux svr-ifb 3.2.0-4-amd64 #1 SMP Debian 3.2.63-2+deb7u2 x86_64 Webserver: Apache/2.2.22 (Debian) Webbrowser: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:48.0) Gecko/20100101 Firefox/48.0 APC cache usage: 31 MB / 32 MB Disk usage: 127 GB / 917 GB
Thanks in advance,
Volker
paulzarucki
Sep 3, 2016, 2:04:43 PM9/3/16
to Simple Groupware
Hi,
You can re-create the initial folder tree and personal folders as follows:
1. Make an archive copy of the installation !!
2. Find the order of creation of the user accounts
3. Find the home folder id for each user account
4. Make a fresh install of Simple Groupware on a separate 'temporary' server (which could be a virtual machine)
5. Add the user accounts (which re-creates the initial personal folders for each user)
6. Save the new folder tree
7. Import the new folder tree into the original system
and then some additional steps to finish off:
8. Consolidate the trash (deleted records) into a new folder (which I call OldTrash)
9. Recover any additional folders which were added manually (e.g. additional calendars or contacts folders)
It is likely that one or more user accounts were added to the original system after some manually created folders were added or records deleted (which creates new folders under System/Trash). In this case we need to know each user's home folder ID in order to ensure that the correct folder IDs will be assigned to each user's personal folders. Provided that the portal items which were installed by default into each home folder are still present, we can find the home folder IDs with the help of an SQL query. So, for simplicity, I will assume that these portal items are still present in each user's home folder.
Here is my suggested procedure.
1. MAKE AN ARCHIVE COPY OF THE EXISTING INSTALLATION !
Archive the files (execute the command as the root user):
tar -czf /path-to-archive-directory/sgs-recovery-archive.tar.gz /var/www/sgs
where /var/www/sgs should be replaced by the path to your Simple Groupware installation.
Archive the database (you will need the password for the MySQL 'root' user):
mysqldump --add-drop-table --flush-logs --lock-tables --complete-insert --default-character-set=utf8 -u root -p <sgs-db-name> | gzip > /path-to-archive-directory/sgs-recovery-archive.sql.gz
where <sgs-db-name> should be replaced by your Simple Groupware database name (the above command should be all on one line).
2. FIND THE ORDER AND TIMES OF CREATION OF THE USER ACCOUNTS
Run the following SQL query and record the result.
select id, created, username from simple_sys_users order by id;
3. FIND THE HOME FOLDER ID FOR EACH USER ACCOUNT
NOTE: this relies on the portal entries which were installed in the home folder of each user account when the user accounts were created. If these portal entries were deleted then this wont work.
Run the following SQL query:
select * from simple_portal where createdby="admin" order by id
This should produce a list of portal entries like the following:
id folder url
901 11301 index.php?folder=^home_paulz/!emails&view=display&limit=50
1001 11301 index.php?folder=^calendar_paulz&view=display&markdate=week&today=last mo...
1101 11301 index.php?folder=^tasks_paulz&view=display&find[]=assets|simple_tasks||re...
1201 11301 index.php?folder=^bookmarks_paulz&view=display
1301 11301 index.php?folder=^notes_paulz&view=display
1401 11301 index.php?folder=^home_paulz/!files&view=display&orderby=lastmodified&ord...
1501 13701 index.php?folder=^home_unprivileged/!emails&view=display&limit=50
1601 13701 index.php?folder=^calendar_unprivileged&view=display&markdate=week&today=...
1701 13701 index.php?folder=^tasks_unprivileged&view=display&find[]=assets|simple_ta...
1801 13701 index.php?folder=^bookmarks_unprivileged&view=display
1901 13701 index.php?folder=^notes_unprivileged&view=display
2001 13701 index.php?folder=^home_unprivileged/!files&view=display&orderby=lastmodif...
The URL field gives a clue to the user account name, e.g. "notes_paulz" indicates the Notes item for user paulz while the folder field gives the home folder ID for this user.
Make a note of the home folder ID for each user account.
4. RECREATE THE INITIAL INSTALLATION ON A TEMPORARY SERVER
Perform a fresh install of the same version of Simple Groupware with the same setup choices (especially the choice to include/exclude the demo folders) ON A DIFFERENT SERVER (this could be a virtual machine).
5. RECREATE EACH USERS' INITIAL PERSONAL FOLDERS
Add the user accounts in the same order as they were added to the original system. Before adding each user account, perform the following steps in order to ensure that the user's personal folders will have the correct IDs.
Run the following SQL query:
select 100*(id+1)+1 as new_ID from simple_seq_simple_sys_tree
Compare the result with the home folder ID from the original server for this user. If it is the same then there is nothing further to do and you can add the new user account.
If it is greater then something has gone wrong and you should go no further until you have identified and corrected the problem.
If it is less then execute the following SQL query:
update simple_seq_simple_sys_tree set id = ROUND(N/100)-1
where N is the desired home folder ID (i.e. the value found from the original server). Now add the account for this user in the normal way via the Simple Groupware web interface.
Once you have successfully added all of the user accounts you are ready to save the folder tree and copy it to the original server.
6. SAVE THE NEW FOLDER TREE
mysqldump --add-drop-table --flush-logs --lock-tables --complete-insert --default-character-set=utf8 -u root -p <sgs-db-name> simple_sys_tree simple_seq_simple_sys_tree | gzip > /path-to-archive-directory/new_folder_tree.sql.gz
(the above command should be all on one line)
7. IMPORT THE NEW FOLDER TREE ON THE ORIGINAL SERVER
zcat /path-to-archive-directory/new_folder_tree.sql.gz | mysql -u root -p <sgs-db-name>
Once the import is complete, log into the admin account in Simple Groupware on the original server and verify that the folders and their contents look reasonable.
8. DELETED RECORDS (TRASH)
Deleted records generate a lot of folders under System/Trash. Rather than trying to re-create these folders, you can create a new folder System/OldTrash with sub-folders for each type of record which you need to keep (e.g. Calendar, Contacts, etc.) as follows.
a) Create the folder System/OldTrash then create sub-folders within this, one for each table which is likely to contain deleted records that you wish to keep.
b) Open an SQL console.
c) For each sub-folder in OldTrash, execute an SQL query to move all of the relevant deleted records into it.
Taking contact records as an example:
Create the folder System/OldTrash/Contacts.
Make a note of this folder's ID number.
In the SQL consol, run the following query:
update simple_contacts set folder=N where history like "Item deleted%"
(where N is the folder ID number for System/OldTrash/Contacts)
All deleted contact records should now be in the folder System/OldTrash/Contact.
9. FOLDERS WHICH WERE ADDED MANUALLY
The problem of manually added folders like additional calendars, contact folders, etc., requires a bit more detective work. Using queries on the database, you need to identify the folder ID numbers of the missing folders and then re-create these folders. You can create each folder in the normal way and then change its id and folder field in simple_sys_tree to the correct value using an SQL query.
You can re-create the initial folder tree and personal folders as follows:
1. Make an archive copy of the installation !!
2. Find the order of creation of the user accounts
3. Find the home folder id for each user account
4. Make a fresh install of Simple Groupware on a separate 'temporary' server (which could be a virtual machine)
5. Add the user accounts (which re-creates the initial personal folders for each user)
6. Save the new folder tree
7. Import the new folder tree into the original system
and then some additional steps to finish off:
8. Consolidate the trash (deleted records) into a new folder (which I call OldTrash)
9. Recover any additional folders which were added manually (e.g. additional calendars or contacts folders)
It is likely that one or more user accounts were added to the original system after some manually created folders were added or records deleted (which creates new folders under System/Trash). In this case we need to know each user's home folder ID in order to ensure that the correct folder IDs will be assigned to each user's personal folders. Provided that the portal items which were installed by default into each home folder are still present, we can find the home folder IDs with the help of an SQL query. So, for simplicity, I will assume that these portal items are still present in each user's home folder.
Here is my suggested procedure.
1. MAKE AN ARCHIVE COPY OF THE EXISTING INSTALLATION !
Archive the files (execute the command as the root user):
tar -czf /path-to-archive-directory/sgs-recovery-archive.tar.gz /var/www/sgs
where /var/www/sgs should be replaced by the path to your Simple Groupware installation.
Archive the database (you will need the password for the MySQL 'root' user):
mysqldump --add-drop-table --flush-logs --lock-tables --complete-insert --default-character-set=utf8 -u root -p <sgs-db-name> | gzip > /path-to-archive-directory/sgs-recovery-archive.sql.gz
where <sgs-db-name> should be replaced by your Simple Groupware database name (the above command should be all on one line).
2. FIND THE ORDER AND TIMES OF CREATION OF THE USER ACCOUNTS
Run the following SQL query and record the result.
select id, created, username from simple_sys_users order by id;
3. FIND THE HOME FOLDER ID FOR EACH USER ACCOUNT
NOTE: this relies on the portal entries which were installed in the home folder of each user account when the user accounts were created. If these portal entries were deleted then this wont work.
Run the following SQL query:
select * from simple_portal where createdby="admin" order by id
This should produce a list of portal entries like the following:
id folder url
901 11301 index.php?folder=^home_paulz/!emails&view=display&limit=50
1001 11301 index.php?folder=^calendar_paulz&view=display&markdate=week&today=last mo...
1101 11301 index.php?folder=^tasks_paulz&view=display&find[]=assets|simple_tasks||re...
1201 11301 index.php?folder=^bookmarks_paulz&view=display
1301 11301 index.php?folder=^notes_paulz&view=display
1401 11301 index.php?folder=^home_paulz/!files&view=display&orderby=lastmodified&ord...
1501 13701 index.php?folder=^home_unprivileged/!emails&view=display&limit=50
1601 13701 index.php?folder=^calendar_unprivileged&view=display&markdate=week&today=...
1701 13701 index.php?folder=^tasks_unprivileged&view=display&find[]=assets|simple_ta...
1801 13701 index.php?folder=^bookmarks_unprivileged&view=display
1901 13701 index.php?folder=^notes_unprivileged&view=display
2001 13701 index.php?folder=^home_unprivileged/!files&view=display&orderby=lastmodif...
The URL field gives a clue to the user account name, e.g. "notes_paulz" indicates the Notes item for user paulz while the folder field gives the home folder ID for this user.
Make a note of the home folder ID for each user account.
4. RECREATE THE INITIAL INSTALLATION ON A TEMPORARY SERVER
Perform a fresh install of the same version of Simple Groupware with the same setup choices (especially the choice to include/exclude the demo folders) ON A DIFFERENT SERVER (this could be a virtual machine).
5. RECREATE EACH USERS' INITIAL PERSONAL FOLDERS
Add the user accounts in the same order as they were added to the original system. Before adding each user account, perform the following steps in order to ensure that the user's personal folders will have the correct IDs.
Run the following SQL query:
select 100*(id+1)+1 as new_ID from simple_seq_simple_sys_tree
Compare the result with the home folder ID from the original server for this user. If it is the same then there is nothing further to do and you can add the new user account.
If it is greater then something has gone wrong and you should go no further until you have identified and corrected the problem.
If it is less then execute the following SQL query:
update simple_seq_simple_sys_tree set id = ROUND(N/100)-1
where N is the desired home folder ID (i.e. the value found from the original server). Now add the account for this user in the normal way via the Simple Groupware web interface.
Once you have successfully added all of the user accounts you are ready to save the folder tree and copy it to the original server.
6. SAVE THE NEW FOLDER TREE
mysqldump --add-drop-table --flush-logs --lock-tables --complete-insert --default-character-set=utf8 -u root -p <sgs-db-name> simple_sys_tree simple_seq_simple_sys_tree | gzip > /path-to-archive-directory/new_folder_tree.sql.gz
(the above command should be all on one line)
7. IMPORT THE NEW FOLDER TREE ON THE ORIGINAL SERVER
zcat /path-to-archive-directory/new_folder_tree.sql.gz | mysql -u root -p <sgs-db-name>
Once the import is complete, log into the admin account in Simple Groupware on the original server and verify that the folders and their contents look reasonable.
8. DELETED RECORDS (TRASH)
Deleted records generate a lot of folders under System/Trash. Rather than trying to re-create these folders, you can create a new folder System/OldTrash with sub-folders for each type of record which you need to keep (e.g. Calendar, Contacts, etc.) as follows.
a) Create the folder System/OldTrash then create sub-folders within this, one for each table which is likely to contain deleted records that you wish to keep.
b) Open an SQL console.
c) For each sub-folder in OldTrash, execute an SQL query to move all of the relevant deleted records into it.
Taking contact records as an example:
Create the folder System/OldTrash/Contacts.
Make a note of this folder's ID number.
In the SQL consol, run the following query:
update simple_contacts set folder=N where history like "Item deleted%"
(where N is the folder ID number for System/OldTrash/Contacts)
All deleted contact records should now be in the folder System/OldTrash/Contact.
9. FOLDERS WHICH WERE ADDED MANUALLY
The problem of manually added folders like additional calendars, contact folders, etc., requires a bit more detective work. Using queries on the database, you need to identify the folder ID numbers of the missing folders and then re-create these folders. You can create each folder in the normal way and then change its id and folder field in simple_sys_tree to the correct value using an SQL query.
Volker
Sep 3, 2016, 3:30:16 PM9/3/16
to simple-g...@googlegroups.com
Dear Paul,
first of all thank you for your efforts and suggestions.
I already took the original folder table (small) and inserted it. Some little things works, but the personal folders etc. are all missing, of course.
I will will try to follow your instructions and see how it works, but this will take umtill next but one week, because I am out of the office next week.
If I run into trouble I would appreciate to come back to you with my questions, ...would this be ok with you?
Have a nice weekend,
thanks again,
Volker
Von meinem CyanogenMod-Telefon gesendet
--
You received this message because you are subscribed to the Google Groups "Simple Groupware" group.
To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupwa...@googlegroups.com.
To post to this group, send email to simple-g...@googlegroups.com.
Visit this group at https://groups.google.com/group/simple-groupware.
For more options, visit https://groups.google.com/d/optout.
paulzarucki
Sep 4, 2016, 5:26:43 AM9/4/16
to Simple Groupware
On Saturday, September 3, 2016 at 9:30:16 PM UTC+2, Volker wrote:
If I run into trouble I would appreciate to come back to you with my questions, ...would this be ok with you?
Yes, of course. No problem.
Regards,
Paul
Regards,
Paul
v.lo...@ifbcon.de
Sep 12, 2016, 5:17:43 AM9/12/16
to Simple Groupware
Hi Paul,
thank you for your support. Meanwhile I managed to have some success thanks to your help.
I could recreate the simple_sys_users and their portals from an older backup I had.
Everythings is fine so far, except some of the folder changes that happened since the backup are not visible, of course.
I have backed up the simple_files and e-Mails and need some idea how to recreate the folders and how to re-assign the files and mails to the folders.
So I would appreciate, if you could help me with this issue.
Tanks in advance,
Volker
P.S.: By the way, I am curious about where you live and what you do with Simple Groupware ..., if you dont mind to answer me.
thank you for your support. Meanwhile I managed to have some success thanks to your help.
I could recreate the simple_sys_users and their portals from an older backup I had.
Everythings is fine so far, except some of the folder changes that happened since the backup are not visible, of course.
I have backed up the simple_files and e-Mails and need some idea how to recreate the folders and how to re-assign the files and mails to the folders.
So I would appreciate, if you could help me with this issue.
Tanks in advance,
Volker
P.S.: By the way, I am curious about where you live and what you do with Simple Groupware ..., if you dont mind to answer me.
Paul Zarucki
Sep 17, 2016, 3:07:47 AM9/17/16
to simple-g...@googlegroups.com
Hi Volker,
I'm happy to help but I am on holiday until Thursday. Perhaps we can catch up then.
Regards,
Paul
I'm happy to help but I am on holiday until Thursday. Perhaps we can catch up then.
Regards,
Paul
v.lo...@ifbcon.de
Sep 18, 2016, 2:12:00 PM9/18/16
to Simple Groupware
Hi Paul,
hope you are enjyoing it ;)
Yes, hopefully we can catch up then, I really appreciate your help.
Have a good time till then.
Cheers, Volker
hope you are enjyoing it ;)
Yes, hopefully we can catch up then, I really appreciate your help.
Have a good time till then.
Cheers, Volker
v.lo...@ifbcon.de
Oct 17, 2016, 12:17:34 PM10/17/16
to Simple Groupware
Hi Paul,
hope you enjoyed your holidays. Did you have a chance to look at my last post how to recreate the folders and how to re-assign the files and mails to the folders?
Any help with this is appreciated.
Best,
Volker
hope you enjoyed your holidays. Did you have a chance to look at my last post how to recreate the folders and how to re-assign the files and mails to the folders?
Any help with this is appreciated.
Best,
Volker
Paul Zarucki
Oct 18, 2016, 2:20:19 PM10/18/16
to simple-g...@googlegroups.com
Hi Volker,
hope you enjoyed your holidays. Did you have a chance to look at my last post how to recreate the folders and how to re-assign the files and mails to the folders?
Sorry for not replying sooner. I was ill for a while after my
holiday and then became busy with work.
Before going any further, I'm afraid there is one step which I should have included between steps 7 and 8 in my earlier email (let's call it step 7.5). Don't worry if you have already carried out step 8, I will deal with this in step 8.5!
7.5 PREVENT CONFLICTS BETWEEN OLD AND NEW RECORD ID NUMBERS
Ensure that the simple_seq counters are set high enough to avoid conflicts with existing record IDs:
update simple_seq_simple_sys_tree set id = ( select round(max(id)/100) from simple_sys_tree )
update simple_seq_simple_calendar set id = ( select round(max(id)/100) from simple_calendar )
update simple_seq_simple_contacts set id = ( select round(max(id)/100) from simple_contacts )
update simple_seq_simple_emails set id = ( select round(max(id)/100) from simple_emails )
update simple_seq_simple_files set id = ( select round(max(id)/100) from simple_files )
...
... and so on for every module that could contain records (even "deleted" records). If you are not sure which modules might contain records then the safest option is to do it for every module.
8.5 FIX THE PROBLEM WITH THE OLD TRASH FOLDERS
Assuming you did carry out step 8 before step 7.5 (!), you will need to take the following corrective action.
Firstly, run these two queries:
update simple_seq_simple_sys_tree set id = id+1
update simple_sys_tree set id = ( select id from simple_seq_simple_sys_tree ) where id = <OldTrash-ID>
where <OldTrash-ID> is the folder ID number of System/OldTrash. Then, for each sub-folder of System/OldTrash, run the following three queries:
update simple_seq_simple_sys_tree set id = id+1
update simple_sys_tree set id = ( select id from simple_seq_simple_sys_tree ) where parent = <OldTrash-ID> and ftype = "<folder-type>"
update simple_<folder-type> set folder = ( select id from simple_seq_simple_sys_tree ) where history like "Item deleted%"
where <folder-type> is the folder type, e.g. "files", "emails", etc. Continue until you have dealt with every sub-folder of System/OldTrash.
We are now ready to deal with the folder changes which occurred after the original installation of Simple Groupware.
Before going any further, I'm afraid there is one step which I should have included between steps 7 and 8 in my earlier email (let's call it step 7.5). Don't worry if you have already carried out step 8, I will deal with this in step 8.5!
7.5 PREVENT CONFLICTS BETWEEN OLD AND NEW RECORD ID NUMBERS
Ensure that the simple_seq counters are set high enough to avoid conflicts with existing record IDs:
update simple_seq_simple_sys_tree set id = ( select round(max(id)/100) from simple_sys_tree )
update simple_seq_simple_calendar set id = ( select round(max(id)/100) from simple_calendar )
update simple_seq_simple_contacts set id = ( select round(max(id)/100) from simple_contacts )
update simple_seq_simple_emails set id = ( select round(max(id)/100) from simple_emails )
update simple_seq_simple_files set id = ( select round(max(id)/100) from simple_files )
...
... and so on for every module that could contain records (even "deleted" records). If you are not sure which modules might contain records then the safest option is to do it for every module.
8.5 FIX THE PROBLEM WITH THE OLD TRASH FOLDERS
Assuming you did carry out step 8 before step 7.5 (!), you will need to take the following corrective action.
Firstly, run these two queries:
update simple_seq_simple_sys_tree set id = id+1
update simple_sys_tree set id = ( select id from simple_seq_simple_sys_tree ) where id = <OldTrash-ID>
where <OldTrash-ID> is the folder ID number of System/OldTrash. Then, for each sub-folder of System/OldTrash, run the following three queries:
update simple_seq_simple_sys_tree set id = id+1
update simple_sys_tree set id = ( select id from simple_seq_simple_sys_tree ) where parent = <OldTrash-ID> and ftype = "<folder-type>"
update simple_<folder-type> set folder = ( select id from simple_seq_simple_sys_tree ) where history like "Item deleted%"
where <folder-type> is the folder type, e.g. "files", "emails", etc. Continue until you have dealt with every sub-folder of System/OldTrash.
We are now ready to deal with the folder changes which occurred after the original installation of Simple Groupware.
9. FOLDERS WHICH WERE ADDED MANUALLY
I could recreate the simple_sys_users and their portals from an older backup I had.
Everythings is fine so far, except some of the folder changes that happened since the backup are not visible, of course.
I have backed up the simple_files and e-Mails and need some idea how to recreate the folders and how to re-assign the files and mails to the folders.
I assume you have already carried out step 8 of my earlier post (if not then please do so now).
The records from the missing folders will still be present in the simple_files and simple_emails folders, but we need to find how they were grouped into folders.
Starting with simple_files, we use the following query to obtain a list of the existing folder numbers
select group_concat(id separator ",") as folderlist from simple_sys_tree where ftype="files"
The result of this query is a string like "7001,6101,12301,14501,31001" which we use in the next query
select distinct folder, createdby, count(*) from simple_files where not folder in ( <folderlist> ) group by folder
where <folderlist> should be replaced by the result of the first query (without quotes). For example,
select distinct folder, createdby, count(*) from simple_files where not folder in ( 7001,6101,12301,14501,31001 ) group by folder
The result will be a list showing each missing folder number with the name of the user who created the folder and the number of items in the folder, like so
folder createdby count(*)
7001 paul 7
12301 paul 3
Hence, in this example, we have found that there are two missing folders with ID numbers 7001 and 12301. Armed with this information, we now manually create two new folders to replace the missing ones, at the appropriate positions within the folder tree. Using the ID numbers of the newly created folders, it is a simple matter to move the records into the new folders by changing their "folder" fields. If, for example, two new folders with IDs of 19801 and 19901 replace the original folders 7001 and 12301, respectively, then the following queries will move the records to the new folders:
update simple_files set folder=19801 where folder=7001
update simple_files set folder=19901 where folder=12301
Having reconstructed the missing simple_files folders, it is a matter of repeating the above procedure for simple_emails, and so on.
I will answer your P.S. in a separate thread called "how we use Simple Groupware".
Regards,
Paul
v.lo...@ifbcon.de
Nov 24, 2016, 2:23:02 AM11/24/16
to Simple Groupware
Dear Paul,
sorry for the late response, but it took me a while to get back to the issue again.
Thank you for your help and very good described advice, it is almost a worksheet to solve this issues.
It just worked out fine and we could reconstruct the folders, files & emails needed!
THANK YOU!!
Cheers Volker
sorry for the late response, but it took me a while to get back to the issue again.
Thank you for your help and very good described advice, it is almost a worksheet to solve this issues.
It just worked out fine and we could reconstruct the folders, files & emails needed!
THANK YOU!!
Cheers Volker
Reply all
Reply to author
Forward
0 new messages