Use external function in update statement

Help
1

Hi,
is there any way to use an external function within an update statement?
I receive the error “ERROR 2572 UNKNOWN_ERR_CODE: External function failure: Unable to rewrite external udf as tvf within update or delete statement. Set batch_external_functions to AUTO to run this query.” But I can’t set batch_external_functions (Unknown system variable ‘batch_external_function’) :frowning: Any workarounds or tips?

Thanks & Best,
Tom

2

batch_external_functions (with an s) exists.

Maybe you left off the s?

Note that batch_external_functions is a session variable, not a global variable, so you can set it like

set batch_external_functions = auto;

3

By the way, can you tell us what you are doing with your external function? That’d be interesting.

4

lol, really forgot an s :smiley: thank you! works now :slight_smile:

context: we are saving user-generated data and want it to be searchable. in the past (<8.1) we had a workaround to also save emojis and other non-utf8 letters: we encoded the text to save it into a column (e.g. description) and additionally removed non-utf8 characters to save it into a fulltext column (e.g. description_searchable). This way we were able to restore the original text and also make it searchable without making the encoding-fragments searchable. Since the 8.1 release allows utf8mb4 to be searchable by fulltext AND allows all types of emojis (the previous implementation had issues with some special emojis) we are now migrating to a single utf8mb4 column with a fulltext index. We have about 600M rows to migrate.

I think the in-place updates using external functions to decode the text is the fastest way, as the data can stay in the db and the python api can run on the same host :sweat_smile:

Another use case is the ability to vectorise text in the db to simplify some queries (which previously were “aggregated” on the application layer). Also looking into batch-processing stuff using external functions instead of some additional applications we would have to deploy - still trying to figure out if there is a way to do these processing tasks in a more real-time way… I can keep you updated in this thread if you are interested.

fyi: we are also looking into https://modal.com/ as they allow to deploy python functions as endpoints. Sadly they are still pretty early in development and currently only support US (our db is in EU) :frowning:

1 Like
5

This is super-interesting. Please let us know how it goes. What code are you going to use to get vectors for your text? And what programming language do you write your external functions in?