So I have the following insert statement that I run through MySQL Workbench:

INSERT INTO PeakOnlineCount
(time_stamp, LocationId, Count)
('2015-03-26', 0, 1)

If I run the following query:

SELECT * FROM PeakOnlineCount

straight after in the same tab, I can see the results. If I disconnect, reconnect and run the select query the table seems empty.

If I run the insert statement on one computer and run the select from another, the table is empty.

I'm not using transactions or anything like that. What's going on?

(And yes I know that I'm using SELECT *, this is only for the question)

Here's the structure of the table:

CREATE TABLE `PeakOnlineCount` (
    `time_stamp` date NOT NULL,
    `LocationId` int(11) NOT NULL,
    `Count` int(11) NOT NULL,
    KEY `IX_LocationDate` (`time_stamp`,`LocationId`)

I'm not using auto-commit and according to http://dev.mysql.com/doc/refman/5.1/en/commit.html:

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back.

I certainly haven't turned off auto-commit.

I have run SHOW TABLES and I've copied and pasted the table name from that. Also, I'm running all the queries in the same window so should be connected to the same schema in all queries.

There are no triggers on the table. I try to avoid triggers as much as possible.

Ran show variables, show session variables, and show global variables. All say that autocommit is turned on.

I am using workbench, but I have a colleague using something else (sequel pro on a mac) who runs the insert query and then I've run the select query and got nothing back.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.