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]

Re: Handle a transaction through a script reload ?

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_em ail_email_no.or g> napisal w wiadomosci
news:40140e66$0 $19265$626a54ce @news.free.fr.. .[color=blue]
> 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[/color]

[Arnaud]

Re: Handle a transaction through a script reload ?

"Chung Leong" <chernyshevsky@ hotmail.com> a écrit dans le message de
news:IJqdnbXQ8d yEPYndRVn-uA@comcast.com. ..[color=blue]
> 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[/color]
ends[color=blue]
> before the transaction block does..."[/color]

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_shutdo wn_function() to unlock
or rollback if a problem occured ?

bye,
Arnaud

[André Næss]

Re: Handle a transaction through a script reload ?

Arnaud:
[color=blue]
> 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.[/color]

Can't you just extend the timeout?

http://www.php.net/manual/en/function.set-time-limit.php

André Næss

[Chung Leong]

Re: Handle a transaction through a script reload ?

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.or g> napisal w wiadomosci
news:4014c118$0 $17131$626a54ce @news.free.fr.. .[color=blue]
> "Chung Leong" <chernyshevsky@ hotmail.com> a écrit dans le message de
> news:IJqdnbXQ8d yEPYndRVn-uA@comcast.com. ..[color=green]
> > Wait. Doesn't the PHP manual give you the solution you're looking for?
> >
> > "... is that when using transactions, a transaction block will also[/color][/color]
carry[color=blue][color=green]
> > over to the next script which uses that connection if script execution[/color]
> ends[color=green]
> > before the transaction block does..."[/color]
>
> 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,[/color]
about[color=blue]
> 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_shutdo wn_function() to[/color]
unlock[color=blue]
> or rollback if a problem occured ?
>
> bye,
> Arnaud
>
>
>[/color]

[Arnaud]

Re: Handle a transaction through a script reload ?


"André Næss" <andrena.spamre allysucks@ifi.u io.no> a écrit dans le message de
news:bv2sg7$juo $1@maud.ifi.uio .no...[color=blue]
> Can't you just extend the timeout?
> http://www.php.net/manual/en/functio...time-limit.php[/color]

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]

Re: Handle a transaction through a script reload ?

On Mon, 26 Jan 2004 00:00:50 -0500, "Chung Leong" <chernyshevsky@ hotmail.com>
wrote:
[color=blue][color=green]
>> 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.[/color]
>
>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..."[/color]

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 <andy@andyh.co. uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>

[Arnaud]

Re: Handle a transaction through a script reload ?


"Andy Hassall" <andy@andyh.co. uk> a écrit dans le message de
news:3g7b10157e qpetkntl4pshfrs lrkea5rl5@4ax.c om[color=blue]
> 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[/color]
get[color=blue]
> the same MySQL session on the next page, so you can't continue the[/color]
transaction.

So an idea to resolve that problem ?
thanks,

Arnaud

[André Næss]

Re: Handle a transaction through a script reload ?

Arnaud:
[color=blue]
> 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.[/color]

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]

Re: Handle a transaction through a script reload ?

Hi!
[color=blue]
>
>"Andy Hassall" <andy@andyh.co. uk> a écrit dans le message de
>news:3g7b10157 eqpetkntl4pshfr slrkea5rl5@4ax. com[color=green]
>> 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[/color]
>get[color=green]
>> the same MySQL session on the next page, so you can't continue the[/color]
>transaction.
>
>So an idea to resolve that problem ?
>thanks,[/color]

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]

Re: Handle a transaction through a script reload ?


"Chung Leong" <chernyshevsky@ hotmail.com> a écrit dans le message de
news:yr2dnROg3O PQyIjdRVn-ig@comcast.com. ..[color=blue]
> You would probably need to see the number of persistent connections to 1,[/color]
in[color=blue]
> order to guarantee that you get the same connection with consecutive
> requests. Maybe that's the inefficiency the manual refers to.[/color]

I'll try.
[color=blue]
> For time consuming tasks like yours, most people would just spawn a new
> instance of PHP and do everything in a single shot.[/color]

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

Thanks,
Arnaud

[Arnaud]

Re: Handle a transaction through a script reload ?


"Jochen Daum" <jochen.daum@ca ns.co.nz> a écrit dans le message de
news:7hfd10t8e5 2o23pctolufpfts id5ur39su@4ax.c om...
[color=blue]
> 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.[/color]

I don't see this function anywhere ?
[color=blue]
> Instead, implement your transactions on the client side[/color]

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]

Re: Handle a transaction through a script reload ?


"André Næss" <andrena.spamre allysucks@ifi.u io.no> a écrit dans le message de
news:bv56rc$oq0 $1@maud.ifi.uio .no...[color=blue]
> Generate a SQL file with the SQL necessary for performing this mass insert
> and process the SQL file with the MySQL command line tool.[/color]

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]

Re: Handle a transaction through a script reload ?

Hi Arnaud!

On Wed, 28 Jan 2004 08:27:09 +0100, "Arnaud"
<no_email@email _no_no_email.or g> wrote:
[color=blue]
>[color=green]
>> 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.[/color]
>
>I don't see this function anywhere ?[/color]

Sorry, mixed it up, its register_shutdo wn_function()[color=blue]
>[color=green]
>> Instead, implement your transactions on the client side[/color]
>
>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...[/color]

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[color=blue]
>
>bye,
>Arnaud
>[/color]

--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/

[Arnaud]

Re: Handle a transaction through a script reload ?


"Jochen Daum" <jochen.daum@ca ns.co.nz> a écrit dans le message de[color=blue][color=green]
> >What do you mean ?
> >I'm trying to construct a script which allow some end user guy to update[/color][/color]
a[color=blue][color=green]
> >database, i don't want them to run a mysql command or things like that...[/color]
>
> 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.[/color]

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

Bye,
Arnaud