Post by SapporoGuy » Mon Jan 03, 2011 4:37 am

X,
Is PDO really crap? (serious question)

I agree that it would be a nightmare since the entire cart would have to be re-done.

What do you think of the INNO db issue?

@ inno db vs my isam
hmmm ... interesting read.
http://drupal.org/node/103402

As of 5.5 mysql is inno db......

looks like a mixture would be best but then shared hosting is still a problem it seems.

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by ekerazha » Mon Jan 03, 2011 4:43 am

SapporoGuy wrote: Is PDO really crap? (serious question)

I agree that it would be a nightmare since the entire cart would have to be re-done.
PDO would be the way to go, but it would require a major overhaul.

New member

Posts

Joined
Tue Nov 10, 2009 10:13 pm
Location - Italy

Post by mystifier » Mon Jan 03, 2011 4:44 am

I initially tried to use opencart with MSSQL when I saw mmsql.php but it quickly became obvious that it would be far from easy.

SQL dialects are different at even the most basic level; for example, SQL-SERVER does not support `tablename` and needs the equivolent [tablename].

There are sometimes very valid reasons for using database transactions to rollback before commital in the event of system failure. Except for maybe on the 'merchant service side', I would not intuitively apply them to a shopping cart though. They can cause more problems than they solve and are no substitute for programmatic error handling to maintain integrity.

Free v1.4.9 Extensions: Default Specials | Improved Search | Customer Activity Report | Customer Groups | Royal Mail With Handling | Improved Product Page | Random Products | Stock Report | All Products


User avatar
Active Member

Posts

Joined
Tue May 18, 2010 5:15 pm

Post by Qphoria » Mon Jan 03, 2011 4:49 am

SapporoGuy wrote: @ inno db vs my isam
hmmm ... interesting read.
http://drupal.org/node/103402
Seems the row locking vs table locking is useful for a popular site that gets a lot of blog posts at the same time.. Not sure that particular feature has any real value for a store.
MyISAM is very fast and excellent for (mostly) read-only tables
InnoDB has row level locking and should be used on tables with frequent writes.
Shopping Carts are mostly read-only so myisam FTW :)

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by ekerazha » Mon Jan 03, 2011 5:03 am

mystifier wrote: There are sometimes very valid reasons for using database transactions to rollback before commital in the event of system failure. Except for maybe on the 'merchant service side', I would not intuitively apply them to a shopping cart though. They can cause more problems than they solve and are no substitute for programmatic error handling to maintain integrity.
"Programmatic error handling" isn't a substitute for transactions.

E.G.
When you accomplish X, you have to update 2 entries and you use 2 queries.

$this->db->query($QUERY_1);
$this->db->query($QUERY_2);

What if you have a system failure between them?

$this->db->query($QUERY_1)
--- SYSTEM FAILURE ---

Your database loses consistency, because you updated an entry but your didn't update the second entry.

This is why you should use transactions to keep integrity.

New member

Posts

Joined
Tue Nov 10, 2009 10:13 pm
Location - Italy

Post by Qphoria » Mon Jan 03, 2011 5:08 am

ekerazha wrote:
mystifier wrote: There are sometimes very valid reasons for using database transactions to rollback before commital in the event of system failure. Except for maybe on the 'merchant service side', I would not intuitively apply them to a shopping cart though. They can cause more problems than they solve and are no substitute for programmatic error handling to maintain integrity.
"Programmatic error handling" isn't a substitute for transactions.

E.G.
When you accomplish X, you have to update 2 entries and you use 2 queries.

$this->db->query($QUERY_1);
$this->db->query($QUERY_2);

What if you have a system failure between them?

$this->db->query($QUERY_1)
--- SYSTEM FAILURE ---

Your database loses consistency, because you updated an entry but your didn't update the second entry.

This is why you should use transactions to keep integrity.
Yea I get it.. it lets you group multiple events into a single "atomic" event. But read vs write seems to be a bigger performance hit and for shopping carts I think performance is a bit more key than potential for errors.

Technically programmatic error handling IS a substitute for innodb transactions because we can test for errors after the first query and then halt or fire off a delete from. Granted we currently just "die" on a db failure but as ugly as that is, it still handles the situation above.. if there was a failure on the first, the second would never fire.

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by ekerazha » Mon Jan 03, 2011 5:24 am

Qphoria wrote: Yea I get it.. it lets you group multiple events into a single "atomic" event. But read vs write seems to be a bigger performance hit and for shopping carts I think performance is a bit more key than potential for errors.
Well, I don't have a deep knowledge of OpenCart code, but in my mind I'd be worried about things like:

sell();
--- SYSTEM FAILURE---
subtract_from_stock();

or

sell();
--- SYSTEM FAILURE ---
payment_confirmation();

;D
Qphoria wrote: Technically programmatic error handling IS a substitute for innodb transactions because we can test for errors after the first query and then halt or fire off a delete from. Granted we currently just "die" on a db failure but as ugly as that is, it still handles the situation above.. if there was a failure on the first, the second would never fire.
What if you have a system failure before the test? Your PHP code isn't "atomic".

You could try something like this http://www.deepbluesky.com/blog/-/myisa ... ctions_20/ but I don't know if it's really reliable, also I think InnoDB transactions are a much more elegant solution.

New member

Posts

Joined
Tue Nov 10, 2009 10:13 pm
Location - Italy

Post by Qphoria » Mon Jan 03, 2011 5:36 am

Fair enough.. it is possible I suppose but typically all db updates like that are grouped together within the code. For that particular feature

It does:

UPDATE order table with new order
INSERT record into order history
UPDATE product stock

back to back

So yes the UPDATE could fire off and then the code fail on the INSERT or lightning strike before the UPDATE Stock occurs. But weighing the pros and cons there is more likely that performance on a daily basis is more important than the 1 in 17million chance of a system failure right at a particular moment between updates. Even if it were to happen.. the end damage of having one stock item mismatch isn't exactly catastrophic or beyond recovery.

There are pros and cons to many methods and at this point I think there is more pros in myISAM along with not reinventing the database.

How does one clarify a transaction anyway? Is there a start and end flag like:

db('START_TRANSACTION'");
db("UPDATE....");
db("UPDATE....");
db("INSERT....");
db('END_TRANSACTION'");
?

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by ekerazha » Mon Jan 03, 2011 6:05 am

Qphoria wrote: But weighing the pros and cons there is more likely that performance on a daily basis is more important than the 1 in 17million chance of a system failure right at a particular moment between updates.
Well, InnoDB isn't that slow. It could be a bit slower but it's not really noticeable unless it's very heavily loaded. But if it's heavily loaded, it's a big site and you have many users, I think integrity should be the highest priority.
Qphoria wrote: Even if it were to happen.. the end damage of having one stock item mismatch isn't exactly catastrophic or beyond recovery.
Tell this to the user who bought that last item >:D
Also... it's "one" if you have only a few customers, but if you have many users, it could break something for multiple users and in different places.
Qphoria wrote: How does one clarify a transaction anyway? Is there a start and end flag like:

db('START_TRANSACTION'");
db("UPDATE....");
db("UPDATE....");
db("INSERT....");
db('END_TRANSACTION'");
?
Yes...

Instead of "END_TRANSACTION'" you have COMMIT and you can also ROLLBACK

New member

Posts

Joined
Tue Nov 10, 2009 10:13 pm
Location - Italy

Post by Xsecrets » Mon Jan 03, 2011 6:24 am

actually this whole discussion is rather mute as I can basically guarantee that the database will not be moved to a transactional database for 1.5

If it ever happens it would be at least 2.0 as it would be a MAJOR change in the coding, so if it is that important to you then you should go to another cart as opencart won't have it any time soon.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by Qphoria » Mon Jan 03, 2011 7:25 am

Xsecrets wrote:actually this whole discussion is rather mute
I believe you mean "moot"

But yes it is true... it won't be happening anytime soon and still .. even with a lot of customers.. it is mostly read only. Only when an order is actually placed is there any real writing done to the DB, and any improvement there can be hidden by payment gateway delay.. so I can't see innodb being good for any shopping cart platform as far as performance goes.

A customer who had a failure can certainly contact the store owner and some quick research can be done to fix it so the one benefit of the atomic commits doesn't quite match the benefits of the read speed.. not that I've tested... either way.. not likely to ever happen.

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by Xsecrets » Mon Jan 03, 2011 7:30 am

you are correct I guess I was just wishing it was mute. LOL

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by ekerazha » Mon Jan 03, 2011 1:53 pm

Qphoria wrote: But yes it is true... it won't be happening anytime soon and still .. even with a lot of customers.. it is mostly read only. Only when an order is actually placed is there any real writing done to the DB, and any improvement there can be hidden by payment gateway delay.. so I can't see innodb being good for any shopping cart platform as far as performance goes.

A customer who had a failure can certainly contact the store owner and some quick research can be done to fix it so the one benefit of the atomic commits doesn't quite match the benefits of the read speed.. not that I've tested... either way.. not likely to ever happen.
I still think reliability is much more important than a mostly negligible performance improvement. Also, If you want more performances, improve your hardware or hosting plan, don't sacrifice the reliability of your system. I wouldn't talk about "quick research" if you have consistency issue for multiple users on multiple places. I hope this point is clear, independently from the fact that v1.5 won't use InnoDB.

New member

Posts

Joined
Tue Nov 10, 2009 10:13 pm
Location - Italy

Post by Qphoria » Mon Jan 03, 2011 2:41 pm

I don't disagree.. but reliability failure in that scenario is so unlikely that its almost of no consequence. A forum seems the place for an innodb.. a popular blog even... but a cart has 1 major write to the database and the first write is actually the order insertion, the rest is order history, stock levels, etc.. so there is nothing for the customer to lose, only a little more maintenance for the store owner.. and a little stock trouble.

There's more worry about multiple asynchronous writes on the backend than the front, so it could be arguably useful there.. But in years of working with carts I can't think of one time I've seen or heard about a partial update unless the actual subsequent query code was bad and it bombed out in which case it is expected to be a partial job. Perhaps in the case of testing new queries it would be useful.

There may be other benefits of innodb, but solely for reliability.. it seems like a fix for an issue nobody has

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by ekerazha » Mon Jan 03, 2011 4:18 pm

Well, I think the opposite :) If I lose some forum messages... that's not a big issue... but for a shop, reliability is critical because money is directly involved.
For that matter, also note that InnoDB is now the default engine for the newer versions of MySQL.

New member

Posts

Joined
Tue Nov 10, 2009 10:13 pm
Location - Italy

Post by SapporoGuy » Mon Jan 03, 2011 4:23 pm

I was wondering about that
Read that mysql was going default on the 5.5 and newer.

I wonder what is going to happen ... ::)

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by ekerazha » Mon Jan 03, 2011 5:06 pm

Also, I think you shouldn't underestimate consequences of this kind of issues. From a shop, you can order 1 item, but you can also order 50 or 100 items. A stock difference of 50 or 100 items is a huge difference. I should pay somebody to recount goods, I waste money, I waste time and time is money. It's an inconvenience for me and for my customers. You can be branded as "unreliable" and lose customers ecc. ecc. And stock counting is just one of the possible issues.

New member

Posts

Joined
Tue Nov 10, 2009 10:13 pm
Location - Italy

Post by bajaber » Mon Jan 03, 2011 6:58 pm

If stock levels are so critical to the extent that you make it sound, then you will never be satisfied. Transactions alone will not be enough. You have staff stealing from your store. Which is more important? Numbers not tallying on your system, and not even losing a cent (maybe just a few minutes), or actual physical product missing from your store (losing money)? If you have time to sit and review the cameras, am sure you would have time to make the correct adjustment of the stock in Admin panel. Besides, I don't think the main purpose of OC is stock management.

If you are selling 50 to 100 items per order, you most probably will need to keep 1000's of stock. And you should be having a trigger amount to replenish the stock. Also, it would be a poor business practice not to have a buffer stock (i.e just putting the actual amount in Admin). What I do is make sure I only put 90% of my actual stock, so I still have an additional 10% for that (as of yet never happened) time when I get a surge in orders.

As Q pointed out, you have to think of the pros and cons.
Think of the little fish that are using OC and who cannot support transactions.
Think of the transaction logs that are regularly larger than databases themselves (not sure if this is the case in MySQL, but it's very common in MSSQL which I have been working with).
Think of the time it will waste of this awesome team to redo and support transactions instead of adding more awesome features!

New member

Posts

Joined
Mon Dec 27, 2010 9:08 pm

Post by ekerazha » Mon Jan 03, 2011 7:18 pm

bajaber wrote:If stock levels are so critical to the extent that you make it sound, then you will never be satisfied. Transactions alone will not be enough. You have staff stealing from your store. Which is more important? Numbers not tallying on your system, and not even losing a cent (maybe just a few minutes), or actual physical product missing from your store (losing money)? If you have time to sit and review the cameras, am sure you would have time to make the correct adjustment of the stock in Admin panel. Besides, I don't think the main purpose of OC is stock management.

If you are selling 50 to 100 items per order, you most probably will need to keep 1000's of stock. And you should be having a trigger amount to replenish the stock. Also, it would be a poor business practice not to have a buffer stock (i.e just putting the actual amount in Admin). What I do is make sure I only put 90% of my actual stock, so I still have an additional 10% for that (as of yet never happened) time when I get a surge in orders.
They are both important, the possibility of staff stealing from the store doesn't justify an unreliable information system. It's like saying "somebody could kill me, so let's have a walk in the middle of a freeway".
Also, an order of 50 or 100 items could be an exceptional order, there's no need to always have "1000's of stock".
Also, stock count isn't the only place where you can have coherency issues.
As Q pointed out, you have to think of the pros and cons.
Think of the little fish that are using OC and who cannot support transactions.
Think of the transaction logs that are regularly larger than databases themselves (not sure if this is the case in MySQL, but it's very common in MSSQL which I have been working with).
Think of the time it will waste of this awesome team to redo and support transactions instead of adding more awesome features!
Transactions work if the are supported, that "little fish" isn't a problem.
The most awesome feature would be reliability.

New member

Posts

Joined
Tue Nov 10, 2009 10:13 pm
Location - Italy

Post by bajaber » Mon Jan 03, 2011 8:11 pm

ekerazha wrote: They are both important, the possibility of staff stealing from the store doesn't justify an unreliable information system.
The possibility of a 1 in a million chance of the error happening does not justify the time and effort to redevelop the system
ekerazha wrote: Transactions work if the are supported, that "little fish" isn't a problem.
Exactly. Keyword, IF.
ekerazha wrote: The most awesome feature would be reliability.
We already have reliability. Do you know of anyone who has had problems because of (lack of) transactions in OC?

New member

Posts

Joined
Mon Dec 27, 2010 9:08 pm
Who is online

Users browsing this forum: No registered users and 2 guests