SQL Syntax Error in Backup Restoration
I was in the process of creating a backup server to our primary, so I went about installing GitLab on the backup server (matching the production version). I created the backup archive on the production server and copied it in place on the backup. Upon running the restore rake task, I received a syntax error:
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keys' at line 1: DROP TABLE keys
I double checked the GitLab versions between the two servers, and the output (below) matched down to the SHA:
System information
System: Debian 8.4
Current User: git
Using RVM: no
Ruby Version: 2.1.8p440
Gem Version: 2.2.5
Bundler Version:1.11.2
Rake Version: 10.5.0
Sidekiq Version:4.0.1
GitLab information
Version: 8.6.1
Revision: a96d737
Directory: /home/git/gitlab
DB Adapter: mysql2
URL: http://<ServerName>
HTTP Clone URL: http://<ServerName>/some-group/some-project.git
SSH Clone URL: git@<ServerName>:some-group/some-project.git
Using LDAP: yes
Using Omniauth: no
I looked through and saw that the code was simply attempting to drop the table keys
, so I connected to the MySQL server and tried it myself. It only seemed to work when I specified the database name in front of the table.
mysql> DROP TABLE keys;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keys' at line 1
mysql> DROP TABLE gitlabhq_production.keys;
Query OK, 0 rows affected (0.02 sec)
This is a new VM with a fresh install of Debian 8.4 and MySQL Server - Server version: 5.5.47-0+deb8u1 (Debian)
.
I did some more tinkering and was able to successfully complete the backup restoration process with the following changes (not that it's the right way to do it).
diff --git a/lib/tasks/gitlab/db.rake b/lib/tasks/gitlab/db.rake
index 4921c6e..5014316 100644
--- a/lib/tasks/gitlab/db.rake
+++ b/lib/tasks/gitlab/db.rake
@@ -29,7 +29,7 @@ namespace :gitlab do
tables.delete 'schema_migrations'
# Truncate schema_migrations to ensure migrations re-run
connection.execute('TRUNCATE schema_migrations')
- tables.each { |t| connection.execute("DROP TABLE #{t}") }
+ tables.each { |t| connection.execute("DROP TABLE gitlabhq_production.#{t}") }
end
end
end
This appears similar to #14653 (closed), which was closed after @gallart asked to confirm the backup and target were matching GitLab versions. I had done this process twice, so I can confirm that versions matched.