0

OS: W10.

This question is specifically about what's possible in MariaDB, specially version "Ver 10.16 Distrib 10.2.11-MariaDB".

Edit
In light of the answer by Rick James I have now upgraded to "Ver 15.1 Distrib 10.7.3-MariaDB".

I want to implement a way of having a "last modified" timestamp for a database, i.e. which shows when the last INSERT or DELETE or UPDATE occurred, regardless of the table where it happened.

Use case (due to comment below): I have a recurrent sys admin task running. Every 10 minutes it checks whether databases need dumping using mysqldump, for backup purposes, to a directory which gets routinely snapshotted/backed up (using restic). At the moment this recurrent script checks whether any of the following MariaDB files have been updated: ibdata1, ib_logfile0, ib_logfile1. If the most recent last-modified date of these 3 is more recent than datetime (incorporated into the name) of the last .sql dumpfile it does a new dump. Otherwise not. I keep the last 5 such .sql dumpfiles. I don't want a dump to happen unless some data has changed. The problem with this arrangement is that it doesn't show which database(s) need dumping, so I dump all the non-system databases. I'm looking for a more fine-grained arrangement.

I found this, which looked promising... but then some comments there about InnoDB and information_schema.tables.update_time led me to do an experiment.

Contrary to what is asserted in some places about update_time always being NULL in InnoDB tables, this does not seem to be the case with what I've got. Unfortunately, the comment there about this value being set to NULL whenever the server starts does appear to be true.

Elsewhere I read that newer versions of MariaDB, pulling away from vanilla MySQL, do in fact implement some sort of history of changes.

I'm currently working on the assumption that I shall have to implement AFTER UPDATE, AFTER INSERT and AFTER DELETE triggers for every table, and then get values for each of these and find the most recent, in order to determine the "last modified" time for the whole database.

Is there any other possibility with this version of MariaDB?

8
  • I would use a trigger. You can read how Oracle uses Sequences to manage autoincrement ids. Apr 14, 2022 at 16:37
  • Thanks. But why would reading about that help with this question? Also note that this is about MariaDB, which is not owned by Oracle (although much of the functionality presumably still dates back to pre-fork days). Apr 14, 2022 at 17:39
  • Is the snapshot via LVM? If not, it may not be complete nor consistent.
    – Rick James
    Apr 15, 2022 at 6:46
  • @RickJames This question is beyond my level of expertise. Are you familiar with restic? I've been using it for about 10 months now and do a check (i.e. a restore of a random snapshot) every week. Maybe this is a question for restic? I just do a dump of the database to a .sql file and it gets backed up/snapshotted as appropriate. Apr 15, 2022 at 6:50
  • Sounds like the dump is "mysqldump"? Consider Percona's or MariaDB's or even Oracle's replacement for it. No, I don't know restic.
    – Rick James
    Apr 15, 2022 at 6:55

4 Answers 4

1

There is no database-wide (or system-wide) "last modified timestamp" for InnoDB in either MySQL or MariaDB.

At the other end of the spectrum, there are Temporal tables in MariaDB. This allows you to keep a history of all changes.

3
  • Thanks, very helpful, that seems to be the thing. From MariaDB version 10.3.4, which is fine, I can upgrade. A first glimpse at that page seems to indicate that implementing a "system-version table" should indeed provide what I'm after... now to read properly. Apr 14, 2022 at 19:06
  • 1
    Keep in mind that temporal tables aren't really for what you want; since they keep every version of every modified row ever your database will grow much faster than it does today. I'd choose a different approach if I were you. But then again, I probably wouldn't need the "last modified" time for the whole database in the first place -- I cannot imagine a scenario where it would be useful.
    – mustaccio
    Apr 14, 2022 at 20:43
  • Adding use case details. Apr 15, 2022 at 6:15
0

Replication would allow you to "continually" have a "Replica" with the entire set of databases up to date.

You can, if you desire, do whatever is desired to back up the Replica.

This technique would not involve stopping the Primary server at all.

Furthermore, the binlog (on the Primary) and the relay-log (on the Replica actually give you an indication of whether any changes have been made -- by looking at the "position" of the replication stream in either of those files.

What is the purpose of the backup? If it is an ordinary backup; please use an existing technique, such as I outline here, not a new technique that is being invented on the fly in this forum.

LVM as a technique for taking a "snapshot" on a single server. It is so fast that some of your design thoughts may be unnecessary. (It uses "copy-on-write" and has some low level help from the OS.)

2
  • Thanks. Lot to process here. I'm trying to keep things simple as far as possible. This is not a situation of giant "industrial scale" databases... Also at the moment I don't have a problem with having to stop the server. The problem alluded to there is that whenever the server does in fact start (reboot, etc.) all values in information_schema.tables.update_time get set to NULL. Apr 15, 2022 at 7:24
  • @mikerodent - Yeah. Unfortunately, MySQL has not made storing the change time a priority. The old Engine=MyISAM had it, but Engine=InnoDB does not. It seems like MariaDB was thinking about, I but I don't have any specifics. Another point: To store the date would [probably] cost an extra disk operation, and that might be a serious performance hit.
    – Rick James
    Apr 15, 2022 at 17:26
0

Yet another approach... (Not centralized, but somewhat "automated" and "simple"):

Add these to every table:

`last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX(last_modified)

Then have a script that checks this on each table:

SELECT MAX(last_modified) FROM ...

When it finds a "new" timestamp, dump the table.

Caution: If two tables are updated "at the same time", you might dump one table without dumping the other. So, you really need to do some kind of locking or shutdown that assures that sets of tables that were updated together are dumped together.

0

I think I may have found an answer for my purposes. As explained, the use case is a recurrent script to check whether a dump (e.g. using mysqldump) of one or more database(s) is needed, for backup purposes.

I was considering the following:

  • make a new table called "dbase_last_modified" with one column, "last_modified" of type DATETIME, and one row of data.
  • putting 3 triggers on each table in each database: AFTER INSERT, AFTER DELETE and AFTER UPDATE. Each trigger then updates dbase_last_modified.last_modified with CURRENT_TIMESTAMP

But there is a simpler solution, I think:

  • make a new table called "dbase_last_modified" with one column, "last_modified" of type DATETIME, and one row of data.
  • each run of the recurrent script, i.e. every 10 minutes, check the values of information_schema.tables.update_time for all tables in each user database. As mentioned, the problem with this is that these are set to NULL when the server starts. But each time a non-NULL value is found, update dbase_last_modified.last_modified to the most recent such value found (for all the tables in that database).

This then obviously makes this a persistent value in the database. The attraction of using information_schema.tables.update_time is that this gets updated automatically by the system by INSERT, DELETE and UDPATE: no need for many manually created triggers.

PS I wondered about putting a trigger on information_schema.tables.update_time, but this is a complete non-starter: the only access to information_schema is read-only: you can't put a trigger on it, you can't change its privileges in any way, and in fact people say that this is a VIEW, not a table.

Opinions from experts?

I can in fact think of a problem with this: what if some changes are made in a table and the server then goes down before the next run of the recurrent script? When the server is restarted there will be no record of any change having occurred.

A simple workaround solution to that is to do a mysqldump for every user database, each time the MariaDB service is started. Not ideal but acceptable.

One final niggle
Haha. Timezones! MariaDB sets information_schema.tables.update_time using the local time. Where does it get the local time from? By default from the OS, which itself may or may not be setting the timezone automatically. The MariaDB policy is found here, which also explains how to obtain the setting and the zone. In order to make things as robust as feasible, the dump files' datetime value (incorporated into the name) should be recorded in UTC, with an indicator in the name that this is a UTC time, e.g.

"my_database_dump_2022-04-16_1928_UTC.sql"

This also means that the code of the utility has to convert information_schema.tables.update_time to UTC (on the basis of what MariaDB tells you about its current timezone) in order to make comparisons. The single column in dbase_last_modified should be called last_modified_UTC (NB MySQL/MariaDB DATETIME fields are always "timezone-naive", to use the Python way of putting it).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.