Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

mapping rules destroyed by backup / restore [CORE6265] #6507

Closed
firebird-automations opened this issue Mar 18, 2020 · 7 comments
Closed

mapping rules destroyed by backup / restore [CORE6265] #6507

firebird-automations opened this issue Mar 18, 2020 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Foodstuffs INCA team (fssi-inca)

Expected behaviour: non-global mapping rules added with create mapping should be preserved by backup and restore with gbak.

Actual behaviour: after one backup / restore cycle, the mapping is still present, but after a second backup / restore it is gone.

Steps to reproduce:

1. Create database with mapping

PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database "c:\temp\mapping-backup-restore\FOO.FDB";
SQL> create role foo;
SQL> create mapping sjd_foo using plugin win_sspi from user "foodstuff\pgmrsd1" to role foo;
SQL> show mapping;
SJD_FOO USING PLUGIN WIN_SSPI FROM USER foodstuff\pgmrsd1 TO ROLE FOO
*** Global mapping ***
TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER
SQL> exit;

2. Back up database - verbose output shows mapping saved

PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -b C:\temp\mapping-backup-restore\FOO.FDB C:\temp\mapping-backup-restore\FOO.FDB.bak
gbak: time delta reads writes
gbak: 0.104 0.104 40 0 readied database C:\temp\mapping-backup-restore\FOO.FDB for backup
gbak: 0.107 0.002 0 0 creating file C:\temp\mapping-backup-restore\FOO.FDB.bak
gbak: 0.112 0.004 0 0 starting transaction
gbak: 0.115 0.003 21 1 database C:\temp\mapping-backup-restore\FOO.FDB has a page size of 8192 bytes.
gbak: 0.118 0.003 0 0 writing domains
...
gbak: 0.148 0.002 2 0 writing table constraints
gbak: 0.150 0.002 0 0 writing referential constraints
gbak: 0.152 0.002 1 0 writing check constraints
gbak: 0.154 0.001 0 0 writing SQL roles
gbak: 0.155 0.001 0 0 writing SQL role: FOO
gbak: 0.157 0.002 0 0 writing names mapping
gbak: 0.159 0.001 3 0 writing map for SJD_FOO
gbak: 0.199 0.002 104 1 total statistics

3. Restore database - verbose output shows mapping preserved

PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -c C:\temp\mapping-backup-restore\FOO.FDB.bak C:\temp\mapping-backup-restore\BAR.FDB
gbak: time delta reads writes
gbak: 0.004 0.004 0 0 opened file C:\temp\mapping-backup-restore\FOO.FDB.bak
gbak: 0.219 0.214 0 0 transportable backup -- data in XDR format
gbak: 0.222 0.002 0 0 backup file is compressed
gbak: 0.224 0.001 0 0 backup version is 10
gbak: 0.362 0.137 0 666 created database C:\temp\mapping-backup-restore\BAR.FDB, page_size 8192 bytes
gbak: 0.365 0.003 0 2 started transaction
gbak: 0.366 0.001 0 0 restoring SQL role: FOO
gbak: 0.368 0.001 0 0 restoring names mapping
gbak: 0.370 0.002 0 0 restoring map for SJD_FOO
gbak: 0.375 0.004 0 13 creating indexes
...
gbak: 0.418 0.006 0 3 finishing, closing, and going home
gbak: 0.421 0.002 0 728 total statistics
gbak:adjusting the ONLINE and FORCED WRITES flags

4. Confirm mapping present in restored database

PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe C:\temp\mapping-backup-restore\BAR.FDB
Database: C:\temp\mapping-backup-restore\BAR.FDB, User: PGMRSD1
SQL> show users;
Users in the database
1 #⁠PGMRSD1
SQL> show roles;
FOO
SQL> show mapping;
SJD_FOO USING PLUGIN WIN_SSPI FROM USER foodstuff\pgmrsd1 TO ROLE FOO
*** Global mapping ***
TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER
SQL> exit;

5. Back up database - verbose output does not show mapping

PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -b C:\temp\mapping-backup-restore\BAR.FDB C:\temp\mapping-backup-restore\BAR.FDB.bak
gbak: time delta reads writes
gbak: 0.124 0.124 40 0 readied database C:\temp\mapping-backup-restore\BAR.FDB for backup
gbak: 0.126 0.002 0 0 creating file C:\temp\mapping-backup-restore\BAR.FDB.bak
gbak: 0.130 0.003 0 0 starting transaction
gbak: 0.133 0.003 21 1 database C:\temp\mapping-backup-restore\BAR.FDB has a page size of 8192 bytes.
gbak: 0.135 0.002 0 0 writing domains
...
gbak: 0.168 0.003 2 0 writing table constraints
gbak: 0.170 0.002 0 0 writing referential constraints
gbak: 0.172 0.001 1 0 writing check constraints
gbak: 0.174 0.001 0 0 writing SQL roles
gbak: 0.175 0.001 0 0 writing SQL role: FOO
gbak: 0.177 0.002 0 0 writing names mapping
gbak: 0.218 0.041 3 0 closing file, committing, and finishing. 512 bytes written
gbak: 0.221 0.002 104 1 total statistics

6. Restore database - verbose output does not show mapping

PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -c C:\temp\mapping-backup-restore\BAR.FDB.bak C:\temp\mapping-backup-restore\BAZ.FDB
gbak: time delta reads writes
gbak: 0.005 0.005 0 0 opened file C:\temp\mapping-backup-restore\BAR.FDB.bak
gbak: 0.072 0.067 0 0 transportable backup -- data in XDR format
gbak: 0.074 0.001 0 0 backup file is compressed
gbak: 0.076 0.001 0 0 backup version is 10
gbak: 0.196 0.120 0 666 created database C:\temp\mapping-backup-restore\BAZ.FDB, page_size 8192 bytes
gbak: 0.200 0.003 0 2 started transaction
gbak: 0.202 0.002 0 0 restoring SQL role: FOO
gbak: 0.208 0.005 0 9 creating indexes
...
gbak: 0.247 0.005 0 3 finishing, closing, and going home
gbak: 0.249 0.001 0 724 total statistics
gbak:adjusting the ONLINE and FORCED WRITES flags

7. Confirm mapping not present in restored database

PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe C:\temp\mapping-backup-restore\BAZ.FDB
Database: C:\temp\mapping-backup-restore\BAZ.FDB, User: PGMRSD1
SQL> show users;
Users in the database
1 #⁠PGMRSD1
SQL> show roles;
FOO
SQL> show mapping;
*** Global mapping ***
TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER
SQL> exit;

Commits: 63170bf 7b4463b

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Confirmed

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

You should repeat restore from correct backup (when you can see "restoring map" line in verbose output)

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

Fix Version: 3.0.6 [ 10889 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Foodstuffs INCA team (fssi-inca)

Thanks for the quick fix and workaround :)
7b4463b

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants