I need to find out the last update time (or last changed time) of a table in the database (i.e. the last time when data in a specific table is updated, deleted or inserted) for deciding whether a backup is needed or not. As per the MySQL/MariaDB manual, I have used the following SQL statement to get the last modified time of a table in the database:  SELECT update_time FROM information_schema.tables WHERE table_schema='my_db' and table_name='my_table'; -- For table named 'my_table' in database 'my_db'  Here's the problem: The value of the resultant update_time field is always returned NULL even though changes are made in the specified table. What's more unanticipated is that when I manually browse the tables table in the information_schema database, there are no entry of my_db and my_table in table_schema and table_name fields respectively, meaning that the activities are not logged. Can someone please help me solve this problem and explain me the reason behind it?  EDIT: This question is not a duplicate as the question also asks about the fields of information_schema.tables being NULL rather than just asking how to get last update time.
11639
In the footer of my page, I would like to add something like "last updated the xx/xx/200x" with this date being the last time a certain mySQL table has been updated.  What is the best way to do that?  Is there a function to retrieve the last updated date? Should I access to the database every time I need this value?
91888
When I do a single row INSERT to a table that has an AUTO_INCREMENT column I'd like to use the LAST_INSERT_ID() function to return the new AUTO_INCREMENT'ed value stored for that row.  As many Microsoft SQL Server devs and admins no doubt are aware the equivalent functionality in SQL Server (SCOPE_IDENTITY and @@IDENTITY) hasn't been .  I know the MySQL docs state:     The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENTvalue generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.     and even go so far as to say:     Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid.     Are there any known risks or scenarios that may cause LAST_INSERT_ID() not to return the correct value?  I'm using MySQL 5.5 on CentOS 5.5 x64 and Fedora 16 x64 and the InnoDB engine.
81999
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.
61064
Is it possible in a sqlite database to craete a table that has a timestamp column that default to DATETIME('now') ?  Like this:  CREATE TABLE test (     id INTEGER PRIMARY KEY AUTOINCREMENT,      t TIMESTAMP DEFAULT DATETIME('now') );   This gives an error... How to resolve?
60878
I've recently finished a project, during which many DB tables were created.   Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables.  Is there a way to list all DB tables sorted according to their creation date?
120364
I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47.  Query is  ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ;     While it is working fine on my local DB with mysql V5.6.56.
24768
I really want to know more about the update, export and the values that could be given to hibernate.hbm2ddl.auto I need to know when to use the update and when not? And what is the alternative?  These are changes that could happen over DB:   new tables new columns in old tables columns deleted data type of a column changed a type of a column changed its attributes tables dropped values of a column changed    In each case what is the best solution?
55154
I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry.  I use recent versions of PostgreSQL and MySQL. I have 2 tables:  CREATE TABLE authors (     id INT,     name VARCHAR )  CREATE TABLE posts (     id INT,     author_id INT,     text VARCHAR,     date DATE )   I need to select one most recent post for each author. Thanks!  UPDATE  Thanks, both links provide answers to my question with some exception. All the following queries give the same result (which one is the most efficient btw?) The issue is when there is more than one post from the same author with the same date. Then the returned result set contains all such posts. How should I modify these queries to return exactly one post per author?  SELECT p1.* FROM posts p1 LEFT JOIN posts p2 ON p1.author_id = p2.author_id AND p1.date &lt; p2.date WHERE p2.author_id IS NULL ORDER BY p1.author_id;  SELECT p1.*  FROM posts p1 INNER JOIN (   SELECT author_id, MAX(date) AS max_date   FROM posts   GROUP BY author_id) p2   ON p1.author_id = p2.author_id AND p1.date = p2.max_date ORDER BY p1.author_id;  SELECT * FROM posts p1 WHERE date = (SELECT MAX(p2.date)               FROM posts p2               WHERE p1.author_id = p2.author_id) ORDER BY author_id;  SELECT * FROM (     SELECT author_id, MAX(date) date     FROM posts GROUP BY author_id ) p1 INNER JOIN posts p2 USING (author_id, date) ORDER BY author_id;
83143
When I am looking to create some timestamp fields (or other date/time style fields), what is the best way to name them? Should I just put record_timestamp?
31856
I've removed the poll style answers in favour of open discussion.  I realise now this isn't the best way to have a proper discussion about this issue - sorry to anyone who commented/voted.    This is mostly a followup on this question:    As I've answered, the reason this is happening is that the root database (in the production Stack Exchange database server) is still called StackExchange.Programmers - a database name that propagates to SEDE and through it to the data dump.  The question here is what to do about it - the current situation is somewhat broken for users of the data dump.  These are the options we have:   Do nothing Update the root database name (requires downtime - so very undesirable) Update the update scripts so the SEDE database name is the new name, change will flow to the data dump export (breaks any queries using the old name) Update the export to the data dump so only it gets the new name Something else we haven't thought about   I have added all of these options as answers below - please vote on the one you most prefer, we will try to accommodate the top voted answer.
60004
Is it possible to convert a timestamp to a date format in a query?  This is the code I am using.  $query = db_select('my_submissions', 's'); $query-&gt;fields('s', array('sid','name','company','email','phone','is_customer','submitted','count')); $entries = $query-&gt;execute();  $rows = array(); foreach ($entries as $entry) {   $row = array_map('check_plain', (array) $entry);   $row['submitted'] = $row['submitted'] ? date('d.m.Y H:i', $row['submitted']) : "";   $rows[] = $row; }
96046
I am having a few issues when people are trying to access a MySQL database and they are trying to update tables with the same information.  I have a webpage written using PHP. In this webpage is a query to check if certain data has been entered into the database. If the data hasn't, then i proceed to insert it. The trouble is that if two people try at the same time, the check might say the data has not been entered yet but when the insert takes place it has been by the other person.  What is the best way to handle this scenario? Can i lock the database to only process my queries first then anothers?
87668
The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL?  When I connect to a fresh installation I see two databases: mysql and information_schema. After using SHOW CREATE TABLE statements on the information_schema database, it looks like it's not implemented as a set of views but with base tables instead. Is this assumption correct? Or there are other system tables that are hidden from the user?
123831
Is there a module that lists which tables are created, owned or modified by a particular module?  I need something that can show me what tables are created or modified by when new modules are installed.
108532
Every time I try to create a table using MySQL Workbench 6.3 CE it adds "broken" comments at the end of the script.  Example:  I am trying to create a table called "user". This is what the table should look like:   However when I click the apply button, the script generates incorrectly:  CREATE TABLE `user` (   `userid` INT NOT NULL AUTO_INCREMENT COMMENT '',   `record_status` INT NOT NULL DEFAULT 1 COMMENT '',   `createdby` VARCHAR(75) NOT NULL COMMENT '',   `createddate` DATETIME NOT NULL COMMENT '',   `updatedby` VARCHAR(75) NULL COMMENT '',   `updateddate` DATETIME NULL COMMENT '',   `username` INT NOT NULL COMMENT '',   `password` VARCHAR(75) NULL COMMENT '',   `firstname` VARCHAR(75) NULL COMMENT '',   `lastname` VARCHAR(75) NULL COMMENT '',   `email` VARCHAR(75) NULL COMMENT '',   `facebook_userid` VARCHAR(75) NULL COMMENT '',   `email_verified` VARCHAR(75) NULL COMMENT '',   PRIMARY KEY (`userid`, `username`)  COMMENT '');   The comment in this line should not be there, so the code should be:   PRIMARY KEY (`userid`, `username`));   and the code executes when I paste it into an editor and remove this comment. However I create massive databases daily. (Each with +- 15 tables). I can't keep doing this for each table as it is time consuming.  How can I disable this feature that adds the comments to the script? I don't need them in my databases
45136
A post here on DBA.StackExchange () has spawned an interesting question (at least, interesting to me) regarding performance in MySQL.  The context is that we want to insert a record in a table for each row that is updated.  Before the row is updated, we want to store off a previous value and then increment one of the columns (a "version" column).  If we do this inside a trigger, it works out nicely.  For MySQL, the triggers are , so it would be an easy solution.  Select the data currently in the table, insert it into the logging table, and the update the "version" column in the new data.  However, it's possible to move this logic to a stored procedure. If you do that, you're performing the insert, then incrementing the "version" column in the table.  The entire thing would be set based.   So, when it comes to performing this insert, would this be more performant to use the set-based stored procedure approach or a trigger based approach?  This question is for MySQL (since it has row-by-row triggers), although it could apply to other row-by-row trigger DBMSs.
69478
I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its own. For example:   Who last executed stored procedure dbo.MyProcedure? Who updated the salary column in the dbo.Employees table? Who last queried the dbo.Orders table from Management Studio?   But there are several other events that SQL Server does track temporarily by default, and can natively answer questions about, such as:   When was the last time an auto-grow happened in the AdventureWorks database, and how long did it take? Who deleted the dbo.EmployeeAuditData table and when? How many memory-related errors have happened today?   How do I get this information, and how long does it stay available?