dap-client v1.0.0 failing when attempting execute mysql_0_to_1.sql

[MarkAgar]

Hi,

After updating the dap-client to v1.0.0 we appear to be experiencing an issue when attempting a syncdb on tables against the pre 1.0.0 syncs that were successful against our on site MySQL database.

When we attempt a syncdb on the accounts table i.e. 

dap --loglevel debug --logfile dap.log syncdb --namespace canvas --table accounts --connection-string mysql://my_connection_string@my_cd2_db_server/CanvasData2

In the dap.log we are seeing ...

2024-03-05 08:50:50,504 - DEBUG - Checking connection to mysql://my_connection_string@my_cd2_db_server/CanvasData2 (abstract_db_command.py:14)
2024-03-05 08:50:50,534 - DEBUG - Client region: eu-west-1 (api.py:114)
2024-03-05 08:50:50,534 - DEBUG - synchronizing table: canvas.accounts (sql.py:61)
2024-03-05 08:50:50,674 - DEBUG - No version records found in "instructure_dap__database_version" (db_version_upgrader.py:67)
2024-03-05 08:50:50,674 - DEBUG - Current version: 0; Latest version: 1 (db_version_upgrader.py:40)
2024-03-05 08:50:50,674 - DEBUG - Upgrading database (db_version_upgrader.py:45)
2024-03-05 08:50:50,674 - DEBUG - Running upgrade scripts from version 0 to 1 (db_version_upgrader.py:81)
2024-03-05 08:50:50,684 - DEBUG - Upgrading from version 0 to 1 (db_version_upgrader.py:85)
2024-03-05 08:50:50,684 - DEBUG - Running upgrade script: C:\Program Files\Python\Lib\site-packages\dap\version_upgrade\mysql_0_to_1.sql (db_version_upgrader.py:89)
2024-03-05 08:50:50,714 - DEBUG - Executing SQL: DROP PROCEDURE IF EXISTS InsertMetaIfTableExists;

Looks like it can't do the upgrade sql query?

Thanks in advance for any pointers.

[RobertSznyida]

Hi @MarkAgar,

Thanks for reaching out and reporting this issue. While the logs you provided don't show an explicit error message, there are a couple of points that might help us diagnose the problem further.

1. Complete Log: Could you share the full dap.log file, especially the part following the line "Executing SQL: DROP PROCEDURE IF EXISTS InsertMetaIfTableExists"? This section might reveal any error messages that the script encountered during execution.

2. Console Output: Did the dap command produce any output on the console after running the syncdb command? There might be additional error details displayed there, but under normal circumstances the console output should be identical to the log file.

3. Database Version: Could you confirm the version of MySQL that you are using? The upgrade script might be encountering an issue due to a specific version of MySQL.

Thanks for your help in providing these details. We'll be able to assist you further once we have more information.

Robert (Instructure)

[MarkAgar]

Hi @RobertSznyida 

Thanks for your reply, here's the info you requested:

1) dap log file attached (dap ran with debug log-level against 'accounts' table from CD2)

2) same as the log file only truncated to first few lines

3) MySQL is version 8.0.36 running on RedHat Linux 8.9

Hopefully something in the above to provide some pointers.

Regards,

Mark

[RobertSznyida · Accepted solution]

Hi @MarkAgar,

Thank you very much, I was now able to reproduce the error in my development environment.

The problem is with our migration script which doesn't take into consideration that "groups" is a reserved word in MySQL, and it fails when trying to rename the "groups" table to "canvas__groups". (See Release Notes#Fixed Issues why it does it.)

We'll fix this issue in the next release, but in the meantime, you can apply the following workaround:

1. Find the migration script that is part of the dap library and is called "mysql_0_to_1.sql". As a hint, in my case it is in the virtual environment at ".venv/lib/python3.12/site-packages/dap/version_upgrade/mysql_0_to_1.sql".

2. Replace the line #125 that says

SET @sqlQuery = CONCAT('RENAME TABLE ', tableName, ' TO canvas__', tableName);

with

SET @sqlQuery = CONCAT('RENAME TABLE "', tableName, '" TO canvas__', tableName);

Note the addition of the double quotes around the table name.

3. Run the original "dap" command again.

This should fix the issue. Let us know if that worked or if you need further assistance.

[MarkAgar]

Hi,

Thanks very much @RobertSznyida the changes to line #125 in that sql file have indeed resolved my issue with v1.0.0 of the dap-client.

I'm sure you're fully aware of this but when sync'ing tables I'm seeing the following warnings about the VALUES function being deprecated? An example being ,..

C:\Program Files\Python\Lib\site-packages\aiomysql\cursors.py:239: Warning: 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead await self._query(query)

Thanks again,

Mark

[LeventeHunyadi]

Unfortunately, this is a limitation in aiomysql, an indirect dependency for the DAP client library.

I shared more specifics in an earlier post.

[daniel_powers]

Would reinitializing the database with 1.0.0 work? What is the expected date of the release containing the fix?

[RobertSznyida]

@daniel_powers Yes, this way there is no need for the library to run the migration script on the existing database tables.

The fix will be part of the next release that is planned for March 18th.