Queries with string values longer than 255 characters result in unexpected server error.
Reproduce with, e.g.,
import caosdb as db
prop = db.Property(name="TestTextProp", datatype=db.TEXT).insert()
rt = db.RecordType(name="TestType").add_property(prop).insert()
query_string = "w"
max_range = int(1E3)
for ii in range(max_range):
tmp = query_string * (ii + 1)
try:
db.execute_query("FIND {} WITH {}='{}'".format(rt.name, prop.name, tmp))
except db.HTTPServerError as e:
print(len(tmp))
raise e
Internally, this is caused by the SQL table being limited to 255 characters in text columns which is correctly reflected by a data truncation error in the server logs:
linkahead | 2021-03-18 08:13:23 172.19.0.1 - 172.19.0.3 10443 GET /Entity query=FIND%20TestType%20WITH%20TestTextProp%3D%27wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww%27 500 - 0 8 https://localhost:10443 caosdb-pylib/0.5.2.dev0+21a7980 - _DefaultCaosDBServerConnection -
linkahead | 08:13:23.843 [qtp2007599722-50] ERROR REQUEST_ERRORS_LOGGER - SRID: 388abbfc-35a4-4b0f-bb95-eb894a37ea5e
linkahead | REQUEST: GET https://localhost:10443/Entity?query=FIND%20TestType%20WITH%20TestTextProp%3D%27wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww%27 HTTPS/1.1
linkahead | REQUEST HEADERS: [Cookie: SessionToken=["S","PAM","admin",[],[],1616055203824,3600000,"6FRZD5KP-ORAEXBZH-FOVKAZHW-NI","4d6146c237b8aa2592130b09e5850c8155b762fd50d7614587a32f91f7cd3fce19e9581e387d333d49f2a9cc9506b8520cbdc0757d91dcf00bbeb91f74bcab3f"];][User-Agent: caosdb-pylib/0.5.2.dev0 21a7980 - _DefaultCaosDBServerConnection][Host: localhost:10443][Accept-Encoding: identity]
linkahead | RESPONSE: HTTPS/1.1 - Internal Server Error (500) - The server encountered an unexpected condition which prevented it from fulfilling the request
linkahead | RESPONSE ENTITY:<?xml version="1.0" encoding="UTF-8"?>
linkahead | <?xml-stylesheet type="text/xsl" href="https://localhost:10443/webinterface/1616055148/webcaosdb.xsl" ?>
linkahead | <Response username="admin" realm="PAM" srid="388abbfc-35a4-4b0f-bb95-eb894a37ea5e" timestamp="1616055203835" baseuri="https://localhost:10443">
linkahead | <UserInfo username="admin" realm="PAM">
linkahead | <Roles>
linkahead | <Role>administration</Role>
linkahead | </Roles>
linkahead | </UserInfo>
linkahead | <Error code="500" description="An unexpected server error has occurred.">SRID = 388abbfc-35a4-4b0f-bb95-eb894a37ea5e
linkahead |
linkahead | Please report this server error to and include the SRID into your report.</Error>
linkahead | </Response>
linkahead |
linkahead | org.caosdb.server.query.Query$QueryException: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'vText' at row 1
linkahead | at org.caosdb.server.query.POV.apply(POV.java:342) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.query.Query.sourceStrategy(Query.java:327) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.query.Query.executeStrategy(Query.java:550) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.query.Query.executeNoCache(Query.java:713) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.query.Query.execute(Query.java:650) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.jobs.core.ExecuteQuery.job(ExecuteQuery.java:43) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.jobs.FlagJob.run(FlagJob.java:35) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.jobs.ScheduledJob.run(Schedule.java:42) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.jobs.Schedule.runJob(Schedule.java:122) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.jobs.Schedule.runJobs(Schedule.java:104) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.transaction.Transaction.execute(Transaction.java:132) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.resource.transaction.RetrieveEntityResource.httpGetInChildClass(RetrieveEntityResource.java:89) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.resource.AbstractCaosDBServerResource.httpGet(AbstractCaosDBServerResource.java:239) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at jdk.internal.reflect.GeneratedMethodAccessor1.invoke(Unknown Source) ~[?:?]
linkahead | at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
linkahead | at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
linkahead | at org.restlet.resource.ServerResource.doHandle(ServerResource.java:511) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.resource.ServerResource.get(ServerResource.java:723) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.resource.ServerResource.doHandle(ServerResource.java:603) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.resource.ServerResource.doNegotiatedHandle(ServerResource.java:662) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.resource.ServerResource.doConditionalHandle(ServerResource.java:348) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.resource.ServerResource.handle(ServerResource.java:1020) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.resource.Finder.handle(Finder.java:236) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Router.doHandle(Router.java:422) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Router.handle(Router.java:641) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.CaosDBServer$1.handle(CaosDBServer.java:525) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Router.doHandle(Router.java:422) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Router.handle(Router.java:641) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.CaosDBServer$2.handle(CaosDBServer.java:543) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.engine.application.StatusFilter.doHandle(StatusFilter.java:140) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.engine.CompositeHelper.handle(CompositeHelper.java:202) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.engine.application.ApplicationHelper.handle(ApplicationHelper.java:77) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.Application.handle(Application.java:385) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Router.doHandle(Router.java:422) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Router.handle(Router.java:641) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Router.doHandle(Router.java:422) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Router.handle(Router.java:641) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.engine.application.StatusFilter.doHandle(StatusFilter.java:140) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.routing.Filter.handle(Filter.java:197) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.engine.CompositeHelper.handle(CompositeHelper.java:202) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.Component.handle(Component.java:408) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.CaosDBComponent.handle(CaosDBServer.java:856) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.Server.handle(Server.java:507) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.engine.connector.ServerHelper.handle(ServerHelper.java:63) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.engine.adapter.HttpServerHelper.handle(HttpServerHelper.java:143) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.restlet.ext.jetty.JettyServerHelper$WrappedServer.handle(JettyServerHelper.java:256) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at java.lang.Thread.run(Thread.java:834) ~[?:?]
linkahead | Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'vText' at row 1
linkahead | at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at com.mysql.cj.jdbc.CallableStatement.executeQuery(CallableStatement.java:889) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at snaq.db.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:144) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.query.POV.initPOV(POV.java:365) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
linkahead | at org.caosdb.server.query.POV.apply(POV.java:227) ~[caosdb-server-0.4.0-SNAPSHOT-jar-with-dependencies.jar:?]
However, the server returns a code-500 error. At least, this should be covered by a meaningful error, but we could also consider increasing the maximum length here (mind that inserting longer text values is no problem, they just can't be searched).