Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    May 2009
    Posts
    41

    Unanswered: Chage detection in MySQL.

    Hi,

    I am trying to build a application in which I can be able to flow data to MySQL and vice -versa.

    I need to understand that how the update, any changes done in MySQL database will be known or immediately flown to other side?

    What is the Change detection mechanism in MySQL?

    What I am trying to understood is :
    If I add some entry in Database it has immediately flown to other side.

    Can you please shed some light on this?

  2. #2
    Join Date
    May 2009
    Posts
    41
    In more details:

    Is there any API, command for MySQL which will return the changes between given time period
    Ex. suppose from 10.11 am to 14.00 pm

    Or something like If given any range
    From last change number 100 to onwords all changes.

  3. #3
    Join Date
    May 2009
    Posts
    41
    Any takers Plz.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by amitbora27
    Any takers Plz.
    It is very unclear what you want but I'll make a stab in dark:

    If each of your tables in MySQL has a field to hold when the record was last updated then you could simply query this field to get the data you want. Of course you'd have to set this field every time you do an insert or an update. Here's a simple example of updating a record and then finding out what the last 100 records to be updated:

    Code:
    update MyTable
    set   dataFields = 'Some new Data',
          lastupdated = now()
    where keyField = 123
    
    select * from MyTable 
    order by lastUpdated desc 
    limit 100
    Deletes might be an issue as the record is deleted and you won't be able to see that this record has changed ie it's been deleted. If this is an issue to you then you'll need to add a flag to each table to indicate whether the record has been "deleted" - then rather than deleting records you could just set this flag.

    You could use triggers on all the tables to automatically set the update time and delete flag. If you don't want to change any existing tables then you could just create a new log table that records the table and id of each record that is changed (ie inserted, updated or deleted) but this would also probably need to be done via triggers.

    Mike

  5. #5
    Join Date
    May 2009
    Posts
    41
    Hi Mike,

    Thanks for the valuable information.

    I was just going through the triggers use for my work. And I think it is the best option to use trigger while update on tables, So we can keep the all information.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    mysql supports the use of a timestamp field, which will automatically be set when the row is first added or on subsequent changes to the row.

    however all that monitors is that something changed. you could optionally note the user id of the person and on which computer but that would require some coding in your user interface.

    so if a row was changed more than once all you know is the time of the last change, you don't know what was changed. to know what was changed you would need to consider implementing some form of audit log, that would allow you to track changes over time.

    you can handle the delete issue by flagging a record for delete, carry the delete flag for a period of time and then finally purge the record after a set period of time. naturally you wouldn't normally then include rows flagged for delete in your normal operations
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2009
    Posts
    41
    Correct me if wrong - If we consider timestamp field, User must have to create tables with timestamp field? Isn't it?

    Or timestamp filed is default in MySQL?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by amitbora27
    Correct me if wrong - If we consider timestamp field, User must have to create tables with timestamp field? Isn't it?

    Or timestamp filed is default in MySQL?
    no timestamp is an inherent MySQL datatype, you do not refernece the column in update or insert queries the time is taken form the server time.

    setting a column to datatype timestamp makes that column record the date/time of the last change you do not need to perform any action to record the timestamp (doing so may result in an error, can't remember offhand).

    if you want to record the userid and or computerid that requires the user interface to find thise details and add them to the query.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2009
    Posts
    41
    ok, timestamp is inherent MySQLdatatype.
    What I mean to ask is while creating any table let say :

    CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL, id INT NOT NULL);

    INSERT INTO t1 VALUES (NOW(), '1');
    Or
    INSERT INTO t1 VALUES ( '1');

    Which is correct insert statement and how we get timestamp of inserted row?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are using a timestamp datatype in a table you do not reference that column in any insert or update statements, the time is completed by the MySQL server using the time taken from that server

    you can retrieve the values in a select statement

    der manual is alwasy a good place to start with such questions
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    May 2009
    Posts
    41
    Can you please tell me that If I want to use the latest version of MySQL with trigger facility then which will be better 5.0 or 6.0 or in between?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    new question new thread please
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    May 2009
    Posts
    41
    Hi,

    I have seen the IDENTITY property in MSSQL used like:
    CREATE TABLE test(idencol INT IDENTITY(1,1), dupcol INT NOT NULL);


    I need to create a same query in MySQL. I saw that there is AUTO_INCREMENT in MySQL. But not sure it is the one which I can use instead if identity.

    Can you please confirm me or suggest me if I am wrong.
    What can I used instead of IDENTITY?
    Amit

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by healdem
    new question new thread please
    I guess you're going to have to be less subtle

  15. #15
    Join Date
    May 2009
    Posts
    41
    Error is :
    -> BEGIN
    -> DECLARE done INT DEFAULT 0;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near '' at
    line 3

Posting Permissions

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