
[id="updating-database-viewing-update-event"]
= Updating the database and viewing the _update_ event

Now that you have seen how the {prodname} MySQL connector captured the _create_ events in the `inventory` database,
you will now change one of the records and see how the connector captures it.

By completing this procedure, you will learn how to find details about what changed in a database commit,
and how you can compare change events to determine when the change occurred in relation to other changes.

.Procedure

. In the terminal that is running the MySQL command line client, run the following statement:
+
--
[source,sql,options="nowrap"]
----
mysql> UPDATE customers SET first_name='Anne Marie' WHERE id=1004;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
----
--

. View the updated `customers` table:
+
--
[source,sql,options="nowrap"]
----
mysql> SELECT * FROM customers;
+------+------------+-----------+-----------------------+
| id   | first_name | last_name | email                 |
+------+------------+-----------+-----------------------+
| 1001 | Sally      | Thomas    | sally.thomas@acme.com |
| 1002 | George     | Bailey    | gbailey@foobar.com    |
| 1003 | Edward     | Walker    | ed@walker.com         |
| 1004 | Anne Marie | Kretchmar | annek@noanswer.org    |
+------+------------+-----------+-----------------------+
4 rows in set (0.00 sec)
----
--

ifdef::community[]
. Switch to the terminal running `watch-topic` to see a _new_ fifth event.
endif::community[]
ifdef::product[]
. Switch to the terminal running `kafka-console-consumer` to see a _new_ fifth event.
endif::product[]
+
--
By changing a record in the `customers` table, the {prodname} MySQL connector generated a new event.
You should see two new JSON documents: one for the event's _key_,
and one for the new event's _value_.

Here are the details of the _key_ for the _update_ event (formatted for readability):

[source,json,options="nowrap"]
----
  {
    "schema": {
      "type": "struct",
      "name": "dbserver1.inventory.customers.Key",
      "optional": false,
      "fields": [
        {
          "field": "id",
          "type": "int32",
          "optional": false
        }
      ]
    },
    "payload": {
      "id": 1004
    }
  }
----

This _key_ is the same as the _key_ for the previous events.

Here is that new event's _value_.
There are no changes in the `schema` section, so only the `payload` section is shown (formatted for readability):

[source,json,options="nowrap",subs="+attributes"]
----
{
  "schema": {...},
  "payload": {
    "before": {
      "id": 1004,
      "first_name": "Anne",
      "last_name": "Kretchmar",
      "email": "annek@noanswer.org"
    },
    "after": {
      "id": 1004,
      "first_name": "Anne Marie",
      "last_name": "Kretchmar",
      "email": "annek@noanswer.org"
    },
    "source": {
      "name": "{debezium-version}",
      "name": "dbserver1",
      "server_id": 223344,
      "ts_sec": 1486501486,
      "gtid": null,
      "file": "mysql-bin.000003",
      "pos": 364,
      "row": 0,
      "snapshot": null,
      "thread": 3,
      "db": "inventory",
      "table": "customers"
    },
    "op": "u",
    "ts_ms": 1486501486308,
    "ts_us": 1486501486308910,
    "ts_ns": 1486501486308910814
  }
}
----

The following list describes select fields in the preceding example of the payload of an `update` event value:

`before`::
The `before` field shows the values that were present in the row before the update.
The original `first_name` value is `Anne`.

`after`::
The `after` field shows the state of the row after the `update` event.
The `first_name` value is now `Anne Marie`.

`source`::
The `source` field structure has many of the same values as before, except that the `ts_sec` and `pos` fields have changed.
In some circumstances, the `file` value might also change.

`op`::
Shows the type of operation that the event describes.
The `u` value indicates that change was due to an `update` event.

`ts_ms`, `ts_us`, `ts_ns`::
Displays timestamps in milliseconds, microseconds, and nanoseconds that indicate when the connector processed the event.
The time is based on the system clock in the JVM that runs the Kafka Connect task.

By viewing the `payload` section, you can learn several important things about the _update_ event:

* By comparing the `before` and `after` structures, you can determine what actually changed in the affected row because of the commit.
* By reviewing the `source` structure, you can find information about MySQL's record of the change (providing traceability).
* By comparing the `payload` section of an event to other events in the same topic (or a different topic), you can determine whether the event occurred before, after, or as part of the same MySQL commit as another event.
--
