RE: Prevalidating queries?

Hi andy,
Before starting your migration maake sur to stop mysql and copy all the
datadir to another backup dir (just like directories).
About single transaction, this will be difficult since you manage myisam
tables.

So what we can think to is :
1. execute one query
2. check log
3. if OK, continue with query n+1
4. else rexecute query n <--- But here data can become
incohrent.

A workaround seems to me to alter your table to innodb engine just for
the upgrade. Then start transactions with n grouped queries. Then decide
a commit or rollback.

At the end of teh upgrade, you can come back to myisam.
This is simplier. But you can also decide to take intermadiate backups
when upgrading.

Best Regards
--------------------
Mathias FATENE

Hope that helps
*This not an official mysql support answer



-----Original Message-----
From: Andy Pieters [mailto:andy@vlaamse-kern.com]
Sent: mardi 14 juin 2005 00:15
To: mysql@lists.mysql.com
Subject: Prevalidating queries?


Hi all

As part of an automated patch system, I am facing the following problem:

* A script will update the program from version x to version y
* The script contains file actions, and database (mysql) actions
* The actions are executed in order
* For each action, a backup copy is created (if necessary)
example if action is deletedir then the dir is moved to a temp
directory
* if an action (with status fail=abort) occurs, then the system must be
restored to previous state.

As far as file/directory operation is concerned, this is easy to
implement,
and that's why we are using backup copies.

For the mysql part I don't really see how to do this. I am not using
inodb
but MyIsam tables.

It is not that I need to know the result of the query in advance, only
if
mysql will accept it or will errormessage on the query.

What I don't want is that query1, and 2 are already executed, and 3
fails
because how could I do a rollback then?


Anybody got any ideas?


With kind regards



Andy


--
Registered Linux User Number 379093
-- --BEGIN GEEK CODE BLOCK-----
Version: 3.1
GAT/O/>E$ d-(---)>+ s:(+)>: a--(-)>? C++++$(+++) UL++++>++++$ P-(+)>++
L+++>++++$ E---(-)@ W+++>+++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++)
PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+)
e>++++$@ h++(*) r-->++ y--()>++++
-- ---END GEEK CODE BLOCK------
--
Check out these few php utilities that I released
under the GPL2 and that are meant for use with a
php cli binary:

http://www.vlaamse-kern.com/sas/
--

--


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Prevalidating queries?

--nextPart1429692.4XXXzzeIZL
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all

As part of an automated patch system, I am facing the following problem:

* A script will update the program from version x to version y
* The script contains file actions, and database (mysql) actions
* The actions are executed in order
* For each action, a backup copy is created (if necessary)
example if action is deletedir then the dir is moved to a temp directory
* if an action (with status fail=3Dabort) occurs, then the system must be=20
restored to previous state.=20

As far as file/directory operation is concerned, this is easy to implement,=
=20
and that's why we are using backup copies.

=46or the mysql part I don't really see how to do this. I am not using ino=
db=20
but MyIsam tables.

It is not that I need to know the result of the query in advance, only if=20
mysql will accept it or will errormessage on the query.

What I don't want is that query1, and 2 are already executed, and 3 fails=20
because how could I do a rollback then?


Anybody got any ideas?


With kind regards



Andy


=2D-=20
Registered Linux User Number 379093
=2D- --BEGIN GEEK CODE BLOCK-----
Version: 3.1
GAT/O/>E$ d-(---)>+ s:(+)>: a--(-)>? C++++$(+++) UL++++>++++$ P-(+)>++
L+++>++++$ E---(-)@ W+++>+++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++)
PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+)
e>++++$@ h++(*) r-->++ y--()>++++
=2D- ---END GEEK CODE BLOCK------
=2D-
Check out these few php utilities that I released
under the GPL2 and that are meant for use with a=20
php cli binary:
=20
http://www.vlaamse-kern.com/sas/
=2D-

=2D-

--nextPart1429692.4XXXzzeIZL
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQBCrgVUc7EayKyip4URAqiYAJ9BQ2vLydM7ICPpsYecNMgok52G/QCf e+DS
qE6NIItAjE4z6oz+/wxcSSk=
=MHSy
-----END PGP SIGNATURE-----

--nextPart1429692.4XXXzzeIZL--

Re: Prevalidating queries?

--=_alternative 004A497785257020_=
Content-Type: text/plain; charset="US-ASCII"

Andy Pieters wrote on 06/13/2005 06:14:42 PM:

> Hi all

> As part of an automated patch system, I am facing the following problem:

> * A script will update the program from version x to version y
> * The script contains file actions, and database (mysql) actions
> * The actions are executed in order
> * For each action, a backup copy is created (if necessary)
> example if action is deletedir then the dir is moved to a temp directory
> * if an action (with status fail=abort) occurs, then the system must be
> restored to previous state.

> As far as file/directory operation is concerned, this is easy to
implement,
> and that's why we are using backup copies.

> For the mysql part I don't really see how to do this. I am not using
inodb
> but MyIsam tables.

> It is not that I need to know the result of the query in advance, only
if
> mysql will accept it or will errormessage on the query.

> What I don't want is that query1, and 2 are already executed, and 3
fails
> because how could I do a rollback then?

>
> Anybody got any ideas?

>
> With kind regards

>
> Andy

>
> --
> Registered Linux User Number 379093


Without allowing the database to manage your transaction (that is what you
call what you are doing with your data, a "transaction". You are
predicating the commitment of queries 1 and 2 based on the performance of
query 3.)

To stay with MyISAM as your storage engine, you will need to archive your
original records and restore them through your code (in case of failure)
just as you archiving and restoring your files. That means you will need a
set of "backup" tables to hold the originals to the records that you are
changing.

InnoDB does this automatically. I seriously encourage you to read up on
transactions and InnoDB and I strongly suggest you change your table
design (to use InnoDB). That way you have an actual "ROLLBACK" command at
your disposal. Otherwise you will be re-inventing the wheel by creating a
versioning-locking system for MyISAM when one already exists in InnoDB.

What you need is a wrench to turn that nut but right now you are trying to
use a hammer. Change your tool and your task will become much easier to
accomplish.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 004A497785257020_=--

Re: Prevalidating queries?

--nextPart1150173.3ZIaeF0vaA
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

> I seriously encourage you to read up on
> transactions and InnoDB and I strongly suggest you change your table
> design (to use InnoDB). That way you have an actual "ROLLBACK" command at
> your disposal. Otherwise you will be re-inventing the wheel by creating a
> versioning-locking system for MyISAM when one already exists in InnoDB.
>
> What you need is a wrench to turn that nut but right now you are trying to
> use a hammer. Change your tool and your task will become much easier to
> accomplish.

Thank you for your reply. I realize that there are limitations with the=20
MyISAM engine but the product has been designed from the ground up to use=20
MyISAM tables and is already deployed. Changing database design would be a=
=20
costly affair in my humble opinion. This is something that could be done =
if=20
the customer wants a big update, then I might be able to force that kind of=
=20
change too but until then I'm stuck in MyISAM.

Until someone can tell me "the" answer, I guess I will implement a sort of=
=20
thing as suggested here.

Its just that I want to avoid at all cost that a patch is only half applied=
=20
Imagine finding out what is going on in a product that is "between" version=
s.

With kind regards


Andy

=2D-=20
Registered Linux User Number 379093
=2D- --BEGIN GEEK CODE BLOCK-----
Version: 3.1
GAT/O/>E$ d-(---)>+ s:(+)>: a--(-)>? C++++$(+++) UL++++>++++$ P-(+)>++
L+++>++++$ E---(-)@ W+++>+++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++)
PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+)
e>++++$@ h++(*) r-->++ y--()>++++
=2D- ---END GEEK CODE BLOCK------
=2D-
Check out these few php utilities that I released
under the GPL2 and that are meant for use with a=20
php cli binary:
=20
http://www.vlaamse-kern.com/sas/
=2D-

=2D-

--nextPart1150173.3ZIaeF0vaA
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQBCruKvc7EayKyip4URAvi/AJ4r11Vu++n+QPghOsrwG7xztwcDqACf XIJg
0FZZbIHgpnH0zWbcrsADghU=
=dkdz
-----END PGP SIGNATURE-----

--nextPart1150173.3ZIaeF0vaA--

Re: Prevalidating queries?

--=_alternative 004E4FEC85257020_=
Content-Type: text/plain; charset="US-ASCII"

Andy Pieters wrote on 06/14/2005 09:59:09 AM:

> > I seriously encourage you to read up on
> > transactions and InnoDB and I strongly suggest you change your table
> > design (to use InnoDB). That way you have an actual "ROLLBACK" command
at
> > your disposal. Otherwise you will be re-inventing the wheel by
creating a
> > versioning-locking system for MyISAM when one already exists in
InnoDB.
> >
> > What you need is a wrench to turn that nut but right now you are
trying to
> > use a hammer. Change your tool and your task will become much easier
to
> > accomplish.

> Thank you for your reply. I realize that there are limitations with the
> MyISAM engine but the product has been designed from the ground up to
use
> MyISAM tables and is already deployed. Changing database design would
be a
> costly affair in my humble opinion. This is something that could be
done if
> the customer wants a big update, then I might be able to force that kind
of
> change too but until then I'm stuck in MyISAM.

> Until someone can tell me "the" answer, I guess I will implement a sort
of
> thing as suggested here.

> Its just that I want to avoid at all cost that a patch is only half
applied.
> Imagine finding out what is going on in a product that is "between"
versions.

> With kind regards

>
> Andy

I am sorry I didn't catch on that this is an infrequent need (only for the
upgrade). Here is how I would do the upgrade: Make a duplicate of the old
database into a new database...

#Assuming the original database is called Working

CREATE DATABASE newWorking;
USE newWorking;

CREATE TABLE working1 LIKE Working.working1;
INSERT working1
SELECT *
FROM Working.working1;

CREATE TABLE working2 LIKE Working.working2;
INSERT working2
SELECT *
FROM Working.working2;

CREATE TABLE working3 LIKE Working.working3;
INSERT working3
SELECT *
FROM Working.working3;

CREATE TABLE working4 (
# new table definition here
)

INSERT working4
SELECT
FROM Working.working4;

# and so on until you "migrate" the tables from Working (old-style) to
newWorking (new-style)

At this point you have a set of "backup" tables in the original Working
database and a set of your converted tables in newWorking. Assuming that
everything checks out OK (now is when you perform any last-chance
validation checks on the data in newWorking) you can easily dump the old
Working tables and move all of the newWorking tables into it.

#this assumes that you are still USE-ing newWorking
DROP DATABASE Working;
CREATE DATABASE Working;
RENAME TABLE working1 to Working.working1, working2 to Working.working2,
...., workingN to Working.workingN;

RENAME TABLE is an atomic function, it won't leave a copy behind and it
can't be interfered with by another process (no INSERTS, SELECTS, UPDATES,
or DELETES are permitted while the rename is in progress). By the time you
have finished RENAME-ing the tables, newWorking will be empty and you can
simply

DROP DATABASE newWorking;

Voila! your migration is complete.

Again, sorry for the confusion.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


--=_alternative 004E4FEC85257020_=--

Re: Prevalidating queries? [SOLVED]

--nextPart1673941.A0zuacKpxs
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi

Thanks that really does answer my question and it meets my requirements as=
=20
well.

Thank you


With kind regards


Andy


=2D-=20
Registered Linux User Number 379093
=2D- --BEGIN GEEK CODE BLOCK-----
Version: 3.1
GAT/O/>E$ d-(---)>+ s:(+)>: a--(-)>? C++++$(+++) UL++++>++++$ P-(+)>++
L+++>++++$ E---(-)@ W+++>+++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++)
PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+)
e>++++$@ h++(*) r-->++ y--()>++++
=2D- ---END GEEK CODE BLOCK------
=2D-
Check out these few php utilities that I released
under the GPL2 and that are meant for use with a=20
php cli binary:
=20
http://www.vlaamse-kern.com/sas/
=2D-

=2D-

--nextPart1673941.A0zuacKpxs
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQBCrugxc7EayKyip4URAqLuAJ9gqq1EQGUaSXvRyd4eSUsUYX4uAwCd FV0q
w4obIfUTo0vY0G+k5SLTjh0=
=ElaI
-----END PGP SIGNATURE-----

--nextPart1673941.A0zuacKpxs--