2

I am dealing with a legacy application that is using MariaDB to emulate a queue. One of the key things missing is that the original design doesn't insert the time the messages in the queue were inserted meaning that the order the messages are processed is not guaranteed.

So far the messages appear to be processed in order as we're only using a single MariaDB instance but I would like to add a created_on column to ensure this continues.

My question is that I need to backfill the created_on column and i was wondering if MariaDB stored the time a given row was inserted into the database?

I realise that unless it is in the schema it is unlikely but occasionally databases will have non-standard extensions that capture this sort of thing. Oracle for example has similar functionality to this.

ahjmorton
  • 965
  • 1
  • 5
  • 18
  • 1
    http://stackoverflow.com/questions/4870536/mysql-automatically-store-record-creation-timestamp – user3741598 Mar 26 '15 at 19:07
  • "I realise that unless it is in the schema it is unlikely but occasionally databases will have non-standard extensions that capture this sort of thing" I am well aware I can create a column that will capture the timestamp, my question was if MariaDB captured this itself. – ahjmorton Apr 01 '15 at 08:47

2 Answers2

4

MariaDB does not have a hidden timestamp. If the table has an AUTO_INCREMENT, that might suffice since you are asking for order, not specifically time.

My opinion of queuing via MySQL/MariaDB: "Don't queue it, just do it". The effort of queuing and dequeuing can become a burden, especially in end cases.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Agree that we shouldn't be using MariaDB as a queue as we're essentially recreating things you get from a standard MQ implementation in the database. Unfortunately we can't make a sweeping architecture change at the current point in the code case. – ahjmorton Mar 31 '15 at 16:49
  • Accepting this answer as it actually answers my question. Thanks – ahjmorton Apr 01 '15 at 08:44
-2

Yes you can, if you were to create a field make sure when you create the field you have the following: 

create table test_created_on_table( 
  created_on timestamp default now() on update now() 
);

If you already have a field just take off the "CURRENT_TIMESTAMP" flag on the created field. Whenever you create a new record in the table, just use "NOW()" for a value.

Or.

On the contrary, remove the 'ON UPDATE CURRENT_TIMESTAMP' flag and send the NOW() for that field. That way actually makes more sense.

This would track when row is inserted or updated.

There's another way of doing it by db trigger:

Adding a ModifiedTime

Adding a modified timestamp to a table is the most straight forward. All your have to do is create the field of type TIMESTAMP, and by default, MySQL will automatically update the field when the row is modified.

There are a couple of things to be aware of:

  • While you can have multiple TIMESTAMP fields in a row, only one of these can be automatically updated with the current time on update.
  • If your UPDATE query contains a value for your ModifiedTime field, this value will be used.

So, to add your modified timestamp field to an existing table, all you need is:

ALTER TABLE my_table ADD ModifiedTime TIMESTAMP;

Adding a CreatedTime

Adding a CreateTime value is a little more involved.

On the latest versions of MySQL it is apparently possible to create a DateTime field with a default value of CURRENT_TIMESTAMP. This wasn’t an option for me as I was having to support a somewhat older version, besides, even on the newer versions of MySQL it is not possible to have more than one field using CURRENT_TIMESTAMP, which of course we are in order to get ModifiedTime working.

So, in order to get a created timestamp, firstly we must add a DATETIME field to the table.

ALTER TABLE my_table ADD CreatedTime datetime NOT NULL;

Note, that this must be created as NOT NULL in order for the next part to work (this is because setting NOT NULL forces an automatic all zeros default).

Next, we must create a trigger, which will automatically be fired when we insert a value into our table and set the created timestamp.

DELIMITER //
DROP TRIGGER IF EXISTS my_table_insert_trigger//
CREATE TRIGGER my_table_insert_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
IF NEW.CreatedTime = '0000-00-00 00:00:00' THEN
SET NEW.CreatedTime = NOW();
END IF;
END;//
DELIMITER ;

Now, when you insert a value into the table, this trigger will fire and, if you’ve not provided a CreatedTime field in your insert query, it will be set to the current time stamp.

unixmiah
  • 3,081
  • 1
  • 12
  • 26
  • So this is what we're going to do to clean it up. My question was whether or not there existed a way to just say when a given row was inserted. – ahjmorton Mar 31 '15 at 16:48
  • No, it does not. nor does mysql posgres or ms sql. You have to keep track of your own row insertion. Otherwise, you'll need to create a script to back full all those dates. Oracle may have this feature, it's a lot bigger, expensive and enterprise. – unixmiah Mar 31 '15 at 17:55