as it turns out it was a MYSQL BUG
http://bugs.mysql.com/bug.php?id=11151&error=no
thanks anyway
regards Haseeb
----- Original Message -----
From: "°l||l° Jinxed °l||l°" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Wednesday, June 08, 2005 3:32 AM
Subject: Re: LOAD DATA INFILE with INNODB
> 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]
>
___________________________________________________________
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]
