Bugs item #2825243, was opened at 2009-07-22 09:01
Message generated for change (Settings changed) made by nielsnes
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2825243&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Core
Group: None
>Status: Closed
Resolution: Fixed
Priority: 9
Private: No
Submitted By: heangu (heangu)
Assigned to: Niels Nes (nielsnes)
Summary: SQL: Data lost when restart
Initial Comment:
I opened two sessions at the same time, use session one to insert data,
session two execute START TRANSACTION but without COMMIT. When restart, I found
the data just inserted was lost!! MY test case is as follows:
1. Start the server.
2. Open one session. mclient -lsql -dtest. (Denote it as S1)
3. Open the second session as step 2. (Denote it as S2)
4. IN S1:
CREATE TABLE t(a int);
CREATE TABLE x(a int);
INSERT INTO t VALUES(1);
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
5. IN S2:
START TRANSACTION;
6. IN S1:
INSERT INTO x VALUES(1);
INSERT INTO x VALUES(2);
7. IN S2:
COMMIT;
UPDATE t SET a = 8;
8. WAIT a few minutes.
9. KILL server and start it.
10. Open session S1. (mclient -lsql -dtest)
11. IN S1:
SELECT * FROM x;
Here is the problem. I should have got two lines (1 and 2) in STEP 11, but in
fact I only got an empty set.
Where has my data gone?
It can't be other person who deleted it. And I can have it occur many times.
----------------------------------------------------------------------
Comment By: heangu (heangu)
Date: 2009-08-15 10:41
Message:
Ok. I've tested it. The bug has been fixed.
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-08-14 12:09
Message:
tagged subject
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-08-11 11:53
Message:
heangu,
could you possibly test whether the latest Aug2009 release candidate of
MonetDB --- available from CVS as Aug2009 branches or as nightly built
super source ball from
http://monetdb.cwi.nl/testing/projects/monetdb/Stable/.DailyBuilds./0_latest/SourceSuperBall/
--- fixes your problem?
Thanks!
Stefan
----------------------------------------------------------------------
Comment By: Niels Nes (nielsnes)
Date: 2009-08-06 19:20
Message:
fixed by first aplying all the ibat inserts on to the central bats, before
a logger_restart.
Not closing jet as we need a test for this
----------------------------------------------------------------------
Comment By: Niels Nes (nielsnes)
Date: 2009-08-03 21:21
Message:
indeed the non empty ibats is a problem.
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-07-30 05:34
Message:
heangu,
could you please share your patch with us,
e.g., by attaching it to this bug report?
Thanks!
Stefan
----------------------------------------------------------------------
Comment By: heangu (heangu)
Date: 2009-07-30 05:22
Message:
I can fix it by adding some code in store_manager to prevent it from
syncing data to disk if any IBATs in gtrans is not empty. Thus kept the log
in disk to let it recover from it on next startup.
----------------------------------------------------------------------
Comment By: Matthew W. Jones (matburt)
Date: 2009-07-29 14:03
Message:
Adding gdk_single_user=yes to my configuration file fixed my database
corruption issue.
----------------------------------------------------------------------
Comment By: Matthew W. Jones (matburt)
Date: 2009-07-27 14:19
Message:
"test_table" and "raw_plays" are the same in this case, I did not do a good
enough job cleaning up the names before posting here.
----------------------------------------------------------------------
Comment By: Matthew W. Jones (matburt)
Date: 2009-07-27 14:15
Message:
I am experiencing widespread data corruption when restarting the
May2009-SP1 MonetDB release also, but mine is much simpler.
On 64-bit ubuntu:
Linux dsvm01 2.6.24-23-server #1 SMP Wed Apr 1 22:14:30 UTC 2009 x86_64
GNU/Linux
With a table defined as follows:
CREATE TABLE "tablespace"."test_table" (
"col1" varchar(50),
"col2" varchar(50),
"col3" varchar(50),
"col4" varchar(50),
"col5" varchar(50),
"col6" int,
"col7" timestamp(7),
"col8" int,
"col9" int,
"col10" int
);
I imported 20998687 records into this table with all columns populated and
ran a couple of queries to check the data:
select count(*) from raw_plays ;
[ 20998687 ]
select count(*) from raw_plays where col1 = '' or col2 = '' or col3 = '' ;
[ 0 ]
select max(col7), min(col7) from test_table ;
[ 2009-07-25 14:17:15.538000, 2009-07-24 15:29:12.524000 ]
During the time I loaded the data and ran these queries I did not restart
the database, it had been up for about 3 days I then issued:
monetdb stop db
and
monetdb start db
and re-ran the queries:
select count(*) from raw_plays ;
[ 20998687 ]
select count(*) from raw_plays where col1 = '' or col2 = '' or col3 = '' ;
[ 20852061 ]
select max(col7), min(col7) from test_table ;
[ 2009-07-25 14:17:15.538000, -1-01-01 00:00:00.000000 ]
This is some serious data corruption, I've lost data in the vast majority
of these records!
I wasn't sure if this is the same issue that is seen here, if need-be I
can open a new bug report.
----------------------------------------------------------------------
Comment By: heangu (heangu)
Date: 2009-07-23 03:38
Message:
I think the bug 2819966 is possible because of this data lost. This bug is
found while trying to reproduce the bug 2819966 that I reported a few days
ago.
In this case, if I execute an update on x before restarting server, older
versions of MONET will complain ASSERT failed in void_inplace. But new
versions of MONET seems OK but data lost.
----------------------------------------------------------------------
Comment By: Fabian (mr-meltdown)
Date: 2009-07-22 09:09
Message:
I can reproduce the problem
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2825243&group_id=56967
------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
