0

I'm fetching countries, cities, and frequently_asked_question from the tables in the server and I store the data locally in the Android app, Look at my scenarios in the Android app:

Old Scenario: When the user opens the app I delete all local data and fetch them again from the server regardless of whether there was any update in the server or the same local data who deleted.

Current Scenario: When the user opens the app I send these variables countries_update_time, cities_update_time, and frequently_asked_question_update_time and compare them inside the PHP file, If the variable who I sent it was the same with the UPDATE_TIME in the information_schema.tables, I'll return a null value, But if the variable was not the same with the UPDATE_TIME, I'll return the data from the specific table.

Everything is working well but I found a weird problem inside the MySQL database, Look at the image below:

enter image description here

I'm sure the third table had a date yesterday but today I found the date converted to a null value, Why?

Taha Sami
  • 1,565
  • 1
  • 16
  • 43
  • Can you add some code? – Dokik Jan 31 '22 at 07:49
  • @Dokik The problem has no any related to code – Taha Sami Jan 31 '22 at 07:50
  • 1
    `UPDATE_TIME` usage is incorrect itself: https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html ***UPDATE_TIME** When the data file was last updated. For some storage engines, this value is NULL. For example, InnoDB stores multiple tables in its system tablespace and the data file timestamp does not apply. Even with file-per-table mode with each InnoDB table in a separate .ibd file, change buffering can delay the write to the data file, so the file modification time is different from the time of the last insert, update, or delete.* `SELECT MAX(updated_at) FROM tablename`. – Akina Jan 31 '22 at 07:56
  • @Akina `Database client version: libmysql - 5.6.43`. Is this version has any related to my problem? – Taha Sami Jan 31 '22 at 08:02
  • *Is this version has any related to my problem?* I doubt. The library can't be imaginative - server tells, library returns. – Akina Jan 31 '22 at 08:04
  • @Akina What is the best alternative to apply the current scenario without using `information_schema.tables`? – Taha Sami Jan 31 '22 at 08:08
  • ? I have provided the query pattern... does autoutilized `updated_at` column is present in the structure(s)? if not then add... alternatively you may create service table where the datetime for last tables update times should be stored (refresh by triggers). – Akina Jan 31 '22 at 08:09
  • I used your query like this `SELECT TABLE_NAME, MAX(UPDATE_TIME) FROM information_schema.tables` Here is the result https://ibb.co/9V2yf8x – Taha Sami Jan 31 '22 at 08:14
  • @Akina I forgot to mention you in my previous reply – Taha Sami Jan 31 '22 at 08:25
  • ??? you must query your working tables (cities, countries, etc.), not information_schema. – Akina Jan 31 '22 at 08:55
  • @Akina But `UPDATE_TIME` exist inside `information_schema.tables` – Taha Sami Jan 31 '22 at 08:58
  • 1
    *does autoutilized updated_at column is present in the structure(s)? if not then add* – Akina Jan 31 '22 at 09:28
  • @Akina This problem will drive me to craziness, I think I'll apply [this scenario](https://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records) instead of the current scenario, Thanks for your time. – Taha Sami Jan 31 '22 at 09:37

0 Answers0