Fix MySQL initialization scripts for MySQL 8.0+ compatibility (Syntax errors and missing USE statements)

[rserit]

Description

While trying to set up the PayrollEngine Backend using the MySQL provider on a Linux environment (MySQL 8.0.46), the database initialization stage fails due to several T-SQL migration leftovers and strict type-casting rules in MySQL 8.0+.

Here are the specific issues found and fixed during the setup:

1. Invalid CAST(... AS INT) in MySql-Routines.merged.sql

MySQL does not support casting directly to INT. Instead, it requires SIGNED or UNSIGNED.

• Error thrown: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual... near 'INT) INTO v_collectorNameHash
• Affected Stored Procedures: * GetCollectorCustomResults
  • GetCollectorResults
  • GetConsolidatedCollectorCustomResults
  • GetConsolidatedCollectorResults
• Fix: Change all occurrences of CAST(jt.val AS INT) to CAST(jt.val AS SIGNED).

2. Missing USE PayrollEngine; and T-SQL Syntax in Update-Model.mysql.sql

The update script fails because no database context is selected, and it contains SQL Server-specific brackets ([PayrollEngine]).

• Error thrown: ERROR 1046 (3D000): No database selected followed by ERROR 1049 (42000): Unknown database '[PayrollEngine]'
• Fix: Ensure USE PayrollEngine; is specified at the top, and replace the brackets [PayrollEngine] with proper MySQL backticks `PayrollEngine` or remove them.

Steps to Reproduce

1. Configure DbProvider as MySql in appsettings.json.
2. Deploy the MySQL container using the provided docker-compose.yml.
3. Let the docker-entrypoint-initdb.d scripts execute automatically.

Environment

• OS: Linux (openSUSE Leap)
• MySQL Version: 8.0.46
• PayrollEngine Version: Latest Main branch

[Giannoudis]

Thanks for the detailed report — the fix is confirmed.

Root cause: MySQL 8.0+ does not support INT as a CAST target type.
All 18 occurrences of CAST(jt.val AS INT) in MySql-Routines.merged.sql
have been replaced with CAST(jt.val AS SIGNED).

The Update-Model.mysql.sql script is not affected — no CAST AS INT present there.
This is a MySQL-only issue; CAST(... AS INT) is valid T-SQL and SQL Server is unaffected.

The fix will ship with Backend release 1.0.2 (release date TBD).