February 01, 2015, 08:24:22 PM *
Welcome, Guest. Please login or register.

Login with username, password and session length
News: Reminder: No political discussions in the public fora.  If you do not have access to the private Politics Forum, please send a PM to Fr. George.
   Home   Help Calendar Contact Treasury Tags Login Register  
Pages: 1   Go Down
Author Topic: WHEW!  (Read 1320 times) Average Rating: 0
0 Members and 1 Guest are viewing this topic.
Offline Offline

Posts: 2,442

« on: January 08, 2003, 10:10:21 AM »

First ever database corruption! Had to do a rollback on the database. The disadvantages of using MySQL, no transaction support!

Anyway, let me know if there are any errors, it should be all fixed.

Sorry for the slight downtime.

the slave
intolerant of intolerance
High Elder
Offline Offline

Faith: Greek Catholic
Jurisdiction: UGCC
Posts: 810

« Reply #1 on: January 08, 2003, 10:32:47 AM »

What ?  When??  Have I missed something ?? Huh

OK here at 10.30am and again at 2.20pm today

"Never let anyone try to tell you that, in order to be Orthodox, you must also be eastern. The West was fully Orthodox for a thousand years; and her venerable liturgy is far older than any of her heresies."
- St. John Maximovitch
Offline Offline

Posts: 2,442

« Reply #2 on: January 08, 2003, 10:46:15 AM »

Twas pretty early, around 8:30 or so.

It's pretty freakish, it usually occurs when two or more people try to write to the same row on a table at the same time. Transaction support and row-level locking are features that most modern DB systems have(Oracle, Postgres, MicrosoftSQL).

Row-level locking basically locks the row that a particular user is writing to, while that row is locked only one user can write to it, but others can't view it until the user is finished.

Modern DBs use Transaction Isolation Levels. (TIL)

Transaction isolation levels dictate how visible a transaction's intermediate product (the state of the data undergoing change before it is committed to the database) is to a second process that also needs to access the resources undergoing a change. The changes result from the traditional CRUD actions (Create, Read, Update, and Delete).
The four transaction isolation levels are:

Read Uncommitted Isolation Level - The benefit of Read Uncommitted is that it offers maximum concurrency; transactions don't have to wait or be blocked because no shared locks or exclusive locks are given. Under the Read Uncommitted isolation level, a second process can see data before the transaction that changes the data is complete. This is called a dirty read. Another possible con is the lost update: a change made by a first process is changed by a second one.
Read Committed Isolation Level - Read Committed guarantees that the data viewed by a transaction is "real." This level holds shared locks, which prevents dirty reads, but a writing transaction could cause either an unrepeatable read or a phantom record.
Repeatable Read Isolation Level - Repeatable Read locks the data viewed to guarantee its stability throughout the entire transaction. A second transaction can add new inserts to the set, however. If the process needs to revisit the data, it will see the same data it did the first time, but it could see additional, "phantom" data as well.
Read Serializable Isolation Level - In the Read Serializable level, the set is guaranteed to be free of phantoms because locks are held throughout the transaction. The set under consideration cannot have any updates, inserts, or deletes that weren't there the first time. The tradeoff for serialized transactions is it has the lowest concurrency. No changes can happen to the data until it is finished.
By serializing the power to change data, the state of the data is guaranteed to be consistent (i.e., stable for each modification made). In general, the four levels illustrate the trade off in transaction isolation: maximum concurrency comes at the price of data integrity.

OrthodoxChristianity.net uses MySQL, which isn't a real database, as it doesn't meat full ACID/CRUD specifications. As such we don't have all the protections that an enterprise level database management system would have.

OC.net guru
Offline Offline

Faith: Orthodox Christian
Jurisdiction: OCA (Diocese of the South)
Posts: 1,952

Retired GM

« Reply #3 on: January 09, 2003, 01:14:38 AM »

Well, Bobby, you know these problems would have never occured had you used Microsoft SQL Server.

Just kidding.  0p3// 50ur<3 r00lz!!!111  Smiley

"When looking at faults, use a mirror, not a telescope."
-Yazid Ibrahim
Offline Offline

Posts: 2,442

« Reply #4 on: January 09, 2003, 08:53:24 AM »



I have it running on the machine I'm on now. Its running in VMWare!


INSERT INTO UserAgentSummary                   -- update UserAgentSummary table
SELECT UAYear = MAX(DATEPART(year, EventDateTime)),
       UAWeek = MAX(DATEPART(week, EventDateTime)),
       UAText = MAX(UserAgent),
       UACount = COUNT(UserAgent),
       UAHostIP = MAX(HostIP)
FROM Sessions
WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0
      AND UserAgent IS NOT NULL                -- ignore any records with no
      AND UserAgent <> ''                      -- value for UserAgent
GROUP BY UserAgent,
         DATEPART(year, EventDateTime),
         DATEPART(week, EventDateTime),
IF (@@ERROR <> 0) GOTO on_error                -- something went wrong

INSERT INTO RefererSummary                     -- update RefererSummary table
SELECT RefYear = MAX(DATEPART(year, EventDateTime)),
       RefWeek = MAX(DATEPART(week, EventDateTime)),
       /* Use a CASE statement to remove any query string from the URL */
         CASE WHEN CHARINDEX('?', Referer) > 5
         THEN SUBSTRING(Referer, 1, CHARINDEX('?', Referer) - 1)
         ELSE Referer
       RefHostIP = MAX(HostIP)
Tags: OC.net Notices 
Pages: 1   Go Up
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.18 | SMF © 2013, Simple Machines Valid XHTML 1.0! Valid CSS!
Page created in 0.042 seconds with 31 queries.