解决Incorrect datetime value: '' for column 'time' at row 1的问题

时间：2022-08-24 10:39:42
环境说明：
操作系统：REHL 6.3
开发语言：C

数据库：Mysql 5.6


病症：
最近做linux 下数据库存储的开发，对于一张数据库表中的一个DATETIME字段进行插入操作，各种存储变量值设置完成后，进行mysql_stmt_execute()操作，mysql_stmt_error()返回：Incorrect datetime value: '' for column 'time' at row 1。错误信息是指time字段的datetime 值不正确,而且此错误仅出现在进行第一次插入的时候，第二次、第三次以及以后的都正常。进行插入操作我是通过C API的预处理语句来执行的。代码如下:

int insert_enydata(MYSQL *mysql, q_socketinfo *p_sock, q_enydata *p)
{
if(mysql == NULL || p == NULL)
return -1;

char insql[] = "insert into q_enydata(srcip, srcport, dstip, dstport, qid, qqver, seqno, ncmd, ntype, data, time) \
values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

MYSQL_BIND bind[11];
unsigned long length, srclen, dstlen;
MYSQL_TIME ts;
MYSQL_STMT *stmt = mysql_stmt_init(mysql);
char str[] = "0";

if (!stmt)
{
fprintf(stderr, " mysql_stmt_init(), out of memory\n");
return -1;
}

if (mysql_stmt_prepare(stmt, insql, strlen(insql)))
{
fprintf(stderr, "\n mysql_stmt_prepare(), INSERT failed");
goto MYSQL_ERROR;
}

//sip
memset(bind, 0, sizeof(bind));
bind[0].buffer= (char *)(p_sock->sip);
bind[0].buffer_type= MYSQL_TYPE_VAR_STRING;
bind[0].buffer_length= STRING_SIZE;
bind[0].is_null= 0;
bind[0].length= &srclen;

//sport
bind[1].buffer = (char *)&(p_sock->sport);
bind[1].buffer_type = MYSQL_TYPE_LONG;
bind[1].length= 0;
bind[1].is_null= 0;

//dip
bind[2].buffer = (char *)(p_sock->dip);
bind[2].buffer_type = MYSQL_TYPE_VAR_STRING;
bind[2].buffer_length = STRING_SIZE;
bind[2].length= &dstlen;
bind[2].is_null= 0;

//dport
bind[3].buffer = (char *)&(p_sock->dport);
bind[3].buffer_type = MYSQL_TYPE_LONG;
bind[3].length= 0;
bind[3].is_null= 0;

//qid
bind[4].buffer = (char *)&(p->qid);
bind[4].buffer_type = MYSQL_TYPE_LONG;
bind[4].length= 0;
bind[4].is_null= 0;

//qqver
bind[5].buffer = (char *)&(p->qqver);
bind[5].buffer_type = MYSQL_TYPE_LONG;
bind[5].length= 0;
bind[5].is_null= 0;

//seqno
bind[6].buffer = (char *)&(p->seqno);
bind[6].buffer_type = MYSQL_TYPE_LONG;
bind[6].length= 0;
bind[6].is_null= 0;

//ncmd
bind[7].buffer = (char *)&(p->ncmd);
bind[7].buffer_type = MYSQL_TYPE_LONG;
bind[7].length= 0;
bind[7].is_null= 0;

//ntype
bind[8].buffer = (char *)&(p->ntype);
bind[8].buffer_type = MYSQL_TYPE_LONG;
bind[8].length= 0;
bind[8].is_null= 0;

//data
bind[9].buffer = str;
bind[9].buffer_type = MYSQL_TYPE_LONG_BLOB;
bind[9].buffer_length = DATA_LEN;
bind[9].length= &length;
bind[9].is_null= 0;

//time
bind[10].buffer = (char *)&ts;
bind[10].buffer_type = MYSQL_TYPE_DATETIME;
bind[10].length= 0;
bind[10].is_null= 0;

// Bind the buffers
if (mysql_stmt_bind_param(stmt, bind))
{
fprintf(stderr, "\n param bind failed");
goto MYSQL_ERROR;
}

// Supply data in chunks to server
srclen = strlen((char *)p_sock->sip);
dstlen = strlen((char *)p_sock->dip);
length = p->len;

if (mysql_stmt_send_long_data(stmt, 9, (char *)p->data, length))
{
fprintf(stderr, "\n send_long_data failed");
goto MYSQL_ERROR;
}

time_t tmt = p->time;
struct tm *ltime = localtime(&tmt);
ts.year = ltime->tm_year+1900;
ts.month = ltime->tm_mon+1;
ts.day = ltime->tm_mday;
ts.hour = ltime->tm_hour;
ts.minute = ltime->tm_min;
ts.second = ltime->tm_sec;

// Execute the query
if (mysql_stmt_execute(stmt))
{
fprintf(stderr, "\n mysql_stmt_execute failed");
goto MYSQL_ERROR;
}
mysql_stmt_close(stmt);

return get_id(mysql, "select MAX(id) from q_enydata");

MYSQL_ERROR:
fprintf(stderr, "\n Stmt error: %s\n Error: %s\n", mysql_stmt_error(stmt), mysql_error(mysql));
mysql_stmt_close(stmt);
return -1;
}

以上代码是返回错误前的代码。

        在数据库表中，time字段是DATETIME类型。根据Mysql 对MYSQL_BIND结构的buffer_type成员的定义，表结构中SQL类型是DATETIME对应Buffer_type是MYSQL_TYPE_DATETIME，其C类型是MYSQL_TIME。表明代码没错，绑定类型也是正确的，所以应该不会出现不正确的DATETIME值。

       继续分析每次进行插入操作时ts和ltime的值进行查看对比，通过GDB调试比较第一次（插入失败）、第二次（插入成功）、第三次（插入成功）.......发现如下情况：

第一次：

(gdb) p ts
$1 = {year = 2013, month = 6, day = 5, hour = 12, minute = 42, second = 42,
  second_part = 140737354126944, neg = 48 '0', time_type = 32767}
(gdb) p *ltime
$2 = {tm_sec = 42, tm_min = 42, tm_hour = 12, tm_mday = 5, tm_mon = 5,
  tm_year = 113, tm_wday = 3, tm_yday = 155, tm_isdst = 0, tm_gmtoff = 28800,
  tm_zone = 0x637040 "CST"}
(gdb)

第二次： 

(gdb) p ts
$3 = {year = 2013, month = 6, day = 5, hour = 12, minute = 42, second = 42, 
  second_part = 0, neg = 111 'o', time_type = MYSQL_TIMESTAMP_DATE}
(gdb) p *ltime
$4 = {tm_sec = 42, tm_min = 42, tm_hour = 12, tm_mday = 5, tm_mon = 5, 
  tm_year = 113, tm_wday = 3, tm_yday = 155, tm_isdst = 0, tm_gmtoff = 28800, 
  tm_zone = 0x637040 "CST"}
(gdb)

第三次： 

(gdb) p ts
$5 = {year = 2013, month = 6, day = 5, hour = 12, minute = 42, second = 42, 
  second_part = 0, neg = 0 '\000', time_type = MYSQL_TIMESTAMP_DATE}
(gdb) p *ltime
$6 = {tm_sec = 42, tm_min = 42, tm_hour = 12, tm_mday = 5, tm_mon = 5, 
  tm_year = 113, tm_wday = 3, tm_yday = 155, tm_isdst = 0, tm_gmtoff = 28800, 
  tm_zone = 0x637040 "CST"}
(gdb)

由上可以比较发现两处特别差异：
ts是MYSQL_TIME类型的结构体变量，其成员second_part和time_type在三次比较中，第一次异于后两次。因此大胆的假设在此情况ts变量的second_part和time_type的值分别为0和MYSQL_TIMESTAMP_DATE。咱在如下代码之后： 

time_t tmt = p->time;
struct tm *ltime = localtime(&tmt);
ts.year = ltime->tm_year+1900;
ts.month = ltime->tm_mon+1;
ts.day = ltime->tm_mday;
ts.hour = ltime->tm_hour;
ts.minute = ltime->tm_min;
ts.second = ltime->tm_sec;

添加如下两句： 

ts.second_part = 0;
ts.time_type = MYSQL_TIMESTAMP_DATE;

正常执行，发觉问题解决了。good！


延伸：
在mysql_time.h中，对于MYSQL_TIME结构体：

typedef struct st_mysql_time
{
  unsigned int  year, month, day, hour, minute, second;
  unsigned long second_part;  /**< microseconds */
  my_bool       neg;
  enum enum_mysql_timestamp_type time_type;
} MYSQL_TIME;

对于枚举enum_mysql_timestamp_type类型： 

enum enum_mysql_timestamp_type
{
  MYSQL_TIMESTAMP_NONE= -2, MYSQL_TIMESTAMP_ERROR= -1,
  MYSQL_TIMESTAMP_DATE= 0, MYSQL_TIMESTAMP_DATETIME= 1, MYSQL_TIMESTAMP_TIME= 2
};

其中可以发现枚举类型对于特定的时间字段DATE、DATETIME、TIMESTAMP有一一对应的值： 

MYSQL_TIMESTAMP_DATE= 0, MYSQL_TIMESTAMP_DATETIME= 1, MYSQL_TIMESTAMP_TIME= 2

以后切记在使用MYSQL_TIME结构体的时候，如果出错，及时的对每一个成员赋相应的值。。。。
Over，好困啊！该午睡了！！！！！！








标签：

相关文章