Mysql – My MySQL server time is not the same as the Server

The cleanest method I can think of is to examine only one table : mysql.user

If you can login to mysql at all, run this query:

SELECT COUNT(1) column_count FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

Here are the answers you should get:

• If you get 43, MySQL 5.6
• If you get 42, MySQL 5.5
• If you get 39, MySQL 5.1
• If you get 37, MySQL 5.0

If you cannot login to mysql, copy the table

• user.frm
• user.MYD
• user.MYI

to another machine running mysql.

On that other machine run CREATE DATABASE junk;

Go to the datadir in that other mysql server

Change directory to junk

Copy the three files for the user table into it. If the other machine is a Linux box, remember to run chown mysql:mysql user.*

Login to MySQL on that other machine and run

DESC junk.user;

It will tell the same numbers I mentioned before.

However, if you get 28 that's MySQL 4.1. If you get 17, that's MySQL 4.0. Have fun with that one.

Give it a Try !!!

You may need to create the counter table with 250 slots to spread the row locks.

First, here is the table:

CREATE TABLE HourCounter
(
    hr INT,
    slot TINYINT,
    cnt INT UNSIGNED,
    PRIMARY KEY (hr,slot)
) ENGINE=InnoDB;

All you need to do is insert into the random slots like this:

SET @NumberOfSlots = 250;
SET @X = UNIX_TIMESTAMP();
SET @uthr = @X - MOD(@X,3600);
INSERT INTO HourCounter (hr,slot,cnt)
    VALUES (@uthr,RAND() * @NumberOfSlots + 1,1)
    ON DUPLICATE KEY UPDATE cnt = cnt + 1
;

If you want to collect the counts, run this:

SELECT hr,SUM(cnt) HourCount
FROM HourCounter GROUP BY hr;

If you want to collapse the slots into a single number, do this once a week:

UPDATE HourCounter c
    INNER JOIN
    (
        SELECT hr,SUM(cnt) cnt,MIN(slot) mslot
        FROM HourCounter GROUP BY hr
    ) x USING (hr)
SET c.cnt  = IF(c.slot = x.mslot, x.cnt, 0),
    c.slot = IF(c.slot = x.mslot, 0, c.slot);
DELETE FROM HourCounter WHERE slot <> 0 AND cnt = 0;

To reclaim all the disk space for this table, do this once a month:

ALTER TABLE HourCounter ENGINE=InnoDB;
ANALYZE TABLE HourCounter;

Give it a Try !!!

To give credit where is credit is due, I got the answer from page 140 of the Book

I have the book and refer to it well.