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.
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!
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.
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
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.
Shopping Carts are mostly read-only so myisam FTWMyISAM is very fast and excellent for (mostly) read-only tables
InnoDB has row level locking and should be used on tables with frequent writes.
"Programmatic error handling" isn't a substitute for transactions.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.
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.ekerazha wrote:"Programmatic error handling" isn't a substitute for transactions.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.
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.
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.
Well, I don't have a deep knowledge of OpenCart code, but in my mind I'd be worried about things like: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.
sell();
--- SYSTEM FAILURE---
subtract_from_stock();
or
sell();
--- SYSTEM FAILURE ---
payment_confirmation();
What if you have a system failure before the test? Your PHP code isn't "atomic".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.
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.
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'");
?
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'");
?
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: 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.
Tell this to the user who bought that last itemQphoria wrote: Even if it were to happen.. the end damage of having one stock item mismatch isn't exactly catastrophic or beyond recovery.
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.
Yes...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'");
?
Instead of "END_TRANSACTION'" you have COMMIT and you can also ROLLBACK
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.
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
I believe you mean "moot"Xsecrets wrote:actually this whole discussion is rather mute
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.
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
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.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 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
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
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.
For that matter, also note that InnoDB is now the default engine for the newer versions of MySQL.
I was wondering about that
Read that mysql was going default on the 5.5 and newer.
I wonder what is going to happen ...
Read that mysql was going default on the 5.5 and newer.
I wonder what is going to happen ...
930sc ... because it is fun!
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.
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!
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!
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".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.
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.
Transactions work if the are supported, that "little fish" isn't a problem.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!
The most awesome feature would be reliability.
The possibility of a 1 in a million chance of the error happening does not justify the time and effort to redevelop the systemekerazha wrote: They are both important, the possibility of staff stealing from the store doesn't justify an unreliable information system.
Exactly. Keyword, IF.ekerazha wrote: Transactions work if the are supported, that "little fish" isn't a problem.
We already have reliability. Do you know of anyone who has had problems because of (lack of) transactions in OC?ekerazha wrote: The most awesome feature would be reliability.
Who is online
Users browsing this forum: No registered users and 2 guests