MySql - filter TABLE_SCHEMA by DB name

[CyberSteelX]

Bug report

The TABLE_SCHEMA of the DB is not considered while reading the tables to port over to v4.

Required System information

• Node.js version: v18.14.1
• NPM version: 9.7.2
• Source Strapi version: 3.6.2
• Target Strapi version: 4.14.2
• Source Database: MySql
• Target Database: MySql
• Operating system: MacOS
• Which script are you running: v3-sql-v4-sql

Describe the bug

While using the v3-sql-v4-sql script on a host that has more than one DB, the tables' names that are being read during the migration include other DB's tables as well. As a result of this, the porting is terminated because the script can't find matching tables between the v3 and v4 DBs (since it is seeing other tables pertaining to other DBs on the specified host)

Steps to reproduce the behavior

1. Launch the script v3-sql-v4-sql with yarn start as for the README file

Expected behavior

The script should read only the appropriate tables by considering the Schema of the DB that is being ported over to v4.

Code snippets

As a temporary solution that seems to work, i did this:

---
 v3-sql-v4-sql/migrate/migrateComponents.js | 1 +
 1 file changed, 1 insertion(+)

diff --git a/v3-sql-v4-sql/migrate/migrateComponents.js b/v3-sql-v4-sql/migrate/migrateComponents.js
index fcf2b34..11b6dda 100644
--- a/v3-sql-v4-sql/migrate/migrateComponents.js
+++ b/v3-sql-v4-sql/migrate/migrateComponents.js
@@ -63,6 +63,7 @@ async function migrateTables(tables) {
       await dbV3('information_schema.tables')
         .select('table_name')
         .where('table_name', 'like', '%_components')
+        .where('TABLE_SCHEMA', '=', process.env.DATABASE_V3_DATABASE)
     )
       .map((row) => row.table_name || row.TABLE_NAME)
       .filter((item) => !componentsToMigrate.includes(item));
-- 
2.39.3 (Apple Git-145)

[derrickmehaffy]

@CyberSteelX do you mean you have multiple tables in the same DB as Strapi that aren't used by Strapi?

[CyberSteelX]

@CyberSteelX do you mean you have multiple tables in the same DB as Strapi that aren't used by Strapi?

Not exactly. The problem seems to arise from the fact that the table information_schema.tables lists all the tables present on each DB on the host, in fact if I launch the following query on DBeaver (which corresponds to the code block mentioned above without the patch), I see an output like this:

Query:

SELECT *
FROM information_schema.tables
WHERE table_name LIKE '%_components'

Result:

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME                                |
|---------------|--------------|-------------------------------------------|
| def           | calV4        | components_slices_heroes_components       |
| ...           | ...          | ...                                       |
| def           | jwdev        | components_section_animated_hero_components_components |

On the other hand, if I add the condition where('TABLE_SCHEMA', '=', process.env.DATABASE_V3_DATABASE) I only get listed the tables of the appropriate DB (e.g. calV4).