Handle a transaction through a script reload ?

[Arnaud]

Hi,
I have a script which inserts some rows in an InnoDB table.
I want to be sure that all the rows are inserted, so i use a mysql
transaction ("start transaction", insertions, and "commit").

Problem is that i also want to avoid a timeout (about 60 000 lines are
concerned and InnoDB tables are much slower than MyISAM ones) and i cut
that process in x reloads of the script.

Let's say for the example my script can insert 10 lines each time it
runs and that i have to insert 15 lines only.
1) the script runs, i guess the "log" of the transaction contains my ten
first lines.
2) the script reloads itself and stop after the last 5 lines (it started
with the 11th line).
3) Guess what i have in my table ?
4) the last 5 lines : the transaction forgot my ten first lines...

I know the mysql connection is lost at the end of the first execution of
the script, but how can i manage this to avoid this loss of data ?
I read in
http://www.php.net/manual/en/feature...onnections.php that a
persistent connection to mysql wasn't the solution, so i don't see today
how handle such a mysql transaction through my x reloads of the script.
The website runs PHP Version 4.3.4 and MySQL version 4.0.16.

Any ideas ?
thanks,
Arnaud

[Chung Leong]

Wait. Doesn't the PHP manual give you the solution you're looking for?

"... is that when using transactions, a transaction block will also carry
over to the next script which uses that connection if script execution ends
before the transaction block does..."

Uzytkownik "Arnaud" <no_email@no_email_email_no.org> napisal w wiadomosci
news:40***********************@news.free.fr...

Hi,
I have a script which inserts some rows in an InnoDB table.
I want to be sure that all the rows are inserted, so i use a mysql
transaction ("start transaction", insertions, and "commit").

Problem is that i also want to avoid a timeout (about 60 000 lines are
concerned and InnoDB tables are much slower than MyISAM ones) and i cut
that process in x reloads of the script.

Let's say for the example my script can insert 10 lines each time it
runs and that i have to insert 15 lines only.
1) the script runs, i guess the "log" of the transaction contains my ten
first lines.
2) the script reloads itself and stop after the last 5 lines (it started
with the 11th line).
3) Guess what i have in my table ?
4) the last 5 lines : the transaction forgot my ten first lines...

I know the mysql connection is lost at the end of the first execution of
the script, but how can i manage this to avoid this loss of data ?
I read in
http://www.php.net/manual/en/feature...onnections.php that a
persistent connection to mysql wasn't the solution, so i don't see today
how handle such a mysql transaction through my x reloads of the script.
The website runs PHP Version 4.3.4 and MySQL version 4.0.16.

Any ideas ?
thanks,
Arnaud

[Arnaud]

"Chung Leong" <ch***********@hotmail.com> a écrit dans le message de
news:IJ********************@comcast.com...

Wait. Doesn't the PHP manual give you the solution you're looking for?

"... is that when using transactions, a transaction block will also carry
over to the next script which uses that connection if script execution ends before the transaction block does..."

Well you're right, i missed that but i think it concerns the persistent
connection method and i can read at the beginning of the link i gave, about
this kind of connection :
"they do not give you an ability to build up a transaction efficiently".

So you think the only way for me to resolve my problem is to use one of
these persistent connection and call register_shutdown_function() to unlock
or rollback if a problem occured ?

bye,
Arnaud

[André Næss]

Arnaud:

Hi,
I have a script which inserts some rows in an InnoDB table.
I want to be sure that all the rows are inserted, so i use a mysql
transaction ("start transaction", insertions, and "commit").

Problem is that i also want to avoid a timeout (about 60 000 lines are
concerned and InnoDB tables are much slower than MyISAM ones) and i cut
that process in x reloads of the script.

Can't you just extend the timeout?
http://www.php.net/manual/en/functio...time-limit.php

André Næss

[Chung Leong]

You would probably need to see the number of persistent connections to 1, in
order to guarantee that you get the same connection with consecutive
requests. Maybe that's the inefficiency the manual refers to.

For time consuming tasks like yours, most people would just spawn a new
instance of PHP and do everything in a single shot.

Uzytkownik "Arnaud" <no_email@email_no_no_email.org> napisal w wiadomosci
news:40***********************@news.free.fr...

"Chung Leong" <ch***********@hotmail.com> a écrit dans le message de
news:IJ********************@comcast.com...

Wait. Doesn't the PHP manual give you the solution you're looking for?

"... is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends

before the transaction block does..."

Well you're right, i missed that but i think it concerns the persistent
connection method and i can read at the beginning of the link i gave,

about this kind of connection :
"they do not give you an ability to build up a transaction efficiently".

So you think the only way for me to resolve my problem is to use one of
these persistent connection and call register_shutdown_function() to unlock or rollback if a problem occured ?

bye,
Arnaud

[Arnaud]

"André Næss" <an*********************@ifi.uio.no> a écrit dans le message de
news:bv**********@maud.ifi.uio.no...

Can't you just extend the timeout?
http://www.php.net/manual/en/functio...time-limit.php

Hi,
Nice try but it's a safe-mode shared server, so i can't :(
So there is no way to run a transaction inside a script reload ?

Arnaud

[Andy Hassall]

On Mon, 26 Jan 2004 00:00:50 -0500, "Chung Leong" <ch***********@hotmail.com>
wrote:

I have a script which inserts some rows in an InnoDB table.
I want to be sure that all the rows are inserted, so i use a mysql
transaction ("start transaction", insertions, and "commit").

Problem is that i also want to avoid a timeout (about 60 000 lines are
concerned and InnoDB tables are much slower than MyISAM ones) and i cut
that process in x reloads of the script.

Wait. Doesn't the PHP manual give you the solution you're looking for?

"... is that when using transactions, a transaction block will also carry
over to the next script which uses that connection if script execution ends
before the transaction block does..."

That's a warning that there are nasty consequences if you try to span
transactions over multiple requests. You're not in any way guaranteed to get
the same MySQL session on the next page, so you can't continue the transaction.

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

[Arnaud]

"Andy Hassall" <an**@andyh.co.uk> a écrit dans le message de
news:3g********************************@4ax.com

That's a warning that there are nasty consequences if you try to span
transactions over multiple requests. You're not in any way guaranteed to get the same MySQL session on the next page, so you can't continue the

transaction.

So an idea to resolve that problem ?
thanks,

Arnaud

[André Næss]

Arnaud:

Hi,
I have a script which inserts some rows in an InnoDB table.
I want to be sure that all the rows are inserted, so i use a mysql
transaction ("start transaction", insertions, and "commit").

Problem is that i also want to avoid a timeout (about 60 000 lines are
concerned and InnoDB tables are much slower than MyISAM ones) and i cut
that process in x reloads of the script.

Generate a SQL file with the SQL necessary for performing this mass insert
and process the SQL file with the MySQL command line tool.

André Næss

[Jochen Daum]

Hi!

"Andy Hassall" <an**@andyh.co.uk> a écrit dans le message de
news:3g********************************@4ax.com

That's a warning that there are nasty consequences if you try to span
transactions over multiple requests. You're not in any way guaranteed to

get

the same MySQL session on the next page, so you can't continue the

transaction.

So an idea to resolve that problem ?
thanks,

Release the transaction with register_unload_function(). However, I
wouldn't trust such a mechanism too much, because a database lock at
2am means 6 hours downtime still.

Instead, implement your transactions on the client side

HTH, Jochen
--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

[Arnaud]

"Chung Leong" <ch***********@hotmail.com> a écrit dans le message de
news:yr********************@comcast.com...

You would probably need to see the number of persistent connections to 1, in order to guarantee that you get the same connection with consecutive
requests. Maybe that's the inefficiency the manual refers to.
I'll try.
For time consuming tasks like yours, most people would just spawn a new
instance of PHP and do everything in a single shot.

I'm on a shared server, i don't think i can do that.

Thanks,
Arnaud

[Arnaud]

"Jochen Daum" <jo*********@cans.co.nz> a écrit dans le message de
news:7h********************************@4ax.com...

Release the transaction with register_unload_function(). However, I
wouldn't trust such a mechanism too much, because a database lock at
2am means 6 hours downtime still.
I don't see this function anywhere ?
Instead, implement your transactions on the client side

What do you mean ?
I'm trying to construct a script which allow some end user guy to update a
database, i don't want them to run a mysql command or things like that...

bye,
Arnaud

[Arnaud]

"André Næss" <an*********************@ifi.uio.no> a écrit dans le message de
news:bv**********@maud.ifi.uio.no...

Generate a SQL file with the SQL necessary for performing this mass insert
and process the SQL file with the MySQL command line tool.

So i have to create a batch or something, because i don't want the end user
to use the mysql command line tool, moreover the php scripts are hosted on a
shared server and the end users can't access to the mysql command line tool
i guess...

Thanks for the suggestion anyway,
Arnaud

[Jochen Daum]

Hi Arnaud!

On Wed, 28 Jan 2004 08:27:09 +0100, "Arnaud"
<no_email@email_no_no_email.org> wrote:

Release the transaction with register_unload_function(). However, I
wouldn't trust such a mechanism too much, because a database lock at
2am means 6 hours downtime still.
I don't see this function anywhere ?

Sorry, mixed it up, its register_shutdown_function()

Instead, implement your transactions on the client side
What do you mean ?
I'm trying to construct a script which allow some end user guy to update a
database, i don't want them to run a mysql command or things like that...

I meant writing php side software, that is able to do the same as a
transaction, ie. running it atomic (all or nothing) and all the other
stuff you need.

HTH, Jochen
bye,
Arnaud

--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

[Arnaud]

"Jochen Daum" <jo*********@cans.co.nz> a écrit dans le message de

What do you mean ?
I'm trying to construct a script which allow some end user guy to update adatabase, i don't want them to run a mysql command or things like that...

I meant writing php side software, that is able to do the same as a
transaction, ie. running it atomic (all or nothing) and all the other
stuff you need.

Hi Jochen,
ok that's clearer, thanks for your help.

Bye,
Arnaud

[André Næss]

Arnaud:

"André Næss" <an*********************@ifi.uio.no> a écrit dans le message
de news:bv**********@maud.ifi.uio.no...

Generate a SQL file with the SQL necessary for performing this mass
insert and process the SQL file with the MySQL command line tool.

So i have to create a batch or something, because i don't want the end
user to use the mysql command line tool, moreover the php scripts are
hosted on a shared server and the end users can't access to the mysql
command line tool i guess...

Now I'm curious, what sort of data are we talking about here? Because the
user can't be punching values yielding an insert of 60,000 lines? How does
the user submit the data?

André Næss