Start a new topic

Search function using tikka-app

p1bhatia @ Wed Aug 13 08:20:41 EEST 2014
We are not able to perform search even for small files.

Here is what showing up in the log

ATH LIKE ? : /MP/shared/abc0008/scripts/stg_scripts/APPWORX/sys04testscripts/APPWORX_start.ksh,
08/13/2014 08:12:54 AM|Search:Got DB results:0
08/13/2014 08:12:55 AM|Search:Connecting to db, executing sql:UPDATE SEARCH_INFO SET ITEM_SIZE = ?, ITEM_MODIFIED = ?, ITEM_KEYWORDS = ? WHERE ITEM_PATH = ?
08/13/2014 08:12:55 AM|W:Idle
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError:216
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult:1515
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement:404
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute:350
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.TDSCommand.execute:5696
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand:1715
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand:180
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement:155
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate:314
08/13/2014 08:12:55 AM|crushftp.db.SearchTools.executeSql:107
08/13/2014 08:12:55 AM|crushftp.db.SearchHandler.buildEntry:189
08/13/2014 08:12:55 AM|crushftp.db.SearchHandler.run:88
08/13/2014 08:12:55 AM|com.crushftp.client.Worker.run:34
08/13/2014 08:12:55 AM|java.lang.Thread.run:744
08/13/2014 08:12:55 AM|Search:Connecting to db, executing sql:INSERT INTO SEARCH_INFO (ITEM_PATH, ITEM_TYPE, ITEM_SIZE, ITEM_MODIFIED, ITEM_KEYWORDS) VALUES (?,?,?,?,?)
08/13/2014 08:12:55 AM|W:Idle
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError:216
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult:1515
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement:404
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute:350
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.TDSCommand.execute:5696
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand:1715
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand:180
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement:155
08/13/2014 08:12:55 AM|com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate:314
08/13/2014 08:12:55 AM|crushftp.db.SearchTools.executeSql:107
08/13/2014 08:12:55 AM|crushftp.db.SearchHandler.buildEntry:197
08/13/2014 08:12:55 AM|crushftp.db.SearchHandler.run:88
08/13/2014 08:12:55 AM|com.crushftp.client.Worker.run:34
08/13/2014 08:12:55 AM|java.lang.Thread.run:744
08/13/2014 08:12:55 AM|Search:Connecting to db, executing sql:SELECT * FROM SEARCH_INFO WHERE ITEM_PATH LIKE ? : /MP/shared/abc0008/scripts/stg_scripts/APPWORX/sys04testscripts/APPWORX_start.ksh.notworking,
08/13/2014 08:12:55 AM|Search:Got DB results:0


it just sits there and come back after some time without searching the content.



the second question related to search we have is when even the user is not there in the search_index_usernames it still try to rebuild and/or the search database.here the abc0021 is not there in search_index_usernames but I can still see the query for this user.

08/13/2014 08:15:59 AM|Search:Got DB results:0
08/13/2014 08:15:59 AM|Search:Connecting to db, executing sql:SELECT * FROM SEARCH_INFO WHERE ITEM_PATH LIKE ? : /MP/shared/abc0021/Tips and tricks for Ultraedit.wmv,
08/13/2014 08:15:59 AM|Search:Got DB results:0
08/13/2014 08:16:00 AM|Search:Connecting to db, executing sql:UPDATE SEARCH_INFO SET ITEM_SIZE = ?, ITEM_MODIFIED = ?, ITEM_KEYWORDS = ? WHERE ITEM_PATH = ?
08/13/2014 08:16:00 AM|W:Idle
08/13/2014 08:16:00 AM|com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
08/13/2014 08:16:00 AM|com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError:216
08/13/2014 08:16:00 AM|com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult:1515
08/13/2014 08:16:00 AM|com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement:404
08/13/2014 08:16:00 AM|com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute:350
08/13/2014 08:16:00 AM|com.microsoft.sqlserver.jdbc.TDSCommand.execute:5696
08/13/2014 08:16:00 AM|com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand:1715
08/13/2014 08:16:00 AM|com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand:180
08/13/2014 08:16:00 AM|com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement:155
08/13/2014 08:16:00 AM|com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate:314
08/13/2014 08:16:00 AM|crushftp.db.SearchTools.executeSql:107
08/13/2014 08:16:00 AM|crushftp.db.SearchHandler.buildEntry:189
08/13/2014 08:16:00 AM|crushftp.db.SearchHandler.run:88
08/13/2014 08:16:00 AM|com.crushftp.client.Worker.run:34
08/13/2014 08:16:00 AM|java.lang.Thread.run:744
08/13/2014 08:16:00 AM|Search:Connecting to db, executing sql:INSERT INTO SEARCH_INFO (ITEM_PATH, ITEM_TYPE, ITEM_SIZE, ITEM_MODIFIED, ITEM_KEYWORDS) VALUES (?,?,?,?,?)


CrushFTP is up to date (Version 7.1.0_73)

spinkb @ Wed Aug 13 10:32:46 EEST 2014
Make your item_keywords column be a "TEXT" item, or a varchar 8000 item.

The errors are saying the column is too small to hold the search data.

Thanks,
Ben
p1bhatia @ Wed Aug 13 13:50:54 EEST 2014
Now when I am try to search (which is only one file in the folder) why it is trying to update all the records which again it is just one. Also why there are multiple select statements to find just one record?


Search for myfile01

08/13/2014 01:10:02 PM|Search:Connecting to db, executing sql:SELECT * FROM SEARCH_INFO WHERE ITEM_PATH LIKE ? : /myuser%,
08/13/2014 01:10:02 PM|W:Idle
08/13/2014 01:10:02 PM|java.lang.NullPointerException
08/13/2014 01:10:02 PM|java.util.Hashtable.put:514
08/13/2014 01:10:02 PM|crushftp.db.SearchTools.executeSqlQuery:238
08/13/2014 01:10:02 PM|crushftp.db.SearchTools.executeSqlQuery:139
08/13/2014 01:10:02 PM|crushftp.db.SearchHandler.run:45
08/13/2014 01:10:02 PM|com.crushftp.client.Worker.run:34
08/13/2014 01:10:02 PM|java.lang.Thread.run:744
08/13/2014 01:10:02 PM|Listing results size for search:0
08/13/2014 01:10:02 PM|Search:Connecting to db, executing sql:SELECT * FROM SEARCH_INFO WHERE ITEM_PATH LIKE ? : /MP/shared/myuser/folder01/test123.txt,
08/13/2014 01:10:02 PM|Search:Got DB results:1
08/13/2014 01:10:02 PM|Search:Connecting to db, executing sql:SELECT * FROM SEARCH_INFO WHERE ITEM_PATH LIKE ? : /MP/shared/myuser/folder01/myfile01.txt,
08/13/2014 01:10:02 PM|Search:Got DB results:1
08/13/2014 01:10:03 PM|Search:Connecting to db, executing sql:SELECT * FROM SEARCH_INFO WHERE ITEM_PATH LIKE ? : /MP/shared/myuser/folder01/test123.txt,
08/13/2014 01:10:03 PM|Search:Got DB results:1
08/13/2014 01:10:03 PM|Search:Connecting to db, executing sql:SELECT * FROM SEARCH_INFO WHERE ITEM_PATH LIKE ? : /MP/shared/myuser/folder01/myfile01.txt,
08/13/2014 01:10:03 PM|Search:Got DB results:1
08/13/2014 01:10:03 PM|Search:Connecting to db, executing sql:UPDATE SEARCH_INFO SET ITEM_SIZE = ?, ITEM_MODIFIED = ?, ITEM_KEYWORDS = ? WHERE ITEM_PATH = ?
08/13/2014 01:10:03 PM|Search:Connecting to db, executing sql:UPDATE SEARCH_INFO SET ITEM_SIZE = ?, ITEM_MODIFIED = ?, ITEM_KEYWORDS = ? WHERE ITEM_PATH = ?
08/13/2014 01:10:03 PM|Search:Connecting to db, executing sql:SELECT * FROM SEARCH_INFO WHERE ITEM_PATH LIKE ? : /MP/shared/myuser/folder01/test123.txt,
08/13/2014 01:10:03 PM|Search:Got DB results:1
08/13/2014 01:10:03 PM|Search:Connecting to db, executing sql:SELECT * FROM SEARCH_INFO WHERE ITEM_PATH LIKE ? : /MP/shared/myuser/folder01/myfile01.txt,
08/13/2014 01:10:03 PM|Search:Got DB results:1
08/13/2014 01:10:03 PM|Search:Connecting to db, executing sql:UPDATE SEARCH_INFO SET ITEM_SIZE = ?, ITEM_MODIFIED = ?, ITEM_KEYWORDS = ? WHERE ITEM_PATH = ?
08/13/2014 01:10:03 PM|Search:Connecting to db, executing sql:UPDATE SEARCH_INFO SET ITEM_SIZE = ?, ITEM_MODIFIED = ?, ITEM_KEYWORDS = ? WHERE ITEM_PATH = ?
08/13/2014 01:10:03 PM|Search:Connecting to db, executing sql:UPDATE SEARCH_INFO SET ITEM_SIZE = ?, ITEM_MODIFIED = ?, ITEM_KEYWORDS = ? WHERE ITEM_PATH = ?
08/13/2014 01:10:03 PM|[HTTPS:19109:myuser:111.11.1.1] WROTE: *HTTP/1.1 200 OK*
spinkb @ Wed Aug 13 15:37:03 EEST 2014
What columns are null in this result? None can be null. They can be blank, but not null.

SELECT * FROM SEARCH_INFO WHERE ITEM_PATH LIKE '/myuser%'

Thanks,
Ben
p1bhatia @ Wed Aug 13 16:13:28 EEST 2014
How to avoid the null, we have few vfs mount points and they have date_modified values as null.
spinkb @ Wed Aug 13 16:27:06 EEST 2014
Can you set the DB to not allow null and default to blank value?
p1bhatia @ Wed Aug 13 16:29:28 EEST 2014
thank you Ben, i will try this and let you know.

thanks again.
p1bhatia @ Thu Aug 14 08:43:18 EEST 2014
Ben, we have changed the search_db_insert to

INSERT INTO SEARCH_INFO (ITEM_PATH, ITEM_TYPE, ITEM_SIZE, ITEM_MODIFIED, ITEM_KEYWORDS) VALUES (?,?,?,ISNULL(?, ''),?)

and search_db_update to

UPDATE SEARCH_INFO SET ITEM_SIZE = ?, ITEM_MODIFIED = ISNULL(?, ''), ITEM_KEYWORDS = ? WHERE ITEM_PATH = ?


Now the problem is that we can't search at the root of login directory. for example the login path is abcuser/shared/abcuser/

Now if I login and try to search in my login directory, which is VFS, I can't find anything, once I am in nfs mountpoint, I can search through. Is there any way we can tell the search funtion to search inside the vfs and recursively go the nfs mount point and bring back the results?
spinkb @ Thu Aug 14 08:45:29 EEST 2014
Does the DB have the entries for where you are looking for?

If your search user configured in the search config doesn't see the path your interested in, then you can't search it... that VFS in the search user config is what is used to create the search index.

Thanks,
Ben
p1bhatia @ Fri Aug 22 15:02:35 EEST 2014
How can I stop the trigger to update sql database, everytime when I try to search and it didn't find the result, it seems trying to update. Also, how can I exclude certain file types in search?
spinkb @ Fri Aug 22 15:14:36 EEST 2014
You can't... This is how it dynamically builds search results without needing to index everything first. If it finds no DB records at all, then it indexes the directory.

We don't have a negative search system, only positive. So using the and condition checkbox, you can search for thinks like:

stuff .csv

Which would find allt exults where the filename contained stuff and contained csv (probably the extension)

Thanks,
Ben
p1bhatia @ Fri Aug 22 15:22:11 EEST 2014
is there a way to index everything first? I mean it is ok if it is trying to build the index dynamically, but I like it build the index first so that it doesn't have to build on the fly, but just update if needed.
spinkb @ Fri Aug 22 15:24:24 EEST 2014
You can set a username for it to use. It will load the VFS from this user, and index everything in this user...and then update the index at the interval specified.

So yes, you have that config there as well i the search config.

Thanks,
Ben
p1bhatia @ Fri Aug 22 15:27:44 EEST 2014
I am still not in favor of building the index dynamically, which basically defeats the purpose of building index at the specified interval, In my system there are more than 70,000 files and it is not very practical to build index dynamically. Any suggestions?
spinkb @ Fri Aug 22 15:36:54 EEST 2014
If there is a folder that has no items indexed in it, it will build dynamically.

If the folder has items indexed, it will not build anything dynamically.

So you would have to search in a folder where there are no items to get it to dynamically try and build for it.
Login to post a comment