Updating DB Locally

[Paige]

I have a database on my local machine that I make entries and
corrections on. I'd like to be able to upload that to my server and have
that update the database that's on the server. What I've been doing is
saving the local file as a backup. Then I have to edit the backup,
deleting the create file info and all previously uploaded entries before
I can import the text file to the server.

Is there an easier way to do this so I won't get duplicate entry
notices. I'd like the info from my local file to overwrite the server
file like FTPing a new web page will overwrite the old one. Is this
possible to do with mySQL?

[Michael Austin]

Re: Updating DB Locally

Paige wrote:
[color=blue]
> I have a database on my local machine that I make entries and
> corrections on. I'd like to be able to upload that to my server and have
> that update the database that's on the server. What I've been doing is
> saving the local file as a backup. Then I have to edit the backup,
> deleting the create file info and all previously uploaded entries before
> I can import the text file to the server.
>
> Is there an easier way to do this so I won't get duplicate entry
> notices. I'd like the info from my local file to overwrite the server
> file like FTPing a new web page will overwrite the old one. Is this
> possible to do with mySQL?
>[/color]

First, you have not given enough information to give you the proper
response.

What platform (specifically the OS)

Are the corrections/changes DDL (table/column definitions) or DML
(insert/update/delete)?

Is the data in the database static (read w/occasional updates) or is it
a read/write database (new information stored daily).

I take it that your "local machine" is a test system and the other is
"production "

If you do not care what is in your production database and it gets
over-written with the new version:

Backup/Restore your database from one place to another...

http://www.phpbb.com/phpBB/viewtopic.php?t=9963

Michael Austin.

PS -- I would backup the "production " database for safe keeping before
trying this...

[Paige]

Re: Updating DB Locally

Michael Austin wrote:[color=blue]
> Paige wrote:
>[color=green]
>> I have a database on my local machine that I make entries and
>> corrections on. I'd like to be able to upload that to my server and
>> have that update the database that's on the server. What I've been
>> doing is saving the local file as a backup. Then I have to edit the
>> backup, deleting the create file info and all previously uploaded
>> entries before I can import the text file to the server.
>>
>> Is there an easier way to do this so I won't get duplicate entry
>> notices. I'd like the info from my local file to overwrite the server
>> file like FTPing a new web page will overwrite the old one. Is this
>> possible to do with mySQL?
>>[/color]
>
> First, you have not given enough information to give you the proper
> response.
>
> What platform (specifically the OS)[/color]

Win XP
[color=blue]
> Are the corrections/changes DDL (table/column definitions) or DML
> (insert/update/delete)?[/color]

I guess they'd be DML. Nothing changes as to columns or definitions. The
only thing that might change would be the info already stored in an
entrylike if someone was to move and change their address, that sort of
thing. And of course, the new version that I would want to upload would
have more total entries than the old one.
[color=blue]
> Is the data in the database static (read w/occasional updates) or is it
> a read/write database (new information stored daily).[/color]

static
[color=blue]
> I take it that your "local machine" is a test system and the other is
> "production "[/color]

I guess that's a fair assessment. I want to do the work on my WinXP,
then upload the table to the server to replace what is there so I don't
have to be online when I want to work on the database.
[color=blue]
> If you do not care what is in your production database and it gets
> over-written with the new version:[/color]
[color=blue]
> Backup/Restore your database from one place to another...
>
> http://www.phpbb.com/phpBB/viewtopic.php?t=9963[/color]

Thank you for this suggestion and maybe it's me but this thread seems to
involve backing up and restoring phpbb databases on a *new* server/host.
If that was my situation, I would have no problem as there would be no
entries on the new server so there couldn't be any duplicate ones in the
upload. But it's the same table on the same server that I'm looking to
do with the new one overwriting the old one.

[Michael Austin]

Re: Updating DB Locally

Paige wrote:
[color=blue]
> Michael Austin wrote:
>[color=green]
>> Paige wrote:
>>[color=darkred]
>>> I have a database on my local machine that I make entries and
>>> corrections on. I'd like to be able to upload that to my server and
>>> have that update the database that's on the server. What I've been
>>> doing is saving the local file as a backup. Then I have to edit the
>>> backup, deleting the create file info and all previously uploaded
>>> entries before I can import the text file to the server.
>>>
>>> Is there an easier way to do this so I won't get duplicate entry
>>> notices. I'd like the info from my local file to overwrite the server
>>> file like FTPing a new web page will overwrite the old one. Is this
>>> possible to do with mySQL?
>>>[/color]
>>
>> First, you have not given enough information to give you the proper
>> response.
>>
>> What platform (specifically the OS)[/color]
>
>
> Win XP
>[color=green]
>> Are the corrections/changes DDL (table/column definitions) or DML
>> (insert/update/delete)?[/color]
>
>
> I guess they'd be DML. Nothing changes as to columns or definitions. The
> only thing that might change would be the info already stored in an
> entrylike if someone was to move and change their address, that sort of
> thing. And of course, the new version that I would want to upload would
> have more total entries than the old one.
>[color=green]
>> Is the data in the database static (read w/occasional updates) or is
>> it a read/write database (new information stored daily).[/color]
>
>
> static
>[color=green]
>> I take it that your "local machine" is a test system and the other is
>> "production "[/color]
>
>
> I guess that's a fair assessment. I want to do the work on my WinXP,
> then upload the table to the server to replace what is there so I don't
> have to be online when I want to work on the database.
>[color=green]
>> If you do not care what is in your production database and it gets
>> over-written with the new version:[/color]
>
>[color=green]
>> Backup/Restore your database from one place to another...
>>
>> http://www.phpbb.com/phpBB/viewtopic.php?t=9963[/color]
>
>
> Thank you for this suggestion and maybe it's me but this thread seems to
> involve backing up and restoring phpbb databases on a *new* server/host.
> If that was my situation, I would have no problem as there would be no
> entries on the new server so there couldn't be any duplicate ones in the
> upload. But it's the same table on the same server that I'm looking to
> do with the new one overwriting the old one.
>[/color]


If users of the "production " database can change their information and
you make different changes to your "local" database, then you must
guarantee that one does not overwrite the other. Basically you need to
be able to synchronize your databases not overwrite them... true? Next
question is this just one table or multiple tables?


Michael.

[Paige]

Re: Updating DB Locally

Michael Austin wrote:[color=blue]
> Paige wrote:
>[color=green]
>> Michael Austin wrote:
>>[color=darkred]
>>> Paige wrote:
>>>
>>>> I have a database on my local machine that I make entries and
>>>> corrections on. I'd like to be able to upload that to my server and
>>>> have that update the database that's on the server. What I've been
>>>> doing is saving the local file as a backup. Then I have to edit the
>>>> backup, deleting the create file info and all previously uploaded
>>>> entries before I can import the text file to the server.
>>>>
>>>> Is there an easier way to do this so I won't get duplicate entry
>>>> notices. I'd like the info from my local file to overwrite the
>>>> server file like FTPing a new web page will overwrite the old one.
>>>> Is this possible to do with mySQL?
>>>>
>>>
>>> First, you have not given enough information to give you the proper
>>> response.
>>>
>>> What platform (specifically the OS)[/color]
>>
>>
>>
>> Win XP
>>[color=darkred]
>>> Are the corrections/changes DDL (table/column definitions) or DML
>>> (insert/update/delete)?[/color]
>>
>>
>>
>> I guess they'd be DML. Nothing changes as to columns or definitions.
>> The only thing that might change would be the info already stored in
>> an entrylike if someone was to move and change their address, that
>> sort of thing. And of course, the new version that I would want to
>> upload would have more total entries than the old one.
>>[color=darkred]
>>> Is the data in the database static (read w/occasional updates) or is
>>> it a read/write database (new information stored daily).[/color]
>>
>>
>>
>> static
>>[color=darkred]
>>> I take it that your "local machine" is a test system and the other is
>>> "production "[/color]
>>
>>
>>
>> I guess that's a fair assessment. I want to do the work on my WinXP,
>> then upload the table to the server to replace what is there so I
>> don't have to be online when I want to work on the database.
>>[color=darkred]
>>> If you do not care what is in your production database and it gets
>>> over-written with the new version:[/color]
>>
>>
>>[color=darkred]
>>> Backup/Restore your database from one place to another...
>>>
>>> http://www.phpbb.com/phpBB/viewtopic.php?t=9963[/color]
>>
>>
>>
>> Thank you for this suggestion and maybe it's me but this thread seems
>> to involve backing up and restoring phpbb databases on a *new*
>> server/host. If that was my situation, I would have no problem as
>> there would be no entries on the new server so there couldn't be any
>> duplicate ones in the upload. But it's the same table on the same
>> server that I'm looking to do with the new one overwriting the old one.
>>[/color]
>
>
> If users of the "production " database can change their information and
> you make different changes to your "local" database, then you must
> guarantee that one does not overwrite the other. Basically you need to
> be able to synchronize your databases not overwrite them... true? Next
> question is this just one table or multiple tables?
>[/color]

I am the only one that changes the database entries. There is no form
info being directly submitted to the db and no one else touches it. So
at all times, the db on my computer is the most up-to-date one.

I'm sorry but I don't know the difference between overwriting and
synchronizing a database. I only know that I'd like to be able to upload
the latest db info from my computer to the server and have that one
overwrite, replace, update (or whatever terminology fits) the one that's
on the server.

As it stands right now, the only way I can accomplish this is to either
drop the entire server database and reinstall it using the updated copy
or else I have to edit all the entries in the .sql backup I make to
delete existing entries and mark the ones I changed as UPDATE instead of
INSERT. Neither way is particularly appealing, thus my inquiry as to an
easieer way to do this.

[Gordon Burditt]

Re: Updating DB Locally

>I am the only one that changes the database entries. There is no form[color=blue]
>info being directly submitted to the db and no one else touches it. So
>at all times, the db on my computer is the most up-to-date one.
>
>I'm sorry but I don't know the difference between overwriting and
>synchronizin g a database.[/color]

If the "production " database is truly read-only, there is no difference.

If there are modifications to both databases (say, there is a 'last
used' field changed when a user accesses the web site, or the user
can change his/her password), then you have to merge the changes
for true synchronization , and overwriting just wipes out all the
changes made in the production database from the web and replaces
them from the master copy. Real synchronization requires intelligent
information about what fields are updated on which database for
what purpose. And if the tables are designed poorly, it may be
impossible to do right at all. Overwriting is always possible
(assuming the system is up and you don't run out of disk space,
etc.) but in practice it may be a disaster (lost updates).

Since your updates seem to be entered in one place only, you might
consider setting up replication if you are willing to have changes
made on the test server appear almost instantly on the production
server (which could ruin its use as a 'test' server, or it might
just eliminate the chore of propagating updates entirely).
[color=blue]
>I only know that I'd like to be able to upload
>the latest db info from my computer to the server and have that one
>overwrite, replace, update (or whatever terminology fits) the one that's
>on the server.[/color]

I'd look at various options of mysqldump. You don't have to delete
and re-create the tables if the schema didn't change (-t, no table
creation info). You can also lock the tables so all the updates
get done, from the point of view of the web server, all at once.
[color=blue]
>As it stands right now, the only way I can accomplish this is to either
>drop the entire server database and reinstall it using the updated copy
>or else I have to edit all the entries in the .sql backup I make to
>delete existing entries and mark the ones I changed as UPDATE instead of
>INSERT. Neither way is particularly appealing, thus my inquiry as to an
>easieer way to do this.[/color]

I don't understand why ignoring warnings about inserting duplicate
keys is a problem. I'm presuming that you have UNIQUE keys set up
so re-inserting the same data you already have won't result in
duplicate records. The mysql command-line utility has the -f option,
keep going on SQL error, which will take care of stopping on hitting
an already-existing record.

mysqldump also permits adding a WHERE clause to the data you dump,
so if there is a time stamp (or some other means you can identify
new records, like an autoincrement sequence number if you only ADD
new records and never change existing ones) on the records, you can
just dump stuff since the last dump and load those. Of course you
have to keep track of this info for the next dump/copy.

Is the volume of data such that uploading all of it each time is a
problem?

Gordon L. Burditt

[Paige]

Re: Updating DB Locally

Gordon Burditt wrote:[color=blue][color=green]
>>I am the only one that changes the database entries. There is no form
>>info being directly submitted to the db and no one else touches it. So
>>at all times, the db on my computer is the most up-to-date one.
>>
>>I'm sorry but I don't know the difference between overwriting and
>>synchronizi ng a database.[/color]
>
>
> If the "production " database is truly read-only, there is no difference.
>
> If there are modifications to both databases (say, there is a 'last
> used' field changed when a user accesses the web site, or the user
> can change his/her password), then you have to merge the changes
> for true synchronization , and overwriting just wipes out all the
> changes made in the production database from the web and replaces
> them from the master copy. Real synchronization requires intelligent
> information about what fields are updated on which database for
> what purpose. And if the tables are designed poorly, it may be
> impossible to do right at all. Overwriting is always possible
> (assuming the system is up and you don't run out of disk space,
> etc.) but in practice it may be a disaster (lost updates).[/color]

But as I said, there is no writing done to the database on the server at
all. It does not contain users/passwords. It contains information only
and is not used as any part of a login system. The only copy that
changes if the one on my computer. When I have finished making those
changes, I need a streamlined way to upload them from my computer to the
server which is not mine.
[color=blue]
> Since your updates seem to be entered in one place only, you might
> consider setting up replication if you are willing to have changes
> made on the test server appear almost instantly on the production
> server (which could ruin its use as a 'test' server, or it might
> just eliminate the chore of propagating updates entirely).[/color]

Where would I find out more about this method?
[color=blue]
>[color=green]
>>I only know that I'd like to be able to upload
>>the latest db info from my computer to the server and have that one
>>overwrite, replace, update (or whatever terminology fits) the one that's
>>on the server.[/color]
>
>
> I'd look at various options of mysqldump. You don't have to delete
> and re-create the tables if the schema didn't change (-t, no table
> creation info). You can also lock the tables so all the updates
> get done, from the point of view of the web server, all at once.
>
>[color=green]
>>As it stands right now, the only way I can accomplish this is to either
>>drop the entire server database and reinstall it using the updated copy
>>or else I have to edit all the entries in the .sql backup I make to
>>delete existing entries and mark the ones I changed as UPDATE instead of
>>INSERT. Neither way is particularly appealing, thus my inquiry as to an
>>easieer way to do this.[/color]
>
>
> I don't understand why ignoring warnings about inserting duplicate
> keys is a problem. I'm presuming that you have UNIQUE keys set up
> so re-inserting the same data you already have won't result in
> duplicate records. The mysql command-line utility has the -f option,
> keep going on SQL error, which will take care of stopping on hitting
> an already-existing record.[/color]

I don't have command line access to the server database, only to the
copy on my computer. The duplicate entries is a problem because
sometimes the info in a specific entry changes (people move, change
phone numbers, marry, etc) and I need the new info to overwrite or
somehow supplant the old when I upload my changes. The db only has 5
fields and the primary is the auto incrementing ID.
[color=blue]
> mysqldump also permits adding a WHERE clause to the data you dump,
> so if there is a time stamp (or some other means you can identify
> new records, like an autoincrement sequence number if you only ADD
> new records and never change existing ones) on the records, you can
> just dump stuff since the last dump and load those. Of course you
> have to keep track of this info for the next dump/copy.[/color]

I understand that part. Adding the new fields only is easy enough even
using my backup file, open file, edit file, save file, upload file
method. It's the fields that changed that are more work. I'll have to
read more on mysqldump to see if it's something I can use or adapt.
[color=blue]
> Is the volume of data such that uploading all of it each time is a
> problem?[/color]

Well there are over 700 entries in it right now and it's still growing.

[Michael Austin]

Re: Updating DB Locally

Paige wrote:
[color=blue]
> Gordon Burditt wrote:
>[color=green][color=darkred]
>>> I am the only one that changes the database entries. There is no form
>>> info being directly submitted to the db and no one else touches it.
>>> So at all times, the db on my computer is the most up-to-date one.
>>>
>>> I'm sorry but I don't know the difference between overwriting and
>>> synchronizing a database.[/color]
>>
>>
>>
>> If the "production " database is truly read-only, there is no difference.
>>
>> If there are modifications to both databases (say, there is a 'last
>> used' field changed when a user accesses the web site, or the user
>> can change his/her password), then you have to merge the changes
>> for true synchronization , and overwriting just wipes out all the
>> changes made in the production database from the web and replaces
>> them from the master copy. Real synchronization requires intelligent
>> information about what fields are updated on which database for
>> what purpose. And if the tables are designed poorly, it may be
>> impossible to do right at all. Overwriting is always possible
>> (assuming the system is up and you don't run out of disk space,
>> etc.) but in practice it may be a disaster (lost updates).[/color]
>
>
> But as I said, there is no writing done to the database on the server at
> all. It does not contain users/passwords. It contains information only
> and is not used as any part of a login system. The only copy that
> changes if the one on my computer. When I have finished making those
> changes, I need a streamlined way to upload them from my computer to the
> server which is not mine.
>[color=green]
>> Since your updates seem to be entered in one place only, you might
>> consider setting up replication if you are willing to have changes
>> made on the test server appear almost instantly on the production
>> server (which could ruin its use as a 'test' server, or it might
>> just eliminate the chore of propagating updates entirely).[/color]
>
>
> Where would I find out more about this method?
>[color=green]
>>[color=darkred]
>>> I only know that I'd like to be able to upload the latest db info
>>> from my computer to the server and have that one overwrite, replace,
>>> update (or whatever terminology fits) the one that's on the server.[/color]
>>
>>
>>
>> I'd look at various options of mysqldump. You don't have to delete
>> and re-create the tables if the schema didn't change (-t, no table
>> creation info). You can also lock the tables so all the updates
>> get done, from the point of view of the web server, all at once.
>>
>>[color=darkred]
>>> As it stands right now, the only way I can accomplish this is to
>>> either drop the entire server database and reinstall it using the
>>> updated copy or else I have to edit all the entries in the .sql
>>> backup I make to delete existing entries and mark the ones I changed
>>> as UPDATE instead of INSERT. Neither way is particularly appealing,
>>> thus my inquiry as to an easieer way to do this.[/color]
>>
>>
>>
>> I don't understand why ignoring warnings about inserting duplicate
>> keys is a problem. I'm presuming that you have UNIQUE keys set up
>> so re-inserting the same data you already have won't result in
>> duplicate records. The mysql command-line utility has the -f option,
>> keep going on SQL error, which will take care of stopping on hitting
>> an already-existing record.[/color]
>
>
> I don't have command line access to the server database, only to the
> copy on my computer. The duplicate entries is a problem because
> sometimes the info in a specific entry changes (people move, change
> phone numbers, marry, etc) and I need the new info to overwrite or
> somehow supplant the old when I upload my changes. The db only has 5
> fields and the primary is the auto incrementing ID.
>[color=green]
>> mysqldump also permits adding a WHERE clause to the data you dump,
>> so if there is a time stamp (or some other means you can identify
>> new records, like an autoincrement sequence number if you only ADD
>> new records and never change existing ones) on the records, you can
>> just dump stuff since the last dump and load those. Of course you
>> have to keep track of this info for the next dump/copy.[/color]
>
>
> I understand that part. Adding the new fields only is easy enough even
> using my backup file, open file, edit file, save file, upload file
> method. It's the fields that changed that are more work. I'll have to
> read more on mysqldump to see if it's something I can use or adapt.
>[color=green]
>> Is the volume of data such that uploading all of it each time is a
>> problem?[/color]
>
>
> Well there are over 700 entries in it right now and it's still growing.
>[/color]


There was an entry recently that had the title "How to copy mysql tables
between two servers?" in this ng. It was ugly, but it appeared to work...

I am guessing you want this update to be somewhat automagic at some
really early hour in the morning...

This is what I would do...

add a column to your "master" database (local) called updatetime of type
date and initdate of type date.

extract data where updatetime = initdate and updatetime > yesterday...
create insert statements for these

extract data where updatetime > yesterday and initdate < yesterday -
create update statements for these...

** yesterday = 00:00 what ever the day before today is...

like:
mysql> select * from t;
+-----------+------------+------------+
| d | initdate | updatedate |
+-----------+------------+------------+
| today | 2004-06-21 | 2004-06-21 |
| not today | 2004-01-01 | 2004-06-21 |
| yesterday | 2004-06-19 | 2004-06-20 |
+-----------+------------+------------+
3 rows in set (0.01 sec)

!!!!!!!!!!!! UPDATES !!!!!!!!!!!!!!! !!!!!
mysql> select * from t where updatedate=curd ate() and initdate <> curdate();
+-----------+------------+------------+
| d | initdate | updatedate |
+-----------+------------+------------+
| not today | 2004-01-01 | 2004-06-21 |
+-----------+------------+------------+
1 row in set (0.00 sec)

!!!!!!!!!!!!! INSERTS !!!!!!!!!!!!!!
mysql> select * from t where updatedate=curd ate() and initdate = curdate();
+-------+------------+------------+
| d | initdate | updatedate |
+-------+------------+------------+
| today | 2004-06-21 | 2004-06-21 |
+-------+------------+------------+
1 row in set (0.01 sec)