dap CLI tool only throws "not all arguments converted during string formatting" error with MySQL 5.7

[daniel_powers]

 When running dap syncdb using our MySQL 5.7 database we get the error shown below for several tables. When dap syncdb is run for the same tables using our development MySQL 8.0 database we do not get these errors. What minimum MySQL release is required by the dap CLI tool.

A sample error and stack trace when running with MySQL 5.7.

dap syncdb --table assignments
2023-10-18 11:47:14,815 - WARNING - ⚠️Warning: aiomysql is classified as being in alpha development status. Instructure cannot assume responsibility for any potential changes or consequences resulting from its usage.
2023-10-18 11:47:15,510 - INFO - Query started with job ID: 5c28b70c-d680-4265-8f4f-50330f63300a
...
2023-10-18 11:47:31,198 - INFO - Data has been successfully retrieved:
{"id": "5c28b70c-d680-4265-8f4f-50330f63300a", "status": "complete", "expires_at": "2023-10-19T15:47:15Z", "objects": [{"id": "5c28b70c-d680-4265-8f4f-50330f63300a/part-00000-794bedd7-b4da-4de3-be9d-3fe29a6f8890-c000.json.gz"}], "schema_version": 2, "since": "2023-10-12T12:41:14Z", "until": "2023-10-18T14:01:35Z"}
2023-10-18 11:47:31,663 - ERROR - not all arguments converted during string formatting
Traceback (most recent call last):
File "...\dap\__main__.py", line 133, in console_entry
main()
File "...\dap\__main__.py", line 125, in main
asyncio.run(dapCommand.execute(args))
File "C:\Users\dpowers\AppData\Local\anaconda3\lib\asyncio\runners.py", line 44, in run
return loop.run_until_complete(main)
File "C:\Users\dpowers\AppData\Local\anaconda3\lib\asyncio\base_events.py", line 641, in run_until_complete
return future.result()
File "...\dap\commands\commands.py", line 31, in execute
executed = await super().execute(args)
File "...\dap\commands\base.py", line 49, in execute
if await subcommand.execute(args):
File "...\dap\commands\base.py", line 45, in execute
await self._execute_impl(args)
File "...\dap\commands\syncdb_command.py", line 42, in _execute_impl
await sync_db(
File "...\dap\actions\sync_db.py", line 16, in sync_db
await SQLReplicator(session, db_connection).synchronize(
File "...\dap\replicator\sql.py", line 120, in synchronize
await client.download(
File "...\dap\downloader.py", line 171, in download
await self._download_and_save(db_lock, context_aware_object, processor)
File "...\dap\downloader.py", line 195, in _download_and_save
await processor.process(obj, records)
File "...\dap\integration\base_processor.py", line 19, in process
await self.process_impl(obj, self._convert(records))
File "...\dap\plugins\mysql\sync_processor.py", line 78, in process_impl
await self._db_connection.execute(
File "...\dap\integration\base_connection.py", line 41, in execute
return await query(self._raw_connection)
File "...\dap\plugins\sqlalchemy\queries.py", line 46, in __call__
return await conn.execute(self._statement, self._parameters)
File "...\sqlalchemy\ext\asyncio\engine.py", line 652, in execute
result = await greenlet_spawn(
File "...\sqlalchemy\util\_concurrency_py3k.py", line 190, in greenlet_spawn
result = context.throw(*sys.exc_info())
File "...\sqlalchemy\engine\base.py", line 1412, in execute
return meth(
File "...\sqlalchemy\sql\elements.py", line 516, in _execute_on_connection
return connection._execute_clauseelement(
File "...\sqlalchemy\engine\base.py", line 1635, in _execute_clauseelement
ret = self._execute_context(
File "...\sqlalchemy\engine\base.py", line 1844, in _execute_context
return self._exec_single_context(
File "...\sqlalchemy\engine\base.py", line 1984, in _exec_single_context
self._handle_dbapi_exception(
File "...\sqlalchemy\engine\base.py", line 2342, in _handle_dbapi_exception
raise exc_info[1].with_traceback(exc_info[2])
File "...\sqlalchemy\engine\base.py", line 1934, in _exec_single_context
self.dialect.do_executemany(
File "...\sqlalchemy\dialects\mysql\mysqldb.py", line 175, in do_executemany
rowcount = cursor.executemany(statement, parameters)
File "...\sqlalchemy\dialects\mysql\aiomysql.py", line 94, in executemany
return self.await_(
File "...\sqlalchemy\util\_concurrency_py3k.py", line 125, in await_only
return current.driver.switch(awaitable) # type: ignore[no-any-return]
File "...\sqlalchemy\util\_concurrency_py3k.py", line 185, in greenlet_spawn
value = await result
File "...\sqlalchemy\dialects\mysql\aiomysql.py", line 112, in _executemany_async
return await self._cursor.executemany(operation, seq_of_parameters)
File "...\aiomysql\cursors.py", line 281, in executemany
return (await self._do_execute_many(
File "...\aiomysql\cursors.py", line 302, in _do_execute_many
v = values % escape(next(args), conn)
TypeError: not all arguments converted during string formatting

[r_layden]

I just came here to log the same problem 😀

I'm using version 0.3.15 of the client on a MariaDB database.

I know that MySQL is explicitly stated but there is broad compatibility.

I can initialise all the tables, take snapshots etc - the error (above) only occurs on syncdb tasks

I've updated to the latest stable version of Mariadb (11.1.2) and still getting the same error.

[ptart]

We are experience the same exact issue. The "initdb" and "dropdb" commands work great. However, "syncdb" is consistently giving the same error: "TypeError: not all arguments converted during string formatting". We're using the MySQL API's to pull data directly into our database. A ticket has been opened so hopefully we can get a fix soon. Any updates from anyone else?

Setup:

• MariaDB Server - 10.5.22
• MySQL
• dap 0.3.17
  

[killerb123]

Same error here. Able to "initdb" all tables, "syncdb" fails on every table.

mysql Ver 15.1 Distrib 10.5.22-MariaDB, for Linux (x86_64)

dap 0.3.17

File "/usr/lib64/python3.11/asyncio/runners.py", line 118, in run
return self._loop.run_until_complete(task)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib64/python3.11/asyncio/base_events.py", line 653, in run_until_complete
return future.result()
^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/dap/commands/commands.py", line 31, in execute
executed = await super().execute(args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/dap/commands/base.py", line 49, in execute
if await subcommand.execute(args):
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/dap/commands/base.py", line 45, in execute
await self._execute_impl(args)
File "/usr/local/lib/python3.11/site-packages/dap/commands/syncdb_command.py", line 42, in _execute_impl
await sync_db(
File "/usr/local/lib/python3.11/site-packages/dap/actions/sync_db.py", line 16, in sync_db
await SQLReplicator(session, db_connection).synchronize(
File "/usr/local/lib/python3.11/site-packages/dap/replicator/sql.py", line 120, in synchronize
await client.download(
File "/usr/local/lib/python3.11/site-packages/dap/downloader.py", line 195, in download
await self.load_file_to_db(db_lock, context_aware_object, file_paths[index], processor)
File "/usr/local/lib/python3.11/site-packages/dap/downloader.py", line 65, in load_file_to_db
await processor.process(obj, records)
File "/usr/local/lib/python3.11/site-packages/dap/integration/base_processor.py", line 19, in process
await self.process_impl(obj, self._convert(records))
File "/usr/local/lib/python3.11/site-packages/dap/plugins/mysql/sync_processor.py", line 78, in process_impl
await self._db_connection.execute(
File "/usr/local/lib/python3.11/site-packages/dap/integration/base_connection.py", line 41, in execute
return await query(self._raw_connection)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/dap/plugins/sqlalchemy/queries.py", line 46, in __call__
return await conn.execute(self._statement, self._parameters)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/ext/asyncio/engine.py", line 654, in execute
result = await greenlet_spawn(
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 190, in greenlet_spawn
result = context.throw(*sys.exc_info())
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
return meth(
^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
return self._exec_single_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
self._handle_dbapi_exception(
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 2346, in _handle_dbapi_exception
raise exc_info[1].with_traceback(exc_info[2])
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1938, in _exec_single_context
self.dialect.do_executemany(
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 175, in do_executemany
rowcount = cursor.executemany(statement, parameters)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/dialects/mysql/aiomysql.py", line 96, in executemany
return self.await_(
^^^^^^^^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 125, in await_only
return current.driver.switch(awaitable) # type: ignore[no-any-return]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 185, in greenlet_spawn
value = await result
^^^^^^^^^^^^
File "/usr/local/lib64/python3.11/site-packages/sqlalchemy/dialects/mysql/aiomysql.py", line 114, in _executemany_async
return await self._cursor.executemany(operation, seq_of_parameters)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/aiomysql/cursors.py", line 281, in executemany
return (await self._do_execute_many(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/aiomysql/cursors.py", line 302, in _do_execute_many
v = values % escape(next(args), conn)
~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~~~
TypeError: not all arguments converted during string formatting
7

[LeventeHunyadi]

The current version series 0.3.x of DAP client library uses two algorithms to replicate the database:

• for initdb, it uses a direct copy that leans directly on the database driver functionality, provided by asyncpg or aiomysql, but
• for syncdb, it uses a higher-level function from SQLAlchemy.

In the upcoming version series 0.4.x of the client library, we are unifying these two approaches into a single approach that leans on the database driver functionality only, and we are dropping the dependency on SQLAlchemy. As a side effect, this also speeds up database insert/update/delete operations, and will likely resolve the reported issue.

[SpencerWilliams]

That's great news! Any idea of when 0.4 will be released?

[LeventeHunyadi]

General availability release target date of the next-generation DAP Client Library is Feb 2024. (Development has finished and the package has entered the testing phase.)

[ptart]

Hope testing went well with dap 0.4.x. Any chance there is a specific release date yet?

[LeventeHunyadi]

I am sure our Product team will share a specific date soon (or come with the great news of an official release right away). As an outcome of the preliminary testing phase, some connectivity issues have been addressed, and the documentation has been updated. However, we have encountered no major blockers thus far as the final touches are being made.

Formerly, the upcoming version of the DAP client library was referred to as the 0.4.x series. However, it has been renamed to 1.0 to signal the major improvement in robustness and execution speed as compared to the 0.3.x series. Despite what the increase in the major version number may suggest, it is backwards compatible with the 0.3.x series, and will perform an auto-migration of internal service tables.

[SpencerWilliams]

What is the best way to be notified of the newest release via email? Since the project is not on GitHub, I can't subscribe for new releases there.

[ptart]

I went to the Release History section on the dap client page in pypi and added teh RSS feed to my RSS reader.
https://pypi.org/project/instructure-dap-client/#history

RSS link - Hoping this is where the update will be released.
https://pypi.org/rss/project/instructure-dap-client/releases.xml

[SpencerWilliams]

Thanks for the suggestion. I am aware of RSS feed, but I prefer email.

[ptart]

Checking back to see if there is an ETA for DAP client 0.4.x? Since Canvas Data 1 is EOL on 12-31-2023, we're in need of syncdb to up and running. Thanks for the hard work on this!

[jaywho]

Hi @LeventeHunyadi, I've now hit this same issue as part of my team's migration to Canvas Data 2. Please advise status on the 1.0 / 0.4.x release of the DAP client library and/or workarounds for this issue?

Our new academic year has just commenced and the exports from Canvas Data Portal / now Canvas Data 2 are critical for our operational and analytics reporting. With CDP now discontinued, we need the CD2 snapshots to work reliably.

Hope to hear from you soon - thanks 🙂

[LeventeHunyadi]

Unlike CD 1, DAP (a.k.a. CD 2) does not support regular snapshots. You need to invoke a single snapshot query for each table at the beginning, and then always use incremental queries thereafter to keep your database up to date with the state in DAP. You need to chain queries along the until/since timestamp as described in the documentation, or simply use dap syncdb in DAP client library.

The new version of DAP client library is in closed beta at the moment, about to be released. We are not hearing any major complaints from members of the community who have already tried out the new library version. The scheduled release date, as far as I am aware, is still end of February. However, I am no longer part of the client library development, and the exact release date is subject to the product roadmap. You might want to reach out to your CSM if you want to join beta testing or getting access to the library before the official release would happen.