Splunk DB Connect 2: Why are events getting indexed with a timestamp different from input_timestamp_column in our MySQL database?

[jasonsun]

I'm using Splunk DB Connect in a Splunk Forwarder to collect data from a MySQL database. My Splunk version is 6.3.

The indexed data date refers to current date instead of the date from the input_timestamp_column_name (bbp_date column in the db in this case), although it did follow the time. I can see that Splunk has managed to create an additional field with the correct date and time as the bbp_date, however, the _time just does not follow. For example:

8/2/16
4:03:52.000 PM  
"2005-12-05 16:03:52" BBP_SWASTA="BBP_SWASTA", bbp_trx_id="3784", bbp_date="2005-12-05 16:03:52", companyno="191484", swastano="J301", bbp_no="J301/S200512/000039", season="200101", gross_paddy_weight="5920", 

Current Setting in inputs.conf:

[mi_input://splunk_purchase_swasta]
connection =doa_db
description = purchase data
index = bbp_purchase
input_timestamp_column_name = bbp_date
interval = 60
max_rows = 10000
mode = tail
output_timestamp_format = yyyy-MM-dd HH:mm:ss
query = SELECT * FROM splunk_bbp_swasta
source = xxx.xxx.xxx.xxx:3306
sourcetype = purchase_swasta
tail_follow_only = 1
tail_rising_column_checkpoint_value = 2005860
tail_rising_column_name = bbp_trx_id
ui_query_catalog = bbpdev
ui_query_mode = advanced
ui_query_schema = NULL
ui_query_table = NULL

*splunk_bbp_swasta is a virtual table created by db admin.
*the data type of bbp_date column is datetime
*the raw data of bbp_date is in format "2005-12-05 16:03:52.0"

I have tried the solutions below but none of them work:
1) Solution mentioned in https://answers.splunk.com/answers/71485/splunk-db-connect-timestamp-not-working.html
2) Put in the input_timestamp_format (nothing will be index if put in this setting)
3) Convert bbp_date column data type to character myself or ask db admin to change the data type during this virtual table creation.
4) Convert to bbp_date column to epoch time during sql elect query.
5) Try put bbp_date as the first or end in the sql select query.

Appreciate that someone can help to solve this, it is important for me. Thanks a lot.

[acharlieh · Accepted solution]

I don't have that much experience with DBConnect... but I notice the event you have there is timestamped for 2005... Are you getting warnings from DateParserVerbose in _internal like https://answers.splunk.com/answers/8230/what-does-this-splunkd-log-dateparserverbose-message-mean.ht... ?

[jasonsun]

Hi acharlieh, your suggestion could be the answer because I did see this error. The server is somehow inaccessible now, let me try on it and confirm with you soon. Thanks.

[jasonsun]

Other than the answer of acharlieh, the problem can be solved without the MAX_DIFF_xxx settings by upgrading the db connect to latest version (as of today date) v2.3.0.

[acharlieh]

Cool! I converted my comment to an answer then.