mysqlsqlyog

Where does MYSQL store logic for "UPDATE CURRENT_TIMESTAMP"

I usually would update a timestamp column from PHP but just realized this could be done at MySQL DBMS level. So I found and tested the code below through SQLYog which I use for DB admin:

 CREATE TABLE `TestLastUpdate` (
`ID` INT NULL,
`Name` VARCHAR(50) NULL,
`Address` VARCHAR(50) NULL,
`LastUpdate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
COMMENT='Last Update'
;

The table was created as expected and LastUpdate changes each time the field is updated.

I could not however find anything in the table info to show where this is stored (current_timestamp() as I know works for insert normally), neither was a trigger or event created by this code.

So where is this logic being stored? I suppose there are some other table attributes (asides foreign keys, indexes) which MySQL keeps but which SQLYog does not display?

Solution

  • You can see these attributes of a timestamp column in the INFORMATION_SCHEMA:

    mysql> create table test.mytable (id serial primary key, ts timestamp default current_timestamp on update current_timestamp);
mysql> select table_name, column_name, column_default, extra from information_schema.columns where table_name='mytable';
+------------+-------------+-------------------+-----------------------------+
| table_name | column_name | column_default    | extra                       |
+------------+-------------+-------------------+-----------------------------+
| mytable    | id          | NULL              | auto_increment              |
| mytable    | ts          | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+-------------+-------------------+-----------------------------+

    In MySQL prior to 8.0, all table metadata is in the .frm file corresponding to your table.

    $ ls -l /usr/local/var/mysql/test
total 256
-rw-r-----  1 bkarwin  admin      67 Jun 16 10:04 db.opt
-rw-r-----  1 bkarwin  admin    8582 Jul  1 07:05 mytable.frm
-rw-r-----  1 bkarwin  admin  114688 Jul  1 07:05 mytable.ibd

    Within the .frm file, there's a bit-field called unireg_check that stores attributes of columns. Some of the bits are:

    How are these bits used? That's buried deep in the MySQL source code. It's all special-case code.

    https://github.com/mysql/mysql-server/blob/5.7/sql/sql_table.cc#L7822-L7844

    /*
   Set CURRENT_TIMESTAMP as default/update value based on
   the unireg_check value.
*/

if ((def->sql_type == MYSQL_TYPE_DATETIME ||
     def->sql_type == MYSQL_TYPE_TIMESTAMP)
    && (def->unireg_check != Field::NONE))
{
  Item_func_now_local *now = new (thd->mem_root) Item_func_now_local(0);
  if (!now)
    DBUG_RETURN(true);

  if (def->unireg_check == Field::TIMESTAMP_DN_FIELD)
    default_value= now;
  else if (def->unireg_check == Field::TIMESTAMP_UN_FIELD)
    update_value= now;
  else if (def->unireg_check == Field::TIMESTAMP_DNUN_FIELD)
  {
    update_value= now;
    default_value= now;
  }
}

    Here's a post by former engineering director Stewart Smith describing this use of the .frm file: https://planet.mysql.com/entry/?id=17539

    You might be wondering, "what is unireg?" It's a database management system developed by Michael Widenius, who is the founder of MySQL. Unireg dates back to 1979 (see https://exadel.com/news/old-reliable-mysql-history/). The design of the .frm file borrows code from the old Unireg project, and according to Stewart's blog, there are even bits of the unireg_check bit-field that are unused in MySQL but were used by Unireg.

    In some ways, MySQL — like a lot of software — is like a skyscraper built on the foundations of a ruined Roman castle.