我的程序是在window VC6.0和MySQL 5.1下编写和测试的,VS2008测试结果也一样。
程序功能为从txt文本中读入大概20万条数据写入某个表中,但是每次测试都是写入大概1万条记录后,在某个特定的位置就写不进去了,数据库插入操作返回的状态不正常,MySQL有默认设置表项的存储空间大小吗,如何设置?
程序功能为从txt文本中读入大概20万条数据写入某个表中,但是每次测试都是写入大概1万条记录后,在某个特定的位置就写不进去了,数据库插入操作返回的状态不正常,MySQL有默认设置表项的存储空间大小吗,如何设置?
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)
+----------------------------+----------------------+
| Variable_name | Value |
+----------------------------+----------------------+
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294963200 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
+----------------------------+----------------------+
19 rows in set (0.00 sec)
mysql> show variables like "Thread_%";
+-------------------+---------------------------+
| Variable_name | Value |
+-------------------+---------------------------+
| thread_cache_size | 8 |
| thread_handling | one-thread-per-connection |
| thread_stack | 196608 |
+-------------------+---------------------------+
3 rows in set (0.00 sec)mysql> show status like "Thread_%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 1 |
| Threads_connected | 1 |
| Threads_created | 2 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
2. 可能的原因 max_allowed_packet 限制
3. 可能的原因 事务限制
4. 可能的原因 数据本身 问题,比如违反 主键,外键,
5 可能的原因 字符集的问题,你的记录中突然出现的中文或者日文。
。总之来说,你提供的信息,是让大家猜谜语。问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
2. 贴出你的MYSQL中的错误日志或者普通日志
1.与数据连接的主要代码
#pragma comment(lib,"libmysql.lib")MYSQL *sock; //declare a MYSQL structure point
MYSQL_RES *results;
bool connectMysql(char *host,char *user,char *password,char *db)
{
MYSQL_ROW record;
sock = mysql_init(0); //initialize MYSQL structure
if(sock){
cout << "sock handle ok!" << endl;
}else{
cout << "sock handle failed!" << mysql_error(sock) <<endl;
} /* connect to server with the CLIENT_MULTI_STATEMENTS option */
if(mysql_real_connect(sock, host, user, password, db, 0, NULL, CLIENT_MULTI_STATEMENTS)){
cout << "connection ok!" << endl;
}else{
cout << "connection fail:" << mysql_error(sock) << endl;
mysql_close(sock);
exit(1);
} mysql_set_character_set(sock,"gb2312"); cout << "connection character set: " << mysql_character_set_name(sock) << endl; if(mysql_query(sock,"select * from user")){
cout << "very good!" << endl;
}
results=mysql_store_result(sock);
cout << "host \t username"<<endl; while(record = mysql_fetch_row(results)){
cout << record[0] <<" \t "<< record[1] << endl;
}
return EXIT_SUCCESS;
}2.建表函数
void createTables(MYSQL *sock)
{
/* execute multiple statements */
mysql_status = mysql_query(sock,
"DROP TABLE IF EXISTS JNCTCrdnt;\
DROP TABLE IF EXISTS NTLink;\
CREATE TABLE NTLink(ID INT NOT NULL PRIMARY KEY,\
NAME VARCHAR(50),\
FRC SMALLINT,\
PJ SMALLINT,\
METRES FLOAT,\
F_JNCTID INT,\
T_JNCTID INT,\
SLIPRD SMALLINT,\
ONEWAY CHAR(1),\
F_ELEV SMALLINT,\
T_ELEV SMALLINT,\
KPH SMALLINT,\
MINUTES FLOAT,\
NSP SMALLINT)TYPE=INNODB DEFAULT CHARSET=gb2312;\
CREATE TABLE JNCTCrdnt(ID INT NOT NULL,\
crdntX DECIMAL(10,6),\
crdntY DECIMAL(10,6),\
CONSTRAINT crdnt_fr FOREIGN KEY(ID) REFERENCES NTLink(ID) \
on update cascade on delete cascade)TYPE=INNODB DEFAULT CHARSET=gb2312"); if (mysql_status){ //mysql_status is 0 if sucess
cout<<"Could not execute statement(s)"<<endl;
mysql_close(sock);
exit(0);
} do {
/* did current statement return data? */
results = mysql_store_result(sock);
if (results){
/* yes; process rows and free the result set */
process_result_set(sock, results);
mysql_free_result(results);
}else{ /* no result set or error */
if(mysql_field_count(sock) == 0){
rows_counter = mysql_affected_rows(sock);
cout <<rows_counter<<" rows affected\n";
}else{ /* some error occurred */
cout<<"Could not retrieve result set\n";
break;
}
}
/* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
if ((mysql_status = mysql_next_result(sock)) > 0)
cout<<"Could not execute statement\n";
} while (mysql_status == 0);
}3.写入数据库的主要代码
void insertDataToMysql()
{
int loop = 0;
CString sSQL;
sSQL.Format("INSERT INTO NTLink VALUES(%d,'%s',%d,%d,%f,%d,%d,%d,'%c',%d,%d,%d,%f,%d)",\
ntlk.id,ntlk.name,ntlk.frc,ntlk.pj,ntlk.meters,ntlk.f_junctid,ntlk.t_junctid,\
ntlk.sliprd,ntlk.oneway,ntlk.f_elev,ntlk.t_elev,ntlk.kph,ntlk.minutes,ntlk.nsp);
_bstr_t vSQL = _bstr_t(sSQL); mysql_status = mysql_query(sock,vSQL); if (mysql_status){ //mysql_status is 0 if sucess
cout<<"Could not execute statement(s)"<<endl
<<"Insert data into table NTLink failed"<<endl;
mysql_close(sock);
exit(0);
} while(loop<= ntlk.nsp){
/* cout<<"loop="<<loop<<endl;
cout<<crdnt.at(loop)<<"\t"<<crdnt.at(loop+1)<<endl;*/
CString cSQL;
cSQL.Format("INSERT INTO JNCTCrdnt VALUES(%d,%f,%f)",ntlk.id,crdnt.at(loop),crdnt.at(loop+1)); mysql_status = mysql_query(sock,_bstr_t(cSQL));
/* mysql_status = mysql_query(sock,
"INSERT INTO JNCTCrdnt VALUES(ntlk.id,crdnt.at(loop),crdnt.at(loop+1))");*/
if (mysql_status){
cout<<"Could not execute statement(s)"<<endl
<<"Insert data into table JNCTCrdnt failed!"<<endl;
}else{
results = mysql_store_result(sock);
if (results){
/* yes; process rows and free the result set */
//process_result_set(sock, results);
mysql_free_result(results);
}else{ /* no result set or error */
if(mysql_field_count(sock) == 0){
rows_counter = mysql_affected_rows(sock);
cout <<rows_counter<<" rows affected\n";
}else{ /* some error occurred */
cout<<"Could not retrieve result set\n";
break;
}
}
}
loop+=2;
}
crdnt.clear();
//Sleep(10);
}
备注:ntlk是我定义的一个结构体,crdnt定义的一个vector对象,创建了两个INNODB类型的数据库表分别存储ntlk和vector的内容。
ntlk.id = 211992408
ntlk.name = Temasek Boulevard <co 4> <re 21> <ci 477☻
Read from file: 2
1 rows affected
1 rows affected
查询数据库表,结果如下:
mysql> select * from ntlink where ID=211992408;
+-----------+-------------------------------------------+------+------+---------
+-----------+-----------+--------+--------+--------+--------+------+---------+--
----+
| ID | NAME | FRC | PJ | METRES
| F_JNCTID | T_JNCTID | SLIPRD | ONEWAY | F_ELEV | T_ELEV | KPH | MINUTES | N
SP |
+-----------+-------------------------------------------+------+------+---------
+-----------+-----------+--------+--------+--------+--------+------+---------+--
----+
| 211992408 | Temasek Boulevard <co 4> <re 21> <ci 477☻ | 2 | 0 | 67.9974
| 817628522 | 817628581 | 0 | U | 0 | 0 | 56 | 4 |
2 |
+-----------+-------------------------------------------+------+------+---------
+-----------+-----------+--------+--------+--------+--------+------+---------+--
----+
1 row in set (0.00 sec)程序出错后cmd的输出:
ntlk.id = 211992660
ntlk.name = St. Andrew's Road <co 4> <re 21> <ci 477☻
Read from file: 4
Could not execute statement(s)
Insert data into table NTLink failed
Press any key to continue . . .