0

Before 4 months I tried to know how can I get the last update for a specific table, So I did it using this command

SELECT UPDATE_TIME
FROM information_schema.tables
WHERE TABLE_NAME = 'specific table'

But most of the time the result is null, I don't know why this problem happening.

The type of the tables is InnoDB.

After that I switched to another solution, I created UPDATE_TIME_OF_TABLES table and it looks like this

TABLE_NAME UPDATE_TIME
... ...
... ...
... ...
... ...
... ...

I created 3 triggers for every table (When insert, update, and delete) that triggers will update the value of the UPDATE_TIME column this method was working perfectly with me but suddenly I discovered this method does not work with cascaded foreign key as mentioned in this answer so now I should change a lot of code in my project after 4 months because the project was depending on this solution.

What can I do now, What is the third solution?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Taha Sami
  • 1,565
  • 1
  • 16
  • 43
  • write triggers instead of cascading? As there is no other solution, i simpy don't understand , why you are asking – nbk Apr 28 '22 at 07:47
  • @nbk Could you look at the question and answer as I mentioned in the link above, I'm facing the same problem. – Taha Sami Apr 28 '22 at 07:50
  • if you created a table and didn't perform any Insert/Update then the UPDATE_TIME is null . – Roshan Nuvvula Apr 28 '22 at 07:50
  • 1
    There is no third method. Your triggers should be aware if the modification of a table would cascade a modification down through FKs and update your stat table for that table as well. Btw, mysql manual on information_schema.tables does explain why update_time field does not work for innodb tables. https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html – Shadow Apr 28 '22 at 07:54
  • @Roshan Are you talking about `information_schema.tables`? if yes I know that and I performed actions such as insert, delete, and update and that updated the column inside the `information_schema.tables`, But after a specific time the result will be back to null automatically – Taha Sami Apr 28 '22 at 07:55
  • @Shadow How my triggers should be aware if the modification of a table? The problem is that cascaded foreign key actions do not activate triggers. – Taha Sami Apr 28 '22 at 08:03
  • @Shadow I saw the link you mentioned above and I think the point is here, `Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.` I know why the problem happening now but I want a solution, for example, can I change `InnoDB` to another type? – Taha Sami Apr 28 '22 at 08:13
  • 1
    possibly the same - https://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated – Roshan Nuvvula Apr 28 '22 at 08:14
  • 1
    @Iuq281 the link provided shows that the issue isn't resolved in mysql. you can like i did, tell the oracle bug report that you face the same problem, like i did already, so take the solutions there given and replace the cascade with triggers – nbk Apr 28 '22 at 08:57
  • @nbk Okay, I think I'll use your provided solution temporarily, But is it better to delete children before or after deleting the parent? Could you give me also the report link to tell them I'm facing the same problem? – Taha Sami Apr 28 '22 at 09:09
  • 1
    @Iuq281 the report link is in the first answer comment, but it is open since 2005 so no hoe there. you need first to delete the children and then the parent – nbk Apr 28 '22 at 10:11
  • @Iuq281 the triggers on the table that cascade a change down the FK must be aware that a change would end up in a cascaded modification. – Shadow Apr 28 '22 at 17:21

0 Answers0