Asterisk CDR数据库插入answer字段报错问题求助

Looks like you're hitting a classic case where Asterisk's CDR module is incorrectly populating the answer field with data that belongs in lastdata—hence the datetime validation error when you switched that column to the correct datetime type. Let's break down the issue and fix it step by step:

Root Cause Analysis #

Looking at your error log and abnormal CDR record:

• The error complains about inserting 'SIP/sip_lbascunan/961258985,160,wWTt' into the answer datetime column
• That exact string is the value of lastdata in your abnormal record

This means there's a misconfiguration or logic error causing Asterisk to map the LASTDATA CDR variable to the answer database column instead of the ANSWEREDTIME variable it should use. Your earlier change to set answer as datetime was the right move (since it's supposed to store a timestamp), but it exposed the underlying mapping issue that was hidden when the column was a varchar.

Step-by-Step Fixes #

1. Verify CDR MySQL Module Field Mapping #

First, check your cdr_mysql.conf file (usually in /etc/asterisk/ or your Asterisk config directory) to ensure the field mappings are correct. The answer column should be tied to the ANSWEREDTIME variable, not any other. Here's what the relevant section should look like:

[global]
hostname=localhost
dbname=asteriskcdrdb
user=asteriskuser
password=your_db_password
table=cdr

; Ensure these mappings are present and correct
uniqueid => uniqueid
linkedid => linkedid
clid => clid
src => src
dst => dst
dcontext => dcontext
channel => channel
dstchannel => dstchannel
lastapp => lastapp
lastdata => lastdata
start => start
answer => answer  ; This maps the ANSWEREDTIME variable to the answer column
end => end
duration => duration
billsec => billsec
disposition => disposition
amaflags => amaflags
accountcode => accountcode
userfield => userfield
sequence => sequence
peeraccount => peeraccount

If you see any line that incorrectly maps answer to lastdata or another variable, fix it immediately.

2. Check Dial Plan for Misconfigured CDR Variables #

Next, inspect your extensions.conf dial plan for any custom code that might be overwriting the answer CDR field. For example, some admins accidentally set CDR(answer) to ${LASTDATA} after a Dial command, which would cause exactly this issue. Look for lines like:

exten => _X.,n,Set(CDR(answer)=${LASTDATA})

Delete any such lines—they're overriding the correct timestamp value with Dial command parameters.

3. Fix the CDR Table's answer Column Constraint #

Your current table defines answer as NOT NULL, but for calls that aren't answered, this field should be NULL. This constraint will cause failures even if the mapping is fixed, so update the table:

ALTER TABLE cdr MODIFY COLUMN answer datetime NULL DEFAULT NULL;

4. Restart Asterisk to Apply Changes #

Once you've fixed the configs and table, restart Asterisk to make sure everything takes effect:

asterisk -rx "core restart now"

Validation #

Test a few calls (both answered and unanswered) and check the CDR records:

• Answered calls should have a valid datetime in answer
• Unanswered calls should have NULL in answer
• You shouldn't see any more Incorrect datetime value errors in your logs

内容的提问来源于stack exchange，提问作者Gustavo Gil