Hi All I have dozens of these messages on CLI complaining about database connection and error writing CDR to disk. The curious thing is I can find them all inside the database. I "selected" them using uniqueid and manually compared each column with the cdr_adaptive_odbc.c error line. "mysqlcheck -a -e -v DBase" and "mysqlcheck -c -e -v DBase" both returned OK for all tables. Environment is: in production Asterisk 11.7.0~dfsg-1ubuntu1 Ubuntu 14.04.1 LTS Any thoughts? Thanx Ethy [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:645 ast_odbc_prepare_and_execute: SQL Execute returned an error -1: 23000: [MySQL][ODBC 5.1 Driver][mysqld-5.5.40-0ubuntu0.14.04.1-log]Duplicate entry '0000-00-00 00:00:00-1234-CLIENT_ID' for key 'PRIMARY' (133) [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:657 ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying connection to MyAsterisk-asterisk [MyAsterisk-asterisk]... [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:761 ast_odbc_sanity_check: Connection is down attempting to reconnect... [Apr 25 10:57:01] NOTICE[19013][C-000002cb]: res_odbc.c:1527 odbc_obj_connect: Connecting MyAsterisk-asterisk [Apr 25 10:57:01] NOTICE[19013][C-000002cb]: res_odbc.c:1559 odbc_obj_connect: res_odbc: Connected to MyAsterisk-asterisk [MyAsterisk-asterisk] [Apr 25 10:57:01] WARNING[19013][C-000002cb]: res_odbc.c:645 ast_odbc_prepare_and_execute: SQL Execute returned an error -1: 23000: [MySQL][ODBC 5.1 Driver][mysqld-5.5.40-0ubuntu0.14.04.1-log]Duplicate entry '0000-00-00 00:00:00-1234-CLIENT_ID' for key 'PRIMARY' (133) [Apr 25 10:57:01] WARNING[19013][C-000002cb]: res_odbc.c:657 ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying connection to MyAsterisk-asterisk [MyAsterisk-asterisk]... [Apr 25 10:57:01] WARNING[19013][C-000002cb]: res_odbc.c:761 ast_odbc_sanity_check: Connection is down attempting to reconnect... [Apr 25 10:57:02] WARNING[7666]: chan_sip.c:4409 __sip_autodestruct: Autodestruct on dialog '34f3f3481b8d1e4772dc111f42d13bd1 at ip.ip.ip.ip:5060' with owner SIP/CLIENT_ID_1-00000178 in place (Method: BYE). Rescheduling destruction for 10000 ms [Apr 25 10:57:06] NOTICE[19013][C-000002cb]: res_odbc.c:1527 odbc_obj_connect: Connecting MyAsterisk-asterisk [Apr 25 10:57:06] NOTICE[19013][C-000002cb]: res_odbc.c:1559 odbc_obj_connect: res_odbc: Connected to MyAsterisk-asterisk [MyAsterisk-asterisk] [Apr 25 10:57:06] WARNING[19013][C-000002cb]: cdr_adaptive_odbc.c:739 odbc_log: cdr_adaptive_odbc: Insert failed on 'MyAsterisk-asterisk:cdr'. CDR failed: INSERT INTO cdr (dst,accountcode,clid,src,dcontext,channel,dstchannel,lastapp,duration,billsec,disposition,amaflags,userfield,lastdata,uniqueid) VALUES (blahblahblah, ... ,'1429970147.612')
> Hi All > > I have dozens of these messages on CLI complaining about database connection and error writing CDR to disk. > > The curious thing is I can find them all inside the database. > I "selected" them using uniqueid and manually compared each column with the cdr_adaptive_odbc.c error line. > > "mysqlcheck -a -e -v DBase" and "mysqlcheck -c -e -v DBase" both returned OK for all tables. > > Environment is: > in production Asterisk 11.7.0~dfsg-1ubuntu1 > Ubuntu 14.04.1 LTS > > Any thoughts? > > Thanx > > Ethy > > [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:645 > ast_odbc_prepare_and_execute: SQL Execute returned an error -1: 23000: > [MySQL][ODBC 5.1 Driver][mysqld-5.5.40-0ubuntu0.14.04.1-log]Duplicate entry > '0000-00-00 00:00:00-1234-CLIENT_ID' for key 'PRIMARY' (133) > > [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:657 > ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying connection to > MyAsterisk-asterisk [MyAsterisk-asterisk]... > > [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:761 ast_odbc_sanity_check: Connection is down attempting to reconnect... > > [Apr 25 10:57:01] NOTICE[19013][C-000002cb]: res_odbc.c:1527 odbc_obj_connect: Connecting MyAsterisk-asterisk > > [Apr 25 10:57:01] NOTICE[19013][C-000002cb]: res_odbc.c:1559 > odbc_obj_connect: res_odbc: Connected to MyAsterisk-asterisk [MyAsterisk-asterisk] > > [Apr 25 10:57:01] WARNING[19013][C-000002cb]: res_odbc.c:645 > ast_odbc_prepare_and_execute: SQL Execute returned an error -1: 23000: > [MySQL][ODBC 5.1 Driver][mysqld-5.5.40-0ubuntu0.14.04.1-log]Duplicate entry > '0000-00-00 00:00:00-1234-CLIENT_ID' for key 'PRIMARY' (133) > > [Apr 25 10:57:01] WARNING[19013][C-000002cb]: res_odbc.c:657 > ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying connection to > MyAsterisk-asterisk [MyAsterisk-asterisk]... > > [Apr 25 10:57:01] > WARNING[19013][C-000002cb]: res_odbc.c:761 ast_odbc_sanity_check: Connection is > down attempting to reconnect... > > [Apr 25 10:57:02] > WARNING[7666]: chan_sip.c:4409 __sip_autodestruct: Autodestruct on dialog > '34f3f3481b8d1e4772dc111f42d13bd1 at ip.ip.ip.ip:5060' with owner > SIP/CLIENT_ID_1-00000178 in place (Method: BYE). Rescheduling destruction > for 10000 ms > > [Apr 25 10:57:06] NOTICE[19013][C-000002cb]: res_odbc.c:1527 > odbc_obj_connect: Connecting MyAsterisk-asterisk > > [Apr 25 10:57:06] > NOTICE[19013][C-000002cb]: res_odbc.c:1559 odbc_obj_connect: res_odbc: > Connected to MyAsterisk-asterisk [MyAsterisk-asterisk] > > [Apr 25 10:57:06] > WARNING[19013][C-000002cb]: cdr_adaptive_odbc.c:739 odbc_log: > cdr_adaptive_odbc: Insert failed on 'MyAsterisk-asterisk:cdr'. CDR failed: INSERT > INTO cdr > (dst,accountcode,clid,src,dcontext,channel,dstchannel,lastapp,duration,billsec,disposition,amaflags,userfield,lastdata,uniqueid) > VALUES (blahblahblah, ... ,'1429970147.612') >Can you post the output of "describe <schema>;"? At least the first error message is probably related to a not so optimal primary key definition. jg
On Sat, 25 Apr 2015 17:11:34 +0200 jg <webaccounts173 at jgoettgens.de> wrote:> > > Hi All > > > > I have dozens of these messages on CLI complaining about database > > connection and error writing CDR to disk. > > > > The curious thing is I can find them all inside the database. > > I "selected" them using uniqueid and manually compared each column > > with the cdr_adaptive_odbc.c error line. > > > > "mysqlcheck -a -e -v DBase" and "mysqlcheck -c -e -v DBase" both > > returned OK for all tables. > > > > Environment is: > > in production Asterisk 11.7.0~dfsg-1ubuntu1 > > Ubuntu 14.04.1 LTS > > > > Any thoughts? > > > > Thanx > > > > Ethy > > > > [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:645 > > ast_odbc_prepare_and_execute: SQL Execute returned an error -1: 23000: > > [MySQL][ODBC 5.1 Driver][mysqld-5.5.40-0ubuntu0.14.04.1-log]Duplicate > > entry '0000-00-00 00:00:00-1234-CLIENT_ID' for key 'PRIMARY' (133) > > > > [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:657 > > ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying > > connection to MyAsterisk-asterisk [MyAsterisk-asterisk]... > > > > [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:761 > > ast_odbc_sanity_check: Connection is down attempting to reconnect... > > > > [Apr 25 10:57:01] NOTICE[19013][C-000002cb]: res_odbc.c:1527 > > odbc_obj_connect: Connecting MyAsterisk-asterisk > > > > [Apr 25 10:57:01] NOTICE[19013][C-000002cb]: res_odbc.c:1559 > > odbc_obj_connect: res_odbc: Connected to MyAsterisk-asterisk > > [MyAsterisk-asterisk] > > > > [Apr 25 10:57:01] WARNING[19013][C-000002cb]: res_odbc.c:645 > > ast_odbc_prepare_and_execute: SQL Execute returned an error -1: 23000: > > [MySQL][ODBC 5.1 Driver][mysqld-5.5.40-0ubuntu0.14.04.1-log]Duplicate > > entry '0000-00-00 00:00:00-1234-CLIENT_ID' for key 'PRIMARY' (133) > > > > [Apr 25 10:57:01] WARNING[19013][C-000002cb]: res_odbc.c:657 > > ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying > > connection to MyAsterisk-asterisk [MyAsterisk-asterisk]... > > > > [Apr 25 10:57:01] > > WARNING[19013][C-000002cb]: res_odbc.c:761 ast_odbc_sanity_check: > > Connection is down attempting to reconnect... > > > > [Apr 25 10:57:02] > > WARNING[7666]: chan_sip.c:4409 __sip_autodestruct: Autodestruct on > > dialog '34f3f3481b8d1e4772dc111f42d13bd1 at ip.ip.ip.ip:5060' with owner > > SIP/CLIENT_ID_1-00000178 in place (Method: BYE). Rescheduling > > destruction for 10000 ms > > > > [Apr 25 10:57:06] NOTICE[19013][C-000002cb]: res_odbc.c:1527 > > odbc_obj_connect: Connecting MyAsterisk-asterisk > > > > [Apr 25 10:57:06] > > NOTICE[19013][C-000002cb]: res_odbc.c:1559 odbc_obj_connect: res_odbc: > > Connected to MyAsterisk-asterisk [MyAsterisk-asterisk] > > > > [Apr 25 10:57:06] > > WARNING[19013][C-000002cb]: cdr_adaptive_odbc.c:739 odbc_log: > > cdr_adaptive_odbc: Insert failed on 'MyAsterisk-asterisk:cdr'. CDR > > failed: INSERT INTO cdr > > (dst,accountcode,clid,src,dcontext,channel,dstchannel,lastapp,duration,billsec,disposition,amaflags,userfield,lastdata,uniqueid) > > VALUES (blahblahblah, ... ,'1429970147.612') > > > Can you post the output of "describe <schema>;"? At least the first > error message is probably related to a not so optimal primary key > definition.Thanx for the reply. request follows... mysql> describe cdr ; +-------------+--------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------------------+-------+ | calldate | datetime | NO | PRI | 0000-00-00 00:00:00 | | | dst | varchar(80) | NO | PRI | NULL | | | accountcode | varchar(20) | NO | PRI | NULL | | | clid | varchar(80) | NO | | NULL | | | src | varchar(80) | NO | MUL | NULL | | | dcontext | varchar(80) | NO | | NULL | | | channel | varchar(80) | NO | | NULL | | | dstchannel | varchar(80) | NO | | NULL | | | lastapp | varchar(80) | NO | | NULL | | | duration | int(11) | NO | | 0 | | | billsec | int(11) | NO | | 0 | | | disposition | varchar(45) | NO | MUL | NULL | | | amaflags | int(11) | NO | | 0 | | | userfield | varchar(255) | NO | | NULL | | | lastdata | varchar(80) | NO | | NULL | | | uniqueid | varchar(32) | YES | MUL | NULL | | +-------------+--------------+------+-----+---------------------+-------+ 16 rows in set (0.00 sec) FYI this has been running smooth for years. This "problem" started a few days ago. Ethy
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 04/25/2015 10:51 PM, Ethy H. Brito wrote:> > Hi All > > I have dozens of these messages on CLI complaining about database > connection and error writing CDR to disk. > > The curious thing is I can find them all inside the database. I > "selected" them using uniqueid and manually compared each column > with the cdr_adaptive_odbc.c error line. > > "mysqlcheck -a -e -v DBase" and "mysqlcheck -c -e -v DBase" both > returned OK for all tables. > > Environment is: in production Asterisk 11.7.0~dfsg-1ubuntu1 Ubuntu > 14.04.1 LTS > > Any thoughts? > > Thanx > > Ethy > > [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:645 > ast_odbc_prepare_and_execute: SQL Execute returned an error -1: > 23000: [MySQL][ODBC 5.1 > Driver][mysqld-5.5.40-0ubuntu0.14.04.1-log]Duplicate entry > '0000-00-00 00:00:00-1234-CLIENT_ID' for key 'PRIMARY' (133)Hi Ethy, why date and time are empty? At least date is used as a unique key and a unique key has to be unique. In other words, the same key can not exist twice like in your case. Check why there is no date and time anymore ... Regards Guenther -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQIcBAEBAgAGBQJVPEuqAAoJENexF5oIz3BCQaoP/2foZJ2Um5m49rgGe8jd+zEY NwQucaye9O+euCNpjae08SaO8RnlD7WSK1H+jCsXw/pNp+NsQ+2kTDzOIdDLdmj7 sg93SjAUdEz0bivrhzcjf9K+Z6DKG4EOsHEpqwStIVaGU95JjUYza79ka1Evsghg roJaSP9DpbpN4+lnVdPTRX2LUUK4okB4YKuVcxRUg7QfZ9BS0M5KzZOSg8HvjmN8 uEbyvNsX2tO6awhxn3pX4Pl/x/z3EzzuIKiVzckT3UdyBu67eIkCgcHzrIPUlwZQ Wk/gz1W5dqjKPoImrGDxEZJi0PnAsuvtAE/TzN3CdyG8gA5vYvf3K2WhJ5ri2XNG Kr78HHV2rA5VOr45yx1vjUYhjdNt1Q0OkJ4WAV2scUOyUvQLgbc29pEXgOrQn0Tr 4JJXuBDyYsvGupLOrKP/c7gBPLYsCHUl9XfRoBKEelM8rQFBbLz3xtxvZuGzNlH9 +4uZ3EvqJ7LuN4cZH9xCqYQHbbXdk5/CXo8OKoz0Nw4FTk/wWSZicEJ/AqCof+be GlEFvlKrPY+NaoLH4l0zf27Lff6k1oaYLnxEMMcvOXaqYPLhK2gvQeBhh43GiIBJ 6fTER9WRtW/RfvzBmnpvI850f1Ws8qfF/gbbGLOqwL8d7WOeHgeQ7SPdnhLFhHYE JPHXBlT9rFqT28M4Z9QJ =NVPO -----END PGP SIGNATURE-----
>> Hi All >> >> I have dozens of these messages on CLI complaining about database >> connection and error writing CDR to disk. >> >> The curious thing is I can find them all inside the database. I >> "selected" them using uniqueid and manually compared each column >> with the cdr_adaptive_odbc.c error line. >> >> "mysqlcheck -a -e -v DBase" and "mysqlcheck -c -e -v DBase" both >> returned OK for all tables. >> >> Environment is: in production Asterisk 11.7.0~dfsg-1ubuntu1 Ubuntu >> 14.04.1 LTS >> >> Any thoughts? >> >> Thanx >> >> Ethy >> >> [Apr 25 10:56:56] WARNING[19013][C-000002cb]: res_odbc.c:645 >> ast_odbc_prepare_and_execute: SQL Execute returned an error -1: >> 23000: [MySQL][ODBC 5.1 >> Driver][mysqld-5.5.40-0ubuntu0.14.04.1-log]Duplicate entry >> '0000-00-00 00:00:00-1234-CLIENT_ID' for key 'PRIMARY' (133) > Hi Ethy, > > why date and time are empty? > > At least date is used as a unique key and a unique key has to be > unique. In other words, the same key can not exist twice like in your > case. > > Check why there is no date and time anymore ... >Or define your table with and independent primary key that gets added automatically: mysql> describe cdr; +------------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------------------+----------------+ *| id | int(11) | NO | PRI | NULL | auto_increment |* | clid | varchar(80) | NO | | | | | src | varchar(80) | NO | MUL | | | | dst | varchar(80) | NO | | | | ... | lastapp | varchar(80) | NO | | | | | lastdata | varchar(80) | NO | | | | | duration | int(11) | NO | | 0 | | | billsec | int(11) | NO | | 0 | | | disposition | varchar(45) | NO | | | | | start | datetime | NO | MUL | 0000-00-00 00:00:00 | | | answer | datetime | NO | | 0000-00-00 00:00:00 | | | end | datetime | NO | | 0000-00-00 00:00:00 | | | uniqueid | varchar(45) | NO | | | | ... Just in case you get bogus records with offending primary keys due to some other problem, you would still have valid data base entries and you would be able to look at the pattern. jg -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://lists.digium.com/pipermail/asterisk-users/attachments/20150426/a3d991a7/attachment.html>