Professional Web Applications Themes

version control for mysql database - MySQL

Hi, That's my first time to send mail to this address for asking help. Sorry for my poor english firstly. My case is like this: Many guys are using a mysql database, each guy has a database copy in his own PC, they need to do some changes in their local database from time to time. then all guys need submit the changes to the central database for sharing. Is there a good tool or good way to do version control of a database? I'm thinking a very stupid way whicy is by mysqldump, but I suspect it maybe will ...

  1. #1

    Default version control for mysql database

    Hi,

    That's my first time to send mail to this address for asking help.
    Sorry for my poor english firstly.

    My case is like this:
    Many guys are using a mysql database, each guy has a database copy in
    his own PC, they need to do some changes in their local database from
    time to time. then all guys need submit the changes to the central
    database for sharing.

    Is there a good tool or good way to do version control of a database?
    I'm thinking a very stupid way whicy is by mysqldump, but I suspect it
    maybe will lost data in some special cases.

    The stupid mysqldump idea is: I can use mysqldump to dump local
    database to a txt file, then using subversion to do version control of
    the txt file. After changing local database, each guy can run mysqldump
    to dump his local database to a txt file, then commit it to a central
    dump file repository, also they need update local dump file from
    central dump file repository to get somebody else's changes. then they
    can restore local database by updated dump file.

    Maybe you will ask me why don't let all guys to share a central
    database, the reason is it seems like very hard to do security control,
    because all guys need to change same table in same database(only
    different records), our current application does not have any security
    control for the database. on the other hand, to do version control of
    database, we can record all guy's change in an easy way, and we can
    arrange a manager to review changing patch, it seems like a better way.

    Another small question is : if two guys add some different records at
    the same time, those different records will have same primary key(which
    is INTEGER auto-incremented), althrough I can merge those new added
    records into one file and try to restore all of them to database, but,
    database will claim "duplicate id" error. So I'm thinking if there is a
    switch which can control mysql to change the duplicated id to a
    auto-incremented value and insert it to database automatically?

    Thanks in advance!

    Hans Guest

  2. #2

    Default Re: version control for mysql database

    On 29 Sep 2006 01:55:13 -0700, I waved a wand and this message
    magically appears in front of Hans:
     

    Why are you not using one dedicated PC with MySQL installed on it, and
    have all these people connect to it from their PC?
    --
    http://www.munted.org.uk

    You've been eating the cat food again, haven't you?
    Alex Guest

  3. #3

    Default Re: version control for mysql database

    Hans wrote: 

    Hans,

    I would suggest fixing your current application. It would not be hard
    at all to limit the rows they can edit, even if they're all in the same
    table. You just have to write your application to only allow
    modification of the appropriate rows. You see it all the time - i.e.
    member databases, where a person can change their own profile but not
    someone else's, or forums where someone can edit their own messages but
    not other people's.

    Other solutions can cause other problems - like you've noticed with the
    primary key.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: version control for mysql database

    On 29 Sep 2006 01:55:13 -0700, Hans wrote: 

    This is pretty much what databases do. That's how they're supposed to
    work. You can write things that use databases to do reviewed and
    unreviewed changes, but that's application-level programming, not
    something managed by the database.
     

    Sadly, that tool is a relational database system. Which means, in order
    to do this version control stuff simply and easily, you need to have all
    the records in a single database to begin with. There are other ways to
    do what you're wanting to do, and some even do it pretty well (at the
    expense of doing other things pretty poorly). Lotus Notes* does the
    whole "distributed maintenance collaborative database" thing pretty
    well, but it's not something that you can just point an SQL client at,
    and you'd need more or less rewrite your entire application to port it
    over to Notes. However, the idea that you're running the application
    without security even on an internal network is one that I think very
    risky from the beginning. Not all risks are external.

    * Disclaim: I do have a small interest in IBM, so my recommendation of
    their products may be biased. Please make your own evaluation of
    suitability.

    --
    89. After I capture the hero's superweapon, I will not immediately disband my
    legions and relax my guard because I believe whoever holds the weapon is
    unstoppable. After all, the hero held the weapon and I took it from him.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  5. #5

    Default Re: version control for mysql database

    > My case is like this: 

    As you state later on that you are using subversion, is there some way
    to run newly created modification files directly on the localhost
    databases? The problem is (as I can see it) that the changes are
    submitted to the central server, but it is not distributed back in the
    same time to the copies.
     

    I usually worked with update scripts and used complete dumps only once
    in a while. Especially in the beginning phase, a complete dump is good.
    Not only for communication, but also for disaster recovery. But this
    script tends to have more structure than data. The more data it gathers,
    the more work it is to maintain it. In that case, update scripts can be
    an answer.
    If you set your local database to logging all SQL, you can easily find
    back what you have done, even if you did it through some sophisticated
    application.
     

    I understand perfectly why you want a localhost sandbox. I used them
    even in companies where it was not standard practice (and made friends
    when the central server was brought down for maintenance and they could
    use my copy for the time being). When developing, I want to be able to
    make stupid mistakes, like UPDATE statements with WHERE clauses that
    accidentally always evaluate to TRUE. Or even "what would a hacker do"
    dirty experiments. Without making your colleagues angry.
     

    I always try to make my update script data-independent. Instead of
    giving the ID of a record to insert it to and use that same ID for the
    related records, you can assign the LAST_INSERT_ID() to a variable and
    use that:
    INSERT INTO Templates(...) VALUES (....);
    SET TemplateID=LAST_INSERT_ID();
    INSERT INTO TemplateFields(TemplateID) VALUES (TemplateID,...);
    etc. No chance of any duplicate IDs now.
    Dikkie Guest

  6. #6

    Default Re: version control for mysql database

    Thanks for reply.
    I'm very interested at your update scripts. Do you mean you use the
    update scripts to work on log tract file of local database, find what
    was changed and then commit the same changes to central database?

    I can not imagine how hard this script will be if without subversion.
    A big advantage of version control system is merge function. two
    actions should be done in the script: one is update from central
    database(merge somebody else's changes to local database), another one
    is commit changes to central database(merge local changes to central
    database). Both need to detect conflict.

    For example,
    1.start from version 10, PC1 and PC2 both hold version 10.
    2. then PC1 changed record A and B in tb1. local script read the log
    and send it to central DB, then central DB's version will be changed to
    11.
    3. then PC2 changed record A and C in tb1. If the script just read the
    local log and send it to central DB, then changes made by PC1 will be
    destroyed silently.
    So srcipt have to compare local version and central version, found
    local version is out of date, then update it, then script will found
    there is conflict for record A, and then ask admin intervention to
    resolve the conflict.

    Kinds of this function is not easy to implement in a script, at least
    for me. So I hope to get help from stable version control system, like
    subversion. but, so sad, subversion cannot do version control to
    database directly.

    multiple-master tion seems like a good idea, but, it can not
    provide "changes review" function and security control function to
    central server.

    Rgds,

    Dikkie Dik wrote: 
    >
    > As you state later on that you are using subversion, is there some way
    > to run newly created modification files directly on the localhost
    > databases? The problem is (as I can see it) that the changes are
    > submitted to the central server, but it is not distributed back in the
    > same time to the copies.

    >
    > I usually worked with update scripts and used complete dumps only once
    > in a while. Especially in the beginning phase, a complete dump is good.
    > Not only for communication, but also for disaster recovery. But this
    > script tends to have more structure than data. The more data it gathers,
    > the more work it is to maintain it. In that case, update scripts can be
    > an answer.
    > If you set your local database to logging all SQL, you can easily find
    > back what you have done, even if you did it through some sophisticated
    > application.

    >
    > I understand perfectly why you want a localhost sandbox. I used them
    > even in companies where it was not standard practice (and made friends
    > when the central server was brought down for maintenance and they could
    > use my copy for the time being). When developing, I want to be able to
    > make stupid mistakes, like UPDATE statements with WHERE clauses that
    > accidentally always evaluate to TRUE. Or even "what would a hacker do"
    > dirty experiments. Without making your colleagues angry.

    >
    > I always try to make my update script data-independent. Instead of
    > giving the ID of a record to insert it to and use that same ID for the
    > related records, you can assign the LAST_INSERT_ID() to a variable and
    > use that:
    > INSERT INTO Templates(...) VALUES (....);
    > SET TemplateID=LAST_INSERT_ID();
    > INSERT INTO TemplateFields(TemplateID) VALUES (TemplateID,...);
    > etc. No chance of any duplicate IDs now.[/ref]

    Hans Guest

Similar Threads

  1. MySQL Database not retrieving the full database
    By geetha.veeraiah@gmail.com in forum MySQL
    Replies: 4
    Last Post: July 21st, 09:34 PM
  2. Replies: 4
    Last Post: March 23rd, 11:21 AM
  3. Version Control Software for Database Objects
    By Mark Dexter in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 14th, 02:40 PM
  4. [PHP] Installing PHP with MySQL RPM version
    By Mark McCulligh in forum PHP Development
    Replies: 2
    Last Post: September 9th, 07:32 PM
  5. Ruby/MySQL bug (0.1.4 version)
    By sdmitry@lrn.ru in forum Ruby
    Replies: 0
    Last Post: August 14th, 03:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139