
[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": {  // <1>
      "id": 1004,
      "first_name": "Anne",
      "last_name": "Kretchmar",
      "email": "annek@noanswer.org"
    },
    "after": {  // <2>
      "id": 1004,
      "first_name": "Anne Marie",
      "last_name": "Kretchmar",
      "email": "annek@noanswer.org"
    },
    "source": {  // <3>
      "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",  // <4>
    "ts_ms": 1486501486308,  // <5>
    "ts_us": 1486501486308910,  // <5>
    "ts_ns": 1486501486308910814  // <5>
  }
}
----
.Descriptions of fields in the payload of an `update` event value
[cols="1,7",options="header",subs="+attributes"]
|===
|Item |Description

|1
|The `before` field shows the values present in the row before the database commit.
The original `first_name` value is `Anne`.

|2
|The `after` field shows the state of the row after the change event.
The `first_name` value is now `Anne Marie`.

|3
|The `source` field structure has many of the same values as before,
except that the `ts_sec` and `pos` fields have changed
(the `file` might have changed in other circumstances).

|4
|The `op` field value is now `u`, signifying that this row changed because of an update.

|5
|The `ts_ms`, `ts_us`, `ts_ns` field shows a timestamp that indicates when {prodname} processed this event.

|===

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.
--
