Cannot import a CTFd backup

[cryptax]

I exported a backup of CTFd using the Admin Panel / Config / Backup / Export, and then tried to import it. I get a popup with an "Integrity Error".
In reality, the backup gets partially applied. For example, all keys and hints are lost.

The docker logs do not show more:

$ docker logs ctfd_ctfd_1 
Waiting for db to be ready
.db is ready
Starting CTFd
 * Loaded module, <module 'CTFd.plugins.keys' from '/opt/CTFd/CTFd/plugins/keys/__init__.py'>
 * Loaded module, <module 'CTFd.plugins.keys' from '/opt/CTFd/CTFd/plugins/keys/__init__.py'>
 * Loaded module, <module 'CTFd.plugins.keys' from '/opt/CTFd/CTFd/plugins/keys/__init__.py'>
 * Loaded module, <module 'CTFd.plugins.keys' from '/opt/CTFd/CTFd/plugins/keys/__init__.py'>

To troubleshoot, I tried to use the import.py script in the container.

/opt/CTFd # python import.py /new-backup.zip

I get the following errors:

 * Loaded module, <module 'CTFd.plugins.keys' from '/opt/CTFd/CTFd/plugins/keys/__init__.py'>
 * Loaded module, <module 'CTFd.plugins.challenges' from '/opt/CTFd/CTFd/plugins/challenges/__init__.py'>
Traceback (most recent call last):
  File "import.py", line 16, in <module>
    import_ctf(sys.argv[1], segments=segments)
  File "/opt/CTFd/CTFd/utils/__init__.py", line 976, in import_ctf
    table.insert(entry)
  File "/usr/local/lib/python2.7/site-packages/dataset/table.py", line 80, in insert
    res = self.db.executable.execute(self.table.insert(row))
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 860, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1061, in _read_query_result
    result.read()
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1349, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1018, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1452, u'Cannot add or update a child row: a foreign key constraint fails (`ctfd`.`tracking`, CONSTRAINT `tracking_ibfk_1` FOREIGN KEY (`team`) REFERENCES `teams` (`id`))') [SQL: u'INSERT INTO tracking (ip, team, date) VALUES (%(ip)s, %(team)s, %(date)s)'] [parameters: {'date': u'2018-05-25T11:24:29', 'ip': u'172.19.0.1', 'team': 2}] (Background on this error at: http://sqlalche.me/e/gkpj)

So, it says: Cannot add or update a child row: a foreign key constraint fails

Environment

• CTFd Version/Commit: 38416ce (May 20, 2018 00:25:17)
• Operating System: Linux Mint 18.3
• Web Browser and Version: Firefox 60.0.1

To deploy the containers, I used docker-compose up --build -d. The 3 containers are up and running.

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
80c2c9cec883        ctfd_ctfd           "/opt/CTFd/docker-en…"   16 minutes ago      Up 16 minutes       0.0.0.0:9000->8000/tcp   ctfd_ctfd_1
d055c8808de5        mariadb:10.2        "docker-entrypoint.s…"   16 minutes ago      Up 16 minutes                                ctfd_db_1
1be242e8fb72        redis:4             "docker-entrypoint.s…"   16 minutes ago      Up 16 minutes                                ctfd_cache_1

[ColdHeat]

Hmm well the issue is that the tracking table is unhappy about something inside of your import causing some kind of issue.

You should likely either not import the teams section and only import the challenges section. Or you can try to truncate the tracking table in your database.

[ColdHeat]

This will be resolved as part of #567 but this issue here can be resolved by messing with the database a bit until the import will take properly.

[cryptax]

@ColdHeat In my case, I tested and the backup imports if I remove challenges and teams. Both must be removed or I still get the integrity error.
In the zip, I opened the challenges.json file. I can't see anything abnormal. There is markdown text, so you'll have **blah**, that's about it for strange things. And plenty of \r\n.
files.json seems just fine to me. No strange uploaded file. No space in names. And the files are not particularly big.

Is #567 planned soon ? :=)

[ColdHeat]

Without a better log it's difficult to tell you exactly what the issue is. Integrity errors mean that your database has a conflict with an ID or name or something to that extent.

Perhaps try with a completely empty database i.e. a CTFd instance that hasn't been setup yet?

No hard timeline for 2.0 unfortunately. In terms of 567, it likely requires waiting on a lot of other changes .

[cryptax]

I've created a backup of a dummy CTFd. It has a single (dummy) challenge and the admin password is test. Even that backup does not import correctly in my case (it only imports if I do not tick challenges and teams at import). I attach the backup file.

Test.2018-05-29.zip

The CTFd setup is fresh out of the box, with only one worker, and running on port 9000.

Logs of container ctfd_ctfd_1:

(pymysql.err.IntegrityError) (1452, u'Cannot add or update a child row: a foreign key constraint fails (`ctfd`.`tracking`, CONSTRAINT `tracking_ibfk_1` FOREIGN KEY (`team`) REFERENCES `teams` (`id`))')
(pymysql.err.IntegrityError) (1452, u'Cannot add or update a child row: a foreign key constraint fails (`ctfd`.`tracking`, CONSTRAINT `tracking_ibfk_1` FOREIGN KEY (`team`) REFERENCES `teams` (`id`))') [SQL: u'INSERT INTO tracking (ip, team, date) VALUES (%(ip)s, %(team)s, %(date)s)'] [parameters: {'date': u'2018-05-29T08:08:47', 'ip': u'172.19.0.1', 'team': 2}] (Background on this error at: http://sqlalche.me/e/gkpj)

In .data/CTFd/logs/access.log, we see my import (correct):

172.19.0.1 - - [29/May/2018:08:13:55 +0000] "POST /admin/import HTTP/1.1" 500 14 "http://127.0.0.1:9000/admin/config" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:60.0) Gecko/20100101 Firefox/60.0"

The other log files in that directory are empty or nothing of value.
The db and cache containers seem ok.

[ColdHeat]

I was able to make this backup import correctly by not including teams in the import. I was also able to make this work in its entirety by deleting my sqlite database and running python import.py ~/Downloads/Test.2018-05-29.zip

[ColdHeat]

You can likely simulate #567 by deleting the database entirely before trying to import.

[cryptax]

How do you delete the database?

[ColdHeat]

You can get a shell on the database server/container and drop the relevant CTFd database.

[cryptax]

Very sorry to bother, but can you explain more how you drop the CTFd database?
I got a shell on the ctfd_db_1 container.
Then, I connect to MariaDB: mysql -u ctfd -p and provide the password.
Then, when I try show databases; I only see information_schema. I don't suppose this is the database to drop, is it?

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

Also, I used the python import.py on the ctfd_ctfd_1 container. I think I understand what's the issue. Look at this log:

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, u"Duplicate entry 'admin' for key 'name'") [SQL: u'INSERT INTO teams (name, email, password, website, affiliation, country, bracket, banned, verified, `admin`, joined) VALUES (%(name)s, %(email)s, %(password)s, %(website)s, %(affiliation)s, %(country)s, %(bracket)s, %(banned)s, %(verified)s, %(admin)s, %(joined)s)'] [parameters: {'website': None, 'verified': 0, 'name': u'admin', 'admin': 1, 'country': None, 'banned': 1, 'joined': u'2018-05-29T08:07:38', 'affiliation': None, 'bracket': None, 'password': u'$bcrypt-sha256$2b,12$8PorrCHq148uIQLxv8c7BO$01Pf.PFdkTfvKCegKfMmYqYgTNzIfM2', 'email': u'blah@blah.com'}] (Background on this error at: http://sqlalche.me/e/gkpj)

It says duplicate entry for admin. This is indeed correct. I am in a CTFd, and want to import a zip. But my current CTF obviously has a admin account (I need an admin or I won't be able to log in !), and the zip also has an admin as well.

I see two ways to fix: either the export does not export the admin account, or (better?) the import does not import the admin account.

Indeed, if you manage to drop the database, you'll have an admin account in only the zip and it'll work, but I haven't managed to drop the database correctly.

[ColdHeat]

To drop the database once you find it you can run DROP DATABASE ctfd;

I'm not sure what's wrong and why you can't find the database but it should be somewhere. Perhaps you're using sqlite or something.

My overall suggestion is to not use Docker because it appears to be adding some complexity for you.

[pich4ya]

same problem here.

ctfd_1 | * Loaded module, <module 'CTFd.plugins.challenges' from '/opt/CTFd/CTFd/plugins/challenges/init.pyc'>
ctfd_1 | (pymysql.err.IntegrityError) (1452, u'Cannot add or update a child row: a foreign key constraint fails (ctfd.tracking, CONSTRAINT tracking_ibfk_1 FOREIGN KEY (team) REFERENCES teams (id))') [SQL: u'INSERT INTO tracking (ip, team, date) VALUES (%(ip)s, %(team)s, %(date)s)'] [parameters: {'date': u'2018-10-21T11:17:37.405218', 'ip': u'x.x.x.x', 'team': 18}] (Background on this error at: http://sqlalche.me/e/gkpj)