MySQL Forums
Forum List  »  Newbie

Re: large number of insert with autocommit set to off
Posted by: Hans-Peter Sloot
Date: April 05, 2012 04:50AM

It looks as if the problem occurs when I wait a long time before committing.
When I do commit more or less immediatly it does not reproduce.

mysql> SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE TABLE_SCHEMA = 'sh';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| CUSTOMERS | NULL | 61863 |
| SALES | SALES_1995 | 0 |
| SALES | SALES_1996 | 0 |
| SALES | SALES_H1_1997 | 0 |
| SALES | SALES_H2_1997 | 0 |
| SALES | SALES_Q1_1998 | 44082 |
| SALES | SALES_Q2_1998 | 33714 |
| SALES | SALES_Q3_1998 | 48784 |
| SALES | SALES_Q4_1998 | 46221 |
| SALES | SALES_Q1_1999 | 64383 |
| SALES | SALES_Q2_1999 | 54523 |
| SALES | SALES_Q3_1999 | 67573 |
| SALES | SALES_Q4_1999 | 62643 |
| SALES | SALES_Q1_2000 | 62353 |
| SALES | SALES_Q2_2000 | 55683 |
| SALES | SALES_Q3_2000 | 59163 |
| SALES | SALES_Q4_2000 | 56263 |
| SALES | SALES_Q1_2001 | 60903 |
| SALES | SALES_Q2_2001 | 63513 |
| SALES | SALES_Q3_2001 | 66123 |
| SALES | SALES_Q4_2001 | 70184 |
| SALES | SALES_Q1_2002 | 0 |
| SALES | SALES_Q2_2002 | 0 |
| SALES | SALES_Q3_2002 | 0 |
| SALES | SALES_Q4_2002 | 0 |
| SALES | SALES_Q1_2003 | 0 |
| SALES | SALES_Q2_2003 | 0 |
| SALES | SALES_Q3_2003 | 0 |
| SALES | SALES_Q4_2003 | 0 |
+------------+----------------+------------+
29 rows in set (0.10 sec)

mysql> select count(*) from SALES;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@nlvora33 mysql]# service mysqld stop
Stopping mysqld: [ OK ]
[root@nlvora33 mysql]# service mysqld start
Starting mysqld: [ OK ]
[root@nlvora33 mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.52 Source distribution

....

mysql> use sh
....

Database changed
mysql> select count(*) from SALES;
+----------+
| count(*) |
+----------+
| 918843 |
+----------+
1 row in set (0.73 sec)

mysql>

So after restarting the service I can see the rows.

Regards Hans-Peter



Edited 1 time(s). Last edit at 04/05/2012 05:18AM by Hans-Peter Sloot.

Options: ReplyQuote


Subject
Written By
Posted
Re: large number of insert with autocommit set to off
April 05, 2012 04:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.