The MIST project https://github.com/gideon-software/mist continues to
gratefully utilize UCanAccess. MIST's job is to import emails into an
Access database.
However, some of my users have reported that MIST hangs in certain
circumstances, notably while trying to import long emails generated via
MailChimp. I've traced the problem to a UCanAccess call. In particular:
I [try to] create a PreparedStatement using a UCanAccessConnection:
At some point, convertSQL() is called, which contains a number of SQL
replacement functions using regular expressions. When using long queries,
some of these functions take quite a while - but most notably is the
convertLike() function. In my testing, it seems to "never" return -
although it continues to use CPU, so my guess is that it's simply churning
through the query. I've had users leave MIST running overnight without
completing its work of importing even one MailChimp message.
I've attached a sample query, scrubbed for privacy. I imagine it could be
simplified further to be just the long text field.
I'm running the latest UCanAccess version of 5.0.1.
I imagine the solution is to create a simpler/faster regEx for convertLike
to use? Notably, my sample query doesn't even contain a LIKE statement, and
so this could be skipped altogether.
The MIST project https://github.com/gideon-software/mist continues to
gratefully utilize UCanAccess. MIST's job is to import emails into an
Access database.
However, some of my users have reported that MIST hangs in certain
circumstances, notably while trying to import long emails generated via
MailChimp. I've traced the problem to a UCanAccess call. In particular:
I [try to] create a PreparedStatement using a UCanAccessConnection:
At some point, convertSQL() is called, which contains a number of SQL
replacement functions using regular expressions. When using long queries,
some of these functions take quite a while - but most notably is the
convertLike() function. In my testing, it seems to "never" return -
although it continues to use CPU, so my guess is that it's simply churning
through the query. I've had users leave MIST running overnight without
completing its work of importing even one MailChimp message.
I've attached a sample query, scrubbed for privacy. I imagine it could be
simplified further to be just the long text field.
I'm running the latest UCanAccess version of 5.0.1.
I imagine the solution is to create a simpler/faster regEx for convertLike
to use? Notably, my sample query doesn't even contain a LIKE statement, and
so this could be skipped altogether.
Hi Tom,
The regex java library hangs but the regex expression is correct whereas concatenating the email body to a sql is an antipattern even for security reasons.
You should use a PreparedStatement placeholder instead (? and PreparedStatement.setString()).
Anyway a workaround will be developed for the 6.0.0. to improve general performance.
Last edit: Marco Amadei 2021-03-10
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Ah, yes! Very true. I just updated the code to use a PreparedStatement, which added security and also worked around the "hang" problem. Thanks for your help!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello to all you hard workers on UCanAccess!
The MIST project https://github.com/gideon-software/mist continues to
gratefully utilize UCanAccess. MIST's job is to import emails into an
Access database.
However, some of my users have reported that MIST hangs in certain
circumstances, notably while trying to import long emails generated via
MailChimp. I've traced the problem to a UCanAccess call. In particular:
I [try to] create a PreparedStatement using a UCanAccessConnection:
At some point, convertSQL() is called, which contains a number of SQL
replacement functions using regular expressions. When using long queries,
some of these functions take quite a while - but most notably is the
convertLike() function. In my testing, it seems to "never" return -
although it continues to use CPU, so my guess is that it's simply churning
through the query. I've had users leave MIST running overnight without
completing its work of importing even one MailChimp message.
I've attached a sample query, scrubbed for privacy. I imagine it could be
simplified further to be just the long text field.
I'm running the latest UCanAccess version of 5.0.1.
I imagine the solution is to create a simpler/faster regEx for convertLike
to use? Notably, my sample query doesn't even contain a LIKE statement, and
so this could be skipped altogether.
Tom
Hi tom,
nice to hear from you.
May you post along the query string?
Il Ven 5 Mar 2021, 14:26 Tom ghaldemar@users.sourceforge.net ha scritto:
Hello Marco! It should be attached to my initial message. It's called "breaking query.txt". (I see it on the SF discussion post, anyway =))
Hi Tom,
The regex java library hangs but the regex expression is correct whereas concatenating the email body to a sql is an antipattern even for security reasons.
You should use a PreparedStatement placeholder instead (? and PreparedStatement.setString()).
Anyway a workaround will be developed for the 6.0.0. to improve general performance.
Last edit: Marco Amadei 2021-03-10
Ah, yes! Very true. I just updated the code to use a PreparedStatement, which added security and also worked around the "hang" problem. Thanks for your help!