Cloud SQL for MySQL - Support for External User Defined Functions

[dcoz]

I am considering migrating an on-prem application database to Cloud SQL for MySQL; however, the application relies on a number of critical MySQL external user-defined functions written in C++ ( invokable via SQL ).  Is there support in Cloud SQL for MySQL to effectively import external functions such as these or other possible workarounds?   TIA

[ms4446 · Accepted solution]

Unfortunately, Google Cloud SQL for MySQL does not directly support the import of external User-Defined Functions (UDFs) written in C++ or other external programming languages. This limitation is due to several factors:

• Security: Cloud SQL prioritizes security. Allowing arbitrary external code could open potential vulnerabilities, compromising the managed environment.
• Compatibility: Maintaining compatibility between varying MySQL versions and external code in a managed environment can be complex.
• Performance: Cloud SQL focuses on optimized performance. External code can introduce performance variability, which is harder to manage in a shared environment.

Alternative Strategies:

1. Rewrite UDFs in Supported Languages (If Feasible):

   • Cloud SQL currently supports Python UDFs. If possible, rewrite your C++ UDFs in Python. Check the latest Cloud SQL documentation for any updated supported languages.

2. External Microservice or Cloud Function:

   • Refactor the C++ UDF logic into a separate microservice or Cloud Function (e.g., on Google Cloud Functions or Cloud Run). This supports languages like Python, Node.js, Go, and others. Your application would then communicate with this service to execute the logic.

3. Restructure Application Logic:

   • Utilize Built-in MySQL Functions: Where possible, replace custom UDFs with built-in MySQL functions.
   • Redesign Application Logic: If feasible, consider redesigning portions of your application to work without specific UDFs, potentially using other Cloud SQL features or Google Cloud services.

Considerations:

• Complexity of UDFs: Simple UDFs might be easier to rewrite or refactor than complex ones.
• Frequency of Use: If UDFs are heavily used, the network overhead of an external service could impact performance.
• Data Size and Transformation: UDFs handling large datasets or complex calculations will require more substantial rearchitecting.
• Separation of Concerns: Externalizing UDF logic can improve modularity, separating database concerns from custom business logic.

Hybrid Approach: You might combine strategies; rewriting simpler UDFs in supported languages while using external services for more complex ones.

Example:

If a C++ UDF performs advanced text parsing, you could:

• Explore SQL: Attempt replication with MySQL's string manipulation functions.
• Create a Python UDF: If Python offers appropriate libraries.
• External Microservice: Choose this for highly specialized parsing or performance-critical scenarios.

Thoroughly assess your specific UDFs and overall application requirements before migrating to Cloud SQL. This limitation might necessitate alternative database solutions if external C++ UDFs are absolutely essential.

[dcoz]

Very much appreciated.  We will investigate using Python UDFs as a possible alternative.