this is strange, when i do this..
===============================================
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tbltemp (tmp_crdd_no,tmp_serial_no,tmp_date)
values('100','100',NOW());
Query OK, 1 row affected (0.00 sec)
mysql> select * from tbltemp;
+--------+--------------+---------------+----------------------+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date |
+--------+--------------+---------------+----------------------+
| 1 | 100 | 100 | 2005-06-08
03:16:58 |
+--------+--------------+---------------+----------------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from tbltemp;
Empty set (0.01 sec)
=============================================
all is ok but when i load the file it autocommits.. example below
=============================================
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> LOAD DATA INFILE '/usr/local/apache2/htdocs/test.csv' INTO TABLE
tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(tmp_crdd_no,tmp_serial_no,tmp_date);
Query OK, 47 rows affected (0.01 sec)
Records: 47 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from tbltemp limit 10;
+--------+-------------+---------------+-----------------+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date |
+--------+-------------+---------------+-----------------+
| 2 | 415162501 | 7198108197 | 9/10/2003 12:00 |
| 3 | 415162502 | 4403223482 | 8/31/2003 12:17 |
| 4 | 415162503 | 4438355415 | 8/31/2003 20:11 |
| 5 | 415162504 | 5023027348 | 8/31/2003 14:22 |
| 6 | 415162505 | 8090096387 | 9/2/2003 22:38 |
| 7 | 415162506 | 8192661837 | 9/1/2003 16:42 |
| 8 | 415162507 | 2746612555 | 8/27/2003 22:06 |
| 9 | 415162508 | 8789620243 | 9/1/2003 22:42 |
| 10 | 415162509 | 5422205728 | 8/27/2003 20:05 |
| 11 | 415162510 | 8760612473 | 8/24/2003 14:40 |
+--------+-------------+---------------+-----------------+
10 rows in set (0.01 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tbltemp limit 10;
+--------+-------------+---------------+-----------------+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date |
+--------+-------------+---------------+-----------------+
| 2 | 415162501 | 7198108197 | 9/10/2003 12:00 |
| 3 | 415162502 | 4403223482 | 8/31/2003 12:17 |
| 4 | 415162503 | 4438355415 | 8/31/2003 20:11 |
| 5 | 415162504 | 5023027348 | 8/31/2003 14:22 |
| 6 | 415162505 | 8090096387 | 9/2/2003 22:38 |
| 7 | 415162506 | 8192661837 | 9/1/2003 16:42 |
| 8 | 415162507 | 2746612555 | 8/27/2003 22:06 |
| 9 | 415162508 | 8789620243 | 9/1/2003 22:42 |
| 10 | 415162509 | 5422205728 | 8/27/2003 20:05 |
| 11 | 415162510 | 8760612473 | 8/24/2003 14:40 |
+--------+-------------+---------------+-----------------+
10 rows in set (0.01 sec)
i also checked server status variables before starting and after ending
transaction. everything looks fine but cant seem to figure out what is
heppeing here.i even upgraded to latest version, any help will be
appriciated thanks in advance
Haseeb Iqbal
----- Original Message -----
From: "°l||l° Jinxed °l||l°" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Tuesday, June 07, 2005 1:21 PM
Subject: Re: LOAD DATA INFILE with INNODB
> hi,
> yes you did it and i failed to do so. thats why i am here.
> i am using MYSQL 5.0.3 beta-standard with RedHat 9 , the test involves
only
> innodb as i mentioned in the very first post,
> i have read docs and it says that there are 3 ways i can start a
transaction
> and i tried all three i.e. begin work statement, start transaction and SET
> AUTOCOMMIT=0;
> regards
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: "°l||l° Jinxed °l||l°" <[EMAIL PROTECTED]>
> Cc: <mysql@lists.mysql.com>
> Sent: Tuesday, June 07, 2005 11:08 AM
> Subject: Re: LOAD DATA INFILE with INNODB
>
>
> > Hi,
> > i did it. If you have myisam tables tables rather than innodb say it.
> > if you have specific os, say it.
> >
> > i think you should elaborate, or read carrefully dev.mysql.com/doc
> >
> > Mathias
> >
> > Selon °l||l° Jinxed °l||l° <[EMAIL PROTECTED]>:
> >
> > > i used start transaction before using SET AUTOCOMMIT=0; also i dont
see
> any
> > > difference between the two. if there is please elaborate.
> > >
> > >
> > > ----- Original Message ----- .
> > > From: <[EMAIL PROTECTED]>
> > > To: "°l||l° Jinxed °l||l°" <[EMAIL PROTECTED]>
> > > Cc: <mysql@lists.mysql.com>
> > > Sent: Tuesday, June 07, 2005 3:17 AM
> > > Subject: Re: LOAD DATA INFILE with INNODB
> > >
> > >
> > > > Hi,
> > > > you transaction is implicit, so there has been an autocommit.
> > > >
> > > > Look at this example !
> > > >
> > > >
> > > > mysql> start transaction;
> > > > ^^^^^^^^^^^^^^^^^^
> > > >
> > > > mysql> load data infile 'd:\\ldfile.txt' into table ldfile;
> > > > Query OK, 3 rows affected (0.00 sec)
> > > > Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
> > > >
> > > > mysql> select * from ldfile;
> > > > +------+
> > > > | i |
> > > > +------+
> > > > | 1 |
> > > > | 2 |
> > > > | 3 |
> > > > +------+
> > > > 3 rows in set (0.00 sec)
> > > >
> > > > mysql> rollback;
> > > > Query OK, 0 rows affected (0.03 sec)
> > > >
> > > > mysql> select * from ldfile;
> > > > Empty set (0.00 sec)
> > > >
> > > >
> > > > This a not a good idea if the file is big. Ideally, truncate the
table
> if
> > > there
> > > > has been a problem witha big file.
> > > >
> > > >
> > > > Mathias
> > > >
> > > >
> > > > Selon °l||l° Jinxed °l||l° <[EMAIL PROTECTED]>:
> > > >
> > > > > hi, I have been pulling my hair for last couple of days.i want to
> put
> > > few sol
> > > > > statements in TRANSACTION BLOCK. all the tables involved are of
type
> > > innodb.
> > > > > the first SQL statement in the block is LOAD DATA INFILE. inside
the
> > > block (
> > > > > using PHP ) i am checking for errors and incase of error i want to
> > > rollback.
> > > > > but strangely when i tried to rollback it just wouldn't do. i
> thought
> > > may be
> > > > > PHP is giving problems. then i did this
> > > > >
> > > > > ===========================================================
> > > > > SET AUTOCOMMIT=0;
> > > > > Query OK, 0 rows affected (0.00 sec)
> > > > >
> > > > > select @@autocommit as autocommit;
> > > > > +-----------------+
> > > > > | autocommit |
> > > > > +-----------------+
> > > > > | 0 |
> > > > > +-----------------+
> > > > > 1 row in set (0.00 sec)
> > > > >
> > > > > LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS
> TERMINATED
> > > BY ','
> > > > > LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date);
> > > > > Query OK, 27265 rows affected (4.48 sec)
> > > > > Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0
> > > > >
> > > > > rollback;
> > > > > Query OK, 0 rows affected (0.00 sec)
> > > > >
> > > > > ===========================================================
> > > > >
> > > > > when i looked in tbltemp i found out that the CSV file has been
> loaded
> > > > > although i rolled back the transaction. i used insert statement
and
> > > rolled
> > > > > back with no problem, so the problem was narrowed down to LOAD
DATA
> > > INFILE. i
> > > > > have read about LOAD DATA INFILE and found nothing about this
> strange
> > > > > behavior. is there anything that i am missing out?
> > > > >
> > > > > Regards
> > > > > Haseeb Iqbal
> > > >
> > > >
> > >
> > >
> > >
> > >
> > >
> > > ___________________________________________________________
> > > Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with
> > > voicemail http://uk.messenger.yahoo.com
> > >
> >
> >
>
>
>
>
>
> ___________________________________________________________
> Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with
voicemail http://uk.messenger.yahoo.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
___________________________________________________________
Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail
http://uk.messenger.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
