List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:September 29 2010 9:13pm
Subject:INSERT DELAYED and created_on timestamps
View as plain text  
I'm doing some reading on INSERT DELAYED
http://dev.mysql.com/doc/refman/5.0/en/insert.html

I have a user_log table:

CREATE TABLE `user_log` (
  `id_user_log` bigint(20) unsigned NOT NULL auto_increment,
  `id_user` int(10) unsigned default '0',
  `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
  `type`
enum('View','Action','Admin','Search','Login','Logout','Access','General','
API') NULL,
  `source` enum('web','mobile') character set latin1 collate
latin1_general_ci default 'web',
  `body` text character set latin1 collate latin1_general_ci,
) ENGINE=InnoDB

We are noticing a lot of these in the logs however:

Sep 29 03:05:02 pse05 mysqld[14640]: TRANSACTION 0 62715480, ACTIVE 0 sec,
process no 14639, OS thread id 2904791952 inserting
Sep 29 03:05:02 pse05 mysqld[14640]: mysql tables in use 1, locked 1
Sep 29 03:05:02 pse05 mysqld[14640]: LOCK WAIT 4 lock struct(s), heap size
320, undo log entries 1
Sep 29 03:05:02 pse05 mysqld[14640]: MySQL thread id 8330, query id 799424
10.10.10.46 OMT_Master update
Sep 29 03:05:02 pse05 mysqld[14640]: INSERT INTO user_log (`id_user`,
`type`, `source`, `body`) VALUES ...)
Sep 29 03:05:02 pse05 mysqld[14640]: *** (1) WAITING FOR THIS LOCK TO BE
GRANTED:

So I'm thinking we could use the DELAYED or LOW_PRIORITY.

My concern is the created_on time.

Is there any difference in the actual timestamp recorded in the database if
I use:

INSERT INTO user_log (id_user) VALUES (3);

INSERT DELAYED INTO user_log (id_user) VALUES (3);

INSERT LOW_PRIORITY INTO user_log (id_user) VALUES (3);

INSERT INTO user_log (id_user, created_on) VALUES (3, NOW());

INSERT DELAYED INTO user_log (id_user, created_on) VALUES (3, NOW());

(or set the date via PHP):

'INSERT INTO user_log (id_user, created_on) VALUES (3, '.gmdate().')';

'INSERT DELAYED INTO user_log (id_user, created_on) VALUES (3,
'.gmdate().')';

My point is, is mySQL smart enough to know what the time WAS when the
INSERT was supposed to be written by default, or if I DELAY it will it
process the NOW() at INSERT time or DELAYED time or what time is NOW() and
lastly if I set it with gmdate() in PHP, then that seems like it's the
exact server time at the right moment??

Thread
INSERT DELAYED and created_on timestampsDaevid Vincent29 Sep
  • Re: INSERT DELAYED and created_on timestampsDan Nelson29 Sep
    • RE: INSERT DELAYED and created_on timestampsDaevid Vincent29 Sep